TR: Question of invisible column such as functiton index?
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 27 Sep 2005 15:31:25 -0700
Sorry, my last e-mail to the group seemed to be missing some text.
Here is the e-mail I meant to send.
segcol# retains its value for an unused column.
intcol# counts all columns including unused columns and columns created for a
function-based index.
see example below (Oracle 9.2)
In my example, the function-based index column has intcol# = 4, and the unused
column has
SQL> create table t (n1 number, d2 date, v3 varchar2 (30)) ;
Table créée.
SQL> create index i1 on t (trunc (d2, 'YYYYY')) ;
Index créé.
SQL> alter table t add (c4 char (5), r5 raw (10), t6 timestamp (9)) ;
Table modifiée.
SQL> alter table t set unused column r5 ;
Table modifiée.
SQL> create index i2 on t (c4) ;
Index créé.
SQL> select
2 b.name,
3 b.col# as column_id,
4 b.segcol# as segment_column_id,
5 b.intcol# as internal_column_id
6 from
7 user_objects a,
8 sys.col$ b
9 where
10 a.object_name = 'T'
11 and a.object_type = 'TABLE'
12 and a.object_id = b.obj#
13 order by
14 b.intcol# ;
NAME COLUMN_ID SEGMENT_COLUMN_ID INTERNAL_COLUMN_ID
------------------------------ --------- ----------------- ------------------
N1 1 1 1
D2 2 2 2
V3 3 3 3
SYS_NC00004$ 0 0 4
C4 4 4 5
SYS_C00006_05092714:41:28$ 0 5 6
T6 5 6 7
7 ligne(s) sélectionnée(s).
SQL> column object_name format a10
SQL> select
2 a.object_name,
3 b.col# as column_id,
4 b.pos# as column_position,
5 b.segcol# as segment_column_id,
6 b.intcol# as internal_column_id
7 from
8 user_objects a,
9 sys.icol$ b
10 where
11 a.object_name in ('I1', 'I2')
12 and a.object_type = 'INDEX'
13 and a.object_id = b.obj#
14 order by
15 a.object_name, b.intcol# ;
OBJECT_NAM COLUMN_ID COLUMN_POSITION SEGMENT_COLUMN_ID INTERNAL_COLUMN_ID
---------- --------- --------------- ----------------- ------------------
I1 0 1 0 4
I2 4 1 0 5
SQL>
________________________________
De : oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] De la
part de Lou Fangxin
As we know in sys.col$ table, the "COL#" column define the display order of
columns, while the "SEGCOL#" column define the storage order in segment, and
when we add a function index, there will be an invisible column, how ever the
COL# and SEGCOL# columns value are zero. Then in which order the table columns
stored? How about I add a new column to table after create the function index?
--
//www.freelists.org/webpage/oracle-l
Other related posts:
- » TR: Question of invisible column such as functiton index?
