Function-based Indexes

A function-based index includes columns that are either transformed by a function, such as the UPPER function, or included in an expression, such as col1 + col2.
Defining a function-based index on the transformed column or expression allows that data to be returned using the index when that function or expression is used in a WHERE clause or an ORDER BY clause. Therefore, a function-based index can be beneficial when frequently-executed SQL statements include transformed columns, or columns in expressions, in a WHERE or ORDER BY clause.
Function-based indexes defined with the UPPER(column_name) or LOWER(column_name) keywords allow case-insensitive searches. For example, the following index:
CREATE INDEX uppercase_idx ON employees (UPPER(last_name));

facilitates processing queries such as:
SELECT * FROM employees
    WHERE UPPER(last_name) = 'MARKSON';
 

Setting Parameters to Use Function-Based Indexes in Queries

To use function-based indexes in queries, you need to set the QUERY_REWRITE_ENABLED and QUERY_REWRITE_INTEGRITY parameters.
QUERY_REWRITE_ENABLED
To enable function-based indexes for queries, set the QUERY_REWRITE_ENABLED session parameter to TRUE. QUERY_REWRITE_ENABLED can be set to the following values:

• TRUE: cost - based rewrite
• FALSE: no rewrite
• FORCE: forced rewrite

When QUERY_REWRITE_ENABLED is set to FALSE, then function-based indexes are not used for obtaining the values of an expression in the function-based index. However, function-based indexes can still be used for obtaining values in real columns.
When QUERY_REWRITE_ENABLED is set to FORCE, Oracle always uses rewrite and does not evaluate the cost before doing so. FORCE is useful when you know that the query will always benefit from rewrite, when reduction in compile time is important, and when you know that the optimizer may be underestimating the benefits of materialized views.
QUERY_REWRITE_ENABLED is a session-level and also an instance-level parameter.

QUERY_REWRITE_INTEGRITY

Setting the value of the QUERY_REWRITE_INTEGRITY parameter determines how function-based indexes are used,

• If the QUERY_REWRITE_INTEGRITY parameter is set to ENFORCED (the default), then Oracle uses function-based indexes to derive values of SQL expressions only. This also includes SQL functions.
• If QUERY_REWRITE_INTEGRITY is set to any value other than ENFORCED, then Oracle uses the function-based index, even if it is based on a user-defined, rather than SQL, function.

Function-based indexes are an efficient mechanism for evaluating statements that contain functions in WHERE clauses. You can create a function-based index to store computation-intensive expressions in the index. This permits Oracle to bypass computing the value of the expression when processing SELECT and DELETE statements. When processing INSERT and UPDATE statements, however, Oracle evaluates the function to process the statement.
For example, if you create the following index:
CREATE INDEX idx ON table_1 (a + b * (c - 1), a, b);

then Oracle can use it when processing queries such as:
SELECT a
    FROM table_1
    WHERE a + b * (c - 1) < 100;

You can also use function-based indexes for linguistic sort indexes that provide efficient linguistic collation in SQL statements.
Oracle treats descending indexes as function-based indexes. The columns marked DESC are sorted in descending order.