您现在的位置是：网站首页> 编程资料编程资料

Oracle中检查是否需要重构索引的sql_oracle_

2023-05-27 330人已围观

简介 Oracle中检查是否需要重构索引的sql_oracle_

复制代码 代码如下:

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 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';

复制代码 代码如下:

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.
您可能感兴趣的文章:

上一篇：Oracle数据库设置任务计划备份一周的备份记录_oracle_

下一篇：Oracle提高sql执行效率的心得建议_oracle_

相关内容

点击排行

本栏推荐

猜你喜欢

-六神源码网