First page Back Continue Last page Graphics
Function-Based Indexes
Problem: This query will not use index due to use of the UPPER function on lastname:
Solution: Oracle8i and later Function-based indexes allow use of a function when creating index
Notes:
With Oracle8i and later, function-based indexes can be created. A function-based index can be a big help if you need to use a function on an indexed column. In this example, I need to perform a case insensitive search on the LASTNAME column. By creating a function-based index, as shown in the 2nd example, the index can be used in the execution plan.
The following has to be in effect for function-based indexes to work:
Cost-based optimization. RBO does not use function-based indexes.
Initialization parameter QUERY_REWRITE_ENABLE = TRUE.
Initialization parameter QUERY_REWRITE_INTEGRITY=TRUSTED
QUERY REWRITE privilege is required to create a function-based index on tables in your schema. Use GLOBAL QUERY REWRITE to create function-based indexes on tables in other schemas.
Don’t forget to collect statistics with DBMS_STATS.
Functions allowed in the CREATE INDEX statement:
SQL functions and arithmetic expressions
PL/SQL, Java or packaged functions or a C callout
Method within an object (only if index is being built on that object)
Restrictions:
Aggregate functions are NOT allowed.