インデックスに関する検証 その８
＜インデックスに関する検証 その8＞ ペンネーム つけまい
— インデックス利用の落とし穴
レンジ検索で全件検索よりも性能ダウン —
前回、大量の削除処理によってインデックス内に空のブロックが数多く発生し、
空のブロックまでもがアクセスの対象となってしまい、そのことが起因してパ
フォーマンスが劣化してしまうという様子を見てきた。
今回は、これらのインデックスの構造上の問題と、SQLオプティマイザとの関係
について見て行く。
●SQLオプティマイザ
実行されたSQL文は、翻訳の過程で実行計画を作成する。その機能を指してオプ
ティマイザと呼んでいる。また、実行計画を作成する方法として
「ルール・ベース」と「コスト・ベース」の二通りがあり、それぞれの機能の
ことを「ルール・ベース・オプティマイザ（RBO）」
「コスト・ベース・オプティマイザ（CBO）」と呼んでいる。
これらの機能とインデックスの関係を簡単に説明すると、
RBO = 条件式にインデックスを用いれば、必ずインデックス検索を行う
CBO = 条件式にインデックスを用いても、ANALYZE情報を参照し、場合によって
はインデックス検索を行わない
つまり、テーブルに対してANALYZEしてあればCBO、していなければRBOとなる。
なお、「コスト・ベース・オプティマイザ」の「コスト」とは、Oracleブロッ
クにアクセスするI/O回数のことであり、実際にはこのI/O回数を基準にし、イ
ンデックス検索時のコストと、全件検索時（Full）のコストとを比較し、I/O回
数が少ない方を選び実行する。
テーブルTEST01（1万件（99万件削除））に対して、ANALYZEする前とANALYZEし
た後にそれぞれ同じSELECT文を発行した際の結果を、以下に示す。
ANALYZE前
ANALYZE後
ANALYZE後の検索結果の＜検索1＞をご覧になればお分かりの通り、条件式にイ
ンデックスを用いているにも関わらず、インデックスに対するI/O回数が0になっ
ている。これは、先に説明したCBOがANALYZE情報を基に、1万件中の5000件（50%）
を検索するのであれば、インデックス検索よりも全件検索の方がコストが低い
と判断したからだ。つまり、インデックスキーが格納されているリーフ・ブロッ
クを一つ一つアクセスするよりも、Oracleの初期設定パラメータである
「db_file_multiblock_read_count」により、実レコードが格納されているOracle
ブロックを数ブロック単位（デフォルト8）で直接アクセスした方が速いと判断
したからだ。
厳密に言うと、Selectivity（参照性）を基に、上記のような判断を行っている。
Selectivityとは、インデックス検索を行うか全件検索を行うかを決定するため
に、全件検索の参照性を導き出すためのものである。
検索コストの多くは、このSelectivityを基に、参照性パーセンテージを求めて
導き出されている。
このSelectivityに関しては、別のシリーズの中で紹介する予定である。
上記の結果は、1万件のテーブルに対して50%に当たる5000件分の検索を行った
ものだ。
では、極端にレンジ検索の範囲を狭くして、1件目以下全てを検索するという
SELECT文を発行してみると・・・
検索結果
上記の条件式にインデックスを用いた検索結果で、インデックスに対してI/Oが
発生しているが、これは当然の結果と言えよう。先ほどの検索は全体の50%だっ
たので、CBOがインデックス検索よりも全件検索を行った方がコストが低いと判
断した結果だが、今回の検索は全体の0.01%にしか及ばないため、CBOがインデッ
クス検索を行った方がコストが低いと判断したからだ。
しかし、ここでも空のリーフ・ブロックに対するI/Oが発生していることに、お
気付きいただけただろうか。リーフ・ブロックには必ず昇順でキーが格納され
ているので、先頭のリーフ・ブロックの一番最初のキーを読むだけであれば、
ルート（1）、ブランチ（1）、リーフ（1）の3I/Oだけで済むはずなのだが、
ANALYZEを行う前と同様、12413回もの空のリーフ・ブロックに対するI/Oが発生
していることになる。
空のリーフ・ブロックをアクセスしてしまう様子
これらの結果から、CBOはANALYZE情報を参照し、何レコード（ブロック）中の
何レコード（ブロック）をアクセスするのかといったCBO独自の基準を基に、イ
ンデックスを参照するか否かを判断している。しかし、インデックスの構造ま
では判断材料に入っていないため、Skew（偏る）してしまった、または密度が
低くなってしまったインデックスに対しては、必ずしも最良のコストを導き出
してくれるものではなくなってしまう。
今後も、このCBOの判断基準については、必ず検証する機会をつくって、読者の
方々に結果を報告する予定である。
読者の方々も、空いている時間を利用して、是非、この検証にチャレンジして
いただきたい。
もし、何らかの結果が得られたら、「つけまい」まで一報をいただきたい。
今回をもちまして、「つけまい」はつけまいの旅に出ます。
暑さが和らいだ頃に、必ずやパワーアップして戻ってまいる所存でございます。
それまでの間、どうぞ「つけまい」に充電期間を与えて下さい。
次回からは、「ちゃむ」の一番弟子である「モンキーターン」が、引き続き
インデックスに関する検証結果を報告していく予定です。
今後とも「おら！ オラ！ Oracle － どっぷり検証生活」に、皆様の熱いご声援
をお願い致します。皆様からのご声援、ご指示に支えられて、今後も質の高い
情報を提供していく予定です。
猛暑 茅ヶ崎にて
～インデックスに関する検証 その8～
by つけまい