効率低下のINDEX検索

INDEX効率が悪くなったINDEXを調べ、再構築する

INDEXは変更の場合も更新ではなく、既存データを削除し、新しく追加を行う。そのため、INDEXを更新したり削除すると、INDEX領域内に削除されて空きになる部分が発生する。
サンプルでは一般INDEXを検索し、20％以上削除されたINDEXに対して再作成させ、INDEXを修復する様にしている。

declare
  W_INX_NM varchar2(1000);
  W_DEL_PER number;

  cursor CUR1 is
  select INDEX_NAME
  from user_indexes
  where table_owner = 'orcl'
  and INDEX_TYPE = 'NORMAL';

  cursor CUR2 is
  select (del_lf_rows/lf_rows)*100 DEL_PERSENT
  from index_stats
  where lf_rows > 0;

begin
  open CUR1;
    loop
      fetch CUR1 into W_IDX_NM;
      exit when CUR1%notfound;
      execute immediate 'analyze index ' || W_IDX_NM || ' validate structure';

      W_DEL_PER := 0;
      open CUR2;
      fetch CUR2 into W_DEL_PER;
      close CUR2;

      --削除率が20％を超えたら再構築する
      if W_DEL_PER > 20 then
        dbms_output.put_line('alter index ' || W_IDX_NM || ' rebuild nologging online;');
      end if;
    end loop;
  close CUR1;
end;

1. INDEXの削除率が20％を超える場合、INDEX再構築が望ましくなる
2. 関連URL:http://www.shift-the-oracle.com/alter-index/alter-index-rebuild.html