Understand Function-Based Indexing in Oracle 23ai

A function-based index is an index on the result of an expression rather than on the raw column value. In Oracle, this is typically a deterministic SQL function applied to one or more columns (for example UPPER(title), TRUNC(order_date), or NVL(flag,'N')). The point is straightforward: if your predicates apply a function to a column, Oracle often cannot use a normal B-tree index on that column efficiently—unless you index the same expression.

Indexes accelerate row lookup when the WHERE clause can match index keys directly. But many real queries transform data before comparing it: case-insensitive search, date bucketing, computed codes, and normalization logic. If you write a predicate like:

WHERE UPPER(title) = 'MY LEDGER'

then an ordinary index on title may not be usable, because Oracle would need to evaluate UPPER(title) for many candidate rows to decide whether they match. A function-based index stores the computed key so Oracle can use index access paths without re-applying the function row-by-row at runtime.

Consider a case-insensitive lookup. A direct equality predicate can use a standard index on title:

SELECT *
FROM bookshelf
WHERE title = 'MY LEDGER';

But if your application requires case-insensitive matching, you typically normalize the comparison using UPPER (or LOWER):

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

To support that predicate with index access, create a function-based index on the expression itself:

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

With this index in place, Oracle can satisfy UPPER(title) = ... using the index key that already contains the transformed value, improving performance and reducing CPU overhead for repetitive function evaluation.

Function-based indexes are powerful, but they should be used intentionally:

1. Match the predicate exactly: the indexed expression must match what your queries use (same function, same arguments, same order).
2. Be selective: add them only for predicates that occur frequently and are performance-relevant.
3. Account for DML overhead: every extra index increases the cost of INSERT, UPDATE, and DELETE.
4. Plan for storage: computed keys consume additional index space; large expressions can enlarge index segments quickly.

In the next lesson, you will build on this definition by creating function-based indexes for realistic query patterns and validating that Oracle chooses the expected index access paths.