インデックスのみのスキャン: テーブルアクセスを 避ける

インデックスのみのスキャンは、あらゆるチューニング法の中でも最強の方法の1つと言えるでしょう。where句の評価のために テーブルにアクセスする必要がないだけでなく、インデックス自体にお目当ての列があるなら、テーブルに全くアクセスしなくてもよいのです。

クエリ全体をカバーするためには、SQL文に出てくる全ての 列をインデックスに含めなくてはなりません。次の例のように、select句に含まれる列も例外ではありません。

CREATE INDEX sales_sub_eur
    ON sales
     ( subsidiary_id, eur_value )

SELECT SUM(eur_value)
  FROM sales
 WHERE subsidiary_id = ?

もちろん、インデックスを作る際は、他の句よりもwhere句が優先されます。 SUBSIDIARY_ID列は、アクセス述語として 使えるように、インデックスの最初にあります。

実行計画では、インデックススキャンが行われているとあり、その後にテーブルアクセス(TABLE ACCESS BY INDEX ROWID)は行われていません。

インデックスがクエリ全体をカバーしていることから、 カバリングインデックスとも呼ばれます。

データベースがインデックスに保存された値を使えるように、 インデックスはEUR_VALUE列を持っています。クエリの結果を返すための全ての情報をインデックスが持っているので、 テーブルにアクセスする必要はありません。

インデックスのみのスキャンは、桁違いにパフォーマンスを改善してくれます。実行計画の行数見積もりを見てみましょう。 オプティマイザは、4万行をまとめる必要があるとしています。つまり、各行が別々のブロックに保存されていたのだとすると、 インデックスのみのスキャンによって、4万回のテーブル読み込みをしなくて済んだということです。このインデックスがそこそこのクラスタ化係数を持っている、 つまり少数のテーブルブロックにデータがクラスタ化されているとすると、この利点はかなり小さくなります。

クラスタ化係数に加え、選択する行数によって、インデックスのみのスキャンの パフォーマンス改善効果は限定されてきます。もし1行しか選択しないとすると、1回分のテーブルアクセスしか削減できません。ツリーの走査でも数ブロックを読み込む必要が あることを考えると、節約できたテーブルアクセスの回数は、無視できる程度かもしれません。

インデックスのみのスキャンは、アグレッシブなインデックス戦略です。不要なメモリを確保したり、update文実行時のメンテナンス コストが増えたりするので、見込みだけでインデックスのみのスキャンを狙ってインデックスをデザインしてはなりません。詳しくは、第8章8, 「データの変更」を 参照してください。実際には、まずはwhere句などを優先し、select句を考えずにインデックスを作って、 必要があればそこから拡張していくべきでしょう。

インデックスのみのスキャンは、あまり嬉しくないことであなたを驚かせる可能性もあります。例えば、クエリを最近の売上だけに限定したものに 変えてみましょう。

SELECT SUM(eur_value)
  FROM sales
 WHERE subsidiary_id = ? AND sale_date > ?

実行計画を見ない内は、選択する行数が減った分、クエリの実行は 速くなると考えるでしょう。しかし、where句が インデックスにない列を参照しようとするので、その列を取得するためデータベースはテーブルへアクセスしなくてはならなくなりました。

クエリが選択する行数は減ったのに、テーブルアクセスによって レスポンスタイムが増えてしまいます。関係があるのは、クエリが返す行数ではなく、行を探し出すためにデータベースが何行にアクセスするか なのです。

インデックスのみのスキャンにインデックスが使えなくなってしまうと、オプティマイザは次点の実行計画を選択します。つまり、オプティマイザは 全く違う実行計画を選ぶか、上の例のように違うインデックスを使った似たような実行計画を選びます。今回は、前の章で作ったSALE_DATEの インデックスを使っています。

オプティマイザの観点だと、このインデックスはSALES_SUB_EURと 比べると2つの優位点があります。オプティマイザは、SALE_DATEに 対するフィルタは、SUBSIDIARY_IDに対するフィルタよりも 選択性が高いと見ているのです。前の実行計画2つの「Rows」列からもそれが分かります(約1万行と4万行)。このクエリはバインドパラメータを使っているので、 これらの見積もりは、あくまで予想です。例えば、SALE_DATEの 条件に、初めて売上があった日が指定されたら、テーブル全体が選択されてしまいます。

SALES_DATEのインデックスの2つ目の優位点は、 クラスタ化係数がよいことです。SALESテーブルは時系列に大きく なっていくものなので、これは正しい考えと言えます。行が削除されない限り、新しい行は常にテーブルの最後尾に追加されていきます。 テーブルもインデックスもおおむね時系列にソートされているので、テーブルの並び方とインデックスの並び方は一致します。これはすなわち、 クラスタ化係数がよいということになります。

クラスタ化係数のよいインデックスということは、行を取りだす時に 少数のブロックしか読まなくてよいようにデータベースがデータを保存しているはずなので、選択する行は近くに保存されています。このようなインデックスを 使うと、インデックスのみのスキャンでなくても十分にクエリは高速になります。 この場合、他のインデックスから不要な列を削除するべきでしょう。

今回の例では、良い方の偶然もあります。SALE_DATEに 対する新しいフィルタは、インデックスのみのスキャンができないようにしただけでなく、新しいアクセスパスがあることも示してくれました。 オプティマイザは、この変更によるパフォーマンスの影響を小さくしてはくれましたが、別の句に列を追加することで、インデックスのみのスキャンを しないようにすることも可能です。一方、select句に 列を追加してしまうと、インデックスのみのスキャンが出来なくなったことによる影響を限定するアクセスパスは登場しなかったでしょう。

関数インデックスも、 インデックスのみのスキャンに関連して、不愉快な不意打ちを仕掛けてくる 可能性があります。LAST_NAME列を選択する時には、 UPPER(last_name)に対するインデックスはインデックスのみのスキャンを使えません。前の節で、 LIKEフィルタをサポートするようにLAST_NAME列にも インデックスを作っておくべきだったかもしれません。そうすれば、 LAST_NAMEを選択する時もインデックスのみのスキャンが使えたでしょう。

前述のようなクエリをまとめるのは、インデックスのみのスキャンを使う候補になり得ます。これらのクエリは、たくさんの行を選択するけれど 対象の列は少ないので、インデックスのみのスキャンをサポートするスリムなインデックスを作れます。多くの列を対象にすればするほど、 インデックスのみのスキャンをサポートするために、たくさんの列をインデックスに 追加しなくてはなりません。開発者としては、本当に必要な列だけを選択するようにしましょう。

多くの行にインデックスを作るとたくさんの容量が必要なのとはまた別に、使用しているデータベースの制限に届いてしまう可能性もあります。 多くのデータベースでは、インデックスあたりの列数やインデックスエントリの 総量に厳格な制限があります。つまり、好きなだけの列をインデックスに追加したり、 どんな長さの列もインデックスに追加したり出来るわけではないということです。特に重要な制限事項を次に列挙します。次の節では、テーブル全体をカバーする インデックスについて取り上げます。

 CREATE INDEX empsubupnam
     ON employees
       (subsidiary_id, last_name)
INCLUDE(phone_number, first_name)

この説明が気に入れば、きっとこの本も 気に入るはず。