マージ結合(merge join)とは
複数のテーブルを結合するSQLを実行すると、ORACLEはネステッドループ、ハッシュ、マージの3種類のいずれかの結合方法を選択して実行計画を立てます。マージ結合には直積(merge join cartesianの実行計画表示される。別名デカルト積)とソート/マージ結合がありますが本ページではソート/マージ結合の特徴やパフォーマンスについて記載します。
ソート/マージ結合の特徴
ソート/マージ結合は表同士をソートして結合する方法ですが、ハッシュ結合と比較するとパフォーマンスが悪い場合が多いため非等価結合等を含むようなSQL以外ではあまり選択されることはありません。 ソート/マージ結合には以下のような特徴があります。・RBO、CBO共に選択可能
ハッシュ結合とは異なりRBOで実行計画を立てる場合も選択可能です
・等価結合以外も可能
ハッシュ結合のように等価結合のみといった制限はなく非等価結合や範囲条件による結合が可能です
・少なくとも片方の表に対して結合対象の表に対して読み込みが終わらなければ結果を返すことができない
最初に小規模表のハッシュ表を作成した後に大規模表と比較するため少なくとも小規模表に対して全結合対象行を読み込んだ後でないと結果を戻すことができません。 したがって、ネステッドループと比較して最初の一件を戻す速度は劣ります。
・外部結合の場合結合順序が固定される
マージ結合ではネステッドループと同様、外部結合時の結合順序は固定となります。
TEST@ORCL112 > select /*+ LEADING(a b) USE_MERGE(b) */ * from tab1 a,tab2 b where a.col1=b.col1(+);
実行計画
----------------------------------------------------------
Plan hash value: 30541447
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN OUTER | | 1 | 12 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| TAB1 | 1 | 6 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_TAB1 | 1 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 1 | 6 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL | TAB2 | 1 | 6 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("A"."COL1"="B"."COL1"(+))
filter("A"."COL1"="B"."COL1"(+))
・結合条件式から内部表の索引スキャンは不可能
ソート/マージ結合はネステッドループのように外部表1行に対して毎回内部表にアクセスするようなロジックではないため 結合条件列からの索引アクセスはできません。以下のように各表に閉じた条件に対する索引アクセスは可能です。
TEST@ORCL112 > select /*+ LEADING(a b) USE_MERGE(b) */ * from tab1 a,tab2 b where a.col1=b.col1 and a.col2=1 and b.col2=1;
実行計画
----------------------------------------------------------
Plan hash value: 57037545
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 6 (34)| 00:00:01 |
| 1 | MERGE JOIN | | 1 | 12 | 6 (34)| 00:00:01 |
| 2 | SORT JOIN | | 1 | 6 | 3 (34)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| TAB1 | 1 | 6 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | I_TAB1 | 1 | | 1 (0)| 00:00:01 |
|* 5 | SORT JOIN | | 1 | 6 | 3 (34)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID| TAB2 | 1 | 6 | 2 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | I_TAB2 | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("A"."COL2"=1)
5 - access("A"."COL1"="B"."COL1")
filter("A"."COL1"="B"."COL1")
7 - access("B"."COL2"=1)
・ソートデータが大きくなると一時領域への書き出しが発生する場合がある
ソートを実施するため、ソート量が多くなりデータが多くPGAにハッシュ表が入りきらなくなった場合は 一時表へのDISK書き出しが発生し、DISK書き出しが発生した途端パフォーマンスが悪化する傾向にあります。
ソート/マージ結合のパフォーマンス
ソート/マージ結合のcostや処理時間は基本的には以下のように考えられます。表1のアクセス時間＋表2のアクセス時間＋ソートの処理時間
ネステッドループと比較して、ハッシュ結合が早くなるのは以下のようなSQLとなります。・結合対象行が多いかつ十分なメモリ(UGA)が確保できる(ソートがメモリ内で完結し一時表への書き出しが必要ない、または少ない)
ハッシュ結合と比較した場合は以下のようなケースでソート/マージ結合に優位性があります。
・事前ソート済み(ソート済みの状態でデータを取得できる索引アクセスや、その前の行ソースでソート済みになっている場合)
・ソート操作を終了する必要がない
マニュアル
・概要・パフォーマンス・チューニングガイド
折りたたみ
展開