皆さんこんにちは、今年は急に寒くなることが多いですね。10月後半から急に冬のような寒さになったり暖かくなったりと、体調を崩しやすい天候が続きそうですので、皆さんも体調に気を付けてください。
今回は、第34回の続きとして、Oracle Database 12cからいくつか追加されたオプティマイザのSQL自動変換について説明しますので、参考にしてください。
1. Oracle Database 12cからの新機能
オプティマイザには、SQLから実行計画を作成すること以外に、最適な実行計画にするための機能がいくつかあります。その一つがSQLの自動変換になります。このSQLの自動変換には、第29回や第34回で説明したような多くの機能がありますが、Oracle Database 12c（Oracle12c）からもいくつか追加されているので、その追加された以下の機能について説明します。最初の二つはセミ結合を行うことができるSQLを増やすための機能、残りの二つは外部結合を効果的に使用するための機能になり、より最適な実行ができるようになっています。
- Partial Join Evaluation
- Null Accepting Semi-Join
- Scalar Subquery Unnesting
- Multi-Table Left Outer Join
オプティマイザのSQL自動変換について
ご存知ない方のために（説明していなかったので）、ここでオプティマイザのSQL自動変換について簡単に説明します。
オプティマイザは、以下のような要素から構成されていて、SQL自動変換のことを内部的には問合せトランスフォーマ（問合せ変換）と呼びます。この問合せ変換では、実行されたSQLから最適と思われるSQLを作成しますが、そのSQLだけが対象になる訳ではありません。CBO（コストベース・オプティマイザ）の場合は、それぞれのアクセス・パスのコストを見積もり、そのコストの中から最適なものを決定します。つまり、CBOの問合せ変換では、アクセス・パスの候補が作成されるイメージになるので、同じようなSQLでも変換する場合としない場合があります。このようにコストに基づいて変換することで、SQLを作成する負荷が軽減されるのは非常に嬉しいことです。ただし、オプティマイザ統計の内容によっては正しく動作しない場合もあるので、SQLチューニングではこの機能が効果的に動作しているかの確認なども必要になります。
それぞれの構成要素は、以下のような動作を行います。
- 問合せトランスフォーマ
構文解析されたSQLから最適なアクセス・パスになるようなSQLの候補を作成します。CBOの場合には、ここで実行計画を作成するSQL文が決定される訳ではありません。 - エスティメータ
それぞれのアクセス・パスに対してコスト計算を行います（CBOではオプティマイザ統計が使用されます）。 - プラン・ジェネレータ
エステメータに複数のアクセス・パスを計算させて、最終的に最もコストの低いアクセス・パスから実行計画の生成を行います（このときアクセス・パス数の制限も行います）。
（１）Partial Join Evaluation
まずは、Partial Join Evaluation（パーシャル結合評価）について説明します。
レポート出力などをする場合に、同じ値を並べて出力するのはあまり意味がないので、結果から重複値をなくすような処理はよく行うと思います。一般的には、DISTINCT演算子やGROUP BY句などを使用して行うので、そのようなSQLについて説明します。
以下のようなDISTINCT演算子などの重複行をなくすSQLでは、結合時に無駄な重複値を結合しないように、結合前に重複行を削減することが効果的です。そのため、Oracle Database 11g（Oracle11g）からは、第29回で説明したDISTINCT Placement（DISTINCTの配置の最適化）機能によって、以下の右側のような実行計画に変換します。
DISTINCT Placement機能でも効果的な表結合を行うことができますが、’HASH UNIQUE’処理を行うオーバーヘッドがあるので、重複行が非常に多いような場合（行数の削減が多いような場合）でないと最適ではありません。そのため、Oracle12cからのPartial Join Evaluationによって、以下の左側のようにセミ結合に変換することで改善します。つまり、以下の右側のSQLのように変換することで、より効果的に実行します。ただし、セミ結合に変換できるのは、結合する片方のテーブルの列を取得するようなSQLの場合だけになります（この例では、テーブル’t1’の列だけSELECTリストに指定しています）。このとき、内部表側の結合列には、重複値がないと効果がありません。ただし、第41回で説明した’HASH JOIN RIGHT SEMI’機能によって、テーブルを入れ替えてセミ結合することも可能です。
セミ結合は、第29回で説明したように、内部表側の重複データを無視してくれるので（1行見つけた時点でそのデータを終了するので）、効果的に結合することができるようになっています。これまでセミ結合は、EXISTS条件やIN条件で使用されていましたが、それ以外のSQLでも変換することが可能になります。つまり、このようなSQLでもEXISTSやINと同じように、インライン・ビューとセミ結合を自動的に使い分けることが可能になるので、更に使いやすいようになります。これはSQLの作成にはとても嬉しいことです。
この機能は、DISTINCT演算子以外にMAX()、MIN()、SUM(DISTINCT)、AVG(DISTINCT)、COUNT(DISTINCT)などの関数でも以下のように適用されます。
（２）Null Accepting Semi-Join
次に、Null Accepting Semi-Join （NULL許可のセミ結合）について説明します。
「ネストした副問合せの解除（副問合せのネスト解除）」で説明したように、EXISTS条件などはセミ結合に変換してくれますが、以下のように結合列で’IS NULL’条件を行うとセミ結合には変換できません。そのため、結合列にNULLが存在するような場合には、このようなSQLを作成する場合があるので注意が必要です。例えば、二つのマスタ・テーブルを結合するテーブルで、片方のマスタだけが存在するときでもデータ行を格納したい場合では、結合列にNULLなどを入れる必要があると思います（私はあまり使用しないので、具体的な例が思いつきませんでした）。
ただし、NULLに意味を持たせるとSQLが複雑になりやすいので（NULLは通常の値と同じように比較や結合をすることができないので）、できればそのような使い方がないことをお薦めします。
このようなSQLにしないことが良いのですが、どうしても行うような場合もあるので、Oracle12cから新しいタイプのセミ結合’Null Accepting Semi-Join’が追加されています。これは、表結合の左側の結合列に対して、NULLチェックを行うようにセミ結合を拡張することで、セミ結合の実行を可能にします（この例の場合は、列’t1.id’でNULLチェックが行われることになります）。このセミ結合は、NULLの場合に主問合せのテーブルから対応する行を返しますが、NULLでない場合にセミ結合を行うようになります。このときの実行計画には、以下のように’HASH JOIN RIGHT SEMI NA’（または’HASH JOIN SEMI NA’）と出力されます。また、Predicate Informationにも’IS NULL’条件が出力されなくなります。
このようにセミ結合が可能なSQLは、できるだけセミ結合させることで、最適に実行できるようにします。
（３）Scalar Subquery Unnesting
次に、Scalar Subquery Unnesting（スカラー副問合せのネスト解除）について説明します。
Scalar Subquery（スカラー副問合せ）は、単一値を返すような副問合せですが、これは「ネストした副問合せ」と同じように、主問合せの行数だけ副問合せを実行する必要があるので、以下の実行計画のように効果的な動作をすることができません。ただし、このSQLでは、副問合せ’max_id’の結果が存在しない場合に、以下の実行結果のようにNULLにする必要があるので、セミ結合を使用することはできません。
そのため、Oracle12cからは、このスカラー副問合せもネスト解除するために（通常の結合方法ができるように）、以下の左側のように外部結合に変換します。つまり、以下の右側のSQLのように変換することで効果的に実行します。これが同じ結果になるSQLとして、使い分けるのは非常に難しいことです。そのため、このようなことを気にする必要がないのは非常に嬉しいことです。
このような通常の結合になることで、Group By Placement（Group Byの配置の最適化）なども可能になるので、更に効果的に動作するようになります。
（４）Multi-Table Left Outer Join
最後に、Multi-Table Left Outer Join（複数表の左外部結合）について説明します。 左側に複数のテーブルがある左外部結合は、第37回でOracle固有の外部結合でも実行できるようになりましたと説明しましたが、実行計画についても拡張されていることを説明していなかったので、この機能についてもう少し説明します。Oracle11gまでのANSI準拠の外部結合は、以下のように左側の複数表に対して、特殊なビューを作成しますが、それをビュー・マージすることができません。そのため、結合順や結合方法などに限界があるので、効果的な表結合ができない場合があります。
Oracle12cからは、以下のようにビュー・マージすることが可能になっているので、最適な実行計画を作成できるようになります。例えば、以下の右側のように、LEADINGヒントで表結合の順番などを変更することもできるようになります。
以下のようなOracle固有の外部結合でも同様にビュー・マージされます。
2. おわりに
今回はOracle Database 12cからいくつか追加されたSQL自動変換について説明しましたが、少しは参考になりましたでしょうか。このような機能は非常に便利ですが、常に最適になるとは限らないので、なぜ効果的なのかなどを知っておくのも重要です。また、チューニングな どにも有効だと思うので、参考にしてください。次回も頑張りますのでよろしくお願いします。
それでは、次回まで、ごきげんよう。