問合せの変換（Query Transformation）

新人研修のネタ。その１

はじめに
コストベース・ルールベースの違いについて聞かれると、統計情報を使って
実行計画を決めるか、決まったルールによって実行計画を決めるかの違いと
答えてしまいますが、実際はもう少し複雑でコストベースのオプティマイザ
にはルールベースの頃には無かったQuery Transformationという機能が追加
されており統計情報を元に受け取ったSQLの冗長な部分をけずったり置き換え
たりする最適化の機能が追加されています。


１．Query Transformationの中身
 Query Transformationは幾つかの機能に分かれており、Oracle11gだと下記の
 通り。（全てではありません。他にも公開・未公開含め幾つかあるはず）
 　Aggregate Subquery Elimination
 　Common subexpression elimination
 　Complex View Merging
 　Filter predicate push-down in a view
 　Group by placement
 　Intersect conversion into join
 　Join Elimination
 　Join predicate push down (JPPD)
 　Materialized view rewrite
 　Minus conversion into anti-join
 　Native Full Outer Join
 　OR expansion
 　Order by Elimination
 　Outer to inner join conversion
 　Predicate move-around
 　Star transformation
 　Subquery unnesting
   　IN / EXISTS -> SEMI JOIN
   　NOT IN / NOT EXISTS -> ANTI JOIN
   　Null-Aware and Single Null-Aware ANTI JOIN
 　Transitive Predicates

 　上記のそれぞれの機能のうち幾つかは、インターネット上の資料や
 　研修資料で公開されています。（インターネット上でCost Based  Query
 　TransformationやCBQTで検索してください。Oracleの研修だと
「SQL チューニングワークショップ」に概要説明が入っています)

ここでは判りやすいやつ1点
OR expansion
  　SELECT *
    　 　FROM emp
    　　WHERE job = 'CLERK' OR deptno = 10;
   　　のようなSQLがあり、jobとdeptnoにそれぞれINDEXがついている場合、
   　　下記のように置き換えることがあります。（かならずという訳ではなく
   　　統計情報等使って判定）

 　　SELECT *
  　　   FROM emp
    　　WHERE job = 'CLERK'
   　  UNION ALL
   　　SELECT *
     　  FROM emp
    　  WHERE deptno = 10 OR job <> 'CLERK' ;

２．最適化の無効化
話を聞くと便利そうなQuery Transformationですが、複雑でより過激に
進化している機能の為不具合が多く、この為か当機能を無効化する
非公開の初期化パラメータが用意されています。

_optimizer_cost_based_transformation
Query Transformation全部の機能のON,OFF
これをOFFにすると上記全てがOFFになる為、稼動後サポートから不具合
対応で設定変更を勧められても無理。

個別の機能についてもそれぞれ、ON、OFFが可能になるパラメータが用意
されていると思われますが、まとまった形では公開されていません。
個別の不具合対応で一つづつ調べている状況。
（おそらく、サポートの[1274553.1]に上げられているパラメータ全て
調べれば判ると思います）

    Order by Elimination
    　 _optimizer_order_by_elimination_enabled
    Optimizer Join Push Predicate
    　 _optimizer_multi_level_push_pred
    Group by placement
    　 _optimizer_group_by_placement



参考資料

Oracleのオプティマイザが問い合わせ処理を最適化する方法～ソースコードを書いている
エンジニアが語る

1. 問合せの変換（Query Transformation）の部分
これ読んでもらえれば気づくと思いますが、各機能の名前は資料によって
微妙に異なります。

Cost Based Query Transformations by Riyaj Shamsudeen
  インターネットの各サイトに出回っているRiyaj Shamsudeenの講演資料。

Optimizer Cost Based Query Transformation [ID 1082127.1]
サポートのページ

Dion Cho氏のページ
Query Transformation関連の記事有り。（韓国の方が書かれた日本語なので読みにくい所
はありますが、日本語で書かれた記事として貴重。）


ablog
  http://d.hatena.ne.jp/yohei-a/20110224/1298529324
    Vew Mergingの例示。　10053のログで結果の説明をされているのでわかりやすい。

Oracle10g Cost Base Optimizerにまつわる検証
  http://www.insight-tec.com/mailmagazine/ora3/vol228.html
  Insight Technologyさんの「おら! オラ! Oracle どっぷり検証生活」。10gの頃。