Function-Based Indexes

1. The Oracle server uses the index only when that particular function is used in a query.
2. If the function is not used in a query and there is no specific index, the Oracle server will full scan.
3. QUERY_REWRITE_ENABLE initialization parameter must be set to TRUE for a function-based index to be used.
   

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