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 is, on average, the number of table data blocks that must be retrieved for every distinct key value.
- CLUSTERING_FACTOR
The measurement of the likelihood that given key values are located close to each other in the table. The higher the value, the more likely that when you retrieve a data block to get the first row from the index, other rows will be in the same block or close to it.
- NUM_ROWS (New in Oracle8)
The total number of rows in the table.
- SAMPLE_SIZE (New in Oracle8)
The number of rows actually used to determine the values. A value of indicates that all rows were used.
- LAST_ANALYZED (New in Oracle8)
Timestamp of the last ANALYZE INDEX command.