インデックスのリビルドは必要か（Oracle12cR2）
一般にデータの更新が多いテーブルはインデックスが断片化するので、一定期間ごとにインデックスをリビルドするのが良いと言われています。
また、Oracleの資料では以下のように記載されています。
HEIGHT が4以上でDEL_LF_ROW/LF_ROWSが0.2を超える場合は効率が悪いので、索引の再構築を行うことを検討して下さい
【検討】というは、リビルドするかどうかはケースバイケースということかと解釈しています。
一般にデータの更新が多いテーブルはインデックスをリビルドしたほうが良いのか簡単な検証をしてみたいと思います。
（今更のネタですが、手を動かしてみるのが大事です。）
データの更新が多いといっても色々なケースがあると思いますが、今回は以下の2ケースで試してみます（最初からトーンダウンです）。
(ケースA) キーが単調増加で、古いデータがまとめて削除されるケース
(ケースB) キーが単調増加で、古いデータが削除されるが一部のデータは残るケース
テスト用のテーブルを作成する
まず、テスト用のテーブルとインデックスを作成します。
レコードは最初に30万件挿入しています。
SQL> create table test_rebuild (id number, name varchar2(50));
表が作成されました。
SQL> create index idx_test_rebuild01 on test_rebuild(id);
索引が作成されました。
SQL> insert into test_rebuild select rownum, 'TEST DATA' || rownum from dual connect by level <= 300000;
300000行が作成されました。
SQL> commit;
コミットが完了しました。
検証方法
ケースAの場合、30万レコードが格納されてテーブルに対して、単調増加で10万件挿入、古い10万件を削除という操作を複数回実行して、インデックスのBツリーの高さとリーフブロック数等の値の変化を見ていきます。同様の作業をレコードの削除方法だけ変えてケースBでも実行します。
取得する情報はインデックスの統計情報です。
インデックスの統計情報を取得するためには、"analyze index"を実行します。
以下は実行例です。
SQL> analyze index idx_test_rebuild01 validate structure;
索引が分析されました。
SQL> select height, blocks, lf_rows, lf_blks, del_lf_rows, pct_used, distinct_keys from index_stats;
なお、テーブルに表ロックがかかるので注意が必要です。
これ以外にもTree Dumpやブロックのダンプとかも取得してみたほうが良いかもしれませんが、読み解くのが大変なので統計情報だけで済ませます（ぉ
ケースAの検証結果
レコードの挿入と削除は以下のDMLを値を変えながら実行していきます。
削除の場合
delete from test_rebuild where id between 1 and 100000;
commit;
挿入の場合
DECLARE
v_no number(8) := 100000;
BEGIN
for i in 1..100000 loop
insert into test_rebuild values(v_no + i, 'TEST DATA' || i);
end loop;
commit;
END;
/
実際に挿入と削除を繰り返した場合のインデックスの統計情報を以下に示します。
(1) 30万レコード挿入直後
HEIGHT BLOCKS LF_ROWS LF_BLKS DEL_LF_ROWS PCT_USED DISTINCT_KEYS
---------- ---------- ---------- ---------- ----------- ---------- -------------
2 640 300000 599 0 100 300000
※PCT_FREEは10%ですが、単調増加で挿入しているのでPCT_USEDは100%になってしまうようです。
(2) 10万レコード削除後（1～10万の間）
HEIGHT BLOCKS LF_ROWS LF_BLKS DEL_LF_ROWS PCT_USED DISTINCT_KEYS
---------- ---------- ---------- ---------- ----------- ---------- -------------
2 640 300000 599 100000 100 300000
※DEL_LF_ROWSが100000になりました。
(3) 10万レコード挿入直後（30万台）
HEIGHT BLOCKS LF_ROWS LF_BLKS DEL_LF_ROWS PCT_USED DISTINCT_KEYS
---------- ---------- ---------- ---------- ----------- ---------- -------------
2 640 300000 601 0 100 300000
※LF_BLKSは変わらず。
(4) 10万レコード削除後（10～20万の間）
HEIGHT BLOCKS LF_ROWS LF_BLKS DEL_LF_ROWS PCT_USED DISTINCT_KEYS
---------- ---------- ---------- ---------- ----------- ---------- -------------
2 640 300000 601 100000 100 300000
(5) 10万レコード挿入直後（40万台）
HEIGHT BLOCKS LF_ROWS LF_BLKS DEL_LF_ROWS PCT_USED DISTINCT_KEYS
---------- ---------- ---------- ---------- ----------- ---------- -------------
2 640 306374 613 6374 100 306374
※LF_BLKSは変わらず。DEL_LF_ROWSが残っている理由は不明。
(6) 10万レコード削除後（20～30万の間）
HEIGHT BLOCKS LF_ROWS LF_BLKS DEL_LF_ROWS PCT_USED DISTINCT_KEYS
---------- ---------- ---------- ---------- ----------- ---------- -------------
2 640 306000 613 106000 100 306000
(7) 10万レコード挿入直後（50万台）
HEIGHT BLOCKS LF_ROWS LF_BLKS DEL_LF_ROWS PCT_USED DISTINCT_KEYS
---------- ---------- ---------- ---------- ----------- ---------- -------------
2 640 305500 613 5500 100 305500
※LF_BLKSは変わらず。
30万件から合計で30万件挿入、30万件の削除を実行しましたが、リーフブロック数は変わっていません。これは削除した約30万件分のデータが入っていたリーフブロックが再利用されたということになります。そのため、インデックスのリビルドは不要です。
ここで、DBMS_STATSで統計情報を取得し、user_indexesテーブルを検索してみました。
BLEVEL LEAF_BLOCKS NUM_ROWS CLUSTERING_FACTOR
---------- ----------- ---------- -----------------
1 601 300000 1031
※LEAF_BLOCKSの数値が違いますが、何回も操作をやり直したので、どこかで間違えたかも・・・
ケースBの検証結果
レコードの挿入と削除は以下のDMLを値を変えて実行していきます。
削除の場合（100,000件中99,000件削除しています）
delete from test_rebuild where id between 1 and 100000 and mod(id, 100) != 0;
commit;
挿入の場合
DECLARE
v_no number(8) := 100000;
BEGIN
for i in 1..100000 loop
insert into test_rebuild values(v_no + i, 'TEST DATA' || i);
end loop;
commit;
END;
/
実際に挿入と削除を繰り返した場合のインデックスの統計情報を以下に示します。
(1) 30万レコード挿入直後
HEIGHT BLOCKS LF_ROWS LF_BLKS DEL_LF_ROWS PCT_USED DISTINCT_KEYS
---------- ---------- ---------- ---------- ----------- ---------- -------------
2 640 300000 599 0 100 300000
(2) 9.9万レコード削除後（1～10万の間）
HEIGHT BLOCKS LF_ROWS LF_BLKS DEL_LF_ROWS PCT_USED DISTINCT_KEYS
---------- ---------- ---------- ---------- ----------- ---------- -------------
2 640 300000 599 99000 100 300000
※DEL_LF_ROWSが99000になりました。
(3) 10万レコード挿入直後（30万台）
HEIGHT BLOCKS LF_ROWS LF_BLKS DEL_LF_ROWS PCT_USED DISTINCT_KEYS
---------- ---------- ---------- ---------- ----------- ---------- -------------
3 896 400000 799 99000 100 400000
※HEIGHTが2->3になり、LF_BLKSも599から799に上がっています。
(4) 9.9万レコード削除後（10～20万の間）
HEIGHT BLOCKS LF_ROWS LF_BLKS DEL_LF_ROWS PCT_USED DISTINCT_KEYS
---------- ---------- ---------- ---------- ----------- ---------- -------------
3 896 399630 799 197630 100 399630
(5) 10万レコード挿入直後（40万台）
HEIGHT BLOCKS LF_ROWS LF_BLKS DEL_LF_ROWS PCT_USED DISTINCT_KEYS
---------- ---------- ---------- ---------- ----------- ---------- -------------
3 1152 499630 999 197630 100 499630
※LF_BLKSが799から999に上がっています。
(6) 9.9万レコード削除後（20～30万の間）
HEIGHT BLOCKS LF_ROWS LF_BLKS DEL_LF_ROWS PCT_USED DISTINCT_KEYS
---------- ---------- ---------- ---------- ----------- ---------- -------------
3 1152 499260 999 296260 100 499260
(7) 10万レコード挿入直後（50万台）
HEIGHT BLOCKS LF_ROWS LF_BLKS DEL_LF_ROWS PCT_USED DISTINCT_KEYS
---------- ---------- ---------- ---------- ----------- ---------- -------------
3 1280 599260 1199 296260 100 599260
※LF_BLKSが999から1199に上がっています。
最終的にHEIGHTは2から3、LF_BLKS（リーフブロック数）は599から1199まで上がりました。
30万件から合計で30万件挿入、約30万件の削除になりますのでレコード数はほとんど変わっていませんが、リーフブロック数は約2倍になっています。これは削除した約30万件分のデータが入っていたリーフブロックが再利用されていないということになります。
ここで、DBMS_STATSで統計情報を取得し、user_indexesテーブルを検索してみました。
exec DBMS_STATS.GATHER_TABLE_STATS(ownname => 'HR', tabname => 'TEST_REBUILD', cascade => TRUE);
BLEVEL LEAF_BLOCKS NUM_ROWS CLUSTERING_FACTOR
---------- ----------- ---------- -----------------
2 1199 303000 2100
LEAF_BLOCKSとNUM_ROWSは想定通りです。BLEVELが2になっていますが、リーフブロックを除いた（HEIGHT-1）数値になっているようです。
ここでインデックスのリビルドの目安を見てみます。
HEIGHT が4以上でDEL_LF_ROW/LF_ROWSが0.2を超える場合は効率が悪いので、索引の再構築を行うことを検討して下さい
HEIGHTはレコードが30万件と少ないので3で下回っていますが、DEL_LF_ROW/LF_ROWSは0.49と高い値になっており、インデックスのリビルドを実行すれば効果がでそうな結果となりました。
しかし、その前にインデックスのリビルド（再構成）ではなく、インデックスの結合（COALESCE）を試してみたいと思います。
Oracleのドキュメントには以下のように記載されています。
COALESCEを指定すると、ブロックを再利用するために、索引ブロックの内容を空きブロックにマージできます(可能な場合)。
インデックスの結合は以下のように実行します。結合により、リーフブロックが結合され、アクセス効率が良くなります。
alter index idx_test_rebuild01 coalesce;
インデックスリビルドと比較し、以下の特徴があります。
- ロックがかからないのでオンラインで実行できる。
- リビルドよりも負荷が低い。
※断片化の状況によっては大量のREDOが生成される？
なお、COALESCEを指定するのは、ALTER INDEX ... SHRINK SPACE COMPACTとするのと同じ意味になります。
今回の結果ではレコードが削除された分のブロック内の使用率が下がっているはずなので、結合の効果がでるはずです。
結合（COALESCE）の実行結果は以下のとおりです。
BLEVEL LEAF_BLOCKS NUM_ROWS CLUSTERING_FACTOR
---------- ----------- ---------- -----------------
2 607 303000 2100
HEIGHT BLOCKS LF_ROWS LF_BLKS DEL_LF_ROWS PCT_USED DISTINCT_KEYS
---------- ---------- ---------- ---------- ----------- ---------- -------------
3 1280 303000 607 0 100 303000
リーフブロック（LF_BLKS）が大幅に減っています。一方、解放はされないなのでブロック数は変わらないです。ただし、再利用はされるためテーブルの利用用途（再利用する場合）によってはこの状態で問題ないと思われます。
なお、Shrink Spaceも実行すると以下のようにブロック数（BLOCKS）も大幅に減ります。
alter index idx_test_rebuild01 shrink space;
HEIGHT BLOCKS LF_ROWS LF_BLKS DEL_LF_ROWS PCT_USED DISTINCT_KEYS
---------- ---------- ---------- ---------- ----------- ---------- -------------
3 632 303000 607 0 100 303000
結合だけで効果がでているようにも思えますが、続いてインデックスのリビルドの場合も試してみます。
alter index idx_test_rebuild01 rebuild;
※ONLINEで実行する場合は、Enterprise Editionが必要です。
BLEVEL LEAF_BLOCKS NUM_ROWS CLUSTERING_FACTOR
---------- ----------- ---------- -----------------
2 675 303000 2100
HEIGHT BLOCKS LF_ROWS LF_BLKS DEL_LF_ROWS PCT_USED DISTINCT_KEYS
---------- ---------- ---------- ---------- ----------- ---------- -------------
3 768 303000 675 0 90 303000
当然ですがBLOCKSも大幅に減少しています。また、PCT_USEDが100%から90%になったのもリビルドの効果ですね。
結合（COALESCE）とリビルドのいずれが良いかは今回の情報だけでは判断できないかと思います（自信ないけど）。
結論
(ケースA) キーが単調増加で、古いデータがまとめて削除されるケース
(ケースB) キーが単調増加で、古いデータが削除されるが一部のデータは残るケース
- ケースAの場合はリーフブロックが再利用されるため断片化せず、インデックスのリビルドは必要ない。
- ケースBの場合はリーフブロックが再利用されないため断片化される。そのため、インデックスのリビルドが必要。※検証できていないが断片化率によってはリビルドまでは必要でなく、結合（COALESCE）だけで十分と思われる。
今回は単調増加なのでケースBはリビルドした方が良いかもという結論になっていますが、単調増加でなければ削除された領域は再利用されるため、実際にはリビルドしないで良いケースの方が多いかと思います。逆キーインデックスの場合ですとあまり必要ないかなと思います（検証していませんが）
なお、シンプルで綺麗なデータで試しているので、データの状況によってはこの結果のとおりではないこともあり得るため参考として考えたほうが良い。
また、今回は挿入・削除だけのケースだったので、以下のようなケースも気が向いたら試してみたいと思います。
- キーが単調増加でない場合
- キーの更新がある場合
- 逆キーインデックスの場合
- etc