Example 1
The following statement creates function-based index IDX on table EMP based on an uppercase evaluation of the ENAME column:

CREATE INDEX idx ON emp (UPPER(emp_name));


Now the SELECT statement uses the function-based index on UPPER(EMP_NAME) to retrieve all employees with names that start with JOH:

SELECT * FROM emp WHERE UPPER(emp_name) LIKE 'JOH%';

This is from the Oracle documentation.

HTH,



>we have a index with this expression - UPPER{"CLASTNAME")
>
>our sql statement
>Select * from MyTable where UPPER("CLASTNAME") LIKE 'SMITH%'
>
>every time we run this, oracle does a full table scan, this is bad when we expect to have over 4 million records in this table.
>
>what am I missing or can't we ever use UPPER() with LIKE ???
>
>
>TIA
>Mark