Function Based Indexes

(Tested on an Oracle 10 database.)

This post introduces function based indexes. A normal B-tree index allows you to access a table quickly using the full column value. A function based index, on the other hand, allows you to apply a function to a column then use the resultant value to access the table. First create a table:

SQL> CREATE TABLE ANDREWS_TABLE
  2  (FIRST_NAME VARCHAR2(10),
  3   DOB        DATE)
  4  /

Table created.

SQL>

Next add some data:

SQL> INSERT INTO ANDREWS_TABLE
  2  VALUES (
  3  'Fred',
  4  TO_DATE('10-MAY-1983','DD-MON-YYYY'))
  5  /

1 row created.

SQL>

Then create a function based index. This one will allow you to access the table using the year of birth in YYYY format.:

SQL> CREATE INDEX YEAR_BORN_INDEX
  2  ON ANDREWS_TABLE(TO_CHAR(DOB,'YYYY'))
  3  /

Index created.

SQL>

And a B-tree index for comparison:

SQL> CREATE INDEX FIRST_NAME_INDEX
  2  ON ANDREWS_TABLE(FIRST_NAME)
  3  /

Index created.

SQL>

USER_IND_COLUMNS shows which columns are indexed. Note that Oracle has added a pseudo column for the function based index:

SQL> SELECT INDEX_NAME, COLUMN_POSITION, COLUMN_NAME
  2  FROM USER_IND_COLUMNS
  3  WHERE TABLE_NAME = 'ANDREWS_TABLE'
  4  ORDER BY 1,2
  5  /

INDEX_NAME           COLUMN_POSITION COLUMN_NAME
-------------------- --------------- --------------------
FIRST_NAME_INDEX                   1 FIRST_NAME
YEAR_BORN_INDEX                    1 SYS_NC00003$

SQL>

You can identify function based indexes as they have
INDEX_TYPE = FUNCTION-BASED NORMAL and FUNCIDX_STATUS = ENABLED:

SQL> SELECT INDEX_NAME, INDEX_TYPE, FUNCIDX_STATUS
  2  FROM USER_INDEXES
  3  WHERE TABLE_NAME = 'ANDREWS_TABLE'
  4  /

INDEX_NAME           INDEX_TYPE                  FUNCIDX_STATUS
-------------------- --------------------------- --------------
FIRST_NAME_INDEX     NORMAL
YEAR_BORN_INDEX      FUNCTION-BASED NORMAL       ENABLED

SQL>

You cannot see the pseudo column used by the function based index when you describe the table:

SQL> DESC ANDREWS_TABLE
 Name                          Null?    Type
 ----------------------------- -------- --------------------
 FIRST_NAME                             VARCHAR2(10)
 DOB                                    DATE

SQL>

But you can see it in USER_TAB_COLS. The DATA_DEFAULT column shows how it is populated and the HIDDEN_COLUMN column shows that it is hidden:

SQL> SELECT TABLE_NAME, COLUMN_NAME,
  2  DATA_DEFAULT DERIVATION, HIDDEN_COLUMN HIDDEN
  3  FROM USER_TAB_COLS
  4  WHERE COLUMN_NAME = 'SYS_NC00003$'
  5  /

TABLE_NAME    COLUMN_NAME   DERIVATION             HIDDEN
------------- ------------- ---------------------- ------
ANDREWS_TABLE SYS_NC00003$  TO_CHAR("DOB",'YYYY')  YES

SQL>

And you can also select the contents of the pseudo column:

SQL> SELECT SYS_NC00003$ "Pseudo Column" FROM ANDREWS_TABLE
  2  /

Pseudo Column
-------------
1983

SQL>