文章来自:
1. 创建表,用于存放需要重建的索引名称
‐‐ Create tablecreate table T_REBUILD_INDEX
(
name VARCHAR2(30),
btree_space NUMBER,
height NUMBER,
pct_used NUMBER,
deleted_pct NUMBER
) ;
2.创建过程
‐‐reason:判断一个索引是否需要重建,介绍一个简单的方法:对一个索引进行结构分析后,
‐‐如果该索引占用超过了一个数据块,且满足以下条件之一:B‐tree树的高度大于3;
‐‐使用百分比低于75%;数据删除率大于15%,就需要考虑对索引重建
CREATE OR REPLACE PROCEDURE P_REBUILD_INDEX IS
V_INDEX_NAME VARCHAR2(30);
IV_SQL1 VARCHAR2(3000);
IV_SQL2 VARCHAR2(3000);
CURSOR C_INDEX(V_INDEX_NAME IN VARCHAR2) IS
SELECT INDEX_NAME FROM USER_INDEXES ORDER BY INDEX_NAME;
BEGIN
DELETE FROM T_REBUILD_INDEX;
COMMIT;
OPEN C_INDEX(V_INDEX_NAME);
LOOP
FETCH C_INDEX
INTO V_INDEX_NAME;
EXIT WHEN C_INDEX%NOTFOUND;
IV_SQL1 := 'ANALYZE INDEX ' || V_INDEX_NAME || ' COMPUTE STATISTICS ';
EXECUTE IMMEDIATE IV_SQL1;
IV_SQL2 := 'ANALYZE INDEX ' || V_INDEX_NAME || ' VALIDATE STRUCTURE ';
EXECUTE IMMEDIATE IV_SQL2;
INSERT INTO T_REBUILD_INDEX
SELECT NAME,
BTREE_SPACE,
HEIGHT,
PCT_USED,
DEL_LF_ROWS / (DECODE(LF_ROWS, 0, 1, LF_ROWS)) * 100 AS DELETED_PCT
FROM INDEX_STATS
WHERE HEIGHT > 3 AND(PCT_USED < 75 OR DEL_LF_ROWS / (DECODE(LF_ROWS, 0, 1,
LF_ROWS)) * 100 >20);
END LOOP;
CLOSE C_INDEX;
COMMIT;
END;
3.select * from t_rebuild_index 查询考虑需要重建的索引;
4.重建索引的语句:
ALTER INDEX INDEX_NAME REBUILD;