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

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

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

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

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.