索引を作成したのにパフォーマンスが悪いケース：Oracle SQLチューニング講座（7）（3/3 ページ）
本連載では、Oracleデータベースのパフォーマンス・チューニングの中から、特にSQLのチューニングに注目して、実践レベルの手法を解説する。読者はOracleデータベースのアーキテクチャを理解し、運用管理の実務経験を積んでいることが望ましい。対象とするバージョンは現状で広く使われているOracle9iの機能を基本とするが、Oracle 10gで有効な情報も随時紹介していく。（編集局）
索引の格納効率の悪化
索引の格納効率は作成直後が最も良い状態であり、表データの更新／挿入／削除により、徐々に格納効率が悪化していきます。「初めはパフォーマンスに問題はなかったのに、しばらくしてから徐々にパフォーマンスが劣化してきた」という問題の最も多い原因の1つといえます。格納効率が悪化した索引では、索引スキャンによるアクセスブロック数が増加するため、索引作成直後に比べてパフォーマンスが劣化してしまいます。
B*Tree索引が作成されている列値を更新／削除すると、既存の索引エントリは削除されますが、削除により空いた領域は、該当ブロックが完全に空になるか、削除された値と同じ値、もしくは前後のエントリの中間となる値を挿入した場合にのみ再利用されます。このため、索引列値の更新、削除などが頻発する索引の格納効率は、次第に悪化していくことになります。
図9は、UPDATE文を実行した際に、索引ブロックが分割される様子を示しています。（2）のUPDATE文の実行によって、更新対象となった「100」のレコードが削除され、ブロック2に「550」のレコードが挿入されます。しかし、このブロックに（PCTFREE注3 の領域も含め）空き領域がない場合には新規ブロック（ブロック3）が作成され、ブロック2内の「550」以降のレコード（ここでは「600」）をブロック3に挿入します。
注3：索引のPCTFREEパラメータについて
索引作成時に指定するPCTFREEパラメータは、表のPCTFREEパラメータとは意味が異なります。表のPCTFREEパラメータは、更新処理によって列長が長くなった場合に備えて各ブロックに空けておく領域の割合を指定しますが、索引のPCTFREEパラメータは、既存の索引エントリ間に新たな索引エントリが挿入されたときに備えて各ブロックに空けておく領域を指定するものです。索引ブロックに空き領域を残しておくことで、索引に対するINSERT／DELETE処理により、索引ブロックが分割（SPLIT）されにくくなります。ブロックの分割を抑止することで、索引列に対するINSERT／UPDATE処理速度の向上、および索引エントリの格納効率低下（索引走査のパフォーマンス劣化）を防ぐことができます。
索引の格納効率が若干悪化した場合でも、プライマリ・キーを条件に指定した一意検索などにはほとんど影響はありませんが、範囲検索などで多くの索引ブロックを読み込む必要がある処理のパフォーマンスに影響します。図10、図11は、作成直後の状態と格納効率が悪化した状態で、索引構造とパフォーマンスを調べた結果になります。
索引の構造を比較すると、図11では索引が使用しているブロック数（BLOCKS）、および索引の階層の高さ（HEIGHT）が増えており、索引の格納効率が悪化していることが分かります。また、索引の範囲スキャンを実施した場合の実行結果からは、「disk」「query」の値に大きな違いが発生していることが確認できます。
このように、更新頻度の激しい表の索引は、定期的な再作成（Rebuild）を検討する必要があります。索引を再作成することでブロックの格納効率を最適化し、パフォーマンスを回復させることができます。
索引の構造を確認する手順
以下に索引の構造を確認するための手順例を示します。
1．ANALYZEコマンドで索引の構造を分析する
SQL >ANALYZE INDEX ix_info_txt VALIDATE STRUCTURE; 索引が分析されました。
2．INDEX_STATSビューから分析結果を取得
INDEX_STATSビューからは直前に実行されたANALYZEコマンドの分析結果のみ取得できます。別のセッションからは参照できません（分析結果をデータベース内に保存しておく場合はテーブルを作成し、データを新規表にコピーします）。
SQL >SELECT name,blocks,height FROM index_stats; NAME BLOCKS HEIGHT ------------------------------ ---------- ---------- IX_INFO_TXT 256 2
今回は、索引を使用するためのSQLと、使用している索引に問題がないかの確認方法を説明しました。次回も引き続き索引に関するテクニックを説明していきます。
Copyright © ITmedia, Inc. All Rights Reserved.