First page Back Continue Last page Graphics
Factors for Index Use
Number of distinct keys
- Used to calculate selectivity (filter factor)
Number of rows
Height
- Number of levels (see ALL_INDEXES.BLEVEL)
Number of leaf blocks and average per key
Clustering Factor
Notes:
Before we look at the specific types of index scans available, consider the factors that affect CBO when deciding to use an index or full scan. These factors include:
The number of distinct keys. This combined with the number of rows indexed by the index (cardinality) helps the optimizer determine how many rows will be returned via the index. These statistics are maintained in ALL_INDEXES.DISTINCT_KEYS and ALL_INDEXES.NUM_ROWS.
The height of the index. This would affect the number of I/O’s required to traverse the index tree. This statistic is maintained in ALL_INDEXES.BLEVEL.
The number of leaf blocks and the number of leaf blocks per distinct key will also have an impact on the I/O count. These statistics are maintained in ALL_INDEXES.LEAF_BLOCKS and AVG_LEAF_BLOCKS_PER_ KEY.
The clustering factor is a measure of how sorted the table data is with respect to the sorted sequence of the index. Clustering factor is discussed in more detail later in this lesson.
Refer to Chapter 3 of the Oracle9i Database Performance and Tuning Guide for more information on statistics that influence CBO’s use of an index. Specifically, see the section “Optimizer Index Determination Criteria”.
Refer to the supplied script index_statistics.sql for a query of these statistics.