复制代码 代码如下:
SELECt
height,
blocks,
name,
lf_rows,
lf_blks,
del_lf_rows,
rows_per_key
blk_gets_per_access
FROM INDEX_STATS
WHERe NAME='INDEX_NAME';
复制代码 代码如下:
ANALYZE index INDEX_NAME VALIDATE STRUCTURE
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.
