Function Based Index by Sameer Wadhwa

Hidden Column Created by Function Based Index

(Thanks to Daniel Harron to provide valuable information for this tip)

When you create a function based index, oracle creates a hidden column on a base table. Oracle creates this column as COL# as 0 and NAME as SYS_NC<Oracle Assign Number>$. More then one function based index on table also have the col# 0 but with different name.

Here I am demonstrating this by a simple test

Step1 : Connect as SYS to Grant Query Rewrite to scott

SQL> connect sys/oracle as sysdba

Connected.

SQL> grant query rewrite to scott;

Grant succeeded.

Step2: Connect as Scott to create a test table

SQL> connect scott/tiger

Connected.

SQL> create table FBITEST (EMPNO NUMBER,ENAME VARCHAR2(10),EMPSTATUS VARCHAR2(10));

SQL> create index FBITESTIND on FBITEST(InitCap(ENAME));

Index created.

Step 3: Connect as SYS to query the data dictionary

SQL> select col#,name from SYS.col$ where obj# in (select object_id from dba_objects where object_name='FBITEST' and owner='SCOTT') order by col#;

COL# NAME

---------- ------------------------------

0 SYS_NC00003$

1 EMPNO

2 ENAME

3 EMPSTATUS

COL# 0 and SYS_NC0003$ is created by index FBITESTIND

Let us create another function based index on SCOTT.FBITEST table and query the data dictionary

SQL> create index fbitestind02 on scott.fbitest(lower(empstatus));

Index created.

COL# NAME

---------- ------------------------------

0 SYS_NC00003$

0 SYS_NC00005$

1 EMPNO

2 ENAME

3 EMPSTATUS

Now Oracle created another hidden column with a col# and name as SYS_NC00005$

*** End of Tip

Author

Sameer Wadhwa