SWAP JOIN INPUTS

基本情報

 

名称

 

SWAP_JOIN_INPUTS

 

Syntax

 

/*+ SWAP_JOIN_INPUTS(table_name) */

 

サポート

 

10g R1 ~

 

説明

 

Oracle 10gで追加されたRight Joinを制御するための情報である。 Right JoinというJoin Orderが左側(Left)に来るしかないTableのJoin順序を右側(Right)に来るように変更するという意味です。 例えば次のようなQueryを仮定してましょう。

 

select * from t1, t2 where t1.c1 = t2.c1(+);

 

この場合には、Outer Joinの属性上Join順序は常に{t1 – > t2}となります。すなわち、Table t1は常にJoinの左(Left)に来るようになります。Hash Joinの場合には、Table t1はBuild Tableになります。もしTable t1の大きさが非常に大きい場合Buildプロセスは非常に重くなるのです。実際にOracle9iまでは、このような制約により、パフォーマンスの問題が発生しました。

 

しかし、Oracle10gから、このような場合にOracleのJoin順序を{t2 – > t1}に変更してくれるのです。すなわち、Joinの左(Left)の位置から右(Right)の位置に変更します。この時、内部的に使用されているH情報がSWAP_JOIN_INPUTS です。

 

これらRight JoinメカニズムはHash Outer Join、Hash Semi Join、Hash Anti Joinで主に使用されます。

 

使用例

 

使用方法は以下の通りです。

 

UKJA@ ukja102> - Table t1は非常に大きい。
UKJA@ ukja102> create table t1(c1、c2)
2 as
3 select level、rpad('x'、10)
4 from dual
5 connect by level<=200000
6;

Table created。

UKJA@ ukja102> - 一方Table t2ははるかに小さい。
UKJA@ ukja102> create table t2(c1、c2)
2 as
3 select level、rpad('x'、10)
4 from dual
5 connect by level<=100
6;

Table created。

UKJA@ ukja102> exec dbms_stats.gather_table_stats(user、「&1」、no_invalidate=> false);

PL/ SQL procedure successfully completed。

UKJA@ ukja102> exec dbms_stats.gather_table_stats(user、「&1」、no_invalidate=> false);

PL/ SQL procedure successfully completed。

UKJA@ ukja102> - Outer Joinでサイズが小さいTable t2がBuild Tableになると、パフォーマンスに有利です。
UKJA@ ukja102> select/*+ gather_plan_statistics*/ count(*)
2 from t1、t2
3 where t2.c1= t1.c1(+)
4;

COUNT(*)
----------
100


UKJA@ukja102> select * from table(dbms_xplan.display_cursor
                    (null, null, 'allstats last outline'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
SQL_ID  fp5uv3ugpja3h, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from t1, t2 where t2.c1 = t1.c1(+)

Plan hash value: 851489259

----------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   |
----------------------------------------------------------------------------
|   1 |  SORT AGGREGATE     |      |      1 |      1 |      1 |00:00:02.53 |  
|*  2 |   HASH JOIN OUTER   |      |      1 |    100 |    100 |00:00:02.53 |  
|   3 |    TABLE ACCESS FULL| T2   |      1 |    100 |    100 |00:00:00.01 |  
|   4 |    TABLE ACCESS FULL| T1   |      1 |    199K|    200K|00:00:00.80 |   
-----------------------------------------------------------------------------
--------------------------------------
| Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------
|     588 |       |       |          |
|     588 |  1066K|  1066K| 1146K (0)|
|       3 |       |       |          |
|     585 |       |       |          |
--------------------------------------


...
36 rows selected.

UKJA@ ukja102> - しかし、サイズが大きいTable t1がBuild Tableになると、パフォーマンスに不利です。
しかし、OracleはTable t1はなく、Table t2がBuild TableになるようにJoin順序を変更します。
UKJA@ ukja102> select/*+ gather_plan_statistics*/ count(*)
2 from t1、t2
3 where t1.c1= t2.c1(+)
4;

COUNT(*)
----------
200000

UKJA@ ukja102>
UKJA@ ukja102> select* from table(dbms_xplan.display_cursor
                                  (null、null、「allstats last outline'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
SQL_ID  c141vrf262yg7, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from t1, t2 where t1.c1 = t2.c1(+)

Plan hash value: 3306442619

----------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   |
----------------------------------------------------------------------------
|   1 |  SORT AGGREGATE     |      |      1 |      1 |      1 |00:00:02.53 | 
|*  2 |   HASH JOIN OUTER   |      |      1 |    100 |    100 |00:00:02.53 |
|   3 |    TABLE ACCESS FULL| T2   |      1 |    100 |    100 |00:00:00.01 | 
|   4 |    TABLE ACCESS FULL| T1   |      1 |    199K|    200K|00:00:00.80 | 
----------------------------------------------------------------------------
--------------------------------------
| Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------
|     588 |       |       |          |
|     588 |  1066K|  1066K| 1146K (0)|
|       3 |       |       |          |
|     585 |       |       |          |
--------------------------------------


Outline Data
-------------

/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "T1"@"SEL$1")
FULL(@"SEL$1" "T2"@"SEL$1")
LEADING(@"SEL$1" "T1"@"SEL$1" "T2"@"SEL$1")
USE_HASH(@"SEL$1" "T2"@"SEL$1")
SWAP_JOIN_INPUTS(@"SEL$1" "T2"@"SEL$1")
END_OUTLINE_DATA
*/

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("T1"."C1"="T2"."C1")

37 rows selected.

UKJA@ ukja102> - もしNO_SWAP_JOIN_INPUTS Hintを使用して、SwapをDisableさせると
          非常に非効率的なJoin順になります。
UKJA@ ukja102> select/*+ gather_plan_statistics no_swap_join_inputs(t2)*/ count(*)
2 from t1、t2
3 where t1.c1= t2.c1(+)
4;

COUNT(*)
----------
200000

UKJA@ ukja102> select* from table(dbms_xplan.display_cursor
                      (null、null、「allstats last outline'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
-------------------------------------
select /*+ gather_plan_statistics no_swap_join_inputs(t2) */ count(*) 
                          from t1, t2 where t1.c1 = t2.c1(+)

Plan hash value: 3781991007

----------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   |
----------------------------------------------------------------------------
|   1 |  SORT AGGREGATE     |      |      1 |      1 |      1 |00:00:02.53 |
|*  2 |   HASH JOIN OUTER   |      |      1 |    100 |    100 |00:00:02.53 |
|   3 |    TABLE ACCESS FULL| T2   |      1 |    100 |    100 |00:00:00.01 |
|   4 |    TABLE ACCESS FULL| T1   |      1 |    199K|    200K|00:00:00.80 |
----------------------------------------------------------------------------
--------------------------------------
| Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------
|     588 |       |       |          |
|     588 |  1066K|  1066K| 1146K (0)|
|       3 |       |       |          |
|     585 |       |       |          |
--------------------------------------



Outline Data
-------------

/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "T1"@"SEL$1")
FULL(@"SEL$1" "T2"@"SEL$1")
LEADING(@"SEL$1" "T1"@"SEL$1" "T2"@"SEL$1")
USE_HASH(@"SEL$1" "T2"@"SEL$1")
END_OUTLINE_DATA
*/

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("T1"."C1"="T2"."C1")

36 rows selected.

 

関連情報

 

1.Right Join – The secret of swapping join input
2.NO_SWAP_JOIN_INPUTS Hint