들어가기 앞서
우리는 앞에서 단일 테이블에서 인덱스를 어떻게 할지 이에 대해서 상당히 많은 예제와 트레이서 로그를 활용해서 보았다 하지만 실무에서는 거의 대부분 테이블이 여러 개로 조인된 상태로 개발을 진행을 하게 된다 이번 시간부터는 조인 테이블에 대해서 알아보자
NL 조인
조인의 기본은 NL 조인이다 NL 조인은 인덱스를 이용하는 조인이기 때문에 튜닝 원리도 그래도 적용할 수 있다
기본 메커니즘
만약 이 두 테이블에서 입사 일자가 1900년대의 사람들이 관리하는 모든 고객 사람들의 데이터를 추출하는 방법
1
2
3
4
5
SELECT C.*
FROM Custom C INNER JOIN EMP E ON C.관리사원번호 = E.사원번호
WHERE E.입사일자 BETWEEN '19000101' AND '19991231'
이런 식의 쿼리로 개발할 수 있다 하지만 이를 프로그래밍으로 옮기려고 한다면 생각보다 난제이다 한번 보자
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
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
package com.example.sqltuningproject.controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.Arrays;
import java.util.List;
@RestController
@RequestMapping("/ex16_nl_join_1")
public class Ex16_nl_join_1
{
private final List<ex16_emp_model> emp = Arrays.asList(
new ex16_emp_model("A1" , "홍길동" , "19930301"),
new ex16_emp_model("A2" , "이민재" , "19940725"),
new ex16_emp_model("A3" , "박명수" , "20050611"),
new ex16_emp_model("A4" , "유재석" , "19970605"),
new ex16_emp_model("A5" , "강호동" , "19930505"),
new ex16_emp_model("A6" , "이경규" , "20000101")
);
private final List<ex16_custom_model> custom = Arrays.asList(
new ex16_custom_model("C1" , "D" , "..." , "A1"),
new ex16_custom_model("C2" , "E" , "..." , "A1"),
new ex16_custom_model("C3" , "F" , "..." , "A2"),
new ex16_custom_model("C4" , "G" , "..." , "A2"),
new ex16_custom_model("C5" , "H" , "..." , "A3"),
new ex16_custom_model("C6" , "I" , "..." , "A3"),
new ex16_custom_model("C7" , "J" , "..." , "A3"),
new ex16_custom_model("C8" , "K" , "..." , "A4"),
new ex16_custom_model("C9" , "L" , "..." , "A4"),
new ex16_custom_model("C10" , "M" , "..." , "A4"),
new ex16_custom_model("C11" , "N" , "..." , "A5"),
new ex16_custom_model("C12" , "O" , "..." , "A5"),
new ex16_custom_model("C13" , "P" , "..." , "A5"),
new ex16_custom_model("C14" , "Q" , "..." , "A6"),
new ex16_custom_model("C15" , "R" , "..." , "A6"),
new ex16_custom_model("C16" , "S" , "..." , "A6")
);
@GetMapping("/create")
public String create()
{
DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyyMMdd");
LocalDate startDate = LocalDate.of(1900, 1, 1);
LocalDate endDate = LocalDate.of(1999, 12, 31);
for(int i = 0; i < emp.size(); i++){
LocalDate join_date = LocalDate.parse(emp.get(i).getJoin_date(),formatter);
if(join_date.isBefore(startDate) || join_date.isAfter(endDate)){
continue;
}
String select_emp_no = emp.get(i).getEmp_no();
for(int j = 0; j < custom.size(); j++){
String emp_no = custom.get(j).getEmp_no();
if(select_emp_no.equals(emp_no)){
System.out.println(custom.get(j));
}
}
}
return "OK";
}
}
class ex16_emp_model{
private final String emp_no;
private final String emp_name;
private final String join_date;
public ex16_emp_model(String emp_no, String emp_name, String join_date) {
this.emp_no = emp_no;
this.emp_name = emp_name;
this.join_date = join_date;
}
public String getEmp_no() {
return emp_no;
}
public String getEmp_name() {
return emp_name;
}
public String getJoin_date() {
return join_date;
}
@Override
public String toString() {
return "ex16_emp_model{" +
"emp_no='" + emp_no + '\'' +
", emp_name='" + emp_name + '\'' +
", join_date='" + join_date + '\'' +
'}';
}
}
class ex16_custom_model{
private final String custom_no;
private final String custom_name;
private final String custom_tel_no;
private final String emp_no;
public ex16_custom_model(String custom_no, String custom_name, String custom_tel_no, String emp_no) {
this.custom_no = custom_no;
this.custom_name = custom_name;
this.custom_tel_no = custom_tel_no;
this.emp_no = emp_no;
}
public String getCustom_no() {
return custom_no;
}
public String getCustom_name() {
return custom_name;
}
public String getCustom_tel_no() {
return custom_tel_no;
}
public String getEmp_no() {
return emp_no;
}
@Override
public String toString() {
return "ex16_custom_model{" +
"custom_no='" + custom_no + '\'' +
", custom_name='" + custom_name + '\'' +
", custom_tel_no='" + custom_tel_no + '\'' +
", emp_no='" + emp_no + '\'' +
'}';
}
}
모델 선언부터 상당히 많은 소스가 들어가게 된다 이러한 간단한 작업을 SQL 하고 비교할 수 없는 효율성이긴 하지만 우리는 그 효율성을 따지려고 소스코드를 작성하는 게 아니라 NL 테이블의 메커니즘을 보려고 일부로 소스코드를 만들었다 다음의 소스코드를 면밀히 보자
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
for(int i = 0; i < emp.size(); i++){
LocalDate join_date = LocalDate.parse(emp.get(i).getJoin_date(),formatter);
if(join_date.isBefore(startDate) || join_date.isAfter(endDate)){
continue;
}
String select_emp_no = emp.get(i).getEmp_no();
for(int j = 0; j < custom.size(); j++){
String emp_no = custom.get(j).getEmp_no();
if(select_emp_no.equals(emp_no)){
System.out.println(custom.get(j));
}
}
}
우리가 데이터를 추출할 때 지금처럼 2개의 for 문을 중첩해서 사용했다 이때 제일 바깥에 도는 for 문을 outer loop라고 하고 안에 있는 for 문을 inner loop라고 한다 일반적으로 NL 조인은 Outer 와 Inner의 양쪽 테이블 모두 인덱스를 사용한다 Outer 쪽 테이블의 사이즈가 크지 않다면 인덱스를 이용하지 않을 수 있다 Table Full Scan 하더라도 한 번에 그치기 때문이다 하지만 Inner 쪽 테이블은 인덱스를 사용해야 한다 그렇지 않으면 Outer loop에서 읽은 건수만큼 inner loop의 Full Scan 을 반복할 것이기 때문이다 위에 소스코드에서는 if 문장이 그것을 방지하고 있기 때문에 인덱스로 선택될 가능성이 높다
NL 조인 메커니즘 그림
NL 조인 메커니즘을 그림으로 나타내었다
EMP 인덱스에서 입사 일자가 일치하는 입사 일자를 찾는다 (입사 일자 Beteewn ‘19000101’ and ‘19991231’)
인덱스에서 읽은 ROWID로 가서 EMP 본 테이블로 가서 ROW 데이터를 찾는다
EMP 데이터를 기반으로 Custom 인덱스에서 사원번호 기반으로 데이터를 찾는다
Custom 인덱스 테이블 읽은 ROWID로 Custom 본 테이블로 가서 데이터 셋을 찾고 그 내용을 결과 셋으로 만들기 시작한다
이것이 기본 메커니즘이고 Custom 인덱스를 살펴보면 한 건이 더 칠해져 있는데 인덱스 결과를 만족하는 내용인지 스캔을 하고 일치하면 한 번 더 같은 과정을 진행하고 그렇지 않으면 멈추는데 지금 이 초록색은 스캔만 하고 멈춘 상태이고 물론 이는 데이터 결과 셋에 반영되지 않는다
이것이 기본적인 조인 메커니즘이다
실행계획
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
EXPLAIN PLAN FOR
SELECT * FROM ex16_emp E INNER JOIN ex16_custom C ON E.emp_no = C.emp_no
WHERE E.join_date BETWEEN '19000101' AND '19991231'
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT |
---------------------------------------------------------------------------------------------------------+
Plan hash value: 494033803 |
|
---------------------------------------------------------------------------------------------------------|
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time ||
---------------------------------------------------------------------------------------------------------|
| 0 | SELECT STATEMENT | | 11 | 396 | 1 (0)| 00:00:01 ||
| 1 | NESTED LOOPS | | 11 | 396 | 1 (0)| 00:00:01 ||
| 2 | NESTED LOOPS | | 12 | 396 | 1 (0)| 00:00:01 ||
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| EX16_EMP | 4 | 64 | 1 (0)| 00:00:01 ||
|* 4 | INDEX RANGE SCAN | EX16_EMP_IDX | 4 | | 1 (0)| 00:00:01 ||
|* 5 | INDEX RANGE SCAN | EX16_CUSTOM_IDX | 3 | | 0 (0)| 00:00:01 ||
| 6 | TABLE ACCESS BY INDEX ROWID | EX16_CUSTOM | 3 | 60 | 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") |
|
Note |
----- |
- dynamic statistics used: dynamic sampling (level=2) |
해석
NESTED LOOPS 가 현재 두 개 있다 안쪽부터 살펴보면 된다 이는 프로그래밍언어에서 바깥쪽 outer Loop를 말하는 것이다 NESTED LOOPS는 두 개의 테이블을 하나씩 반복적으로 조인하는 방식이고 일반적으로 인덱스를 활용한 효율적인 검색이 가능할 때 사용이 된다 지금은 안쪽 NESTED LOOPS Ex16_EMP 테이블에서 데이터를 찾고 바깥의 NESTED LOOPS의 EX16_CUSTOM의 데이터를 찾는 것이다