RE: Question of invisible column such as functiton index?

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Tue, 27 Sep 2005 15:24:18 -0700
Message-ID: <B5C5F99D765BB744B54FFDF35F60262109F87F31@irvmbxw02>

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] 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 Received on Tue Sep 27 2005 - 17:26:31 CDT