[Hint] SWAP_JOIN_INPUTS

Hint 명

SWAP_JOIN_INPUTS

Syntax

/*+ SWAP_JOIN_INPUTS(table_name) */

지원

10g R1 ~

상세 설명

Oracle 10g에서 추가된 Right Join을 제어하기 위한 Hint이다. 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 과정이 매우 무거워진다. 실제로 Oracle 9i까지는 이런 제약으로 인해 성능 문제가 발생하곤 했다.

하지만 Oracle 10g부터는 이런 경우에 Oracle의 Join 순서를 { t2 –> t1 }으로 변경해준다. 

즉 Join의 왼쪽(Left) 위치에서 오른쪽(Right) 위치로 변경한다. 이때 내부적으로 사용되는 Hint가 SWAP_JOIN_INPUTS Hint이다.

이러한 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   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE     |      |      1 |      1 |      1 |00:00:02.53 |     588 |       |       |          |
|*  2 |   HASH JOIN OUTER   |      |      1 |    100 |    100 |00:00:02.53 |     588 |  1066K|  1066K| 1146K (0)|
|   3 |    TABLE ACCESS FULL| T2   |      1 |    100 |    100 |00:00:00.01 |       3 |       |       |          |
|   4 |    TABLE ACCESS FULL| T1   |      1 |    199K|    200K|00:00:00.80 |     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   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE        |      |      1 |      1 |      1 |00:00:03.76 |     588 |       |       |          |
|*  2 |   HASH JOIN RIGHT OUTER|      |      1 |    199K|    200K|00:00:03.20 |     588 |  1066K|  1066K| 1146K (0)|
|   3 |    TABLE ACCESS FULL   | T2   |      1 |    100 |    100 |00:00:00.01 |       3 |       |       |          |
|   4 |    TABLE ACCESS FULL   | T1   |      1 |    199K|    200K|00:00:00.80 |     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
---------------------------------------------------------------------------------------------------------------
SQL_ID  cbx3kd6puqhsd, child number 0
-------------------------------------
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   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE     |      |      1 |      1 |      1 |00:00:03.93 |     588 |       |       |          |
|*  2 |   HASH JOIN OUTER   |      |      1 |    199K|    200K|00:00:03.39 |     588 |  4486K|  1381K| 6557K (0)|
|   3 |    TABLE ACCESS FULL| T1   |      1 |    199K|    200K|00:00:00.80 |     585 |       |       |          |
|   4 |    TABLE ACCESS FULL| T2   |      1 |    100 |    100 |00:00:00.01 |       3 |       |       |          |
-----------------------------------------------------------------------------------------------------------------

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.

사례 예제 

select /*+ leading(dtl) full(dtl) PARALLEL(4) dynamic_sampling(0) */ COUNT(*) 
from spmkt.TST_ST_PRCHS_DTL dtl
where prchs_dt between to_date('20171101000000','YYYYMMDDHH24MISS') and to_date('20181031000000','YYYYMMDDHH24MISS')
--and dtl.prchs_dtl_id = '1'
and dtl.prchs_dtl_id = 1
and dtl.prchs_id like '1%'
and exists(
			select /*+ unnest hash_sj swap_join_inputs(TB_DP_SHPG_PROD) pq_distribute(TB_DP_SHPG_PROD none broadcast) */1 
			from spsac.TB_DP_SHPG_PROD 
			where prod_id = dtl.prod_id and PROD_CASE_CD IN ('DP006301','DP006302','DP006305'))
--and exists(select prod_id from TB_PR_PRCHS_DTL where prchs_id = dtl.prchs_id and CPN_USE_STATUS_CD = 3)
and exists(
			select /*+ unnest nl_sj */prod_id 
			from sppra.TB_PR_PRCHS_DTL 
			where prchs_id = dtl.prchs_id and CPN_USE_STATUS_CD = '3');