About Oracle Function-based Indexes

By: Richard Niemiec


One of the largest problems with indexes is that indexes are often suppressed by developers and ad-hoc users. Developers using functions often suppress indexes. There is a way to combat this problem. Function-based indexes allow you to create an index based on a function or expression. The value of the function or expression is specified by the person creating the index and is stored in the index. Function-based indexes can involve multiple columns, arithmetic expressions, or maybe a PL/SQL function or C callout.

The following example shows how to create a function-based index:

 

0430_001

 

An index that uses the UPPER function has been created on the ENAME column. The following example queries the EMP table using the function-based index:

 

0430_002

 

 

The function-based index (EMP_IDX) can be used for this query. For large tables where the condition retrieves a small amount of records, the query yields substantial performance gains over a full table scan. The following initialization parameters must be set (subject to change with each version) to use function-based indexes (the optimization mode must be cost-based as well). When a function-based index is not working, this is often the problem.

 

0430_003

 

TIP

Function-based indexes can lead to dramatic performance gains when used to create indexes on functions often used on selective columns in the WHERE clause.

To check the details for function-based indexes on a table, you may use a query similar to this:

 

0430_004

 

 

 

 

Comments

  1. In oracle apps, It is often recommended to use function based index on Standard tables. Is there a benefit of that? Does it affect Insert/update in a same way as other indexes? If I write a poorly performing Function, Do I slow down the data load as well?

Trackbacks

Leave a Reply