Simple B-tree Access

Summary

Although the chapter was only intended to be an introduction to how the optimizer assesses the cost of using a simple B-tree index, we have covered a lot of ground. Key points to remember though are as follows:

The typical cost of using a B-tree index comprises three components—the depth of the index, based on the blevel; the number of index leaf blocks that will be visited, based on leaf_blocks; and the number of visits to table blocks, based on the clustering_factor.

The clustering_factor of an index is a primary indicator of how desirable the index appears to be to the optimizer. The drawback is that you need to compare the clustering_factor to the number of rows and blocks in the table—the number by itself is effectively meaningless.

If you have columns in an index that are often omitted from the where clause or have range-based tests applied to them, then they should generally be pushed towards to the end of the index definition; otherwise your query could end up doing a lot of work with index leaf blocks. The optimizer’s cost calculation will reflect this, and may result in the index being ignored in favor of alternative paths.

System statistics should be enabled as part of your migration to 9i (or 10g), and you need to be aware of the impact this will have on execution plans.