津島博士のパフォーマンス講座 
第46回 パーティション・プルーニングとハッシュ結合について

■津島博士による解説が動画でも! セミナー動画公開中です。
津島博士のパフォーマンス講座「パフォーマンス問題はなぜ起きるのか」  【WMV】 【MP4】 【PDF

皆さんこんにちは、ゴールデンウィーク頃から急に夏のような暑さになってきましたね。体がまだ慣れていないせいか、私は少しダウン気味ですが、皆さんは大丈夫でしょうか。
今回は、あまり説明できていないパーティション・プルーニングとハッシュ結合の実行計画について説明しようと思います。最近は、データの大規模化やBIツールの使用によるSQLの複雑化などによって、実行計画の分析も大変になっていると思います。そのため、使用する機会が多いパーティション・プルーニングやハッシュ結合について説明しますので、参考にしてください。

1. パーティション・プルーニング

まずは、第22回で説明したパーティション・プルーニングについてもう少し説明しましょう。
パーティションは、大規模なテーブルを小さなテーブルのように扱うことができる非常に便利な機能です。これは、アクセスするときにパーティション・プルーニングを行うことで、必要のないデータにアクセスしないようにしますが、これには静的プルーニングと動的プルーニングがあります(動的は、静的が不可能なときに使用されます)。静的プルーニングは、第33回で説明したパーティション統計を使用して最適な実行計画を作成することができます。それに対して、動的プルーニングは、実行時にパーティションを特定するので、パーティション統計を使用して実行計画を作成できません(バインド変数については、第7回の「優れたカーソル共有」などの使用によってパーティション統計が使用されます)。そのため、それぞれのパーティションのサイズが大きく異なると、最適な実行計画にならない場合があるので、注意して使用する必要があります。
以下に、パーティション・プルーニングされたときの実行計画の出力をまとめてみました(OperationのXXXXは、RANGEなどのパーティション・タイプです)。それぞれには有効な場合などがあるので、これを見てテーブル構造やSQLなどを検討してください。ただし、パラレル実行時には、Operationが出力されないので、Pstart/Pstopで判断する必要があります。

ここからは、第22回で説明していない以下の動的プルーニングとパーティション・プルーニングの注意点について説明していきます。

  • ネステッド・ループ結合
  • INリスト条件
  • OR条件
  • 複数述語(Oracle Database 11gR2から)

(1)ネステッド・ループ結合
ネステッド・ループ結合は、駆動表の1行に対して内部表を結合するときに、パーティション・プルーニングを使用することができますが、事前にパーティションを特定できないので、動的プルーニングになります。ただし、ネステッド・ループ結合は、少ない行数の結合に使用されるものなので、パーティション・プルーニングのことよりも索引を効果的に使用するようにします(第10回で説明したパーティション索引のように使用してください)。そうでない場合は、ハッシュ結合でジョイン・フィルターを使用する方が効果的になります。
動作すると以下の実行計画のように、索引アクセスのPstart/PstopにKEY、表アクセスのOperationに'TABLE ACCESS BY LOCAL INDEX ROWID'(グローバル索引は'TABLE ACCESS BY GLOBAL INDEX ROWID')が出力されます。

(2)INリスト条件
INリスト条件は、複数のOR条件になりパーティションが特定できないので、以下のような動的プルーニングになります(実行計画のOperationに'PARTITION RANGE INLIST'、Pstart/Pstopに'KEY(I)'が出力されます)。OR条件でもINリストに変換できるものはこれになります(例えば、'c1=10 OR c1=100'などです)。それぞれのパーティション・サイズが大きく異なる場合は、このような列でパーティションしないようにしてください。どうしてもパーティション化する場合には、同等なパーティション・サイズとアクセスするパーティションを少なくし易い、リスト・パーティションにすると良いでしょう。また、ハッシュ・パーティションは、範囲条件でパーティション・プルーニングされないので、この条件の方が効果的になります。このようなことを考慮して使用してください。

(3)OR条件
OR条件とは、INリストに変換できないOR条件(以下のような範囲条件などのOR条件)のことで、これも動的プルーニングになります(実行計画のOperationに'PARTITION RANGE OR'、Pstart/Pstopに'KEY(OR)'が出力されます)。これもINリスト条件と同じように、それぞれのパーティション・サイズが大きく異なる場合は、このような列でパーティションしないようにしてください。どうしてもパーティション化する場合には、範囲条件を使用しているので、レンジ・パーティションで少ないアクセスになるようにしましょう。

(4)複数述語
複数述語パーティション・プルーニング(Multi-Predicate Partition Pruning)は、静的と動的のどちらも使用することが可能になったOracle Database 11gR2からの機能で、これを使用すると更に効果的になる場合があります。
これまでは静的と動的の両方を使用できても静的プルーニングだけが適用されますが、静的プルーニングだけでは対象パーティションが多く最適でない場合があります。例えば、2013年と2014年のデータを月単位のパーティションに格納している表'sales'に対して、以下のSQL(2014年のQ1にアクセスする)を実行するような場合です。このとき静的と動的は次のように特定できますが、これまでは以下の実行計画のように、静的プルーニングが使用され効率よくありませんでした。

  • 静的プルーニングは12個のパーティション(p_201401~p_201412)
  • 動的プルーニングは6個のパーティション(p_201301~p_201303とp_201401~p_201403)

 

2つのパーティション・プルーニングを適用できれば3個のパーティション(p_201401~p_201403)に特定することができるので、Oracle Database 11gR2から複数の述語を使用したパーティション・プルーニングが可能になっています。これが動作すると実行計画は以下のように、Operationに'PARTITION RANGE AND'、Pstart/Pstopに'KEY(AP)'が出力されます(このAPは、AND PRUNINGの略になります)。

ただし、このようなジョイン・フィルターの場合は、'PART JOIN FILTER CREATE'が出力されてもジョイン・フィルターの使用位置('PARTITION RANGE JOIN-FILTER'、ファイルター名)の出力がなくなるので、使用されている場所が分からなくなります。この例の場合は、フィルター'BF0000'の使用場所が不明ですが、'KEY(AP)'が出力されているところが使用場所になるので注意してください。

パラレル実行のジョイン・フィルターについて
ご存知ない方のために(説明していなかったので)、ここでパラレル実行のジョイン・フィルター(ブルーム・フィルタリング)について簡単に説明します。
パーティション・プルーニングのジョイン・フィルターは、第22回で説明したように結合列でパーティションしていると動作しますが、結合列でパーティションしていない場合でもパラレル実行することでジョイン・フィルターを使用することができます。これは、Build表側の結合列でフィルターを作成するのは同じですが、Probe表側のパラレル・スキャン・プロセスが結合プロセスに転送するデータをフィルタリングするために使用します(データ転送と結合する行数を削減するために使用します)。これが動作すると実行計画は以下のように、Build表側に'JOIN FILTER CREATE'、Probe表側に'JOIN FILTER USE'が出力されます(Predicate InformationにもfilterとしてSYS_OP_BLOOM_FILTERが出力されます)。そのため、パラレル実行するときは、パーティション・プルーニングと間違えないようにしてください。

(5)パーティション・プルーニングの注意点
最後に、パーティション・プルーニングの注意点として、最近目にしたビューと文字列の範囲条件について説明します。

  • ビュー(インライン・ビュー)
    アクセス制御などでビューを使用しますが、ビューの外で条件を指定している場合にはパーティション・プルーニングは行われません。そのため、第29回で説明したSQL変換(ビュー・マージ、述語のプッシュ)を行いますが、複雑なビューは変換されない場合があります。GROUP BYやDISTICTなどが存在するビューについてはComplex View Mergingとして可能ですが、それ以外の複雑なビューは変換されないので、作成しないようにしてください。これはインライン・ビューでも同じことになるので、このようなことを意識してSQLを作成する必要があります。例えば、以下のような分析ファンクションRANKを使用したビューでは、MERGEヒントを使用してもマージしないので、パーティション・プルーニングされません。

    右側のようにPARTITION BY句の列でパーティションしている場合は行われます。第45回で説明したようにパーティション毎に処理できるので、述語のプッシュをすることができています(Predicate Informationから分かります)。ただし、このようなビュー/インライン・ビューは使用しないようにした方が良いでしょう。
  • 文字列の範囲条件
    日付や数字を文字列型で使用する場合があると思いますが、このときの範囲検索にも注意する必要があります(日付などの範囲ではなく内部コードの範囲になるからです)。例えば、この範囲('1409'-'1410'、'1410'-'1411'、'1412'-'1501')はすべて2ヶ月になりますが、内部的な範囲幅は異なっています。そのため、オプティマイザが正しく行数を見積もることができないので、最適な実行計画を作成することができなくなります(グローバル索引を選択してパーティション・プルーニングが行われない場合などもあります)。そのような場合には、ヒストグラムを作成する必要がありますが、個別値が多いと第27回や第35回で説明した頻度ヒストグラムが作成できないので、正確なヒストグラムが作成できなくなります。その場合に、範囲条件を行うと最適な実行計画にならない場合があるので、そのような場合にはヒントを使用するなどしてください。

2. ハッシュ結合

ここでは、これまであまり説明していなかったハッシュ結合について説明しましょう。
ハッシュ結合は、索引を使用しない(結合行数が多い)等価結合で最も効果的な結合方法になるので、使用する機会がとても多いと思います。ただし、基本的な使い方では問題ないですが、3表以上の複雑な結合では少し注意が必要になるので、実行計画やチューニングについて少しまとめてみました。
ハッシュ結合は、最初にメモリ上にハッシュ・テーブルを作成して、それを使用して結合を行うので、片方のテーブルが小さいと非常に効果的な動作になります(このとき最初にアクセスするのがBuild表、2番目にアクセスするのがProbe表です)。そのため、どちらをBuild表にするかによって性能が大きく変わってくるので、ハッシュ結合では結合順序(小さい方から)が非常に重要になります(基本は、オプティマイザが効果的に決定するかLEADINGヒントなどで調整します)。ただし、3表目からはBuild表とProbe表のどちらも可能になるので、どちらが効果的(結合結果とこの表のどちらが小さい)かを分析できることやLEADINGヒントだけでは調整できないことを知っておく必要があります。
以下の2つの実行計画は、3番目の表'tab1'が左側はBuild表、右側はProbe表になっています(Build表のときは'HASH JOIN'のすぐ下にきます)。

右側の実行計画は、表'tab1'をProbe表にするためにNO_SWAP_JOIN_INPUTSヒントを使用しています(表'tab2'と表'tab3'の結合の下に出力されていることからProbe表であることが分かります)。このように3表目からは、以下のヒントを使用することで、Build表とProbe表を指定することが可能になります(あまり使用することはないと思いますが覚えておくと良いでしょう)。

・SWAP_JOIN_INPUTSヒント(Build表にする)
・NO_SWAP_JOIN_INPUTSヒント(Probe表にする)

もう1つ注目して欲しいのが、左側の実行計画は表'tab1'のジョイン・フィルター'BF0000'を表'tab3'で使用しているということです(「複数述語」で説明したAND Pruningになっているので、Pstart/Pstopにジョイン・フィルター名は出力されませんが、ここで使用していることが分かります)。つまり、表'tab3'は、表'tab1'の後にアクセスしていることになるので、表'tab2'、'tab1'でハッシュ・テーブルを作成してから表'tab3'をハッシュ結合する動作になります。このような結合方法をRight-deep Joinと呼ぶので覚えておきましょう(Right-deep Joinについては、「Left-deep JoinとRight-deep Joinについて」を参照してください)。
このように3表以上になると、実行計画も複雑になるので、間違わないように分析してください。

Left-deep JoinとRight-deep Joinについて
ご存知ない方のために、ここでLeft-deep JoinとRight-deep Joinについて簡単に説明します。
通常の結合は、サイズの小さい(アクセスするデータの少ない)テーブルから結合していくので、結合結果に対して次の表を結合するように行います。ハッシュ結合では、結合結果からハッシュ・テーブルを作成して結合するように行います(これをLeft-deep Joinと呼びます)。ネステッド・ループ結合については、Left-deep Joinだけになります。ただし、スター・スキーマのように小さいテーブルから結合できない場合もあります(以下の例のように、表't1'がファクト表で、その他がディメンション表になっているような場合です)。

このような場合は、大きいサイズのファクト表を2番目に結合する必要があるので、効率が悪くなってしまいます。そのため、以下のようなRight-deep Joinという方法を行うことで、最適に結合することが可能になります。これは、すべてのディメンジョン表のハッシュ・テーブルを先に作成してからファクト表をアクセスするように行います(この例の場合は、ディメンジョン表't2'、't3'、't4'のハッシュ・テーブルを作成してからファクト表't1'を結合します)。これはオプティマイザが自動的に決定するので、どちらが動作したかを以下のように判断します(大きく異なるので分かりやすいと思います)。

以下のようにLEADINGヒントとSWAP_JOIN_INPUTSヒントを使用することで、この例のRight-deep Joinを明示的に指定することもできます。このように指定して行うことはあまりないと思いますが、実行計画の見方については知っておくのが良いでしょう。

3. おわりに

今回はパーティション・プルーニングとハッシュ結合について説明しましたが、少しは参考になりましたでしょうか。これからも頑張りますのでよろしくお願いします。質問をお待ちしています。
それでは、次回まで、ごきげんよう。