-->

Spiga

AVOID USING SUBSTR FUNCTION ON INDEXED COLUMNS

A non unique index exists on the ename column of the emp table(ie ENAME_INDX). All employees with their names having the first two letters as 'KI' have to be selected.

Query 1

SELECT ename FROM emp
WHERE SUBSTR (ename,1,2) = 'KI';




















OPERATIONOPTIONSOBJECT_NAMECOST
SELECT STATEMENT

822
TABLE ACCESSFULLEMP822

Query 2

SELECT ename FROM emp
WHERE ename LIKE 'KI%';




















OPERATIONOPTIONSOBJECT_NAMECOST
SELECT STATEMENT

148
INDEXRANGE SCANENAME_INDX148

Inference

Here again, the cost is reduced by more than 82% by avoiding the use of SUBSTR function in the WHERE clause. As explained above, the Optimizer will not make use of the index even though it is available, since there is a function on the indexed column in the WHERE condition.

There was an error in this gadget