||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.
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.
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
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
for 30% off directly from the publisher.