Oracle Tips by Burleson

Function Based Indexes (FBI)

In the examples above, we have used filters on columns to limit the rows returned.  The indexes used those filters to locate the needed rows before accessing the table.  A problem arises when we filter on a column in a function.

select
  author_last_name
  author_contract_nbr
from
  author
where upper(author_last_name) = 'PETTY'; 

Even if you have an index on the author_last_name column, the database can not use it because you are filtering on upper(author_last_name). 

To use an index, you need to create a function based index (FBI).  A FBI is simply an index that uses the function so that the database can make direct comparisons between the index values and the filter values.

create index auth_last_nm_fbi on
  author (upper(author_last_name)); 

The FBI, auth_last_nm_fbi, can be used in any query to filter the rows on upper(author_last_name). The key to using FBIs is that the functions in the WHERE clause must exactly match the function in the FBI. 

Like materialized views, FBIs also require that you use the cost based optimizer and enable QUERY REWRITE.  Talk to your DBA concerning these parameters.

Get the Complete Oracle SQL Tuning Information  The landmark book "Advanced Oracle SQL Tuning  The Definitive Reference"  is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.