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

» 2010年01月18日 00時00分公開

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

ブロックの最大値を挿入

　次に、リーフブロック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

最大値の確認（leaf -1）

　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

最小値（leaf 0）の確認

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行を約半分に分割して格納しており、リーフ分割が発生したことが分かります。

　つまり、リーフブロックの最大値を挿入してもリーフ分割は発生してしまうということであり、逆にいうと、リーフ分割が発生しないケースは、そのインデックス内に格納されている最大値より大きい値を挿入した場合のみということがいえます。

図4　値と値の間の値を挿入するとリーフ分割が発生

　極端な話ですが、これは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

カラムの最大値を挿入後、300件のデータを挿入

　上記のように、項目の最大値を格納したあとにINSERTしたところ、すべてのブロックで109行より少ない行数で格納されています。そして、ブロック数も普通にINSERTしたときはリーフブロックは3だったのに最大値を挿入したあとだと5に増えていることが分かります。

　つまり、最大値を挿入したあとでそれより小さな値を挿入した場合、インデックスのサイズは通常時と比較して、単純計算で約1.6倍【注2】大きくなってしまったということになります。当該環境ではブロックサイズが2KBと少し小さいこともありますが、それを考慮しても1.6倍は大きな差といえます。

【注2】
この数値は今回のテストでの結果であり、すべての環境でこのような結果になるわけではありません。