RE: Question of invisible column such as functiton index?

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
Envoyé : lundi, 26. septembre 2005 17:52
À : Oracle-L
Objet : Question of invisible column such as functiton index?


Hi all:
 
    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? 


--
http://www.freelists.org/webpage/oracle-l

Other related posts: