A case-insensitive search such as WHERE UPPER(last_name) = 'KHAN' cannot use an index on last_name, because the index holds the original values, not their uppercase forms. A function-based index stores the result of an expression instead, so queries that use the same expression can find rows through it. The same technique indexes JSON paths.
Code for This Guide
The main examples are in the examples/indexes folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
create index index on table (expression [, ...])
The expression must be deterministic: the same input always gives the same result. Gather statistics afterward so the optimizer knows the expression's values.
A Case-Insensitive Search
Example:
create index customers_last_name_upper_ix on customers (upper(last_name)); exec dbms_stats.gather_table_stats(user, 'CUSTOMERS') explain plan for select * from customers where upper(last_name) = 'KHAN'; select * from table(dbms_xplan.display(format => 'BASIC'));
Output:
Index CUSTOMERS_LAST_NAME_UPPER_IX created. PL/SQL procedure successfully completed. Explained. PLAN_TABLE_OUTPUT _______________________________________________________________________________ Plan hash value: 998004927 ---------------------------------------------------------------------------- | Id | Operation | Name | ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| CUSTOMERS | | 2 | INDEX RANGE SCAN | CUSTOMERS_LAST_NAME_UPPER_IX | ---------------------------------------------------------------------------- 9 rows selected.
The plan reads CUSTOMERS_LAST_NAME_UPPER_IX with a range scan, then the matching rows.
The Expression Must Match
An index on UPPER(first_name) does not help a condition on first_name itself.
Example:
-- the index on UPPER(first_name) does not serve a condition on first_name itself explain plan for select * from customers where first_name = 'Neha'; select * from table(dbms_xplan.display(format => 'BASIC')); select index_name, column_expression from user_ind_expressions where index_name = 'CUSTOMERS_UPPER_IX2';
Output:
Explained.
PLAN_TABLE_OUTPUT
__________________________________________
Plan hash value: 2008213504
---------------------------------------
| Id | Operation | Name |
---------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS FULL| CUSTOMERS |
---------------------------------------
8 rows selected.
INDEX_NAME COLUMN_EXPRESSION
______________________ ______________________
CUSTOMERS_UPPER_IX2 UPPER("FIRST_NAME")The plan is a full scan. USER_IND_EXPRESSIONS shows the expression stored for the index; queries must use the same expression to benefit. The index was created and dropped by the example.
Index a JSON Path
An index on a JSON path, in dot notation with an item method, serves queries on that path.
Example:
create index customers_tier_ix on customers c (c.loyalty.tier.string()); explain plan for select customer_id from customers c where c.loyalty.tier.string() = 'Gold'; select * from table(dbms_xplan.display(format => 'BASIC'));
Output:
Index CUSTOMERS_TIER_IX created. Explained. PLAN_TABLE_OUTPUT _______________________________________________________ Plan hash value: 4288211797 ---------------------------------------------------- | Id | Operation | Name | ---------------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | VIEW | index$_join$_001 | | 2 | HASH JOIN | | | 3 | INDEX RANGE SCAN | CUSTOMERS_TIER_IX | | 4 | INDEX FAST FULL SCAN| CUSTOMERS_PK | ---------------------------------------------------- 11 rows selected.
Here the optimizer answers the query from two indexes alone, the JSON path index and the primary key, joining them without reading the table.
Things to Know
- A virtual column with an index on it has the same effect and gives the expression a name.
- Functions written in PL/SQL must be declared DETERMINISTIC to be indexed.
- Drop the example indexes with drop index customers_last_name_upper_ix and drop index customers_tier_ix.
Related Guides
Conclusion
A function-based index stores the value of an expression, so queries filtering on the same expression, such as UPPER(name) or a JSON path, can use an index. Keep the expression deterministic, use it identically in queries, and gather statistics after creating the index.
