소스
https://gitlab.com/kimdongy1000/sqltuningproject
NL 조인의 실행계획 제어
기본적으로 NL 조인을 제어할 때는 use_nl이라는 힌트를 사용합니다
1
2
3
4
5
SELECT /*+ ordered use_nl(C)*/
* FROM Ex17_emp_model E INNER JOIN Ex17_custom_model C ON E.emp_no = C.emp_no
WHERE E.join_date BETWEEN '19000101' AND '19991231'
이때 ordered 힌트는 테이블 조인 순서를 명시 적으려 한다는 의미이며 지금처럼 두 개일 때는 E 와 C를 조인하는 데 아래를 보면
1
2
3
4
5
6
SELECT /*+ ORDERED */ *
FROM table1 t1
JOIN table2 t2 ON t1.id = t2.id
JOIN table3 t3 ON t2.id = t3.id;
이렇게 되어 있는 경우 t1 , t2 와 조인을 먼저 하고 t2 , t3를 차례대로 조인을 하겠다는 의미입니다 즉 FROM에 적혀 있는 순서대로 조인을 한다는 의미입니다
1
2
3
4
5
6
7
SELECT /*+ ORDERED use_nl(t2) use_nl(t3) use_hash(t4)*/ *
FROM table1 t1
JOIN table2 t2 ON t1.id = t2.id
JOIN table3 t3 ON t2.id = t3.id
JOIN table3 t4 ON t3.id = t4.id
이렇게 되어 있다면 테이블 조인 순서는 (t1 , t2) (t2 , t3) (t3 , t4) 이렇게 구성이 되고 use_nl 조인은 nl 조인을 사용하겠다는 명시적인 힌트이며 use_hash는 hash 조인을 명시적으로 사용하겠다는 의미입니다
옵티마지어는 테이블 조인을 스스로 결정
사실 조인 순서에 대한 메커니즘은 사람보다 옵티마이저가 그간 쌓아왔던 통계정보를 바탕으로 만들어내는 것이라서 사람이 직접 개입하는 ORDERED LEADING 이 역효과를 낼 수 있습니다 그렇기에 굳이 복잡한 쿼리가 아니라면 순서에 대한 힌트는 가급적 삼가는 것이 좋습니다
스키마
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
DROP TABLE Ex17_emp_model
CREATE TABLE Ex17_emp_model(
emp_no VARCHAR(10) PRIMARY KEY ,
emp_name VARCHAR(10),
join_date VARCHAR(8)
)
CREATE INDEX ex17_emp_idx ON Ex17_emp_model(join_date)
DROP TABLE Ex17_custom_model
CREATE TABLE Ex17_custom_model(
custom_no VARCHAR(10) PRIMARY KEY ,
custom_name VARCHAR(10),
custom_sal_amt NUMBER,
emp_no VARCHAR(10)
)
CREATE INDEX ex17_custom_idx1 ON Ex17_custom_model(emp_no)
CREATE INDEX ex17_custom_idx2 ON Ex17_custom_model(custom_sal_amt)
이번 예제에 사용할 스키마와 인덱스입니다 그리고 다음 쿼리를 보겠습니다
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
EXPLAIN PLAN FOR
SELECT
* FROM Ex17_emp_model E INNER JOIN Ex17_custom_model C ON E.emp_no = C.emp_no
WHERE E.join_date BETWEEN '19000101' AND '19991231'
AND C.CUSTOM_SAL_AMT > 2000
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT |
-----------------------------------------------------------------------------------------------------------+
Plan hash value: 715687838 |
|
-----------------------------------------------------------------------------------------------------------|
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time ||
-----------------------------------------------------------------------------------------------------------|
| 0 | SELECT STATEMENT | | 12 | 648 | 1 (0)| 00:00:01 ||
| 1 | NESTED LOOPS | | 12 | 648 | 1 (0)| 00:00:01 ||
| 2 | NESTED LOOPS | | 21 | 648 | 1 (0)| 00:00:01 ||
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| EX17_EMP_MODEL | 7 | 140 | 1 (0)| 00:00:01 ||
|* 4 | INDEX RANGE SCAN | EX17_EMP_IDX | 7 | | 1 (0)| 00:00:01 ||
|* 5 | INDEX RANGE SCAN | EX17_CUSTOM_IDX1 | 3 | | 0 (0)| 00:00:01 ||
|* 6 | TABLE ACCESS BY INDEX ROWID | EX17_CUSTOM_MODEL | 2 | 68 | 0 (0)| 00:00:01 ||
-----------------------------------------------------------------------------------------------------------|
|
Predicate Information (identified by operation id): |
--------------------------------------------------- |
|
4 - access("E"."JOIN_DATE">='19000101' AND "E"."JOIN_DATE"<='19991231') |
5 - access("E"."EMP_NO"="C"."EMP_NO") |
6 - filter("C"."CUSTOM_SAL_AMT">2000) |
|
Note |
----- |
- dynamic statistics used: dynamic sampling (level=2) |
기본적으로 힌트에 인덱스를 명시하지 않았으니 옵티마이저가 효율적인 인덱스를 검색해서 사용할 것입니다
순서
먼저 EX17_EMP_MODEL의 인덱스 테이블에서 EX17_EMP_IDX 인덱스로 Range Scan 을 하고 있습니다 이때 만족하는 데이터를 가져옵니다
1번에서 추출된 ROWID를 바탕으로 본 테이블 EX17_EMP_MODEL 가서 TABLE ACCESS BY INDEX ROWID BATCHED 해서 데이터를 가져옵니다
2번에서 추출한 데이터를 바탕으로 EX17_CUSTOM_MODEL 인덱스 테이블에서 EX17_CUSTOM_IDX1을 사용해서 Range Scan 을 하고 있습니다
3번에서 추출한 데이터를 바탕으로 TABLE ACCESS BY INDEX ROWID 진행하고 이때 Filter 조건으로 CUSTOM_SAL_AMT > 2000 만족하는지 아닌지 판단하게 됩니다
여기서 주의할 점은 내가 표현을 저렇게 해서 데이터 전체를 스캔하는 것 같이 보이지만 실제로 레코드 한건 한건 진행을 하는 것입니다 그것을 알 수 있는 오퍼레이션은 NESTED LOOPS입니다 즉 반복문을 통해서 내부에 있는 오퍼레이션을 반복하는 것입니다 이 부분은 매우 중요한 것입니다 그래서 우리가 앞에서 NESTED LOOPS 오퍼레이션이 프로그래밍언어 반복문하고 비슷하다고 말씀드린 것입니다 반복문은 한 건 한 건 추출해서 비교를 해서 진행하게 되는데 이것과 동일하게 조인이 진행이 되는 것입니다
NL 조인의 특징
NL 조인의 첫 번째 특징은 한 건 한 건씩 데이터를 비교해서 추출하는 방식이다 보니 랜덤 액세스 위주의 주인 방식입니다 그렇기에 인덱스 구성이 아무리 완벽해도 대량 데이터 조인할 때는 NL 조인이 불리한 이유입니다
두 번째는 한 레코드씩 순차적으로 진행하는데 이는 대량의 데이터에서는 매우 불리한 조인 방식이지만 이 특징 때문에 아무리 큰 테이블을 조인하더라도 매우 빠른 응답 속도를 낼 수 있습니다
트레이스
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
********************************************************************************
SELECT
* FROM Ex17_emp_model E INNER JOIN Ex17_custom_model C ON E.emp_no = C.emp_no
WHERE E.join_date BETWEEN '19000101' AND '19991231'
AND C.CUSTOM_SAL_AMT > 2000
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 3 0.01 0.01 0 22 0 0
Execute 3 0.00 0.00 0 0 0 0
Fetch 6 0.00 0.00 0 30 0 39
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 12 0.01 0.01 0 52 0 39
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 110
Number of plan statistics captured: 3
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
13 13 13 NESTED LOOPS (cr=10 pr=0 pw=0 time=81 us starts=1 cost=1 size=648 card=12)
21 21 21 NESTED LOOPS (cr=8 pr=0 pw=0 time=189 us starts=1 cost=1 size=648 card=21)
7 7 7 TABLE ACCESS BY INDEX ROWID BATCHED EX17_EMP_MODEL (cr=4 pr=0 pw=0 time=87 us starts=1 cost=1 size=140 card=7)
7 7 7 INDEX RANGE SCAN EX17_EMP_IDX (cr=2 pr=0 pw=0 time=23 us starts=1 cost=1 size=0 card=7)(object id 77949)
21 21 21 INDEX RANGE SCAN EX17_CUSTOM_IDX1 (cr=4 pr=0 pw=0 time=33 us starts=7 cost=0 size=0 card=3)(object id 77952)
13 13 13 TABLE ACCESS BY INDEX ROWID EX17_CUSTOM_MODEL (cr=2 pr=0 pw=0 time=22 us starts=21 cost=0 size=68 card=2)
********************************************************************************
데이터가 적어서 지금 같은 환경에서는 거의 비효율이 없다
INDEX RANGE SCAN EX17_EMP_IDX에서 메모리에 읽은 블록 수 (cr) EX17_EMP_MODEL 테이블 액세스 할 때 메모리 읽은 블록 수 (cr) EX17_CUSTOM_IDX1 인덱스 테이블 (cr) 4개를 기준으로 EX17_CUSTOM_MODEL 본 테이블 cr=2 개를 읽은 것입니다
이때 EX17_EMP_MODEL 읽을 때 추출된 row 수는 총 7개입니다 이 7개가 7번으로 EX17_CUSTOM_MODEL에 있는 인덱스 테이블과, EX17_CUSTOM_MODEL 본 테이블을 조인을 반복하게 될 것인데 이때 반복되는 횟수는 인덱스 21번 반복되고 그 결과로 13개의 row 가 추출되는 것입니다
다음 장에서는 같은 테이블로 대량 데이터를 생성해서 직접 튜닝하는 방법에 대해서 알아보도록 하겠습니다