-->

Spiga

MAKE THE MOST OFTEN USED COLUMN IN A WHERE CLAUSE AS THE LEADING COLUMN IN A CONCATENATED INDEX

A composite index exists on the empno and deptno columns of the emp table (i.e. EMPNO_DEPTNO_INDX in which empno is the leading column). All employees belonging to department 10 have to be selected.

Query 1

SELECT * FROM emp
WHERE deptno = 10;


















OPERATIONOPTIONSOBJECT_NAMECOST
SELECT STATEMENT

822
TABLE ACCESSFULLEMP822


Query 2

SELECT * FROM emp
WHERE empno > 0 AND deptno = 10;
























OPERATIONOPTIONSOBJECT_NAMECOST
SELECT STATEMENT

293
TABLE ACCESSBY INDEX ROWIDEMP293
INDEXRANGE SCANEMPNO_DEPTNO_INDX26


Inference
There is a huge cost difference between the above two queries, almost a 62% reduction in the cost when the second approach is used.
Thus, the leading column should be the most selective column, and it should also be the column most often used by limiting conditions in queries. So it is advisable to include a dummy WHERE clause for such queries as shown in the second query.

There was an error in this gadget