계속해서 인덱스를 타지 못하는 다음 경우를 보겠습니다 앞에서는 선두 컬럼 자체를 가공한 예제하면 다음은 where 조건에 따라서 범위 스캔이 되는지 아닌지 알아보겠습니다 예제 테이블은
java 소스예제
이곳에서 Ex2_Student 를 찾자 아래 .sql 파일도 스키마도 마련해두었습니다
https://gitlab.com/kimdongy1000/sqltuningproject
간단한 예제 테이블 스키마
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
DROP TABLE ex2_student;
DELETE FROM ex2_student;
CREATE TABLE ex2_student(
student_name varchar(10),
student_id Number(4)
)
CREATE UNIQUE INDEX ex2_student_pk_idx ON ex2_student (student_name);
ALTER TABLE ex2_student ADD CONSTRAINT pk_ex2_student PRIMARY KEY (student_name) USING INDEX ex2_student_pk_idx;
테이블 구조는 이렇게 되어 있습니다 이번에는 이름으로만 인덱스를 설정하고 진행을 하겠습니다
java 소스
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
package com.example.sqltuningproject.controller;
import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import java.util.*;
@RestController
@RequestMapping("/ex2_student")
public class Ex2_Student {
private static final String[] LAST_NAMES = { "강" , "곽" , "김", "이", "유" , "박", "진" , "최" , "한"};
private static final String[] FIRST_NAME = {
"대", "혜", "민", "서", "수", "예" , "윤", "지", "은"
};
private static final String[] MIDDLE_NAME = {
"빈", "연", "린", "호", "아", "후", "영" , "주"
};
@Autowired
private SqlSessionTemplate sqlSessionTemplate;
@GetMapping("/create")
@Transactional(rollbackFor = Exception.class)
public String create() {
List<Ex2_Student_Model> ex2_Student_list = new ArrayList<>();
// 각 성씨별로 이름을 순차적으로 배치
int student_index = 1;
for(int last_name_idx = 0; last_name_idx < LAST_NAMES.length; last_name_idx++){
for(int first_name_idx = 0; first_name_idx < FIRST_NAME.length; first_name_idx++){
for(int middle_name_idx = 0; middle_name_idx < MIDDLE_NAME.length; middle_name_idx++){
String fullName = LAST_NAMES[last_name_idx] + FIRST_NAME[first_name_idx] + MIDDLE_NAME[middle_name_idx];
ex2_Student_list.add(new Ex2_Student_Model(fullName , student_index));
student_index++;
}
}
}
for(int i = 0; i < ex2_Student_list.size(); i++){
//System.out.println(ex2_Student_list.get(i));
sqlSessionTemplate.insert(this.getClass().getName() + ".create", ex2_Student_list.get(i));
}
return "OK";
}
}
class Ex2_Student_Model {
private final String name;
private final int studentId;
public Ex2_Student_Model( String name , int studentId) {
this.studentId = studentId;
this.name = name;
}
@Override
public String toString() {
return String.format("INSERT INTO ex1_student (student_name , student_id) VALUES (%s, '%d');", name , studentId);
}
}
성 첫 번째 이름 두 번째 이름 각각 돌리는 것으로 해서 648개가 나오게 됩니다
선두컬럼 Like
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
EXPLAIN PLAN FOR
SELECT * FROM ex2_student
WHERE student_name LIKE '김대%'
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT |
----------------------------------------------------------------------------------------------------------+
Plan hash value: 1124826087 |
|
----------------------------------------------------------------------------------------------------------|
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time ||
----------------------------------------------------------------------------------------------------------|
| 0 | SELECT STATEMENT | | 8 | 160 | 1 (0)| 00:00:01 ||
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EX2_STUDENT | 8 | 160 | 1 (0)| 00:00:01 ||
|* 2 | INDEX RANGE SCAN | EX2_STUDENT_PK_IDX | 8 | | 1 (0)| 00:00:01 ||
----------------------------------------------------------------------------------------------------------|
|
Predicate Information (identified by operation id): |
--------------------------------------------------- |
|
2 - access("STUDENT_NAME" LIKE '김대%') |
filter("STUDENT_NAME" LIKE '김대%') |
|
Note |
----- |
- dynamic statistics used: dynamic sampling (level=2) |
이렇게 LIKE를 걸어도 지금처럼 김대%로 하게 되면 INDEX Branch는 강대는 모여 있기 때문에 이때는 Range Scan 이 가능한 것입니다 하지만 다음과 같은 경우
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
EXPLAIN PLAN FOR
SELECT * FROM ex2_student
WHERE student_name LIKE '%김대%'
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT |
----------------------------------------------------------------------------------+
Plan hash value: 3390474459 |
|
----------------------------------------------------------------------------------|
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time ||
----------------------------------------------------------------------------------|
| 0 | SELECT STATEMENT | | 8 | 160 | 4 (25)| 00:00:01 ||
| 1 | SORT ORDER BY | | 8 | 160 | 4 (25)| 00:00:01 ||
|* 2 | TABLE ACCESS FULL| EX2_STUDENT | 8 | 160 | 3 (0)| 00:00:01 ||
----------------------------------------------------------------------------------|
|
Predicate Information (identified by operation id): |
--------------------------------------------------- |
|
2 - filter("STUDENT_NAME" LIKE '%김대%') |
|
Note |
----- |
- dynamic statistics used: dynamic sampling (level=2) |
이 경우 결과는 다르지만 Index 를 타고 안타고 차이가 발생하게 됩니다 그 이유를 그림을 통해서 알아보겠습니다
먼저 student_name 이 인덱스로 되어 있음으로 이름이 비슷한 컬럼들은 군집을 이루게 됩니다 즉 전체적으로 이름이 비슷한 데이터가 모여 있게 됩니다 이때
LIKE 검색 시 앞의 2개의 데이터를 고정으로 두고 마지막을 % 하게 되면 비슷한 이름이 군집되어 있기 때문에 옵티마이저는 시작과 끝 지점을 정확하게 짚고 이를
범위 스캔에 넣을 수 있게 되는 것입니다
하지만 다음 쿼리 ‘%김대%’ 같은 경우는 고정 범위에 가 있는지 없는지 이름이 두 글자인지 세 글자인지 네 글자인지도 확실하지 않기 때문에 옵티마이저는 인덱스의 시작과 끝을 정할 수가 없기 때문에 Talbe
Access Full 을 선택하게 됩니다 이때는
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
EXPLAIN PLAN FOR
SELECT * FROM ex2_student
WHERE student_name LIKE '%김대'
PLAN_TABLE_OUTPUT |
---------------------------------------------------------------------------------+
Plan hash value: 1582056179 |
|
---------------------------------------------------------------------------------|
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time ||
---------------------------------------------------------------------------------|
| 0 | SELECT STATEMENT | | 1 | 20 | 3 (0)| 00:00:01 ||
|* 1 | TABLE ACCESS FULL| EX2_STUDENT | 1 | 20 | 3 (0)| 00:00:01 ||
---------------------------------------------------------------------------------|
|
Predicate Information (identified by operation id): |
--------------------------------------------------- |
|
1 - filter("STUDENT_NAME" LIKE '%김대') |
|
Note |
----- |
- dynamic statistics used: dynamic sampling (level=2) |
이와 같이 앞에 고정컬럼이 아니고 유동적으로 들어가게 된다면 옵티마이저는 인덱스를 사용할 수 없고 테이블 Full Scan으로 진행을 하게 됩니다 즉 LIKE를 사용할 때 INDEX를 활용할 수 있는 부분은 패턴의 시작 부분이 무조건 고정이어야 합니다 마찬가지로 다음과 같은 LIKE 도 불가능합니다
한글자 패턴
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
EXPLAIN PLAN FOR
SELECT * FROM ex2_student
WHERE student_name LIKE '_서%'
PLAN_TABLE_OUTPUT |
---------------------------------------------------------------------------------+
Plan hash value: 1582056179 |
|
---------------------------------------------------------------------------------|
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time ||
---------------------------------------------------------------------------------|
| 0 | SELECT STATEMENT | | 72 | 1440 | 3 (0)| 00:00:01 ||
|* 1 | TABLE ACCESS FULL| EX2_STUDENT | 72 | 1440 | 3 (0)| 00:00:01 ||
---------------------------------------------------------------------------------|
|
Predicate Information (identified by operation id): |
--------------------------------------------------- |
|
1 - filter("STUDENT_NAME" LIKE '_서%') |
|
Note |
----- |
- dynamic statistics used: dynamic sampling (level=2) |
마찬가지로 앞에 시작 부분이 고정이 아니기 때문에 한 글자가 온다는 명확한 조건에도 불구하고 옵티마이저는 인덱스를 활용할 수 없습니다