Search code examples
performanceoraclefull-table-scan

Avoid full table scan by a different select

Why does the first query use full table scan while the second uses index?

SELECT * 
FROM   emp
WHERE  job = 'PRESIDENT';

and

SELECT job 
FROM   emp
WHERE  job = 'PRESIDENT';
Solution

  • The second query selects only column "job". The value of this column is already known when you select the index. Nothing else is required by your query, so there is no need to read any additional data from table.

    For the first query it is more difficult. Usually Oracle should access the index. How many different job titles are in this column? In case the cardinality (i.e. selectivity) is low, then a full table scan can be quicker than an index scan. In this case we can assume there is only 1 PRESIDENT in the table, but Oracle has evaluate the execution plan based on statistics and estimations. For other jobs e.g. 'CLERK' the full table scan would be the fasted was of getting the data, I assume.