index_stats

经常碰到人问我index 什么时候需要rebuild,今天看了个文章，记录了一下.

The INDEX_STATS view is useful for seeing how efficiently an index is using its space. Large indexes
have a tendency to become unbalanced over time if many deletions are in the table (and therefore
index) data. Your goal is to keep an eye on those large indexes with a view to keeping them balanced.
Note that the INDEX_STATS view is populated only if the table has been analyzed by using the
ANALYZE command, as follows:
SQL> ANALYZE index hr.emp_name_ix VALIDATE STRUCTURE;
Index analyzed.
The query in Listing 7-26 using the INDEX_STATS view helps determine whether you need to
rebuild the index. In the query, you should focus on the following columns in the INDEX_STATS view

to determine if your index is a candidate for a rebuild:
• HEIGHT: This column refers to the height of the B-tree index, and it’s usually at the 1, 2, or 3
level. If large inserts push the index height beyond a level of 4, it’s time to rebuild, which flattens
the B-tree.
• DEL_LF_ROWS: This is the number of leaf nodes deleted due to the deletion of rows. Oracle
doesn’t rebuild indexes automatically and, consequently, too many deleted leaf rows can lead
to an unbalanced B-tree.
• BLK_GETS_PER_ACCESS: You can look at the BLK_GETS_PER_ACCESS column to see how
much logical I/O it takes to retrieve data from the index. If this row shows a double-digit
number, you should probably start rebuilding the index.

Using the INDEX_STATS View to Determine Whether to Rebuild an Index
SQL> SELECT height, /*Height of the B-Tree*/
2 blocks, /* Blocks in the index segment */
3 name, /*index name */
4 lf_rows, /* number of leaf rows in the index */
5 lf_blks, /* number of leaf blocks in the index */
6 del_lf_rows, /* number of deleted leaf rows
in the index */
7 rows_per_key /* average number of rows
per distinct key */
8 blk_gets_per_access /* consistent mode block reads (gets) */
8 FROM INDEX_STATS
9* WHERE name='EMP_NAME_IX';
HEIGHT BLOCK LF_ROWS LF_BLKS DEL_LF_ROWS ROWS_PER_KEY BLK_GETS
------ ----------- ------- -------- ----------- ------------ ---------
16 EMP_NAME_IX 107 1 0 1