フィルタ述語の意図的な使用

多くの場合、フィルタ 述語は、複合インデックスの列の順番が正しくないことで、インデックスが 正常に使われていないことを表すものと見なされます。しかし、フィルタ述語はいい意味で使われることもあります。範囲検索のパフォーマンスを改善しては くれませんが、連続的にアクセスするデータをまとめる意味があるからです。

アクセス述語としては使われないwhere句の述語は、 このテクニックのちょうどいい例になります。

SELECT first_name, last_name, subsidiary_id, phone_number
  FROM employees
 WHERE subsidiary_id = ?
   AND UPPER(last_name) LIKE '%INA%'

ワイルドカードから始まるLIKEの条件では、インデックスツリーを使えないことを思い出してみましょう。 そうすると、LAST_NAMEやUPPER(last_name)に インデックスがあっても、LAST_NAMEのインデックスは、スキャンされる インデックスの範囲を狭めてはくれないということが分かります。つまり、このような条件はインデックスにとってはよいものではないのです。

その一方、SUBSIDIARY_IDに対する条件は、 インデックスを使うのに適したものになっています。SUBSIDIARY_IDは 既にプライマリキーの最初の列になっていますので、追加でインデックスを作る必要はありません。

上の実行計画では、TABLE ACCESS BY INDEX ROWIDのコストが、 それに続くINDEX RANGE SCANのコストに比べて、100倍も大きく なっています。言い換えると、処理のほとんどはテーブルへのアクセスだという ことです。これはよくあるパターンで、それ自体に問題があるわけではありません。 しかし、クエリの実行時間のほとんどを占めているのは間違いありません。

アクセスされる行が1つのテーブルブロックに格納されている場合、データベースは全ての行を1回の読み込み処理で取得できるため、 テーブルアクセスは必ずしもボトルネックになりません。一方、行が複数のブロックに分散してしまっている場合、各行を取得するために データベースは多くの行を読み取らねばならないので、深刻なパフォーマンス問題になることがあります。つまりパフォーマンスは、アクセスされる 行の物理的な分散具合、すなわち行のクラスタ化の状況に左右されるということです。

クエリのパフォーマンスを改善するために、実際にインデックスの順序に合わせて行を並び替えることも可能です。しかし、 テーブルの行は一方向にしか並べられないので、この方法はほとんど使われません。テーブルが1つのインデックスにしか最適化されなくなって しまうからです。最適化のために1つのインデックスだけを選び出したとしても、多くのデータベースではそういったタスクのためには簡単なツールしか 用意していません。これはいわゆる行の並べ替えと呼ばれる方法ですが、やはりあまり現実的ではない方法です。

これが、インデックスの強力さの2つ目である、 データのクラスタ化です。データが自動的に理想的な順序で並べて保存されるように、 インデックスに列を追加することができます。それによってインデックスは、 データをクラスタ化する強力だけれどもシンプルなツールになるのです。

前述のクエリにこのコンセプトを適用すると、スキャンされるインデックスの範囲は狭めなくても、where句の全ての列を含むインデックスを作る必要があります。

CREATE INDEX empsubupnam ON employees
       (subsidiary_id, UPPER(last_name))

SUBSIDIARY_IDは、インデックスの最初の列なので、 アクセス述語として使われます。UPPER(last_name)は、 フィルタ述語としてのLIKEを カバーします。大文字表現にインデックスを作ることによって、実行時の CPU使用を少し抑えることができますが、単にLAST_NAMEに 対するインデックスでも同じように動作します。これについては次の節で説明します。

新しい実行計画でも、前とほとんど同じ処理を行っている様子が分かります。 しかし、コスト値は劇的に下がっています。述語情報を見ると、LIKEフィルタが既にINDEX RANGE SCANに適用されています。つまり、LIKEフィルタの条件を満たさない行は、即座に捨てられているのです。 テーブルアクセスにはフィルタ述語が使われていません。従って、where句を満たさない行はロードされていないということです。

2つの実行計画の違いは、「Rows」列に明確に表れています。オプティマイザの見積もりによると、クエリは最終的に17レコードに一致すると されています。最初の実行計画のインデックススキャンは、333行を返していました。データベースは、この333行をテーブルから読み出して、それを 17行まで減らすLIKEフィルタを適用していた訳です。2番目の 実行計画では、最初のインデックスアクセスではこのような行を読み出していないので、データベースはTABLE ACCESS BY INDEX ROWIDを17回だけ実行すればよいようになっています。

もう1つ、INDEX RANGE SCAN処理のコスト値が2から3に 増えているのにも気付けるとよいでしょう。これは、列を追加したことでインデックスが大きくなったためです。パフォーマンス向上の観点から言うと、 これは許容できる範囲と言えます。

次のアニメーションは、2つの実行計画の違いを表したものです。

図5.1意図的なフィルタ述語

この一目瞭然の例は、where句の各列にインデックスを 作った時の一般常識を確認したに過ぎないかもしれません。しかしこの「常識」は、 どんな条件がアクセス述語に使われるかを決定し、パフォーマンスに多大な影響を及ぼす、列の順序を無視しています。列の順序の決め方は、成り行き任せにして しまうべきではありません。

列の数が増えるほど、インデックスのサイズは増えていき、テキスト列を追加した際は特に増加します。対数的スケーラビリティにより、影響はかなり限定されますが、 インデックスが大きいと、パフォーマンスもよくなりません。where句にある全ての列をインデックスに追加すれば いいという意味ではなく、実行ステップの初期段階でのデータ量を減らすために、フィルタ述語を意図的に使っていくべきです。

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