理系学生日記

おまえはいつまで学生気分なのか

Query Transformation と OR-expansion

db

もろもろ悲しい目にあったので、もろもろまとめないといけないという使命感に駆られています。
Oracle Database に限らず DBMS というのは、SQL をいかに速く実行したろかと粘着しまくるようにできていて、アクセスパス解析だったりを駆使しまくります。その技術の一つに Query Transformation というのがあり、日本語だと「問合わせの変換」と呼ばれますが、SQL を「こうした方が速いやろがアホか」と DBMS 自身が書き換えちゃうヤツです。もちろん、SQL の論理的な意味は変化させず、です。

Query Transformation にはいくつかのパターンがあります。

  • Order-By Elimination
  • Common Sub-expression Elimincation
  • Predicate Transformation

などなど。
その中に Or-Expansion と言うヤツがあって、まぁこいつがぼくを八つ裂きにしていった。


Or-Expansion、具体的な例を見てもらった方が話が速いと思うので、https://blogs.oracle.com/optimizer/entry/or_expansion_transformation からいくつか拝借します。
元々入力された SQL がこうだとしましょう。

Select *
From products
Where prod_category ='Photo' or prod_subcategory ='Camera Media';

このままだと、Oracle は products テーブルにフルスキャンをかけたりします。悲しいですね。
OR で繋がれた条件に対してインデックススキャンができないことが問題なので、Oracle のオプティマイザは、この SQL を以下のように変換します。これが Or-Expansion って呼ばれるヤツです。

Select *
  From products
  Where prod_subcategory ='Camera Media'
UNION ALL
Select *
  From products
  Where prod_category ='Photo'
    And lnnvl(prod_subcategory ='Camera Media');

UNION ALL で繋がれた 2 つ目の SQL に lnnvl が使われているのでメンドくさい感じですが、要するに 2 つの OR 文で構成された SQL を、2 つの SQL に分割し UNION ALL で繋いでます。こうすることで、2 つの SQL はそれぞれインデックススキャンを使用することができ、最後に UNION ALL すれば論理的に意味は同じになりますよね、と。


基本的にオプティマイザは「よかれ」とおもってこういうことをします。当然です。
しかし、これが逆効果になり得るケースというのもある。こうなったらどうしようもない。どうしようもない！！！！マジで！！！！！　なんなの！！！！！！いったいなんなの！！！！！！！！！わざわざ書き換えといて劣化とかなんなの！！！！！！！！！！
どうしようもないので、OR-expansion 無効化しましょう。ヒント句 NO_EXPAND で OR-expansion の最適化を無効にするよう指示することができます。アグレッシブに攻めたてるならパラメータ_no_or_expansionを false にするのも良いでしょう。

その他参考