複合索引（コンポジット索引）が有効なケース：Oracle SQLチューニング講座（8）（1/3 ページ）

本連載では、Oracleデータベースのパフォーマンス・チューニングの中から、特にSQLのチューニングに注目して、実践レベルの手法を解説する。読者はOracleデータベースのアーキテクチャを理解し、運用管理の実務経験を積んでいることが望ましい。対象とするバージョンは現状で広く使われているOracle9iの機能を基本とするが、Oracle 10gで有効な情報も随時紹介していく。（編集局）

[倉田寛正，株式会社アゲハ]

連載目次

　前回「索引を作成したのにパフォーマンスが悪いケース」では、索引を使用するためのSQLの記述方法や、索引を使用した検索時の注意点について説明しました。今回は、索引を利用した検索速度をさらに向上させるテクニックとして、複合索引（コンポジット索引）を利用する方法を説明します。

複合索引（コンポジット索引）の使用

　複合索引とは、複数の列を指定した索引で、SQLでWHERE条件に指定する列が決まっている場合や、選択性が低い複数の列を組み合わせることで選択性を高めることができる場合に使用します。

　ここで、B*Tree索引の構造を思い出してください。第2回「SQLチューニングの必須知識を総ざらい（前編）Page 2　索引スキャン」の「図6　B*Tree索引スキャン」で説明したように、B*Tree索引のリーフブロックには索引列のデータが格納されています。複合索引の場合も、指定したすべての索引列のデータがリーフブロックに格納されます。

　そのため、OracleではSQLで必要とされるすべての列（WHERE条件列、参照列）が索引に含まれている場合、表にアクセスすることなく、索引へのアクセスのみで処理を完了することができます。つまり、複合索引をうまく利用することで、表へのアクセスを排除し、処理速度を大幅に向上できる場合があります。

図1　索引のみへのアクセスによるデータ取得（クリックで拡大します）

　それでは、実際に以下のようなSQLを実行して、全表スキャン、単一列索引による索引スキャン、そして複合索引による索引スキャン時のSQLトレース、TKPROFユーティリティの結果を比較して、どのような違いが出てくるのかを見てみましょう。

図2　全表スキャンの場合

図3　単一列索引による索引スキャンの場合

　図2、図3の実行統計を比較すると、索引スキャンを実行したにもかかわらず全表スキャンよりも実行時間、アクセスブロック数が多くなっています。これは前回に説明したように、検索対象となるレコードの割合が多過ぎるためです（詳細は、第7回「索引を作成したのにパフォーマンスが悪いケース」を参照してください）。

　また、図3の実行計画を見ると、「idx1_lineitem」索引だけでなく「lineitem」表へのアクセスも発生していることが分かります。このような場合には、実行しているSQLの参照列に注目して、それらすべての列を含む複合索引注1 の作成を検討してみます。

注1：複合索引で指定できる列数　

索引に指定できる列数は最大32列で、ビットマップ索引では30列になります。

　図2、図3で実行しているSQLの参照列は、「l_shipmode」、「l_shipinstruct」、「l_quantity」、「l_extendedprice」、「l_tax」になりますので、これらすべての列を含む複合索引を作成することで、表へのアクセスを排除することができます。

　図4では、ヒント文で新規に作成した複合索引を使用するようにオプティマイザに指示し、SQLトレース、TKPROFユーティリティを使用して実行計画を取得しています。

図4　複合索引による索引スキャンの場合

　図3と図4の実行統計を比較すると、参照列をすべて索引列としたことで表へのアクセスが排除され、実行時間、アクセスブロック数が大きく減少していることが確認できます。また、「idx2_lineitem」索引のみへのアクセスでデータを取得していることから、「GROUP BY句」でのソート処理も排除されています。

図5　全表スキャン対索引スキャン対複合索引スキャンの性能比較