インデックスに関する検証その６

＜インデックスに関する検証その6＞ペンネームつけまい

— インデックス利用の落とし穴
レンジ検索で全件検索よりも性能ダウン —

●インデックスが失速する？
これまでの検索結果（1万件及び100万件）では、特に驚いた内容のものは何一
つない。むしろ、当然の結果しか得られていない。そこで今度は、挿入ではな
く大量の削除を行ってみた結果、「インデックス検索よりも全件検索の方がは
るかに速くなる」という驚くべき結果が得られた。

削除後のインデックス構造（B-Tree）のイメージ

このイメージ図のテーブルTEST01は、1万件から100万件に拡張したテーブルを、
もとの1万件に戻したものである。
ここで注意していただきたいのが、もとの1万件に戻したからといって、100万
件に拡張する前の1万件のテーブルとは、全く異なった値が入っていることであ
る。
100万件に拡張した際の過程を思い出していただきたい。
（末尾 0 → 1 → 2 ～ 8 → 9 の順に挿入）
今回の削除も同様、挿入していった順に末尾0～8の削除を行った。次に、残っ
た末尾9のみの10万件に対して、挿入した順に9万件の削除を行った。

以下に示す図は、削除後のイメージを解りやすく説明したものである。

この1万件に縮小したテーブルTEST01に対して、最初の1万件の時と同様1～5000
件目までのレンジ検索を行った結果を、以下に示す。

この検索結果で特に注目すべき点は、インデックスなしの検索が約23秒かかっ
たのに対し、インデックスありの検索がインデックスなしの検索に比べて3倍以
上かかっているということである。また、いくらリーフ分割を多発させたから
といっても、最初の1万件のインデックスへのI/O回数が45回なのに対し、今回
は13106回と約300倍になっているのも不可解である。

次回は、この不可解な現象について検証結果を基に、Oracleが空のブロックを
アクセスしてしまう様子を見ていく。

以下に、検証結果を記載しておくので、興味のある方は推測し、次回に紹介す
る内容と照らし合わせていただきたい。

●TEST01（1万件（99万件削除））のINDEX_STATSの内容

ANALYZE INDEX TEST01 VALIDATE STRUCTURE ;
索引が分析されました。
SELECT HEIGHT,BLOCKS,LF_ROWS,LF_BLKS,BR_ROWS,BR_BLKS FROM INDEX_STATS ;
HEIGHT    BLOCKS   LF_ROWS   LF_BLKS   BR_ROWS   BR_BLKS
--------- --------- --------- --------- --------- ---------
3     51200     10000     13793     13792       112

●TEST01（1万件（99万件削除））のTREEDUMPの内容（縮小後）

ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME TREEDUMP LEVEL 3539' ;

----- begin tree dump
branch: 0x2000003 33554435 (0: nrow: 111, level: 2)
branch: 0x20000a3 33554595 (-1: nrow: 120, level: 1)
leaf: 0x2000004 33554436 (-1: nrow: 0)
leaf: 0x2001b2c 33561388 (0: nrow: 0)
leaf: 0x2000d98 33557912 (1: nrow: 0)
leaf: 0x2002c34 33565748 (2: nrow: 0)
leaf: 0x200036b 33555307 (3: nrow: 0)
leaf: 0x2001b2d 33561389 (4: nrow: 0)
leaf: 0x2000d99 33557913 (5: nrow: 0)
:
:
branch: 0x200145f 33559647 (98: nrow: 107, level: 1)
leaf: 0x2000988 33556872 (-1: nrow: 0)
leaf: 0x2002a7a 33565306 (0: nrow: 0)
:
:
leaf: 0x2002a81 33565313 (16: nrow: 0)
leaf: 0x200145d 33559645 (17: nrow: 0)
leaf: 0x2002a82 33565314 (18: nrow: 0)
leaf: 0x2000d41 33557825 (19: nrow: 0)
leaf: 0x200337b 33567611 (20: nrow: 3)
leaf: 0x2001a8f 33561231 (21: nrow: 7)
leaf: 0x200337c 33567612 (22: nrow: 7)
:
:
leaf: 0x2000d97 33557911 (125: nrow: 7)
leaf: 0x200344d 33567821 (126: nrow: 6)
leaf: 0x2001b2b 33561387 (127: nrow: 7)
leaf: 0x200344e 33567822 (128: nrow: 7)
leaf: 0x2000368 33555304 (129: nrow: 7)
----- end tree dump

初夏茅ヶ崎にて

～インデックスに関する検証その6～
by つけまい

インデックスに関する検証 その６