選出需要rebuild的索引

自己編寫的一個指令碼，該指令碼的主要功能是列出需要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 過程已成功完成。

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16978544/viewspace-705699/，如需轉載，請註明出處，否則將追究法律責任。