Avoid full table scan by a different select

Question

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

Answer 1

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.

Answer 2

Quite simply, because the optimiser has calculated that the cost of a full scan for the first query is less than the cost of an index-based access method.

It seems like an unlikely situation, because instinctively you expect only one row to match the job = 'PRESIDENT' condition, but if you imagine that 50% of the rows match that condition then the reading of the full table with multiblock reads is going to be faster than reading the index and then reading the table in potentially smaller reads.

In the case of the second query, the optimiser knows that accessing the table is not required in order to give the correct answer, so unless the table is extremely narrow it is going to be quicker to use an index range scan or fast full index scan to return the result.

The lesson being taught here may be that you should only return the columns that you need. A subsidiary lesson is that most abstraction layers (eg. ActiveRecord) are by default going to be slower than they need to be, because they tend to always return every column.