-->

Spiga

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';




















OPERATIONOPTIONSOBJECT_NAMECOST
SELECT STATEMENT

822
TABLE ACCESSFULLEMP822

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



















OPERATIONOPTIONSOBJECT_NAMECOST
SELECT STATEMENT

2
INDEXRANGE SCANENAME_INDX2

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.

There was an error in this gadget