Function-Based Indexes

• Based on expressions
• Built from table columns, constants, SQL functions, and user-defined functions.

Examples)

CREATE        INDEX    upper_dept_name_index ON        dept2(UPPER(department_name));

 

SELECT        * FROM        dept2 WHERE     UPPER(department_name) = 'SALES'

 

The function-based index is used only when a query uses a particular function in the query otherwise the Oracle server may perform a full table scan.