スマートなロジック

SQLデータベースの主な機能のひとつに、アドホックなクエリ、つまり、新しい種類のクエリをいつでも実行可能であることが 挙げられるでしょう。これは、クエリ オプティマイザ(クエリプランナ)が、文を受け取る度に解析し、妥当な実行計画を直ちに生成してくれるからに他なりません。 実行時に最適化が行われることのオーバーヘッドは、バインドパラメータによって 小さく抑えることができます。

つまりそれは、データベースは動的SQLに最適化されているということです。必要ならこの機能を使っていくべきでしょう。

しかし、「動的SQLは 遅い」という都市伝説のせいか、静的SQLを好んで、動的SQLを避ける傾向も 多いと言わざるを得ません。しかしこれは、Db2やOracle、SQL Serverと言った、実行計画の共有キャッシュを使ったデータベースに おいては、利益以上に害をもたらす習慣です。

デモのため、EMPLOYEESテーブルを 検索するアプリケーションを考えてみて下さい。アプリケーションは、子会社IDや従業員ID、姓(大文字小文字は問わない)の様々な 組み合わせで検索できるとしましょう。「スマートな」仕組みで、全ての場合を1つのクエリでカバーすることも可能です。

SELECT first_name, last_name, subsidiary_id, employee_id
  FROM employees
 WHERE ( subsidiary_id    = :sub_id OR :sub_id IS NULL )
   AND ( employee_id      = :emp_id OR :emp_id IS NULL )
   AND ( UPPER(last_name) = :name   OR :name   IS NULL )

このクエリは、読みやすさのために名前付き バインド変数を使っています。取り得る全てのフィルタ表現は、静的に文に埋め込まれています。フィルタが必要ない時は、検索語の代わりに NULLを指定すればよいだけです。ORの論理で条件を無効にできます。

これは、完全に正しいSQL文です。NULLの使い方も、SQLの3つの値の論理演算に一致した定義に 沿っています。にもかかわらずこの文は、全くもって最悪の パフォーマンスを招くアンチパターンです。

データベースは、それぞれのフィルタが実行時にキャンセルされる可能性があるため、実行計画を最適化できません。そのためデータベースは、 全てのフィルタが使えないという最悪のケースに備えなくてはならないのです。

----------------------------------------------------
| Id | Operation         | Name      | Rows | Cost |
----------------------------------------------------
|  0 | SELECT STATEMENT  |           |    2 |  478 |
|* 1 |  TABLE ACCESS FULL| EMPLOYEES |    2 |  478 |
----------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter((:NAME   IS NULL OR UPPER("LAST_NAME")=:NAME) 
       AND (:EMP_ID IS NULL OR "EMPLOYEE_ID"=:EMP_ID) 
       AND (:SUB_ID IS NULL OR "SUBSIDIARY_ID"=:SUB_ID))

その結果、各列にインデックスがあったとしても、 データベースはフルテーブルスキャンを実行してしまいます。

データベースは、「スマートな」仕組みをうまく解決できなかったわけではありません。バインドパラメータを使ったため、一般的な 実行計画を作り、それをキャッシュして、それ以降異なる値が与えられた時にも使いまわせるようにしたのです。バインドパラメータを使わず実際の 値をSQL文に埋め込んだ場合、オプティマイザはフィルタに対して正しいインデックスを選択します。

SELECT first_name, last_name, subsidiary_id, employee_id
  FROM employees
 WHERE( subsidiary_id    = NULL     OR NULL IS NULL )
   AND( employee_id      = NULL     OR NULL IS NULL )
   AND( UPPER(last_name) = 'WINAND' OR 'WINAND' IS NULL )

---------------------------------------------------------------
|Id | Operation                   | Name        | Rows | Cost |
---------------------------------------------------------------
| 0 | SELECT STATEMENT            |             |    1 |    2 |
| 1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES   |    1 |    2 |
|*2 |   INDEX RANGE SCAN          | EMP_UP_NAME |    1 |    1 |
---------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
  2 - access(UPPER("LAST_NAME")='WINAND')

これは解決策とは言えません。そういう条件にすればデータベースはうまくできる、と証明したに過ぎません。

動的なクエリに対する一番の解は、動的SQLです。KISSの 原則に従い、データベースには今何が必要かだけを伝え、それ以外は伝える必要はないのです。

SELECT first_name, last_name, subsidiary_id, employee_id
  FROM employees
 WHERE UPPER(last_name) = :name

クエリではバインドパラメータを使っていることに注意しましょう。

この節で挙げた問題は、広範囲にわたるものです。実行計画の共有キャッシュを使う全てのデータベースには、この問題をうまく処理する 機能がありますが、その多くは新しい問題やバグを抱えています。

ヒューリスティックな手法によって、「スマートなロジック」の問題はある程度までは解決できますが、そういった手法は、列のヒストグラムや LIKE式にバインドパラメータを当てはめて解決しようという試みです。

最適な実行計画を得られる最も信頼できる方法は、SQL文において不要なフィルタを使わないことです。