ハッシュ結合をヒント句で制御する方法

ハッシュ結合の実行計画は一部、公開されているヒント句だけでは選択させることができない実行計画が存在します。 本ページではこういった実行計画を明示的にヒントで選択させる方法を記載します。


ケース１ - ３つ以上のハッシュ結合

ハッシュ結合は結合する２つの表の内小さいほうをメモリ上にハッシュテーブルとして作成します。 したがって、A表->B表->C表の順にハッシュ結合で結合する場合、以下の2パターンが存在することになります。

  • AとBを結合した結果をハッシュテーブルとして作成 
    ----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |    39 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN          |      |     1 |    39 |     7  (15)| 00:00:01 |
|*  2 |   HASH JOIN         |      |     1 |    26 |     5  (20)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| A    |     1 |    13 |     2   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| B    |     1 |    13 |     2   (0)| 00:00:01 |
|   5 |   TABLE ACCESS FULL | C    |     1 |    13 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------
  • Cをハッシュテーブルとして作成 
    ----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |    39 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN          |      |     1 |    39 |     7  (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL | C    |     1 |    13 |     2   (0)| 00:00:01 |
|*  3 |   HASH JOIN         |      |     1 |    26 |     5  (20)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| A    |     1 |    13 |     2   (0)| 00:00:01 |
|   5 |    TABLE ACCESS FULL| B    |     1 |    13 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------

上記2パターンをそれぞれ明示的にヒント句で固定するには「(NO_)SWAP_JOIN_INPUTS」ヒントを利用します。 SWAP_JOIN_INPUTS(表別名)と指定すると表別名がハッシュ表となり、NO_SWAP_JOIN_INPUTS(表別名)とすると表別名がハッシュ表にならなくなります。

  • AとBを結合した結果をハッシュテーブルとして作成する場合のヒント指定 
    TEST@ORCL112SJIS > select /*+ leading(a b c) use_hash(b c) no_swap_join_inputs(c) */ * from a,b,c where a.col1=b.col1 and a.col1=c.col1;

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

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |    39 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN          |      |     1 |    39 |     7  (15)| 00:00:01 |
|*  2 |   HASH JOIN         |      |     1 |    26 |     5  (20)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| A    |     1 |    13 |     2   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| B    |     1 |    13 |     2   (0)| 00:00:01 |
|   5 |   TABLE ACCESS FULL | C    |     1 |    13 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------
  • Cをハッシュテーブルとして作成する場合のヒント指定 
    TEST@ORCL112SJIS > select /*+ leading(a b c) use_hash(b c) swap_join_inputs(c) */ * from a,b,c where a.col1=b.col1 and a.col1=c.col1;

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

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |    39 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN          |      |     1 |    39 |     7  (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL | C    |     1 |    13 |     2   (0)| 00:00:01 |
|*  3 |   HASH JOIN         |      |     1 |    26 |     5  (20)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| A    |     1 |    13 |     2   (0)| 00:00:01 |
|   5 |    TABLE ACCESS FULL| B    |     1 |    13 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------

ケース２ - ハッシュ結合の右側外部結合(hash join right outer)

ハッシュ結合は外部結合時もハッシュ表にする表をどちらにするか選択することが可能です。 A->B(外部結合)とする場合こちらも以下の2パターン存在します。

  • Aをハッシュテーブルとして作成する場合(左側外部結合) 
    ---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    26 |     5  (20)| 00:00:01 |
|*  1 |  HASH JOIN OUTER   |      |     1 |    26 |     5  (20)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| A    |     1 |    13 |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| B    |     1 |    13 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------
  • Bをハッシュテーブルとして作成する場合(右側外部結合) 
    ------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |     1 |    26 |     5  (20)| 00:00:01 |
|*  1 |  HASH JOIN RIGHT OUTER|      |     1 |    26 |     5  (20)| 00:00:01 |
|   2 |   TABLE ACCESS FULL   | B    |     1 |    13 |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL   | A    |     1 |    13 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------

このケースでもSWAP_JOIN_INPUTSヒントを指定することで実行計画を指定することが可能です。 注意点として、HASH JOIN RIGHT OUTERの実行計画はB->Aの順に結合しているように一見して見えますが、ヒントの指定はleading(a b)、use_hash(b)となることに注意してください。 なお、実際にORACLEがアクセスする順番はB->Aとなります。

  • Aをハッシュテーブルとして作成する場合 
    TEST@ORCL112SJIS > select /*+ leading(a b) use_hash(b) swap_join_inputs(a) */ * from a,b where a.col1=b.col1(+);

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

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    26 |     5  (20)| 00:00:01 |
|*  1 |  HASH JOIN OUTER   |      |     1 |    26 |     5  (20)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| A    |     1 |    13 |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| B    |     1 |    13 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------
  • Bをハッシュテーブルとして作成する場合 
    TEST@ORCL112SJIS > select /*+ leading(a b) use_hash(b) swap_join_inputs(b) */ * from a,b where a.col1=b.col1(+);

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

------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |     1 |    26 |     5  (20)| 00:00:01 |
|*  1 |  HASH JOIN RIGHT OUTER|      |     1 |    26 |     5  (20)| 00:00:01 |
|   2 |   TABLE ACCESS FULL   | B    |     1 |    13 |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL   | A    |     1 |    13 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------
★ORACLE案件承ります