リーフ分割検証：意外と増えていたリーフブロック！：おら! オラ! Oracle再検証 ＠IT出張所（2）（4/4 ページ）
サイジングに頭を悩ませている技術者の皆さん、 インデックスのリーフ分割はどうして発生するのか、知りたくありませんか？ 今回はリーフ分割の増え方を徹底的に検証します（編集部）
ブロックの最大値を挿入
次に、リーフブロック1（leaf -1）の最大値を確認し、それより大きな値をブロックに格納してみましょう。
SQL> SELECT DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(46138039) as "FILE_ID(LEAFBLOCK-1)",
2 DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(46138039) as "BLOCK_ID(LEAFBLOCK-1)"
3 FROM dual;
FILE_ID(LEAFBLOCK-1) BLOCK_ID(LEAFBLOCK-1)
-------------------- ---------------------
11 695
alter system dump datafile 11 block 695;
row#107[1858] flag: ----S-, lock: 2, len=15
col 0; len 5; (5): c4 0b 01 0b 51
col 1; len 6; (6): 02 80 03 86 00 05
row#108[1873] flag: ----S-, lock: 2, len=15
col 0; len 5; (5): c4 0b 01 0b 5b
col 1; len 6; (6): 02 80 03 86 00 06
col0のc4以降の0b 01 0b 5bが実際の値であり、10進数に直すと以下になります。
0b 01 0b 5b ⇒ 11 01 11 91
そして、それぞれの値を-1した値が実際の値なので、実際に格納されている値は、「10001090」ということになります。つまり、「10001090」より大きな値を挿入することで、リーフブロック1（leaf -1）の最大値を挿入することができます。念のため、次ブロックであるリーフブロック2（leaf 0）の最小値も見ておきましょう。
SQL> SELECT DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(46138022) as "FILE_ID(LEAFBLOCK0)",
2 DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(46138022) as "BLOCK_ID(LEAFBLOCK0)"
3 FROM dual;
FILE_ID(LEAFBLOCK0) BLOCK_ID(LEAFBLOCK0)
------------------- --------------------
11 678
SQL> alter system dump datafile 11 block 678;
row#0[264] flag: ----S-, lock: 2, len=14
col 0; len 4; (4): c4 0b 01 0c
col 1; len 6; (6): 02 80 03 86 00 07
row#1[278] flag: ----S-, lock: 2, len=15
col 0; len 5; (5): c4 0b 01 0c 0b
col 1; len 6; (6): 02 80 03 86 00 08
0b 01 0c ⇒ 11 01 12
なぜか最後の00が省略されているようですが、各値に-1をして00を追加すると「10001100」となります。
ということで、リーフブロック1（leaf -1）の最大値である「10001091」を挿入して、その結果を確認してみることにしましょう。
SQL> INSERT INTO INDEX_TEST VALUES(10001091,10001091,'SCOTT','ENGINEER',9999,SYSDATE,1024,100 ); SQL> COMMIT; SQL> ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME TREEDUMP LEVEL 73010'; ----- begin tree dump branch: 0x2c002a5 46138021 (0: nrow: 4, level: 1) leaf: 0x2c002b7 46138039 (-1: nrow: 59 rrow: 59) leaf: 0x2c002a9 46138025 (0: nrow: 51 rrow: 51) leaf: 0x2c002a6 46138022 (1: nrow: 109 rrow: 109) leaf: 0x2c002a7 46138023 (2: nrow: 82 rrow: 82) ----- end tree dump
上記を見ると、leaf -1とleaf 0がそれぞれ59行、51行と110行を約半分に分割して格納しており、リーフ分割が発生したことが分かります。
つまり、リーフブロックの最大値を挿入してもリーフ分割は発生してしまうということであり、逆にいうと、リーフ分割が発生しないケースは、そのインデックス内に格納されている最大値より大きい値を挿入した場合のみということがいえます。
極端な話ですが、これは1行目にそのカラムの最大値を挿入してしまうと、そのあとに追加されるブロックは必ずリーフ分割してしまうということです。
SQL> INSERT INTO INDEX_TEST VALUES(99999999,99999999,'SCOTT','ENGINEER',9999,SYSDATE,1024,100 ); SQL> COMMIT; 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 / ----- begin tree dump branch: 0x2c002a5 46138021 (0: nrow: 5, level: 1) leaf: 0x2c002b7 46138039 (-1: nrow: 59 rrow: 59) leaf: 0x2c002a6 46138022 (0: nrow: 59 rrow: 59) leaf: 0x2c002a7 46138023 (1: nrow: 59 rrow: 59) leaf: 0x2c002a9 46138025 (2: nrow: 52 rrow: 52) leaf: 0x2c002aa 46138026 (3: nrow: 72 rrow: 72) ----- end tree dump
上記のように、項目の最大値を格納したあとにINSERTしたところ、すべてのブロックで109行より少ない行数で格納されています。そして、ブロック数も普通にINSERTしたときはリーフブロックは3だったのに最大値を挿入したあとだと5に増えていることが分かります。
つまり、最大値を挿入したあとでそれより小さな値を挿入した場合、インデックスのサイズは通常時と比較して、単純計算で約1.6倍【注2】大きくなってしまったということになります。当該環境ではブロックサイズが2KBと少し小さいこともありますが、それを考慮しても1.6倍は大きな差といえます。
この数値は今回のテストでの結果であり、すべての環境でこのような結果になるわけではありません。
まとめ
Oracleの物理設計ではよくインデックスのサイズを大きめに見積もりますが、今回の検証結果を理解すれば、なぜインデックスサイズを大きくしなければいけないのかを理解できると思います。
逆にこの点を考慮していないと、インデックスのサイズが予想以上に大きくなってしまい、最悪の場合、領域不足ということにもなります。そうならないよう、インデックスのサイズを決めるときは、今回の検証のことを思い出していただければと思います。
今回は検証環境まで作成しました。次回はいよいよインデックスによるパフォーマンス劣化について検証をしようと思います。
筆者紹介
内山 義夫（うちやま よしお）
内山 義夫は細い目の奥にキラリと光る鋭い洞察力で、数多くのパフォーマンストラブルを解決してきたデータベースコンサルタント。コンサルティング業務を行うかたわら、現場で必要とされる知識を追求する「おら！ オラ！ Oracle - どっぷり検証生活」の制作も行っている。
Copyright © ITmedia, Inc. All Rights Reserved.