ヒント句で表結合の仕方を固定したいんです

このSQLに対して実験。

select * from employees e, jobs j where e.job_id = j.job_id;

ヒント句なし

------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |   107 | 10914 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN                  |           |   107 | 10914 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| JOBS      |    19 |   627 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | JOB_ID_PK |    19 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |           |   107 |  7383 |     4  (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL         | EMPLOYEES |   107 |  7383 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

ネステッドループ結合にする

USE_NLに内部表を指定する形で強制できる。

フルスキャンする外部表にEMPLOYEESを、インデックススキャンする内部表にJOBSを指定すると

SQL> select /*+ ORDERED USE_NL(j) */ * from employees e, jobs j where e.job_id = j.job_id;

108行が選択されました。


実行計画
----------------------------------------------------------
Plan hash value: 1536616604

------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |   107 | 10914 |   110   (0)| 00:00:02 |
|   1 |  NESTED LOOPS                |           |       |       |            |          |
|   2 |   NESTED LOOPS               |           |   107 | 10914 |   110   (0)| 00:00:02 |
|   3 |    TABLE ACCESS FULL         | EMPLOYEES |   107 |  7383 |     3   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | JOB_ID_PK |     1 |       |     0   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| JOBS      |     1 |    33 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

その逆は

SQL> select /*+ ORDERED USE_NL(e) */ * from jobs j, employees e where e.job_id = j.job_id;

108行が選択されました。


実行計画
----------------------------------------------------------
Plan hash value: 980169617

-------------------------------------------------------------------------------------------
| Id  | Operation                    | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |            |   107 | 10914 |    22   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |            |       |       |            |          |
|   2 |   NESTED LOOPS               |            |   107 | 10914 |    22   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL         | JOBS       |    19 |   627 |     3   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN          | EMP_JOB_IX |     6 |       |     0   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES  |     6 |   414 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

この例では後者のほうがコストが低い。EMPLOYEESの方がレコード数が多いので、外部表としてフルスキャンしている前者の方が単純に遅くなったと考えられる。
後者であっても、EMP_JOB_IXの効率が悪ければ前者より遅くなることはあるだろう。

ポイントは結合するレコード数が少ない方を外部表に、効率的な索引を使える方を内部表にすること。

ソートマージ結合にする

もとからソートマージでしたが……

SQL> select /*+ USE_MERGE(e, j) */ * from employees e, jobs j where e.job_id = j.job_id;

108行が選択されました。


実行計画
----------------------------------------------------------
Plan hash value: 303035560

------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |   107 | 10914 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN                  |           |   107 | 10914 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| JOBS      |    19 |   627 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | JOB_ID_PK |    19 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |           |   107 |  7383 |     4  (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL         | EMPLOYEES |   107 |  7383 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

ハッシュ結合にする

SQL> select /*+ USE_HASH(e, j) */ * from employees e, jobs j where e.job_id = j.job_id;

108行が選択されました。


実行計画
----------------------------------------------------------
Plan hash value: 1300016118

--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |   107 | 10914 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN         |           |   107 | 10914 |     7  (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| JOBS      |    19 |   627 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMPLOYEES |   107 |  7383 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------

ORDEREDについて少し

ちなみにANSI準拠のSQLでも変わらずORDEREDは必要だった。
SQLの書きっぷりが結合順を表していそうに見えなくもないから勝手に解釈してくれるかと思ったけど、さすがにそんなことはなかった。

あとUSE_NL以外でも効いた。でもネステッドループ結合以外ではあまり使い出がないかも。。

参考

Oracle SQLチューニング講座（10）：表の結合を極めるチューニング・テクニック (1/4) - ＠IT

いいところだけ引用します

ネステッド・ループ結合ソート／マージ結合ハッシュ結合

件数の多い表同士を結合し、全レコード出力する不向き結果を結合列でソートして出力する場合に有効。双方の結合列にNOT NULL制約が指定されており、索引が存在する場合、非常に効率的システム・リソースに余裕がある場合には最適

一方の表に絞り込み条件を指定して表を結合し、少数のレコードを出力する目安として索引を使用して表の15％以内の絞り込みであれば最適不向き目安として索引を使用して表の15％以上の絞り込みで、なおかつ等価条件があれば使用を検討

なんでや。。

ヒント句で表結合アルゴリズムを固定したいんです