今回はOracleの実行計画のうち、テーブルの結合について調査、検証しました。
テーブルの結合方法は4種類（ネステッドループ結合、ソートマージ結合、ハッシュ結合、直積結合）あり、それぞれ下記の図のような特徴があります。
なお、3つ以上の表を結合する場合、先ず2つの表を結合し、その結果と3つ目の表を結合します（すべての表が結合されて結果が生成されるまでこれを繰り返す）
■前提
・DBMS_XPLAN.DISPLAY_CURSOR()のformatパラメータに「'ALL ALLSTATS LAST'」は指定せずに実行計画を取得
■検証環境
OS：Oracle Linux 6.5
DB/GI：Oracle Database 12c Release 1 (12.1.0.2.0) Enterprise Edition
※2ノードRAC(管理者管理型DB)
■検証パターン
①ネステッドループ結合（NESTED LOOPS）
②ハッシュ結合（HASH JOIN）
③ソートマージ結合（SORT JOIN / MERGE JOIN）
④直積結合（デカルト積）
■検証
①ネステッドループ結合（NESTED LOOPS）
外部表（駆動表）から検索条件に一致するキー値を1行ずつ取得し、取得したキー値を内部表のキー値と突き合わせてキー値が同じ場合、データを結合する
外部表から取得するキー値が少なければ内部表に対するループ回数も減るため、検索条件で取得するキー値の行数が少ない方を外部表に指定するのがセオリーとなっている（結合列のカーディナリティが低い方を外部表に指定する）
最初の1件を返すのが速いため、OLTP向き
少量データの結合に採用される可能性が高い
※プログラムのfor文やwhile文の多重ループのイメージ
while { ※外部表ループ
外部表から検索条件に一致するキー値を1行取得
→1行もなければループから抜ける
while { ※内部表ループ
外部表から取得したキー値に一致する内部表のデータを1行取得
→1行もなければループから抜ける
}
}
【発生条件】
・外部表、内部表の検索条件に一致するデータが比較的少量
・外部表、内部表の検索条件に指定する列に索引がある
【ヒント句】
/*+ USE_NL(内部表別名) LEADING(外部表別名) */
※USE_NL：指定した内部表を使用してネステッドループ結合する
※LEADING：表を結合する順序
【検証手順】
1. SELECT（ネステッドループ結合）を実行
2. 実行計画を確認
【作業ログ】
1. SELECT（ネステッドループ結合）を実行
SQL> SELECT /* XPLAN_TEST1 */ E.EMPNO, E.ENAME, D.DEPTNO, D.DNAME FROM DEPT D, EMP E WHERE D.DEPTNO = E.DEPTNO AND D.DEPTNO = 10;
EMPNO ENAME DEPTNO DNAME
---------- --------------- ---------- ---------------
7782 CLARK 10 ACCOUNTING
7839 KING 10 ACCOUNTING
7934 MILLER 10 ACCOUNTING
2. 実行計画を確認
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('05wg5gppgxj6w'));
PLAN_TABLE_OUTPUT
-----------------
SQL_ID 05wg5gppgxj6w, child number 0
-------------------------------------
SELECT /* XPLAN_TEST1 */ E.EMPNO, E.ENAME, D.DEPTNO, D.DNAME FROM DEPT
D, EMP E WHERE D.DEPTNO = E.DEPTNO AND D.DEPTNO = 10
Plan hash value: 1561127466
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | NESTED LOOPS | | 3 | 165 | 1 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 22 | 1 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 1 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP | 3 | 99 | 0 (0)| |
|* 5 | INDEX RANGE SCAN | IDX1_EMP | 3 | | 0 (0)| |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
【実行順序】
3→2→5→4→1→0
②ハッシュ結合（HASH JOIN）
結合対象の一方の表をメモリ（PGA）上に展開後、ハッシュ演算を適用してハッシュ表を作成し、もう一方の表のキー値（ハッシュ関数を適用したキー値）と突き合わせてキー値（ハッシュ値）が同じ場合、データを結合する
ハッシュ値をもとに結合するため、結合条件は等価結合（'='）になる
PGAに作成されるハッシュ表が小さくなり、結合処理が効率的になるため、カーディナリティが低い方の表を先に処理するのがセオリーとなっている
最初にハッシュ表を生成するためフルスキャンが発生するが、以降の結合処理はPGAで行われ非常に高速なため、OLAP向き（大量のレコードを扱うバッチ処理や帳票処理、DWHなど）
大量データの結合に採用される可能性が高い
※PGAが不足した場合、一時表領域が使用される（ディスクI/Oによる性能劣化が発生する可能性がある）
【発生条件】
・各表の検索条件に一致するデータが比較的大量
・各表の検索条件に指定する列に索引がない
・各表の検索条件が等価条件
【ヒント句】
/*+ USE_HASH(内側の表別名) LEADING(外側の表別名) */
※USE_HASH：指定した(内側の)表を使用してハッシュ結合する
※ハッシュ結合やソートマージ結合は外部表、内部表という呼び方をしないため、あえて外部表に相当する表を「外側の表」、内部表に相当する表を「内側の表」と書いています
【検証手順】
1. SELECT（ハッシュ結合）を実行
2. 実行計画を確認
【作業ログ】
1. SELECT（ハッシュ結合）を実行
SQL> SELECT /* XPLAN_TEST2 */ E.EMPNO, E.ENAME, D.DEPTNO, D.DNAME FROM DEPT D, EMP E WHERE D.DEPTNO = E.DEPTNO;
EMPNO ENAME DEPTNO DNAME
---------- --------------- ---------- ---------------
7369 SMITH 20 RESEARCH
7499 ALLEN 30 SALES
7521 WARD 30 SALES
7566 JONES 20 RESEARCH
7654 MARTIN 30 SALES
7698 BLAKE 30 SALES
7782 CLARK 10 ACCOUNTING
7788 SCOTT 20 RESEARCH
7839 KING 10 ACCOUNTING
7844 TURNER 30 SALES
7876 ADAMS 20 RESEARCH
7900 JAMES 30 SALES
7902 FORD 20 RESEARCH
7934 MILLER 10 ACCOUNTING
14行が選択されました。
2. 実行計画を確認
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('3kbj95gsq0ftk'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 3kbj95gsq0ftk, child number 0
-------------------------------------
SELECT /* XPLAN_TEST2 */ E.EMPNO, E.ENAME, D.DEPTNO, D.DNAME FROM DEPT
D, EMP E WHERE D.DEPTNO = E.DEPTNO
Plan hash value: 615168685
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
|* 1 | HASH JOIN | | 14 | 770 | 4 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 4 | 88 | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 14 | 462 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("D"."DEPTNO"="E"."DEPTNO")
【実行順序】
2→3→1→0
③ソートマージ結合（SORT JOIN / MERGE JOIN）
結合対象の各表をメモリ（PGA）上に展開後、各表の結合列のキー値でソートし、ソート結果をPGAでマージしてデータを結合する
オペレーションの「SORT JOIN」はソート処理、「MERGE JOIN」はマージ処理
ソートマージ結合も大量データの結合に適しているが、結合条件に非等価結合（'>'、'<'、BETWEENなど）を使用した場合、採用される可能性が高い
※PGAが不足した場合、一時表領域が使用される（ディスクI/Oによる性能劣化が発生する可能性がある）
【発生条件】
・各表の検索条件に一致するデータが比較的大量
・各表の検索条件に指定する列に索引がない
・各表の検索条件が非等価条件
【ヒント句】
/*+ USE_MERGE(内側の表別名) LEADING(外側の表別名) */
※USE_MERGE：指定した表を使用してソートマージ結合する
※ハッシュ結合やソートマージ結合は外部表、内部表という呼び方をしないため、あえて外部表に相当する表を「外側の表」、内部表に相当する表を「内側の表」と書いています
【検証手順】
※発生条件のとおり検証しましたが再現しなかったため、ヒント句を使用して検証しました
1. SELECT（ソートマージ結合）を実行
2. 実行計画を確認
【作業ログ】
1. SELECT（ソートマージ結合）を実行
SQL> SELECT /* XPLAN_TEST3 */ /*+ USE_MERGE(e) LEADING(d) */ E.EMPNO, E.ENAME, D.DEPTNO, D.DNAME FROM DEPT D, EMP E WHERE D.DEPTNO < E.DEPTNO;
EMPNO ENAME DEPTNO DNAME
---------- --------------- ---------- ---------------
7566 JONES 10 ACCOUNTING
7902 FORD 10 ACCOUNTING
7876 ADAMS 10 ACCOUNTING
7369 SMITH 10 ACCOUNTING
7788 SCOTT 10 ACCOUNTING
7521 WARD 10 ACCOUNTING
7844 TURNER 10 ACCOUNTING
7499 ALLEN 10 ACCOUNTING
7900 JAMES 10 ACCOUNTING
7698 BLAKE 10 ACCOUNTING
7654 MARTIN 10 ACCOUNTING
7521 WARD 20 RESEARCH
7844 TURNER 20 RESEARCH
7499 ALLEN 20 RESEARCH
7900 JAMES 20 RESEARCH
7698 BLAKE 20 RESEARCH
7654 MARTIN 20 RESEARCH
17行が選択されました。
2. 実行計画を確認
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('73bv800j2mbhd'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 73bv800j2mbhd, child number 0
-------------------------------------
SELECT /* XPLAN_TEST3 */ /*+ USE_MERGE(e) LEADING(d) */ E.EMPNO,
E.ENAME, D.DEPTNO, D.DNAME FROM DEPT D, EMP E WHERE D.DEPTNO < E.DEPTNO
Plan hash value: 1407029907
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6 (100)| |
| 1 | MERGE JOIN | | 3 | 165 | 6 (34)| 00:00:01 |
| 2 | SORT JOIN | | 4 | 88 | 3 (34)| 00:00:01 |
| 3 | TABLE ACCESS FULL| DEPT | 4 | 88 | 2 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 14 | 462 | 3 (34)| 00:00:01 |
| 5 | TABLE ACCESS FULL| EMP | 14 | 462 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("D"."DEPTNO"<"E"."DEPTNO")
filter("D"."DEPTNO"<"E"."DEPTNO")
【実行順序】
3→2→5→4→1→0
④直積結合（デカルト積）
結合対象の各表をメモリ（PGA）上に展開後、直積（各表の行数の掛け算）で各表のデータを結合する
結合条件を指定しない場合、採用される結合方法
オペレーションの「MERGE JOIN CARTESIAN」は結合条件を指定しないで直積を取得する処理、「BUFFER SORT」は内部表の行をあらかじめソートしておく処理
【発生条件】
・各表を結合するための検索条件（結合条件）を指定しない
【ヒント句】
なし
【検証手順】
1. SELECT（直積結合）を実行
2. 実行計画を確認
【作業ログ】
1. SELECT（直積結合）を実行
SQL> SELECT /* XPLAN_TEST4 */ E.EMPNO, E.ENAME, D.DEPTNO, D.DNAME FROM DEPT D, EMP E;
EMPNO ENAME DEPTNO DNAME
---------- --------------- ---------- ---------------
7369 SMITH 10 ACCOUNTING
7499 ALLEN 10 ACCOUNTING
7521 WARD 10 ACCOUNTING
（略）
7900 JAMES 40 OPERATIONS
7902 FORD 40 OPERATIONS
7934 MILLER 40 OPERATIONS
56行が選択されました。
2. 実行計画を確認
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('g86znfr5hwvcn'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID g86znfr5hwvcn, child number 0
-------------------------------------
SELECT /* XPLAN_TEST4 */ E.EMPNO, E.ENAME, D.DEPTNO, D.DNAME FROM DEPT
D, EMP E
Plan hash value: 2034389985
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 5 (100)| |
| 1 | MERGE JOIN CARTESIAN| | 56 | 2352 | 5 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL | DEPT | 4 | 88 | 2 (0)| 00:00:01 |
| 3 | BUFFER SORT | | 14 | 280 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | EMP | 14 | 280 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
【実行順序】
2→4→3→1→0
■参考資料
コメント
https://www.oracle.com/jp/a/tech/docs/technical-resources/100811-sql-tuning.pdf
NESTED LOOPS – Oracle SQL実行計画 | 技術情報 | 株式会社コーソル
HASH JOIN – Oracle SQL実行計画 | 技術情報 | 株式会社コーソル
SORT JOIN – Oracle SQL実行計画 | 技術情報 | 株式会社コーソル
MERGE JOIN – Oracle SQL実行計画 | 技術情報 | 株式会社コーソル
MERGE JOIN CARTESIAN – Oracle SQL実行計画 | 技術情報 | 株式会社コーソル
パフォーマンス改善と事前対策に役立つ Oracle SQLチューニング 本気で学ぶ実践的な考え方とテクニック
新・門外不出のOracle現場ワザ エキスパートが明かす運用・管理の極意
オラクルマスター教科書 ORACLE MASTER Expert パフォーマンス・チューニング編
Oracleの現場を効率化する100の技
■おわりに
記事内の各種オペレーションの発生条件はあくまでも書籍やサイト、実機検証で確認した条件ですが、必ず該当オペレーションが発生するわけではありません（どのオペレーションを採用するかは最終的にオプティマイザが判断します）
ヒント句を使用すれば採用されるオペレーションを狙いやすくなります。
