Oracle Function-Based Index: Syntax, Example & Gotchas

A plain index on a column can't help a query that filters on a function of that column — UPPER(last_name), TRUNC(hire_date), and similar expressions all need their own index. Here's how function-based indexes work, and the exact-match rule that decides whether the optimizer actually uses one.

Indexing the result of an expression, not just a column

A function-based index indexes the result of a function or expression applied to a column, rather than the raw column value. It exists to fix a specific, common problem: a plain index on last_name can't be used by a query filtering on UPPER(last_name), because the index stores raw values and the query is asking about a transformed one.

The problem, and the fix

A case-insensitive search is the classic use case.

-- A plain index on last_name does NOT help this query
SELECT * FROM employees WHERE UPPER(last_name) = 'SMITH';

-- Fix: index the exact expression the query uses
CREATE INDEX idx_emp_upper_lastname ON employees (UPPER(last_name));

-- Now this uses the function-based index:
SELECT * FROM employees WHERE UPPER(last_name) = 'SMITH';

The expression has to match exactly

This is the rule that trips people up: the optimizer only uses a function-based index when the query's WHERE clause contains the exact same expression the index was built on — not just a semantically similar one. An index on UPPER(last_name) won't be used by a query filtering on LOWER(last_name), or on plain last_name with no function at all. Consistency between how the index is built and how queries are written is what makes function-based indexes work.

Alternative: a virtual column

Oracle 11g+ lets you name the expression as a column and index that instead — sometimes clearer for reporting and reuse.

ALTER TABLE employees ADD (
  last_name_upper VARCHAR2(50) GENERATED ALWAYS AS (UPPER(last_name)) VIRTUAL
);
CREATE INDEX idx_emp_lnu ON employees (last_name_upper);

-- Now this uses the index too, and the column is queryable by name:
SELECT * FROM employees WHERE last_name_upper = 'SMITH';

Beyond case-insensitive search

Function-based indexes aren't just for UPPER/LOWER. Common uses include indexing TRUNC(hire_date) to speed up date-only comparisons that ignore the time component, indexing an arithmetic expression used consistently in reports, or indexing a substring extraction that's queried often. The rule is the same in every case: the index only helps queries that use the identical expression.

Frequently asked questions

Do I need any special database setting to use function-based indexes?

On modern Oracle versions, no special session or database setting is required beyond the normal cost-based optimizer being in use (the default). Older Oracle versions had additional requirements around query rewrite settings, but this isn't a practical concern on current releases.

Why isn't my function-based index being used even though I built it on the right expression?

Check that the query's WHERE clause uses the identical expression, including the same function and argument — not a rewritten or semantically equivalent form. Also confirm statistics have been gathered on the index (function-based indexes rely on statistics for the optimizer to judge them worth using).

Is a function-based index the same as a virtual column index?

They solve the same problem differently. A function-based index indexes an expression directly without changing the table's visible columns. A virtual column names the expression as a real (if computed) column and then gets indexed normally — slightly more visible and reusable in other queries, at the cost of an extra table definition change.

Can I build a function-based index on more than one column?

Yes — a function-based index can be a composite index where one or more key parts are expressions, for example (UPPER(last_name), department_id). The same leftmost-prefix and exact-expression-match rules apply to each part.