选出需要rebuild的索引
来源:互联网 发布:txt电子书制作软件 编辑:程序博客网 时间:2024/06/05 12:39
该脚本的主要功能是列出需要rebuild的索引,列出状态为unusable的索引。没有将ORACLE内置账户的索引考虑在内。
需要rebuild的索引遵循如下原则:
1.索引高度大于等于4
2.索引中被删除的数据超过索引数据的20%。
3.索引的状态为VALID
警告:别胡乱在生产库中运行该脚本,千万别在繁忙的时候运行该脚本,慎重,慎重
set serveroutput on
set linesize 200
set pagesize 100
declare
cursor spetial is
select index_name,owner from dba_indexes where owner not in
('SYS','SYSMAN','SYSTEM','MDSYS','OLAPSYS','DMSYS','ORDSYS','EXFSYS','XDB','CTXSYS','WMSYS','OUTLN','ISMSYS','DBSNMP','TSMSYS') and status='UNUSABLE';
cursor index_name is
select index_name,owner from dba_indexes where owner not in
('SYS','SYSMAN','SYSTEM','MDSYS','OLAPSYS','DMSYS','ORDSYS','EXFSYS','XDB','CTXSYS','WMSYS','OUTLN','ISMSYS','DBSNMP','TSMSYS') and status='VALID';
height index_stats.height%TYPE :=0;
lf_rows index_stats.lf_rows%TYPE :=0;
del_lf_rows index_stats.del_lf_rows%TYPE :=0;
distinct_keys index_stats.distinct_keys%TYPE :=0;
begin
for c_spetial in spetial loop
dbms_output.put_line(c_spetial.owner ||'.' || c_spetial.index_name ||' is unusable.');
end loop;
for indexname in index_name loop
execute immediate 'analyze index '|| indexname.owner ||'.'|| indexname.index_name ||' validate structure';
select height,decode(lf_rows,0,1,lf_rows),del_lf_rows,decode(distinct_keys,0,1,distinct_keys)
into height,lf_rows,del_lf_rows,distinct_keys from index_stats;
if(height>=4) or ((del_lf_rows/lf_rows)>0.2) then
dbms_output.put_line(' The height of '|| indexname.owner || '.'|| indexname.index_name || ' is '||
height || ' and the DEL_LF_ROWS/LF_ROWS is ' || del_lf_rows/lf_rows || ' needs rebuild!!!');
end if;
end loop;
exception
when others then
null;
end;
/
例子:
SQL> declare
2 cursor spetial is
3 select index_name,owner from dba_indexes where owner not in
4 ('SYS','SYSMAN','SYSTEM','MDSYS','OLAPSYS','DMSYS','ORDSYS','EXFSYS','XDB','CTXSYS','WMSYS','OUTLN','ISMSYS','DBSNMP','TSMSYS') and status='UNUSA
5 cursor index_name is
6 select index_name,owner from dba_indexes where owner not in
7 ('SYS','SYSMAN','SYSTEM','MDSYS','OLAPSYS','DMSYS','ORDSYS','EXFSYS','XDB','CTXSYS','WMSYS','OUTLN','ISMSYS','DBSNMP','TSMSYS') and status='VALID
8 height index_stats.height%TYPE :=0;
9 lf_rows index_stats.lf_rows%TYPE :=0;
10 del_lf_rows index_stats.del_lf_rows%TYPE :=0;
11 distinct_keys index_stats.distinct_keys%TYPE :=0;
12 begin
13 for c_spetial in spetial loop
14 dbms_output.put_line(c_spetial.owner ||'.' || c_spetial.index_name ||' is unusable.');
15 end loop;
16 for indexname in index_name loop
17 execute immediate 'analyze index '|| indexname.owner ||'.'|| indexname.index_name ||' validate structure';
18 select height,decode(lf_rows,0,1,lf_rows),del_lf_rows,decode(distinct_keys,0,1,distinct_keys)
19 into height,lf_rows,del_lf_rows,distinct_keys from index_stats;
20 if(height>=4) or ((del_lf_rows/lf_rows)>0.2) then
21 dbms_output.put_line(' The height of '|| indexname.owner || '.'|| indexname.index_name || ' is '||
22 height || ' and the DEL_LF_ROWS/LF_ROWS is ' || del_lf_rows/lf_rows || ' needs rebuild!!!');
23 end if;
24 end loop;
25 end;
26 /
SCOTT.LOWERNAME is unusable.
PL/SQL 过程已成功完成。
- 选出需要rebuild的索引
- 选出需要rebuild的索引
- 索引的rebuild和rebuild online的区别
- 索引的rebuild和rebuild online的区别
- 索引是如何定期rebuild的
- rebuild 分区索引
- MSSQL Rebuild(重建)索引
- 批量rebuild索引
- 转:rebuild索引
- 生成指定表rebuild所有索引的语句
- 关于重构索引--index rebuild的OCP 题
- 2种方法发现一个索引是否碎片过多,而需要rebuild
- 索引create|rebuild|rebulid online
- rebuild 索引遭遇ORA-02243
- Elasticsearch索引重建(Rebuild)
- Elasticsearch索引重建(Rebuild)
- elasticsearch 索引重建(Rebuild)
- 重建索引:ALTER INDEX..REBUILD ONLINE vs ALTER INDEX..REBUILD
- 【java】itoo项目实战之常见问题一
- 出色的移动线框图设计工具
- 链表---约瑟夫问题
- ***1240 Ivan的阿柒工作室 解题报告
- 典型相关分析
- 选出需要rebuild的索引
- 【第10篇】通过json-lib的JsonConfig去读取对象数据json
- Linux常用命令-网络
- 简单的php自定义错误日志
- jquery 获取 html 标签的 class 值
- Foundation框架(2)——读取文件中的字符串、把字符串写入文件中
- android asmack 注册 登陆 聊天 多人聊天室 文件传输
- ie不支持getElementsByClassName,所以要自己实现获取类名为className的所有元素。
- 程序异常监视脚本