続・INDEXに関する検証 その6

<続・INDEXに関する検証 その6> ペンネーム モンキーターン

前回のメルマガで、空の Leaf_BLOCKにアクセスしてしまう原因は、Branch_BLOCK
が空の Leaf_BLOCKまでも管理の対象にしてしまっているためであることは理解
していただけたと思う。

今回は、Branch_BLOCKが Leaf_BLOCKを管理するというインデックスの構造によっ
て、思わぬところでリーフ・分割が発生してしまうという現象を、検証結果を
交えながら説明していこう。

— どっぷり検証生活 START! —

<<<検証手順>>>

(1)構成
テーブル名:EMP_TEST(EMPNO(インデックス・キー)以外はEMP表と同じ)
データ件数:EMPNO 1001~1123および 3001の計124件

(2)EMPNO = 3001を削除

(3)EMPNO = 2001を挿入

上記の手順で処理を行った際の、それぞれの TREEDUMPを比較しながら、リーフ・
分割が発生する様子を見て行こう。

<<<検証用テーブルとINDEXの作成>>>

1.ユーザー SCOTTでテーブル EMP_TESTという 124件のテーブルを作成
(EMPNO 1001~1123および 3001の計 124件を挿入)

ここで EMPNO 1001~1123および 3001の値を挿入した理由は、1Leaf_BLOCK
(先頭の Leaf_BLOCK)中に EMPNO 1001~1123を FULLに格納し(計算上、
1Leaf_BLOCK中に格納可能なキーの数が 123件(DEFAULT STORAGE句の設定
によって異なる)、もう1つの Leaf_BLOCK(2番目の Leaf_BLOCK)には、
EMPNO = 3001のみの 1件を格納したいためである。

2.テーブル EMP_TESTの項目 EMPNOに対して、EMP_TEST_INDEXという INDEX
を作成(INDEXは別の表領域に作成)

<<<結果>>>

Leaf_BLOCK(1)に EMPNO = 1001~1123(123件)
Leaf_BLOCK(2)に EMPNO = 3001(1件)

注:括弧の数字(1)及び(2)は、下記の TREEDUMPの結果を参照。

----- begin tree dump
branch: 0x2400003 37748739 (0: nrow: 2, level: 1)
leaf: 0x2400004 37748740 (-1: nrow: 123 rrow: 123) ---→(1)
leaf: 0x2400005 37748741 (0: nrow: 1 rrow: 1) --------→(2)
----- end tree dump

次に、テーブル内の EMPNO = 3001を削除する。

DELETE FROM EMP_TEST WHERE EMPNO = 3001 ;

----- begin tree dump
branch: 0x2400003 37748739 (0: nrow: 2, level: 1)
leaf: 0x2400004 37748740 (-1: nrow: 123 rrow: 123) ---→(1)
leaf: 0x2400005 37748741 (0: nrow: 1 rrow: 0) --------→(2’)
----- end tree dump

(2’)の rrow = 0 により、EMPNO = 3001が削除され、Leaf_BLOCKが空になっ
たことが確認できる。

次に、テーブル EMP_TESTに EMPNO = 2001を挿入する。

INSERT INTO
EMP_TEST
VALUES (2001,'SMITH','CLERK',7902,
to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
----- begin tree dump
branch: 0x2400003 37748739 (0: nrow: 3, level: 1)
leaf: 0x2400004 37748740 (-1: nrow: 67 rrow: 67)---→(1)
leaf: 0x2400006 37748742 (0: nrow: 57 rrow: 57)
leaf: 0x2400005 37748741 (1: nrow: 0 rrow: 0)------→(2’)
----- end tree dump

上記の結果より、検証テーブル EMP_TESTに EMPNO = 2001を挿入することによ
って、リーフ・分割が発生している様子が確認できる。

シーケンシャルにデータを挿入する場合、Leaf_BLOCKが満杯になったら新たな
Leaf_BLOCKを用意し、その Leaf_BLOCKに対して順次挿入を行っていくので、リ
ーフ・分割など発生するはずがない。

しかし、今回のケースは、シーケンシャルに値を挿入しているにも関わらず、
リーフ・分割が発生しているのはなぜだろう?

Leaf_BLOCK(1)が満杯の状態でかつ、Leaf_BLOCK(2’)が空の状態であれば、
リーフ・分割することなく、Leaf_BLOCK(2’)に格納されることを予想した方
も少なくないであろう。

しかし、Leaf_BLOCK(2’)は、ブランチで 3001以上の値を格納するための
Leaf_BLOCKとして管理されているので、Leaf_BLOCK(1)とLeaf_BLOCK(2’)
の間に新たなブロックを用意し、その Leaf_BLOCKに値 2001(1件)のみを格納
するというのが、私自身が予想した結果であった。

にも関わらず、Leaf_BLOCK(1)に対してリーフ・分割が発生してしまっている。

理由として考えられるのが、インデックスの構造上、Leaf_BLOCKが満杯になっ
た場合、もし、その Leaf_BLOCKの隣に既存の Leaf_BLOCKが存在しかつ、ブラン
チ上でその Leaf_BLOCKが挿入されようとしている値よりも大きい値のキーを格
納するための Leaf_BLOCKとして管理されている場合、新たな Leaf_BLOCKを用意
するのではなく、リーフ・分割を発生させるものと考えられる(推測)。

数回に渡り、インデックスに関する構造上の問題や、空のブロックをアクセス
してしまう原因などを紹介してきたが、結論として強調したいのは、レンジ検
索である。インデックスの構造がある程度歪んでいても、イコール検索では、
そこそこの効果は得られるはずである。問題は、レンジ検索時に、削除処理に
よって空になった Leaf_BLOCKや、リーフ・分割によって密度が薄くなった
Leaf_BLOCKなどを大量にアクセスしてしまうことである。

是非、TREEDUMPを活用し、読者(貴社)なりの REBUILDのタイミング(周期)を
掴んでもらいたいものである。

今回でモンキーターンが送るメルマガは最終回です。
約1ヶ月半の間、ご購読ありがとうございました。
少しは、読者の皆様のお役に立てたでしょうか?
より良いメルマガにするために、感想等をメールでお聞かせくだされば幸いです。
また、いつか会うときまで・・・さようなら・・・

引き続き、ご購読よろしくお願いします。

冒頭で説明したORACLEと親和性のあるWEBに適した新言語 自社開発フリーソフト
言語「SQeeL」のこともよろしくお願いします!!!!
*詳細をご希望の方は、弊社までお問い合わせください。
mailto:insight-mktg@insight-tec.co.jp

今までありがとうございました。

茅ヶ崎にて

~続・INDEXに関する検証 その6~

by モンキーターン