Learning is not a spectator sport

Quick tip on Function Based Indexes

1–2 minutes

For “normal” indexes, USER_IND_COLUMNS will contain the column name(s) for an index, but things (appear to) 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$

Don’t panic. 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")

Easy peasy

One response to “Quick tip on Function Based Indexes”

  1. Rajeshwaran Jeyabal Avatar
    Rajeshwaran Jeyabal

    Also the same info available in user_tab_cols.

    demo@ORA12C> create table t as select * from all_objects;

    Table created.

    demo@ORA12C> create index t_idx on t( owner, lower(object_type), upper(object_name) );

    Index created.

    demo@ORA12C> column column_name format a15
    demo@ORA12C> column data_default format a20
    demo@ORA12C> select column_name, column_position
    2 from user_ind_columns
    3 where table_name =’T’
    4 and index_name =’T_IDX’;

    COLUMN_NAME COLUMN_POSITION
    ————— —————
    OWNER 1
    SYS_NC00027$ 2
    SYS_NC00028$ 3

    demo@ORA12C> select column_name, data_default
    2 from user_tab_cols
    3 where table_name =’T’
    4 and column_name in (‘SYS_NC00027$’,’SYS_NC00028$’);

    COLUMN_NAME DATA_DEFAULT
    ————— ——————–
    SYS_NC00027$ LOWER(“OBJECT_TYPE”)
    SYS_NC00028$ UPPER(“OBJECT_NAME”)

    demo@ORA12C>

    Reply

Got some thoughts? Leave a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Trending