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.

Answer 2

I think I see what's happening here.

When you have the index in place, and you do:

SELECT Occupation FROM EMPLOYEE WHERE Occupation = 'DOCTOR';

The execution plan will use the index. This is a no-brainer, cause all the data that's required to satisfy the query is right there in the index, and Oracle never even has to reference the table at all.

However, when you do:

SELECT Fname FROM EMPLOYEE WHERE Occupation = 'DOCTOR';

then, if Oracle uses the index, it will do an INDEX RANGE SCAN followed by a TABLE ACCESS BY ROWID to look up the Fname that corresponds to that Occupation. Now, depending on how many rows have DOCTOR for Occupation, Oracle will have to make one or more trips to the table, to look up the Fname. If, for example, you have a table, and all the employees have Occupation set to 'DOCTOR', the index isn't of much use, and Oracle will simply do a FULL TABLE SCAN of the table. If there are 10,000 employees, and only one is a DOCTOR, then again, it's a no-brainer, and Oracle will use the index.

But there are some subtleties, when you're somewhere between those two extremes. People like to talk about 'selectivity', i.e., how many rows are identifed by the index, vs. the size of the table, when discussing whether the index will be used. But, that's not really true. What Oracle really cares about is block selectivity. That is, how many blocks does it have to visit, to satisfy the query? So, first, how "wide" is the RANGE SCAN? The more limited the range of values specified by the predicate values, the better. Second, when your query needs to do table lookups, how many different blocks will it have to visit to find all the data it needs. That is, how "random" is the data in the table relative to the index order? This is called the CLUSTERING_FACTOR. If you analyze the index to collect statistics, and then look at USER_INDEXES, you'll see that the CLUSTERING_FACTOR is now populated.

So, what's CLUSTERING_FACTOR? CLUSTERING_FACTOR is the "orderedness" of the table, with respect to the index's key column(s). The value of CLUSTERING_FACTOR will always be between the number of blocks in a table and the number of rows in a table. A low CLUSTERING_FACTOR, that is, one that is very near to the number of blocks in the table, indicates a table that's very ordered, relative to the index. A high CLUSTERING_FACTOR, that is, one that is very near to the number of rows in the table, is very unordered, relative to the index.

It's an important concept to understand that the CLUSTERING_FACTOR describes the order of data in the table relative to the index. So, rebuilding an index, for example, will not change the CLUSTERING_FACTOR. It's also important to understand that the same table could have two indexes, and one could have an excellent CLUSTERING_FACTOR, and the other could have an extremely poor CLUSTERING_FACTOR. The table itself can only be ordered in one way.

So, why have I spent so much time describing CLUSTERING_FACTOR? Because when you have an execution plan that does an INDEX RANGE SCAN followed by TABLE ACCESS BY ROWID, you can be sure that the CLUSTERING_FACTOR has been considered by Oracle's optimizer, to come up with the execution plan. For example, suppose you have a 10,000 row table, and suppose 100 of the rows have Occupation = 'DOCTOR'. You write the query above, asking for the Fname of the employees whose occupation is DOCTOR. Well, Oracle can very easily and efficiently determine the rowids of the rows where occupation is DOCTOR. But, how many table blocks will Oracle need to visit, to do the Fname lookup? It could be only 1 or 2 table blocks, if the data is clustered (ordered) by Occupation in the table. But, it could be as many as 100, if the data is very unordered in the table! So, again, 10,000 row table, and, let's assume, (for the purposes of illustration and simple math) that the table has 100 rows/block, and so, 100 blocks. Depending on table order (i.e. CLUSTERING_FACTOR), the number of table block visits could be as few as 1, or as many as 100.

So, I hope this helps you understand why the optimizer may be reluctant to use an index in some cases.

Answer 3

An index is the copy of the table which only stores the following data:

• Indexed field(s)
• A pointer to the original row (rowid).

Say you have a table like this:

rowid    id  name  occupation
[1]      1   John  clerk
[2]      2   Jim   manager
[3]      3   Jane  boss

Then an index on occupation would look like this:

occupation  rowid
boss        [3]
manager     [2]
clerk       [1]

, with the records sorted on occupation in a B-Tree.

As you can see, if you only select the indexed fields, you only need the index (the second table).

If you select anything other than occupation:

SELECT  *
FROM    mytable
WHERE   occupation = 'clerk'

then the engine should make two things: first find the relevant records in the index, second, find the records in the original table by rowid. It's like if you joined the two tables on rowid.

Since the rowids in the index are not in order, the reads to the original table are not sequential and can be slow. It may be faster to read the original table in sequential order and just filter the records with occupation = 'clerk'.

The engine does not "unlock" the records: it just finds the rowid in the index, and if there are not enough data in the index itself, it looks up data in the original table by the rowid found.

Answer 4

As a WAG. Analyze the table, and the index, then see if the plan changes.

When you are selecting just the occupation, the entire query can be satisfied from the index. The index literally has a copy of the occupation. The moment you add an additional column to the select, Oracle has to go to the data record, to get it. The optimizer chooses to read all of the data rows instead of all of the index rows, and the data rows. It's cheaper.