インデックスのリビルドは必要か（Oracle12cR2）

# インデックスのリビルドは必要か（Oracle12cR2）

一般にデータの更新が多いテーブルはインデックスが断片化するので、一定期間ごとにインデックスをリビルドするのが良いと言われています。

また、Oracleの資料では以下のように記載されています。

>HEIGHT が4以上でDEL_LF_ROW/LF_ROWSが0.2を超える場合は効率が悪いので、索引の再構築を行うことを検討して下さい

- [津島博士のパフォーマンス講座　第6回 パフォーマンスの基礎である索引について](https://www.oracle.com/technetwork/jp/database/articles/tsushima/tsm06-1598252-ja.html)

【検討】というは、リビルドするかどうかはケースバイケースということかと解釈しています。

一般にデータの更新が多いテーブルはインデックスをリビルドしたほうが良いのか簡単な検証をしてみたいと思います。
（今更のネタですが、手を動かしてみるのが大事です。）

データの更新が多いといっても色々なケースがあると思いますが、今回は以下の2ケースで試してみます（最初からトーンダウンです）。

(ケースA) キーが単調増加で、古いデータがまとめて削除されるケース
(ケースB) キーが単調増加で、古いデータが削除されるが一部のデータは残るケース


# テスト用のテーブルを作成する

まず、テスト用のテーブルとインデックスを作成します。
レコードは最初に30万件挿入しています。

```console
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"を実行します。
以下は実行例です。

```console
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を値を変えながら実行していきます。

```console
削除の場合
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を値を変えて実行していきます。

```console
削除の場合（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を指定すると、ブロックを再利用するために、索引ブロックの内容を空きブロックにマージできます(可能な場合)。 

- [SQL Language Reference Release 12.2 - 10.15 ALTER INDEX](https://docs.oracle.com/cd/E82638_01/sqlrf/ALTER-INDEX.html#GUID-D8F648E7-8C07-4C89-BB71-862512536558)

インデックスの結合は以下のように実行します。結合により、リーフブロックが結合され、アクセス効率が良くなります。

```
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


# 参考

- [津島博士のパフォーマンス講座　第6回 パフォーマンスの基礎である索引について](https://www.oracle.com/technetwork/jp/database/articles/tsushima/tsm06-1598252-ja.html)

- [SQL Language Reference Release 12.2 - 12.10 ANALYZE](https://docs.oracle.com/cd/E82638_01/sqlrf/ANALYZE.html#GUID-535CE98E-2359-4147-839F-DCB3772C1B0E)

- [SQL Language Reference Release 12.2 - 10.15 ALTER INDEX](https://docs.oracle.com/cd/E82638_01/sqlrf/ALTER-INDEX.html#GUID-D8F648E7-8C07-4C89-BB71-862512536558)