【问题描述】

  大部分情况下，ORACLE数据库的索引，并不需要人为干预，但若表在短时间内大幅删除数据，致使表出现大量的空块，可以考虑重建其索引，

【解决方案】

1  使用SQLPLUS工具（建议），以业务账号身份登录，建辅助表：

CREATE TABLE USER_INDEX_STATS

(ANALYZED_DATE                      DATE DEFAULT SYSDATE,

 REBUILD_DATE                       DATE,

 TABLE_NAME                         VARCHAR2(30), 

 INDEX_NAME                         VARCHAR2(30),

 HEIGHT                             NUMBER,

 BLOCKS                             NUMBER,

 PARTITION_NAME                     VARCHAR2(30),

 LF_ROWS                            NUMBER,

 LF_BLKS                            NUMBER,

 LF_ROWS_LEN                        NUMBER,

 LF_BLK_LEN                         NUMBER,

 BR_ROWS                            NUMBER,

 BR_BLKS                            NUMBER,

 BR_ROWS_LEN                        NUMBER,

 BR_BLK_LEN                         NUMBER,

 DEL_LF_ROWS                        NUMBER,

 DEL_LF_ROWS_LEN                    NUMBER,

 DISTINCT_KEYS                      NUMBER,

 MOST_REPEATED_KEY                  NUMBER,

 BTREE_SPACE                        NUMBER,

 USED_SPACE                         NUMBER,

 PCT_USED                           NUMBER,

 ROWS_PER_KEY                       NUMBER,

 BLKS_GETS_PER_ACCESS               NUMBER,

 PRE_ROWS                           NUMBER,

 PRE_ROWS_LEN                       NUMBER,

 OPT_CMPR_COUNT                     NUMBER,

 OPT_CMPR_PCTSAVE                   NUMBER,

 COMMENTS                           VARCHAR2(300))

/

2  执行下面分析、重建索引的脚本，

set serverout on;

declare

  v_sql          varchar2(300);

  v_Errmsg       varchar2(300);

begin

  delete from user_index_stats;

  commit;

  for i in (select table_name, index_name, num_rows from user_indexes where index_type like '%NORMAL%' and partitioned='NO' 

            and not (regexp_like(table_name,'^TMP[[:alnum:]]{27}$')) and temporary='N' and num_rows > 0 order by 3 desc) loop

    begin

      v_sql := 'analyze index '||i.index_name||' validate structure ';

      execute immediate v_sql;

      insert into user_index_stats(table_name, height, blocks, index_name, partition_name, 

                                   lf_rows, lf_blks, lf_rows_len, lf_blk_len, br_rows, br_blks,

                                   br_rows_len, br_blk_len, del_lf_rows, del_lf_rows_len, distinct_keys, 

                                   most_repeated_key, btree_space, used_space, pct_used, rows_per_key, blks_gets_per_access, 

                                   pre_rows, pre_rows_len, opt_cmpr_count, opt_cmpr_pctsave)

      select i.table_name, height, blocks, name, partition_name, 

             lf_rows, lf_blks, lf_rows_len, lf_blk_len, br_rows, br_blks,

             br_rows_len, br_blk_len, del_lf_rows, del_lf_rows_len, distinct_keys, 

             most_repeated_key, btree_space, used_space, pct_used, rows_per_key, blks_gets_per_access, 

             pre_rows, pre_rows_len, opt_cmpr_count, opt_cmpr_pctsave from index_stats;

    exception when others then 

        v_Errmsg :=substr(dbms_utility.format_error_backtrace,1,150);

        v_Errmsg :=v_Errmsg||substr(dbms_utility.format_error_stack,1,150);

        insert into user_index_stats(table_name, index_name, comments) values (i.table_name, i.index_name, v_Errmsg); 

    end;

  end loop;

  commit;

  for i in (select table_name, index_name, round(del_lf_rows/lf_rows,2) del_pct, blocks, lf_rows, lf_rows_len,

                   del_lf_rows, del_lf_rows_len, (lf_rows - del_lf_rows) gap_num_rows

            from user_index_stats where height >= 3 and (lf_rows > 0 and del_lf_rows/lf_rows >= 0.2) 

            order by height desc, del_lf_rows/lf_rows desc) loop

    begin

      v_sql := 'alter index '||i.index_name||' rebuild online parallel 4';

      execute immediate v_sql;

      v_sql := 'alter index '||i.index_name||' noparallel';

      execute immediate v_sql;

      --dbms_output.put_line(i.index_name);

      update user_index_stats set rebuild_date = sysdate where index_name = i.index_name;

      commit;

    exception when others then 

      v_Errmsg :=substr(dbms_utility.format_error_backtrace,1,150);

      v_Errmsg :=v_Errmsg||substr(dbms_utility.format_error_stack,1,150);

      update user_index_stats set comments = v_Errmsg where index_name = i.index_name; end;

      commit;

  end loop;

end;

/

【注意事项】

1、选择在系统空闲时间执行脚本，避免在业务高峰期操作，也避免锁堵塞等原因导致重建失败。

2、可以先执行分析代码（注释掉第2个FOR 循环），待执行完，确认分析结果无误后，再注释掉第1个FOR循环，

单独执行第2个FOR循环。

3、第2个FOR循环，只对 height=3，删除行数占索引总行数（del_lf_rows/lf_rows）20%以上的索引重建，这个条件

可以自行调整。

4、索引被重建后，有个  rebuild_date 字段，记录重建完的时间，没有此值的，说明没有被重建。

5、脚本没有处理分区索引。