Where is my function ?

See my previous tip on how to enable function-based indexes. One thing I neglected to mention is that using function-based indexes requires some modifications to your standard scripts that report on the columns for an index.

For "normal" indexes, USER_IND_COLUMNS will contain the column name(s) for an index, but things go astray when looking at function-based indexes.

SQL> create table blah ( x varchar2(30));

Table created.

SQL> create index blah_ix on blah ( upper(x));

Index created.

SQL> select column_name from user_ind_columns
  2  where index_name = 'BLAH_IX'
  3  /

COLUMN_NAME
------------------------------
SYS_NC00002$

Luckily the good folks at Oracle, haven't left us out on a limb. Simply take a look at USER_IND_EXPRESSIONS to find the function that you used.

SQL> select column_expression from user_ind_expressions
  2  where index_name = 'BLAH_IX'
  3  /

COLUMN_EXPRESSION
--------------------------------------------------------------------------------
UPPER("X")