Creating Function-Based Indexes in Oracle

A function-based index stores the result of an expression that Oracle computes from one or more columns (for example, UPPER(title) or TRUNC(order_date)). This lets the optimizer use an index even when your SQL applies a function to a column in the WHERE clause.

In modern Oracle releases (including Oracle 23ai), you will see the same concept implemented either as a classic function-based index or as an index on a virtual column (a virtual column index is effectively equivalent to a function-based index).

The syntax is the standard CREATE INDEX statement, except that the “column list” can be an expression:

CREATE INDEX index_name
ON table_name (function_expression);

Example (case-insensitive search on a title column):

CREATE INDEX books_upper_title_ix
ON bookshelf (UPPER(title));

Oracle can only use a conventional index on title when the predicate references title directly. If you wrap the column in a function, the database may need to evaluate that function for many rows—unless a matching function-based index exists.

Without a matching function-based index (may force more work):

SELECT *
FROM bookshelf
WHERE UPPER(title) = 'MY LEDGER';

With a matching function-based index (optimizer can use the index):

CREATE INDEX books_upper_title_ix
ON bookshelf (UPPER(title));

SELECT *
FROM bookshelf
WHERE UPPER(title) = 'MY LEDGER';

1. Prefer virtual columns for clarity in some schemas
   If you want the expression to be visible as a named “computed column,” create a virtual column and index it. Oracle treats an index on a virtual column equivalently to a function-based index.
2. Index expressions that are stable and predictable
   Avoid expressions whose results change based on session settings unless you control those settings (for example, NLS-dependent comparisons). If you need case-insensitive behavior, be explicit about your approach and keep it consistent.
3. Validate that queries actually use the index
   Confirm with DBMS_XPLAN.DISPLAY_CURSOR (or your preferred plan inspection workflow) that the optimizer is choosing the function-based index for the target statements.
4. Statistics: use DBMS_STATS, not ANALYZE, for optimizer stats
   Oracle’s supported approach for optimizer statistics is DBMS_STATS. If you need stats immediately after creating the index (instead of waiting for automatic stats collection), gather them explicitly:

   EXEC DBMS_STATS.GATHER_INDEX_STATS(USER, 'BOOKS_UPPER_TITLE_IX');

Click the Exercise link below to practice creating a function-based index.
Create Function Based - Exercise