A function-based index in Oracle isn’t your typical index. Instead of storing raw column data, it stores the result of an expression, function, or transformation. That way, Oracle can quickly find rows even when your query applies functions to columns—something regular indexes can’t handle.
Quick Fix Summary
When a query crawls because it applies a function to a column without an index, create a function-based index on that expression. Say you’re hunting for WHERE UPPER(last_name) = 'SMITH'. Just run:
CREATE INDEX idx_upper_last_name ON employees (UPPER(last_name));
Check if Oracle actually uses it with EXPLAIN PLAN or SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY). When it’s no longer needed, drop it:
DROP INDEX idx_upper_last_name;
What’s Really Going On
Regular B-tree indexes only help when your WHERE clause matches the indexed column exactly. Need WHERE UPPER(name) = 'ALICE' or WHERE salary * 1.05 > 50000? Oracle can’t use a standard index on name or salary because the values don’t match directly. A function-based index solves this by pre-computing UPPER(name) or salary * 1.05 and storing those results. The database engine then performs fast lookups even when functions appear in the query.
Oracle lets you build these indexes on expressions, built-in functions, deterministic PL/SQL functions, and even external C routines. They’re stored permanently and updated automatically whenever you insert, update, or delete data.
How to Fix a Slow Query in 4 Steps
1. Spot the bottleneck
Run EXPLAIN PLAN on the slow query to confirm the function call is forcing a full table scan:
EXPLAIN PLAN FOR SELECT * FROM employees WHERE UPPER(last_name) = 'SMITH';
Then inspect the plan:
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
2. Build the index
Use the exact expression from your WHERE clause in the CREATE INDEX command. Want case-insensitive searches? Try:
CREATE INDEX idx_upper_last_name ON employees (UPPER(last_name));
Need to filter on a calculated bonus? Build an index on the arithmetic:
CREATE INDEX idx_salary_bonus ON employees (salary * 1.05);
3. Confirm it’s being used
Re-run the EXPLAIN PLAN and look for INDEX (or INDEX (FULL SCAN)) in the output. You can also peek at the live cursor plan:
SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(sql_id => '<your_sql_id>'));
4. Drop or rebuild when necessary
To delete the index:
DROP INDEX idx_upper_last_name;
If data has drifted and the index needs freshening:
ALTER INDEX idx_upper_last_name REBUILD;
Still Not Seeing the Speed Boost?
1. Check your permissions
You’ll need both CREATE INDEX and QUERY REWRITE privileges. Sometimes the base table also requires query access.
2. Make sure your function is deterministic
PL/SQL functions must be marked DETERMINISTIC to be indexable. Otherwise Oracle refuses to use them in function-based indexes.
CREATE OR REPLACE FUNCTION get_discount_rate(p_id NUMBER) RETURN NUMBER DETERMINISTIC IS BEGIN RETURN CASE WHEN p_id < 100 THEN 0.1 ELSE 0.05 END; END;
After that, create the index:
CREATE INDEX idx_discount ON orders (get_discount_rate(customer_id));
3. Force the optimizer’s hand
If the query plan stubbornly ignores your shiny new index, add a hint to make Oracle use it:
SELECT /*+ INDEX(employees idx_upper_last_name) */ * FROM employees WHERE UPPER(last_name) = 'SMITH';
Keep Your Queries Fast Long-Term
Index the expressions that show up most often in your application’s WHERE clauses. Run case-insensitive searches on LOWER(email) every day? Build an index on LOWER(email). Then document the indexed expression in the schema comments so future maintainers know why it exists:
COMMENT ON INDEX idx_lower_email IS 'Indexes LOWER(email) for case-insensitive searches';
Every so often, check which indexes are actually being used. Query V$OBJECT_USAGE and drop the ones gathering dust to save storage and speed up DML operations:
SELECT index_name, used FROM v$object_usage WHERE table_name = 'EMPLOYEES';
(Honestly, this is one of those “set it and forget it” tasks that pays off later.)
Looking ahead, Oracle Database 23c and later versions improve how the optimizer handles function-based indexes, so upgrading can squeeze out even more performance.
Want to dive deeper? The Oracle Database Documentation and the Oracle Technology Network have everything you need.
