AVOID USING UPPER OR LOWER FUNCTIONS ON COLUMNS WHICH ARE INDEXED

A non unique index exists on the ename column of the emp table (ie ENAME_INDX). All employees having the name 'KING' have to be selected.

Query 1
SELECT ename FROM emp
WHERE UPPER (ename) = 'KING';

or

SELECT ename FROM emp
WHERE LOWER(ename) = 'king';

OPERATION	OPTIONS	OBJECT_NAME	COST
SELECT STATEMENT			822
TABLE ACCESS	FULL	EMP	822

Query 2
SELECT ename FROM emp
WHERE ename='KING';

OPERATION	OPTIONS	OBJECT_NAME	COST
SELECT STATEMENT			2
INDEX	RANGE SCAN	ENAME_INDX	2

Inference
The cost is reduced by an incredible 99% when the use of functions in the WHERE clause is eliminated.
Thus, even though the leading column of the index forms a part of the WHERE clause, the available index will not be used. This is because, an SQL function is applied to that column. In such cases, the Oracle Optimizer will not make use of the available index and will opt for a FULL scan on the table. The optimizer has to apply the function to the value in each row which satisfy the condition specified which increases the cost incurred.