Oracle function based indexes

Oracle Database Tips by Donald Burleson

One of the most important Oracle Silver Bullets is the function-based index. A function-based index allows you to match any WHERE clause in an SQL statement and remove unnecessary large-table full-table scans with super-fast index range scans.

- See here how to display function based indexes.

- Here are the steps for tuning SQL with function-based indexes.

From the Oracle documentation we see a method for computing histograms on function-based indexes:

You should gather new column statistics on a table after creating a function-based index, to allow Oracle to collect column statistics equivalent information for the expression. This is done by calling the statistics-gathering procedure with the METHOD_OPT argument set to FOR ALL HIDDEN COLUMNS.

Note: Once an FBI is created, you need to create CBO statistics, but beware that there are numerous bugs and issues when analyzing a function-based index. See these important notes on statistics and function-based indexes.

The benefits of using a function-based index includes:

function-based indexes are easy to create
An function-based index can compute complex values without using a trigger
A single unction-based indexe can change hundreds of SQL statements plans
A function-based-index can be built on an arithmetic expression or expression containing
PL/SQL, package functions, C callout or SQL built-in functions
The SQL optimizer can estimate selectivity more accurately if the expressions are materialized in a function-based index.

I also discuss function-based indexes in my new book "Oracle Silver Bullets", and show how single acts (like building a function-based index) can have a positive effect on your entire Oracle database. I've got a lot of great Oracle tuning tips and an online code depot with working examples of function-based indexes:

Also, see these related notes on Function-based indexes:

Oracle function based index - SQL WHERE clause predicate invalid

Regular Expressions and Oracle Performance function based indexes

Oracle performance tuning with function based indexes

Oracle index_ffs hint & optimizer_mode

Oracle - Function Based Indexes (FBI)

Tuning Third-party Vendor Oracle Systems

Locating bad Oracle SQL

Understand Oracle null values

Compare Oracle & SQL Server

Oracle SQL Tuning scans - CBO execution plan

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.

��