Special Columns Of Function-based Indexes

Many times we need to know which columns (in composite indexes) that make the index is function-based (BFI) instead of a normal index. If it is a simple index no mystery (index on only one column). In the case of multiple columns may be all, only one column or a few columns.

The more usual way, from what I’ve seen in my years of experience is to use the package DBMS_METADATA invoking the GET_DDL function, get the sql code and from there, make a study of it. The other less common way is by consulting the DBA_IND_EXPRESSIONS, as shown below;

 

 SELECT 
  COLUMN_POSITION,
  COLUMN_EXPRESSION    -- Long type
FROM  
   DBA_IND_EXPRESSIONS
WHERE INDEX_NAME = '<MY_BFI>'
/

 

 
HTH – Antonio NAVARRO

 

Advertisements

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