インデックスに関する検証 その1
＜インデックスに関する検証 その1＞ ペンネーム つけまい
— インデックス利用の落とし穴
レンジ検索で全件検索よりも性能ダウン —
「インデックスを作成すれば早くなる」という考えは、決して間違ってはいな
い。そもそもインデックスとは、処理を高速化させるためだけに一番多く用い
られる手段だからだ。しかし、「全ての処理においてインデックスは有効なも
のなのだろうか」と疑問を持つユーザは、意外に少ないのではないだろうか。
今回は、そんな素朴な疑問に対して、実際にインデックス構造を悪化させ、様
々な角度からの検証を試みた。
●実験の狙いと環境
以下に、実験の目的と想定するシステム及び前提条件をまとめた。
-------------------------------------------------------------------- |目 的|・どの様な処理を行えば、インデックスが遅くなるのかを把握する | | |・なぜ遅くなるのかを、実験によって検証する | |--------------------------------------------------------------------| |想 定|・Insert中心の業務 | | |・古いデータの大量削除が発生する業務 | |--------------------------------------------------------------------| |実験の|・各テストの結果をサンプリングする際、Oracleの再起動を行い、 | |前 提| データベース・バッファ内をクリアしている | | |・インデックスを含む全てのデータは、二次エクステントすること | | | なく一つのエクステント内に格納されており（初期エクステント | | | のみ）、それぞれ一つのデータファイルに納まっている | | |・データ及びインデックスは、別々のテーブルスペースに格納され | | | ている | | |・測定時間の計測は、画面出力I/O時間が含まれない様に工夫してい| | | る | | |・初期設定パラメータdb_file_multiblock_read_countは、デフォル| | | トの8に設定 | | |・実験結果に用いているPHYSICAL READ（DATA）とは、上記の初期設| | | 定パラメータdb_file_multiblock_read_countにより、8ブロック | | | 単位でアクセスしたI/O回数のことであり、このI/O回数に８を掛 | | | けた値が、実際にアクセスしたブロック数となる（ただし、イン | | | デックス検索時は1ブロック単位でアクセスするため、この時のPH| | | YSICAL READ（DATA）の値は、実際にアクセスしたブロック数と等| | | しくなる） | | |・実験結果に用いているPHYSICAL READ（INDEX）とは、1ブロック単| | | 位でアクセスした際に発生したI/O回数であるため、実際にアクセ| | | スしたブロック数と等しくなる（db_file_multiblock_read_count| | | はINDEXに対しては機能しない） | --------------------------------------------------------------------
データ件数は、以下の通りである。
1. 1万件
2. 100万件（1万件から挿入を繰り返し100万件まで拡張）
3. 1万件 （100万件から99万件を削除し1万件に縮小）
上記の3タイプを基に、様々な検証を行った。
なお、実験の目的は互いのパフォーマンス性能を比較するものではなく、イン
デックス構造が悪化する原因を突き止めることが狙いだ。
今回の実験は、できるだけ多くの方々に「インデックス検索が突然遅くなる」
という不可解な現象を手軽に体験してもらいたいために、NT環境を選んだ。
●削除データまでは管理していない
そもそも、この実験を始めるきっかけになったのは1つのTREEDUMP（後述）だっ
た。削除したにも関わらず、いつまでも0の値が入ったままなのである。「もし
や・・・0ということはROWID（後述）を1つも含んでいないブロック（空のブロ
ック）が存在しているということではないのか？」。この疑問に対して、100万
件のテーブルを実際に作成し、そのテーブルに対して99万件の削除を行い条件
式にインデックスを用いたSELECT文の発行を試みたところ・・・。
●予感的中
実験結果から述べると、予測は的中していた。削除してブロックが空になった
としても、Oracleは空であるかどうかの判断ができず、意味の無いブロックま
でアクセスしてしまっているということである。具体的な数値で表すと、イン
デックス無しの検索に対して、インデックス有りの検索の方が3倍以上遅かった。
また、同じインデックス有りの検索同士でも、削除を一度も行っていない1万件
のテーブルに対して、削除した結果1万件になったテーブルの方が約300倍I/O回
数が多かった。
以下に、この実験によって得られた結果をまとめた。
1. Oracleは削除した結果、空になったブロックでも実際には切り離さずに、い
つまでも保持している
2. ブロック中にデータが存在するか否かを判断する要素を持っていないため、
～以下（＜＝）といったレンジ検索を行った場合、空のブロックとは知らず
に次のブロックを読んだら次のブロックへと、ひたすらアクセスを続けてしまう
この現象は、インデックスの構造上の問題であり、削除されたからといってそ
のブロックを切り離すのではなく、また同じ値が入ってきた時のためのPlace
Holderとして保持しているものと考えられる。現に、一度削除した値をもう一
度入れた場合、インデックスの構造上の問題で大きな組み替えが起きていない
限り、必ず同じブロックに入ることが実証された。
以上のことから言えることは、インデックスはイコール検索には向いているが、
レンジ検索には必ずしも向いているとは言い難く、少なくとも、大量の削除処
理が多発しているテーブルに対しての広範囲なレンジ検索は、全くの不向きで
あることが断言できる。
ただし、定期的にメンテナンス（REBUILDなど）を施しているケースでは一概に
は言えない。しかし、どのタイミングでどのブロックの密度が低くなったり、
どこからどこまでのブロックが空になるかなどを予測することなど不可能に近
い。だからといって、ただ闇雲にメンテナンスを頻発させるのも、これまた無
意味（無駄）なことである。
となると、DBAが各々のテーブルの特性（このテーブルは大量の削除が頻繁に発
生する。テーブルは昇順に挿入されるとは限らない）などを十分に把握した上
で、メンテナンスで回避できる側面と、SQLの構文で回避（インデックスを状況
に応じて使い分ける）できる側面とを両方加味しなければならない状況が生ま
れてくる。
これらの要素を踏まえた上で、次回から紹介していく検証結果を、今後のメン
テナンスを行うタイミングを判断する目安に加えていただければ幸いである。
初夏 茅ヶ崎にて
～インデックスに関する検証 その1～
by つけまい