
SWAP JOIN INPUTS - 日本エクセム株式会社 Oracle 技術情報
基本情報
ヒント名
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