Oracle Database Administration: The Essential Refe

ANALYZE INDEX

The next way the ANALYZE command is used is to analyze an index. There are actually two versions of this command, ANALYZE INDEX and ANALYZE TABLE ... FOR ALL INDEXES. When you use this command, Oracle calculates information about the B-tree depth and the distribution of leaf and branch blocks. Perhaps most importantly, it calculates the number of distinct rows in the index; the more distinct rows there are, the more likely that a given index lookup will result in very few rows. Fewer rows means fewer disk I/Os.

You should ANALYZE any index that could be a candidate for a join. Since your indexes have been created either in support of a unique or primary key or to facilitate a WHERE clause, this basically means that you must analyze all indexes.

Examples of using the ANALYZE command to analyze an index are shown here:

ANALYZE TABLE scott.emp COMPUTE STATISTICS FOR ALL INDEXES;
ANALYZE INDEX scott.emp_I COMPUTE STATISTICS;

When you analyze an index, Oracle populates the following columns in the DBA_INDEXES, ALL_INDEXES, and USER_INDEXES data dictionary views:

BLEVEL

The depth of the B-tree.

LEAF_BLOCKS

The number of leaf blocks in the index.

DISTINCT_KEYS

The number of distinct keys in the index. For a unique index, this will equal the value of NUM_ROWS.

AVG_LEAF_BLOCKS_PER_KEY

The average number of leaf blocks per key; that is, on average, the number of index leaf blocks that contain a given distinct key.

AVG_DATA_BLOCKS_PER_KEY

The average number of data blocks per key. That ...