リーフ分割検証：意外と増えていたリーフブロック！：おら! オラ! Oracle再検証 ＠IT出張所（2）（3/4 ページ）

サイジングに頭を悩ませている技術者の皆さん、 インデックスのリーフ分割はどうして発生するのか、知りたくありませんか？ 今回はリーフ分割の増え方を徹底的に検証します（編集部）

[内山義夫，株式会社インサイトテクノロジー]

手順2　テーブルのサイズを拡張させる

　手順2で、テーブルのサイズを拡張させるために90万件のデータを格納しています。これでINDEX_TESTテーブルには合計100万件のデータが格納されたことになります。

SQL> BEGIN
 2     FOR ii IN 1..9 LOOP
 3        FOR i IN 0..99999 LOOP
 4           INSERT INTO INDEX_TEST
 5           VALUES(10000000 + (i * 10) + ii,10000000 + (i * 10) + ii,'SCOTT','ENGINEER',9999,SYSDATE,1024,100 );
 6           IF MOD(i,10000) = 0 THEN
 7             COMMIT;
 8           END IF;
 9        END LOOP;
 10        COMMIT;
 11     END LOOP;
 12     COMMIT;
 13  END;
 14  /
 
 PL/SQL procedure successfully completed.

　10000001から11000000の間で、末尾が1から9の値が順に挿入され、合計100万件のデータが作成されました。手順2では手順1とは異なり、値と値の間の値を挿入しているため、リーフ分割が発生しています。ここで勘違いしやすいのは、手順2で挿入したすべての値でリーフ分割が発生していると思われがちですが、実際には一部のINSERT時にしかリーフ分割は発生していないということです。これは、リーフ分割が発生した直後はリーフブロック内に空き領域が作成されて、その空きに値が挿入されるためです（図2）。

図2　手順1から手順2までのブロックの遷移

手順3　データの削除

　最後に、99万件のデータを削除して1万件のデータを作成しましょう。

SQL> SQL> BEGIN
 2     FOR i IN 1..100000 LOOP
 3          DELETE FROM INDEX_TEST WHERE EMPNO1 = 10000000 + (i * 10);
 4     END LOOP;
 5     COMMIT;
 6  END;
 7  /
 
 PL/SQL procedure successfully completed.

SQL> SQL> BEGIN
   2     FOR ii IN 1..8 LOOP
   3        FOR i IN 0..99999 LOOP
   4          DELETE FROM INDEX_TEST WHERE EMPNO1 = 10000000 + (i * 10) + ii;
   5        END LOOP;
   6     END LOOP;
   7     COMMIT;
   8  END;
   9  /
   
   PL/SQL procedure successfully completed.

SQL> SQL> BEGIN
   2     FOR i IN 0..89999 LOOP
   3          DELETE FROM INDEX_TEST WHERE EMPNO1 = 10000000 + (i * 10) + 9;
   4     END LOOP;
   5     COMMIT;
   6  END;
   7  /
   
   PL/SQL procedure successfully completed.

手順3：データ削除（99万件）

　これで、99万件分の空き領域ができたことになります。挿入した順に削除して、最終的に最後に格納した末尾9のデータの1万件のみ格納された状態になっています。

環境作成時の疑問

　環境が作成できたので、早速検証！ といきたいところですが、今回の環境作成で疑問に思ったことがあります。それは、各リーフブロックの最大値を挿入した場合、リーフ分割されるのか、リーフブロックが追加されるのか、という点です（図3）。

図3　ブロックの最大値を挿入した場合の動き

　手順1では、リーフブロックの最後の値を挿入したときはリーフ分割は発生せず、新たなリーフブロックが追加されていました。では、図3のように、リーフブロック2がある状態でリーフブロック1の最後の値を挿入した場合、リーフ分割は発生するのでしょうか？

　この結果は非常に気になるところです。なぜなら、値の格納順によってはリーフ分割が大量に発生してしまう可能性があるからです。

　 ということで、少し横道にそれますが、今回は最後にこの点について検証をしてみることにします。パフォーマンスの検証については次回行います。

検証

　それでは、実際に検証してみます。検証は、先ほど実行した手順1のプロシージャを流用して確認しましょう。今回はいっぱいになったブロックが数ブロックあればよいので、データは300件に絞って挿入します。

SQL> BEGIN
 2     FOR i IN 1..300 LOOP
 3        INSERT INTO INDEX_TEST
 4        VALUES(10000000 + (i * 10),10000000 + (i * 10),'SCOTT','ENGINEER',9999,SYSDATE,1024,100 );
 5     END LOOP;
 6     COMMIT;
 7  END;
 8  /
 PL/SQL procedure successfully completed.

　データが挿入されました。それでは、ブロックの中身を見てみることにしましょう。TREEDUMPにて、インデックスの状態を確認します。

SQL> ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME TREEDUMP LEVEL 73010';
----- begin tree dump
branch: 0x2c002a5 46138021 (0: nrow: 3, level: 1)
   leaf: 0x2c002b7 46138039 (-1: nrow: 109 rrow: 109)
   leaf: 0x2c002a6 46138022 (0: nrow: 109 rrow: 109)
   leaf: 0x2c002a7 46138023 (1: nrow: 82 rrow: 82)
----- end tree dump

　上記から、挿入した300件のデータは3つのリーフブロックに格納されていることが分かります。