SQLチューニング（第39回）「照会パターンに合うようにSQL実行計画を分岐すること」（1／2回）

今回から、「照会パターンに合うようにSQL実行計画を分岐すること」と題しまして、2回のシリーズで情報をお届けします。では、早速始めましょう。

---

11.1 多くの照会パターンを持つSQLは一つのSQLでない

"照会パターンに合わせてSQL実行計画を分岐する”とは、SQLの実行計画を主なアクセス条件別に最適化できるようにSQL構造を分離することを意味します。一般的にLIKE '%'条件やNVL条件を使った類型が代表的なケースです。

色々な照会条件がありますが、これを一つのSQLで作成した場合、特定のいくつかの条件に対してだけ効率的な処理をして残りの照会条件に対しては効率的な処理をすることのできない確率がとても多くあります。 すべてのRDBMSで重要な原則一つは’一つのSQLは一つの実行計画だけで実行される’ということです。

一つの実行計画という意味は’結合順序、結合方法、テーブル格別アクセス方法が一つ’という意味です。

利用者画面で問い合わせる条件は多様ですが(場合によっては何の条件も選択しないことも可能)、これを処理するSQLをLIKE ’%’や、NVLなどを利用して一つのSQLで作成する場合、使用者はOracle OptimizerがSQLを条件によりそれぞれ効率的に実行するのを期待します。 しかし、使用者の望みどおり実行されません。 なぜなら、前で話したように一つのSQLは一つの実行計画だけで実行されるためです。

以下の[例1]のWhere句を見ると、変数の値によってT1、T2、T3各テーブルへのアクセス条件が存在する場合と存在しない場合があるなど、照会パターンが非常に多様なSQLです。 ユーザーの立場では、このように様々な照会パターンを持つSQLを1つのSQLで作成しても、実行時にはOptimizerが効率的に処理することを期待します。 しかし実際にはそうではありません.

[例題1] LIKEとNVLを使って一つのSQLで作成した例

SELECT t1.*, t2.*, t3.*
 FROM t1, t2, t3
WHERE t1.c1 LIKE:b1 || ‘%’
   AND t1.c2 LIKE:b2 || ‘%’
   AND t2.c1 LIKE:b3 || ‘%’
   AND t2.c2 LIKE:b4 || ‘%’
   AND t3.c1 LIKE:b5 || ‘%’
   AND t3.c2 LIKE:b6 || ‘%’
   AND t1.no = t2.no
   AND t1.no = t3.no
   AND t2.no = t3.no


SELECT t1.*, t2.*, t3.*
 FROM t1, t2, t3
WHERE t1.c1 = NVL(:b1, t1.c1)
   AND t1.c2 = NVL(:b2, t1.c2)
   AND t2.c1 = NVL(:b3, t2.c1)
   AND t2.c2 = NVL(:b4, t2.c2)
   AND t3.c1 = NVL(:b5, t3.c1)
   AND t3.c2 = NVL(:b6, t3.c2)
   AND t1.no = t2.no
   AND t1.no = t3.no
   AND t2.no = t3.no

( ※ C1,C2はNOT NULLカラム)

[例題1]と同じ型のSQLに対するOptimizerの判断は次のとおりです。利用可能なインデックス設定を参照して、特定のインデックスを使用するか、フルテーブルスキャンを選択できます。 このときフルテーブルスキャンを選択してT1、T2、T3のテーブルをHash Joinで処理すれば、少なくとも常に一定の（高速で遅いに関係なく）性能を出すことができます。

しかしT1,T2,T3テーブルの特定インデックスを選択した後、Nested Loops Joinで実行する場合、照会条件にどんな値が入力されたかにより性能偏差が大きく発生することになるでしょう。たとえば、ユーザーがOptimizerによって選択されたインデックスに対応する条件値を入力すると、応答が速くなりますが、そうでない場合は、各テーブルの全データに対してインデックススキャンを実行することができ、非効率性は非常に深刻です。

多様な照会パターンを持つSQLを一つのSQLで作成する場合、常に上のような問題が存在します。 そのためにSQLが各照会パターン別で効率的に実行されるべきだとすれば、原則的に’一つのSQL’でない’多数のSQL’で実行されるように分離しなければなりません。

すなわち、SQLを問い合わせる多くの条件のうちで利用者が必須で入力しなければならない条件の照会パターンを何種類と規定して、各照会パターン別で最も効率的なAccess Pathを検証した後、各パターン別で最適化された実行計画で実行されるようにSQLを分離しなければならないのです。

もし、[例題1]のSQLで:B1から:B6までの値が無作為で照会されるならば、それぞれどんな実行計画で実行されなければ奈良にのでしょうか? 難しい質問のようですが、各照会パターンに対して性能が最も良い順で優先順位を定めることができるならば、質問に対して難なく答えられるはずです。

例えば、先にT1.C1とT1.C2条件を含む条件で照会される時T1テーブルを先に読むことが性能が良いならば、該当条件で照会される場合、T1テーブルを先に実行されるようにSQLを分離すれば良いのです。

もし、T1.C1とT1.C2が照会されないで、T2.C1とT2.C2条件で照会される時はT2テーブルを先に実行されるようにSQLを分離すれば良いことになります。

このような方法でSQLを各照会パターン別に分けて実行計画を分離したい場合、先に照会パターンにより結合順序を決めなければなりません。

結合順序が決定されたら、各照会パターン別でSQL内のテーブル間結合方式と各テーブルのアクセス方法を決めれば良いのです。

テーブル12-1. 照会パターンにともなう結合順序の例

照会条件照会の有無
パターン	T1.C1 LIKE :B1||’%’	T2.C1 LIKE :B3||’%’	T3.C1 LIKE :B5||’%’	適切なテーブル 結合順序
1	○	○	○	T1 →T2 →T3
2	○	○	×	T1 →T2 →T3
3	○	×	○	T1 →T3→T2
4	○	×	×	T1 →T2 →T3
5	×	○	○	T2→T3 →T2
6	×	○	×	T2 →T1 →T3
7	×	×	○	T3 →T1 →T2
8	×	×	×	T1 →T2 →T3

[プログラムに適用した例]

IF      :B1 IS NOT NULL T
HEN
           SELECT /*+ LEADING(T1) USE_NL(T1 T2 T3) */
...
   ELSIF  :B3 IS NOT NULL THEN
           SELECT /*+ LEADING(T2) USE_NL(T2 T1 T3) */
...
   ELSIF  :B5 IS NOT NULL THEN
           SELECT /*+ LEADING(T3) USE_NL(T3 T1 T2) */
...
   ELSE
           SELECT /*+ FULL(T1) FULL(T2) FULL(T3) USE_HASH(T1 T2 T3) */
...
   END IF;

---

いかがでしたでしょうか？次回は、シリーズ２回目として、「SQLの照会パターン別の実行計画を分岐」に関する情報をお届けします。ご期待ください。だえ、See You ^^

---