インデックスに関する検証 その７
＜インデックスに関する検証 その7＞ ペンネーム つけまい --- インデックス利用の落とし穴 レンジ検索で全件検索よりも性能ダウン --- 前回、インデックスに対して大量の削除処理を行った結果、全件検索よりもイ ンデックス検索の方がはるかに遅くなってしまうという様子を見てきた。 今回は、この不可解な現象について検証結果を基に、Oracleが空のブロックを アクセスしてしまう様子を見ていく。 ●インデックスは物持ちがいい？ この不可解な結果を裏付けるために、下記のINDEX_STATSの内容及びTREEDUMPを 見ていただきたい。 ●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まず始めに着目してもらいたいのが、LF_BLKSと BR_ROWS及びBR_BLKSである。
99万件削除したにも関わらず100万件の時と値が全く変わっていないことだ。●TEST01（100万件）のINDEX_STATSの内容
HEIGHT BLOCKS LF_ROWS LF_BLKS BR_ROWS BR_BLKS --------- --------- --------- --------- --------- --------- 3 51200 1000000 13793 13792 112また、実在するリーフ・ブロックの数を表した項目がLF_BLKSであるが、この値
13793がインデックスへのI/O回数13106に近いことも頭に入れておいてもらいた
い。TEST01（1万件（99万件削除））に対する検索結果
次に、TREEDUMPである。表示上、全体のごく一部しか掲載できなかったが、先
頭のリーフ・ブロックから0が続いている。この0が、実際には12413ブロック目
まで続いている。そして、0以外のリーフ・ブロックは1380ブロック存在する。
つまり、12413 + 1380がLF_BLKSの値13793と一致することになる。以上のことから、インデックスへのI/O回数13106を検証すると、以下の様にな
る。13106 = ルート(1) + ブランチ(1) + 5000件分のリーフ(691) + 0のリーフ(12413)
（）内の数字はインデックスへのI/O回数を表しているこの計算式を、もっと解りやすくしたものを、以下に示す。
この結果は、インデックスシリーズの冒頭でも述べた様に、削除され空になっ
たブロックがあっても、実際には切り離さずに、いつまでも保持し続けている
ことを実証したものと言えよう。しかも、その空になったブロックまでアクセ
スの対象になっていることを証明する、まさに驚きの結果である。このことは、
「インデックスの構造上の問題」と前述しているが、いくらパフォーマンスを
優先した結果から生まれた構造上の問題とは言え、あまりにもお粗末な構造で
はないだろうか？例えば、「年度が変わった時点で、一昨年のデータを全て削
除する」などといった運用を行っているユーザは少なくないはずだ。このよう
な運用を行っているシステムのDBAが、この構造上の問題を知らずに、削除後、
REBUILDなどのメンテナンスを施していなかった場合、パフォーマンスを上げる
ためのインデックスが、逆にパフォーマンスを低下させてしまう危険性がある
ことを、十分に理解してもらいたい。次回は、これらのインデックスの構造とSQLオプティマイザとの関係について見
ていく。茅ヶ崎にて
～インデックスに関する検証 その7～
by つけまい
