You are here

When is an index access path better than a full table scan?

The cost based optimizer makes decisions that can be hard to understand. One of the hardest may be why it chooses indexed or scan access paths: a burning question for many DBAs.

You will often hear a rule-of-thumb regarding what percentage of a table needs to be selected before a full table scan is more efficient thatn an index range scan. 2% is frequently suggested. However, you cannot place any reliance on such a figure. The cross over point will be dependent on many factors, including (but not limited to):

Number of rows in the table
Block size
Average row length
Key length
Speed of the disc
Multiblock read count
Direct or indirect read
Memory configuration
Partitioning and parallelism

All of this comes down to statistics. Object statistics, and system statistics. One of the most critical statistics is the clustering factor of the index: how closely the physical order of the rows conforms to the order of the keys.

This (not very scientific) test shows that for a perfectly clustered index, the CBO switches from index range scan to table full scan when the percentage of rows selected is 14%, but for an unclustered index it switches at just 0.06%.