Oracle

Function-Based Indexes

Figo Kim 2006. 12. 3. 17:30
  • 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.