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;
Query 2
SELECT * FROM emp
WHERE empno > 0 AND deptno = 10;
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.
Query 1
SELECT * FROM emp
WHERE deptno = 10;
| OPERATION | OPTIONS | OBJECT_NAME | COST |
|---|---|---|---|
| SELECT STATEMENT | 822 | ||
| TABLE ACCESS | FULL | EMP | 822 |
Query 2
SELECT * FROM emp
WHERE empno > 0 AND deptno = 10;
| OPERATION | OPTIONS | OBJECT_NAME | COST |
|---|---|---|---|
| SELECT STATEMENT | 293 | ||
| TABLE ACCESS | BY INDEX ROWID | EMP | 293 |
| INDEX | RANGE SCAN | EMPNO_DEPTNO_INDX | 26 |
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.
Posts
