오라클 19c nl 조인 순서 제어 테스트

OS환경 : Oracle Linux 7.6 (64bit)

 

DB 환경 : Oracle Database 19.18.0.0

 

방법 : 오라클 19c nl 조인 순서 제어 테스트

일반적으로 hash join은 leading(A B C) use_hash(B) use_hash(C) 이렇게 힌트를 지정할 경우

A와 B테이블 간 조인에서 A는 무조건 build input으로 지정되고 B는 무조건 probe input으로 지정됨

그리고 C 테이블을 조인할때는 C테이블이 buile input이 될수도 있고, probe input이 될수도 있음

C 테이블이 어디로 갈지 지정하는 힌트는 swap_join_inputs(build input 지정), no_swap_join_inputs(probe input 지정) 임

 

 

하지만 nl 조인의 경우는 swap_join_inputs 와 같은 힌트가 존재하지 않음

동일하게 leading(A B C) use_nl(B) use_nl(C) 이렇게 있을때 

(A 조인 B) + C 로 풀리게됨

만약 A + (B 조인 C) 와 같은 형태로 풀기위해선 아래 내용처럼 인라인뷰를 사용해줘야함

 

 

테스트

샘플 테이블 생성

SQL> 
create table test1 (col1 number);
create table test2 (col1 number);
create table test3 (col1 number);

 

 

경우1. (A 조인 B) + C

(test1 join test2) -> test3

SQL> 
select /*+ gather_plan_statistics leading(A B C) use_nl(B) use_nl(C) */ *
from test1 A, test2 B, test3 C
where A.col1 = B.col1;
and B.col1 = C.col1;
 
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ADVANCED ALLSTATS LAST'));
 
-------------------------------------------------------------------------------------------------------------
| Id  | Operation        | Name  | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |        |       1 |          |       |     6 (100)|          |      0 |00:00:00.01 |
|   1 |  NESTED LOOPS        |        |       1 |        1 |    39 |     6    (0)| 00:00:01 |      0 |00:00:00.01 |
|   2 |   NESTED LOOPS        |        |       1 |        1 |    26 |     4    (0)| 00:00:01 |      0 |00:00:00.01 |
|   3 |    TABLE ACCESS FULL| TEST1 |       1 |        1 |    13 |     2    (0)| 00:00:01 |      0 |00:00:00.01 |
|*  4 |    TABLE ACCESS FULL| TEST2 |       0 |        1 |    13 |     2    (0)| 00:00:01 |      0 |00:00:00.01 |
|   5 |   TABLE ACCESS FULL | TEST3 |       0 |        1 |    13 |     2    (0)| 00:00:01 |      0 |00:00:00.01 |
-------------------------------------------------------------------------------------------------------------

 

 

경우2. A + (B 조인 C)

test1 -> (test2 join test3)

SQL> 
select /*+ gather_plan_statistics leading(A) use_nl(B) */ *
from test1 A,
(select /*+ leading(X) use_nl(Y) no_merge */ X.col1
from test2 X, test3 Y
where X.col1 = Y.col1) B
where A.col1 = B.col1;
 
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ADVANCED ALLSTATS LAST'));
 
--------------------------------------------------------------------------------------------------------------
| Id  | Operation         | Name  | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |         |        1 |        |       |     6 (100)|           |      0 |00:00:00.01 |
|   1 |  NESTED LOOPS         |         |        1 |      1 |    26 |     6     (0)| 00:00:01 |      0 |00:00:00.01 |
|   2 |   TABLE ACCESS FULL  | TEST1 |        1 |      1 |    13 |     2     (0)| 00:00:01 |      0 |00:00:00.01 |
|*  3 |   VIEW             |         |        0 |      1 |    13 |     4     (0)| 00:00:01 |      0 |00:00:00.01 |
|   4 |    NESTED LOOPS      |         |        0 |      1 |    26 |     4     (0)| 00:00:01 |      0 |00:00:00.01 |
|   5 |     TABLE ACCESS FULL| TEST2 |        0 |      1 |    13 |     2     (0)| 00:00:01 |      0 |00:00:00.01 |
|*  6 |     TABLE ACCESS FULL| TEST3 |        0 |      1 |    13 |     2     (0)| 00:00:01 |      0 |00:00:00.01 |
--------------------------------------------------------------------------------------------------------------

 

 

참조 : https://cafe.naver.com/dbian/6239