Function-based indexes, which are based on expressions. They enable you to construct queries that evaluate the value returned by an expression, which in turn may include functions (built-in or user-defined).
The following statements creates a function-based index on the emp table based on an uppercase evaluation of the ename column:
CREATE INDEX emp_i ON emp (UPPER(ename));
To ensure that Oracle will use the index rather than performing a full table scan, be sure that the value of the function is not null in subsequent queries. For example, the statement
SELECT * FROM emp WHERE UPPER(ename) IS NOT NULL
ORDER BY UPPER(ename);