Systems Engineering and RDBMS

Check for column expression of function based index in the schema

Posted by decipherinfosys on February 12, 2009

We have covered function based indexes (Oracle), computed columns (SQL Server) and generated columns (DB2 LUW) in our blog posts before. You can search for FBI or computed columns on our blog to find those posts.

In this blog post, we will show you a small query to check what column expressions are used in any function based index (FBI).  Let us create a table and then create a FBI on the table.

col1 VARCHAR2(10),
col2 VARCHAR2(10),
col3 NUMBER(9),
col4 DATE


It is very easy to find out the existence of FBI in the schema. We can query the index_type column in the user_indexes view. Index_type indicates whether an index is function based index or not. We can even filter by index type to see all the function based indexes in the schema.

SELECT index_name,index_Type
FROM user_indexes
WHERE index_name = ‘TEST_IND_1’;

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

If we query the user_ind_columns view, we don’t see columns on which the function is applied. Column name contains some system generated name which doesn’t tell us what columns were involved in the index and what functions were applied to them. Check the following query:

SELECT column_name,column_position
FROM user_ind_Columns
WHERE index_name = ‘TEST_IND_1’;

-----------        ---------------
COL1            1
SYS_NC00005$    2
SYS_NC00006$    3

So where can we find column expression for the FBI?  We can query the user_ind_expressions view to see it.  Following query reveals that:

SELECT column_expression, column_position
FROM user_ind_expressions
WHERE index_name = ‘TEST_IND_1’;

-----------------    ---------------
UPPER("COL2")    2
NVL("COL3",0)    3

As mentioned earlier, it contains values for only those columns on which the function is applied. It doesn’t contain all the records.  In our case, we have applied function on col2 and col3 and hence only two records are there but user_ind_columns view contain all the records by column position. We need to remember that for any SYS_XXX columns in user_ind_columns, we have to refer to user_ind_expressions view.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: