Copy Code code as follows:
SELECT
Height,/*height of the b-tree*/
blocks,/* blocks in the index segment * *
Name,/*index name */
Lf_rows,/* Number of leaf rows in the index * *
Lf_blks,/* Number of the leaf blocks in the index * *
Del_lf_rows,/* Number of deleted leaf rows in the index * *
Rows_per_key/* Average number of rows per distinct key * *
Blk_gets_per_access/* Consistent mode block reads (gets) * *
From Index_stats
WHERE name= ' index_name ';
Copy Code code as follows:
ANALYZE index index_name VALIDATE STRUCTURE
HEIGHT:
This column is 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 a leaf nodes deleted due to the deletion of rows.
Oracle doesn ' t rebuild indexes automatically and, consequently, too many, deleted leaf rows can leads to a unbalanced b-tre E.
Blk_gets_per_access:
Can look in the blk_gets_per_access column to the 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.
