关于REBUILD 大量INDEX问题

kail

希望这个脚本有用，不过如果索引很多很大，执行的时间很长!

This script determines whether an index is a good candidate for a rebuild or for
a bitmap index.  All indexes for a given schema or for a subset of schema's are
analyzed (except indexes under SYS and SYSTEM)

REM =============================================================
REM
REM                         rebuild_indx.sql
REM
REM  Copyright (c) Oracle Software, 1998 - 2000
REM
REM  Author  : Jurgen Schelfhout
REM
REM  The sample program in this article is provided for educational
REM  purposes only and is NOT supported by Oracle Support Services.
REM  It has been tested internally, however, and works as documented.
REM  We do not guarantee that it will work for you, so be sure to test
REM  it in your environment before relying on it.
REM
REM  This script will analyze all the indexes for a given schema
REM  or for a subset of schema's. After this the dynamic view
REM  index_stats is consulted to see if an index is a good
REM  candidate for a rebuild or for a bitmap index.
REM
REM  Database Version : 7.3.X and above.
REM
REM =============================================================

prompt
ACCEPT spoolfile CHAR prompt 'Output-file : ';
ACCEPT schema CHAR prompt 'Schema name (% allowed) : ';
prompt
prompt
prompt Rebuild the index when :
prompt   - deleted entries represent 20% or more of the current entries
prompt   - the index depth is more then 4 levels.
prompt Possible candidate for bitmap index :
prompt   - when distinctiveness is more than 99%
prompt
spool &spoolfile

set serveroutput on
set verify off
declare
c_name        INTEGER;
ignore        INTEGER;
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;
cursor c_indx is
  select owner, table_name, index_name
  from dba_indexes
  where owner like upper('&schema')
    and owner not in ('SYS','SYSTEM');
begin
dbms_output.enable (1000000);
dbms_output.put_line ('Owner           Index Name                              % Deleted Entries Blevel Distinctiveness');
dbms_output.put_line ('--------------- --------------------------------------- ----------------- ------ ---------------');

c_name := DBMS_SQL.OPEN_CURSOR;
for r_indx in c_indx loop
  DBMS_SQL.PARSE(c_name,'analyze index ' || r_indx.owner || '.' ||
                 r_indx.index_name || ' validate structure',DBMS_SQL.NATIVE);
  ignore := DBMS_SQL.EXECUTE(c_name);

  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;
--
-- Index is considered as candidate for rebuild when :
--   - when deleted entries represent 20% or more of the current entries
--   - when the index depth is more then 4 levels.(height starts counting from 1 so > 5)
-- Index is (possible) candidate for a bitmap index when :
--   - distinctiveness is more than 99%
--
  if ( height > 5 ) OR ( (del_lf_rows/lf_rows) > 0.2 ) then
    dbms_output.put_line (rpad(r_indx.owner,16,' ') || rpad(r_indx.index_name,40,' ') ||
                          lpad(round((del_lf_rows/lf_rows)*100,3),17,' ') ||
                          lpad(height-1,7,' ') || lpad(round((lf_rows-distinct_keys)*100/lf_rows,3),16,' '));
  end if;

end loop;
DBMS_SQL.CLOSE_CURSOR(c_name);
end;
/

spool off
set verify on

胡一刀

最初由 baikejia 发布
[B]如果表很大，要做很久。
上班的时候做，会导致别人无法使用那个索引，影响很大的。
还是下班做吧 [/B]

使用online参数重建索引，不影响其他用户使用吧？就是对空间需求大些，
惭愧不知道啥版本以上才有，不过8i上就有了

sundog315

在给你个过程，首先去判断碎片率，如果大于2再进行重建。支持分区及子分区。

create or replace procedure CHECK_INDEX is
  percent                       number;               --碎片率
  chained                       number;               --碎片
  indxname                      varchar2(100);        --索引名称
  partname                      varchar2(100);        --partition_name
  segtype                       varchar2(100);        --segment_type
  tablespace                    varchar2(100);        --表空间
  
  cursor cSele_indx is
    select t.index_name,t.tablespace_name
      from user_indexes t
     where t.temporary='N';
  
  cursor cSele_seg is
    select t.partition_name,t.segment_type,t.tablespace_name
      from dba_segments t
     where t.segment_name=indxname;
begin
  open cSele_indx;
  fetch cSele_indx into indxname,tablespace;
  
  while cSele_indx%found loop
    execute immediate 'analyze index '||indxname||' validate structure';  --进行分析
   
    select count(*)
      into chained
      from index_stats t
     where t.lf_rows_len>0;
   
    if chained>0 then                                                     
      select Round((t.del_lf_rows_len/t.lf_rows_len)*100,2)
        into percent                                                      --检查碎片率
        from index_stats t
       where t.lf_rows_len>0;
    end if;
   
    if percent>2 then                                                     --如碎片率大于2%时进行索引重建
                                                                          --可以根据实际情况对这个比率进行修改
      dbms_output.put_line(indxname||':'||to_char(percent));
            
      open cSele_seg;
      fetch cSele_seg into partname,segtype,tablespace;
      while cSele_seg%found loop
        if segtype='INDEX' then                                           --未分区索引
          execute immediate 'alter index '||indxname||' rebuild tablespace '||tablespace||' nologging';
        end if;
        if segtype='INDEX PARTITION' then                                 --分区索引
          execute immediate 'alter index '||indxname||' rebuild partition '||partname||' tablespace '||tablespace||' parallel (degree 4)';
        end if;
        if segtype='INDEX SUBPARTITION' then                              --组合分区索引
          execute immediate 'alter index '||indxname||' rebuild subpartition '||partname||' tablespace '||tablespace||' parallel (degree 4)';
        end if;
        
        fetch cSele_seg into partname,segtype,tablespace;
      end loop;
      
      close cSele_seg;
    end if;
   
    fetch cSele_indx into indxname,tablespace;
  end loop;
  
  close cSele_indx;

end CHECK_INDEX;

baikejia

最初由 胡一刀 发布
[B]

使用online参数重建索引，不影响其他用户使用吧？就是对空间需求大些，
惭愧不知道啥版本以上才有，不过8i上就有了 [/B]

使用online参数重建，会慢很多的。
最可怕的是，如果你的重建索引的sql被杀掉了，那么你只能重启数据库了。你要是想重建的话，会报告说“正在重建”。这个oracle的一个bug