When Custom Function Indexes Fail: Pitfalls and Best Practices in Oracle

When a column is used in a function expression (e.g., WHERE SUBSTR(name,1,3)='abc') and no function‑based index exists, a normal index cannot be used. Oracle allows Function Based Indexes (FBI), which can be built on built‑in or user‑defined functions.

Key Requirements for Custom Function Indexes

Deterministic keyword : The function must be declared DETERMINISTIC so Oracle trusts that identical input parameters always produce the same result. Without this, the optimizer will ignore the index.

Rebuild after changes : If the function definition changes, the corresponding index must be rebuilt (or dropped and recreated) because Oracle does not automatically refresh the stored expression values.

Example 1 – Deterministic Misuse

A custom function uses TO_DATE(param,'yyyy') to convert a year string to a date. Although declared DETERMINISTIC, the function returns the first day of the month, so rows inserted in May and June store different dates (2013‑05‑01 vs 2013‑06‑01). A query filtering on 2013‑05‑01 returns only one row, causing confusion.

Oracle does not verify the logic; it only checks the presence of the DETERMINISTIC flag. If the function’s logic is not truly deterministic (e.g., uses SYSDATE, DBMS_RANDOM), query results become unreliable.

Example 2 – Index Not Rebuilt After Function Change

If a function’s implementation is altered, the stored index values remain based on the old logic. Continuing to use the index yields incorrect results because the index keys no longer match the current function output.

Additionally, without a function index, each row incurs a function call during a full table scan, dramatically increasing CPU usage (e.g., 999 calls for a single query). With a function index, the function is evaluated once per DML operation, and the result is stored in the index, eliminating repeated calls during query execution.

Additional Oracle Settings

For function‑based indexes to participate in query rewrite, the following parameters should be enabled, though in the author's 11g test environment they had no noticeable effect:

QUERY_REWRITE_ENABLED=TRUE
QUERY_REWRITE_INTEGRITY=TRUSTED

Conclusion

When using custom function indexes to improve performance, always ensure the function is truly deterministic, declare it with the DETERMINISTIC keyword, and rebuild the index whenever the function definition changes. Neglecting these steps can lead to misleading query results and maintenance headaches.