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.

CREATE TABLE test
(
col1 VARCHAR2(10),
col2 VARCHAR2(10),
col3 NUMBER(9),
col4 DATE
);

CREATE INDEX TEST_IND_1 ON TEST(col1,UPPER(col2),NVL(col3,0));

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’;

INDEX_NAME    INDEX_TYPE
----------    ----------
TEST_IND_1    FUNCTION-BASED NORMAL

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’;

COLUMN_NAME        COLUMN_POSITION
-----------        ---------------
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’;

COLUMN_EXPRESSION    COLUMN_POSITION
-----------------    ---------------
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:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

 
%d bloggers like this: