ほとんどのRDBは、受け取ったクエリの最適な実行計画をプランナ/オプティマイザが計算する。
例えば、WHERE句の条件式も評価順序によってどのインデックスが使えるかが決まってくる。 RDBのプランナ/オプティマイザは、予想されるリソースコストが最小になるようにWHERE句の条件式の評価順序を入れ替える。 そして、RDBのユーザであるプログラマは、このような最適化の詳細を特に意識することなくクエリを書くことができる。
このように、「ユーザが技術的詳細を知らずにその技術を使える性質」のことを透過性 (transparency) という。 以上の例では、条件式の評価順序の最適化は透過性をもっていると言える。
しかし、Oracleでは条件式の評価順序の最適化の透過性が破れるケースが存在する。 その一例がOracle Communityに相談として寄せられていた。
Using to_number in a where clause — oracle-tech
| カラム1 | カラム2 |
|---|---|
'A' | '1' |
'A' | '2' |
'B' | 'X' |
'B' | 'Y' |
このようなテーブルが存在するとき、以下のクエリを実行する。
SELECT
*
FROM テーブル
WHERE カラム1 = 'A'
AND to_number(カラム2) BETWEEN 1 AND 2
すると
ORA-01722: 数値が無効です
というエラーが返り、クエリは失敗する。
カラム1 = 'A' の絞り込みの時点で、to_number(カラム2) が失敗するレコードが存在しないにも関わらずである。
最適化によって条件式の評価順序が入れ替えられた結果、すべてのレコードに対して to_number(カラム2) を実行しエラーになった。
つまり、条件式の評価順序の最適化の透過性が破れたということだ。
上記のような to_number に限った話であれば、Oracle 12c以上から使用可能な DEFAULT ON CONVERSION ERROR 句を使えばよい。
しかし、レガシー伝統のある現場では、12c未満のOracleが現役であることも多いだろう。
そこで、プランナ/オプティマイザが条件式の並び替えを行わないように、ORDERED_PREDICATES ヒント句を用いる。
ORDERED_PREDICATES ヒント句が付与されたSELECT文では、WHERE句に記述された通りに条件式を評価するようになる。
SELECT /*+ ORDERED_PREDICATES */
*
FROM テーブル
WHERE カラム1 = 'A'
AND to_number(カラム2) BETWEEN 1 AND 2
ただし、これはプランナ/オプティマイザの条件式の評価順序の最適化を無効にするため、SELECT文の実行過程で予期しない操作をしていないかを慎重に確認しなければならない。
Oracleのヒント句の詳細についてはWebで見れる情報が圧倒的に少ない。
かなり古いバージョンの情報にはなるが、以下のページでOracle 9iの ORDERED_PREDICATES ヒント句の情報を見つけた。