ORACLE/Performance Tuning
오라클 19c nl 조인 순서 제어 테스트
내맘대로긍정
2023. 3. 3. 05:41
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) 와 같은 형태로 풀기위해선 아래 내용처럼 인라인뷰를 사용해줘야함
테스트
샘플 테이블 생성
|
1
2
3
4
|
SQL>
create table test1 (col1 number);
create table test2 (col1 number);
create table test3 (col1 number);
|
경우1. (A 조인 B) + C
(test1 join test2) -> test3
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
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)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
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 |
--------------------------------------------------------------------------------------------------------------
|