1、先分析索引,语句如下:
analyze index index_name validate structure
然后查询 index_stats
select name, height, pct_used, del_lf_rows/lf_rows from index_stats;
DEL_LF_ROWS于LF_ROWS的比例 这是最基本的了
如果查询结果中:
Height > 4
PCT_USER < 50
del_lf_rows/lf_rows > 0.2
则说明索引需要重建。
可以通过程序来实现该功能
注意:analyze index 和查询index_stats 必须在同一个session中
create table MONITORINDEX
(
INDEX_NAME VARCHAR2(50),
DEL_LF_ROWS NUMBER,
LF_ROWS NUMBER,
RATE NUMBER(4,2),
MONITORDATE DATE default sysdate not null
)
create or replace procedure analyzeindex is
v_sql varchar2(100);
begin
for a in (select index_name from all_indexes where owner = USER) loop
v_sql := ' analyze index ' || a.index_name || ' validate structure';
execute immediate v_sql;
insert into monitorindex
(index_name, del_lf_rows, lf_rows, rate)
select name,
del_lf_rows,
lf_rows,
round(del_lf_rows * 100 / (lf_rows + del_lf_rows), 2)
from index_stats;
end loop;
end analyzeindex;