Indexes, EXPLAIN PLAN, and record access in Oracle SQL

Question

I have been learning about indexes in Oracle SQL, and I wanted to conduct a small experiment with a test table to see how indexes really worked. As I discovered from an earlier post made here, the best way to do this is with EXPLAIN PLAN. However, I am running into something which confuses me.

My sample table contains attributes (EmpID, Fname, Lname, Occupation, .... etc). I populated it with 500,000 records using a java program I wrote (random names, occupations, etc). Now, here are some sample queries with and without indexes:

NO INDEX:

SELECT Fname FROM EMPLOYEE WHERE Occupation = 'DOCTOR';

EXPLAIN PLAN says:

OPERATION                         OPTIMIZER COST
TABLE ACCESS(FULL) TEST.EMPLOYEE  ANALYZED  1169

Now I create index:

CREATE INDEX occupation_idx
    ON EMPLOYEE (Occupation);

WITH INDEX "occupation_idx":

SELECT Fname FROM EMPLOYEE WHERE Occupation = 'DOCTOR';

EXPLAIN PLAN says:

OPERATION                         OPTIMIZER COST
TABLE ACCESS(FULL) TEST.EMPLOYEE  ANALYZED  1169

So... the cost is STILL the same, 1169? Now I try this:

WITH INDEX "occupation_idx":

SELECT Occupation FROM EMPLOYEE WHERE Occupation = 'DOCTOR';

EXPLAIN PLAN says:

OPERATION                              OPTIMIZER COST
INDEX(RANGE SCAN) TEST.OCCUPATION_IDX  ANALYZED  67

So, it appears that the index only is utilized when that column is the only one I'm pulling values from. But I thought that the point of an index was to unlock the entire record using the indexed column as the key? The search above is a pretty pointless one... it searches for values which you already know. The only worthwhile query I can think of which ONLY involves an indexed column's value (and not the rest of the record) would be an aggregate such as COUNT or something.

What am I missing?

Answer 1

Even with your index, Oracle decided to do a full scan for the second query.

Why did it do this? Oracle would have created two plans and come up with a cost for each:-

1) Full scan

2) Index access

Oracle selected the plan with the lower cost. Obviously it came up with the full scan as the lower cost.

If you want to see the cost of the index plan, you can do an explain plan with a hint like this to force the index usage:

SELECT /*+ INDEX(EMPLOYEE occupation_idx) */ Fname
FROM EMPLOYEE WHERE Occupation = 'DOCTOR';

If you do an explain plan on the above, you will see that the cost is greater than the full scan cost. This is why Oracle did not choose to use the index.

A simple way to consider the cost of the index plan is:-

• The blevel of the index (how many blocks must be read from top to bottom)

• The number of table blocks that must be subsequently read for records matching in the index. This relies on Oracle's estimate of the number of employees that have an occupation of 'DOCTOR'. In your simple example, this would be:

  number of rows / number of distinct values

More complicated considerations include the clustering factory and index cost adjustments which both reflect the likelyhood that a block that is read is already in memory and hence does not need to read from disk.

Perhaps you could update your question with the results from your query with the index hint and also the results of this query:-

SELECT COUNT(*), COUNT(DISTINCT( Occupation ))
FROM EMPLOYEE;

This will allow people to comment on the cost of the index plan.