인덱스 컬럼 추가
튜닝의 목적은 랜덤 I/O를 줄이는데 목적이 있다 그 목적을 달성하기 위해서는 인덱스 컬럼을 추가하는 것이 그 출발점이다 예를 들어서 다음과 같은 데이터와 정보가 있다고 하자
소스 및 간단 스키마
https://gitlab.com/kimdongy1000/sqltuningproject
Ex9_Employee sql 참조하시면됩니다
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
DROP TABLE Ex9_Employee
CREATE TABLE Ex9_Employee(
employee_name varchar(10),
employee_dept NUMBER,
employee_job varchar(20),
employee_sal number
)
CREATE INDEX Ex9_Employee_idx1 ON Ex9_Employee(employee_dept);
CREATE INDEX Ex9_Employee_idx2 ON Ex9_Employee(employee_dept , employee_job);
CREATE INDEX Ex9_Employee_idx3 ON Ex9_Employee(employee_dept , employee_job , employee_sal);
우리는 총 4개의 실행계획을 볼 것이다 순서대로 Table Full Scan , INDEX(Ex9_Employee_idx1) , INDEX(Ex9_Employee_idx2) , INDEX(Ex9_Employee_idx3) 이렇게 실행계획을 둘 것이고 공통 쿼리는 다음과 같다
공통쿼리
1
2
3
4
5
6
7
8
SELECT
*
FROM Ex9_Employee
WHERE employee_dept = 30
AND employee_job = 'MANAGER'
AND employee_sal > 3000
이때 이 결과는 1개만 나오도록 일부로 데이터는 조정했다 이때 각각의 실행계획을 보자
인덱스의 함정
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
PLAN_TABLE_OUTPUT |
----------------------------------------------------------------------------------+
Plan hash value: 2368488117 |
|
----------------------------------------------------------------------------------|
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time ||
----------------------------------------------------------------------------------|
| 0 | SELECT STATEMENT | | 1 | 24 | 3 (0)| 00:00:01 ||
|* 1 | TABLE ACCESS FULL| EX9_EMPLOYEE | 1 | 24 | 3 (0)| 00:00:01 ||
----------------------------------------------------------------------------------|
|
Predicate Information (identified by operation id): |
--------------------------------------------------- |
|
1 - filter("EMPLOYEE_SAL">3000 AND "EMPLOYEE_DEPT"=30 AND |
"EMPLOYEE_JOB"='MANAGER') |
PLAN_TABLE_OUTPUT |
---------------------------------------------------------------------------------------------------------+
Plan hash value: 2269012019 |
|
---------------------------------------------------------------------------------------------------------|
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time ||
---------------------------------------------------------------------------------------------------------|
| 0 | SELECT STATEMENT | | 1 | 24 | 6 (0)| 00:00:01 ||
|* 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EX9_EMPLOYEE | 1 | 24 | 6 (0)| 00:00:01 ||
|* 2 | INDEX RANGE SCAN | EX9_EMPLOYEE_IDX1 | 200 | | 1 (0)| 00:00:01 ||
---------------------------------------------------------------------------------------------------------|
|
Predicate Information (identified by operation id): |
--------------------------------------------------- |
|
1 - filter("EMPLOYEE_SAL">3000 AND "EMPLOYEE_JOB"='MANAGER') |
2 - access("EMPLOYEE_DEPT"=30) |
LAN_TABLE_OUTPUT |
--------------------------------------------------------------------------------------------------------+
lan hash value: 1057096137 |
|
--------------------------------------------------------------------------------------------------------|
Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time ||
--------------------------------------------------------------------------------------------------------|
0 | SELECT STATEMENT | | 1 | 24 | 6 (0)| 00:00:01 ||
* 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EX9_EMPLOYEE | 1 | 24 | 6 (0)| 00:00:01 ||
* 2 | INDEX RANGE SCAN | EX9_EMPLOYEE_IDX2 | 50 | | 1 (0)| 00:00:01 ||
--------------------------------------------------------------------------------------------------------|
|
redicate Information (identified by operation id): |
-------------------------------------------------- |
|
1 - filter("EMPLOYEE_SAL">3000) |
2 - access("EMPLOYEE_DEPT"=30 AND "EMPLOYEE_JOB"='MANAGER') |
PLAN_TABLE_OUTPUT |
---------------------------------------------------------------------------------------------------------+
Plan hash value: 2202526983 |
|
---------------------------------------------------------------------------------------------------------|
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time ||
---------------------------------------------------------------------------------------------------------|
| 0 | SELECT STATEMENT | | 1 | 24 | 2 (0)| 00:00:01 ||
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EX9_EMPLOYEE | 1 | 24 | 2 (0)| 00:00:01 ||
|* 2 | INDEX RANGE SCAN | EX9_EMPLOYEE_IDX3 | 1 | | 1 (0)| 00:00:01 ||
---------------------------------------------------------------------------------------------------------|
|
Predicate Information (identified by operation id): |
--------------------------------------------------- |
|
2 - access("EMPLOYEE_DEPT"=30 AND "EMPLOYEE_JOB"='MANAGER' AND "EMPLOYEE_SAL">3000 AND |
"EMPLOYEE_SAL" IS NOT NULL) |
지금 이 각각의 실행계획을 보고 성능 순위를 매겨보면 EX9_EMPLOYEE_IDX3 > TABLE ACCESS FULL > EX9_EMPLOYEE_IDX1 = EX9_EMPLOYEE_IDX2 이런 순서로 가는 것을 볼 수 있다 이것이 인덱스 컬럼 추가의 함정이다 인덱스가 데이터를 빨리 찾는 것에 도움을 주는 것은 사실이다 그러나 그런 인덱스도 어떻게 설정하느냐 데이터 분포가 어떻게 되어 있으냐 갖은 변수들이 이러한 결과를 보여주게 되는데 왜 이렇게 되는지 그림을 한번 보자
쿼리 성능은 랜덤 I/O 전쟁
이 그림은 현재 EX9_EMPLOYEE_IDX1 힌트를 주었을 대 인덱스 테이블과 실 테이블이 어떻게 데이터를 찾아오는지 표현을 해두었다 인덱스 칼럼이 하나이다 보니 스캔 양이 엄청나다 지금 실행 계획상에서도 200Row를 스캔해 오는데 이 스캔 해온 데이터를 일일이 블록을 따라다니면서 찾는다 심지어 중간에 1개의 올바른 데이터가 나온다고 할지라도 거기서 멈추지 않고 아래에 같은 것을 만족하는 데이터가 또 있는지 찾게 된다
같은방식으로 EX9_EMPLOYEE_IDX2번도 보자
이번엔 employee_job 이 인덱스로 들어와서 선두 컬럼 역할을 해서 인덱스 스캔 범위가 줄어드는 것을 볼 수 있다 실제 실행계획에서도 EX9_EMPLOYEE_IDX1 은 200 줄을 스캔하는 반면 EX9_EMPLOYEE_IDX2는 50줄만 스캔하는 것을 볼 수 있다 이 스캔 범위에 해당되는 본 테이블 블록을 가서 데이터를 찾아온다 마찬가지로 1개가 발견되었다고 할지라도 하단에 더 있는지 살펴보기 위해 스캔을 더하게 된다
마지막 EX9_EMPLOYEE_IDX3번을 보자
이 그림들로 알 수 있다 싶히 인덱스를 스캔하는 것도 비용이다 그 스캔 한 데이터로 본 테이블로 가서 서칭을 진행하는데 EX9_EMPLOYEE_IDX1 은 터무니없이 의미 없는 데이터를 많이 바라본다 실제로 원하는 결과는 1개인데 반면에 그래서 인덱스 컬럼을 하나 더 추가한 EX9_EMPLOYEE_IDX2 같은 경우도 인덱스 테이블 스캔 범위가 줄어들었고 그만큼 테이블에 대한 랜덤 I/O 또한 줄어들 것이다 그리고 마지막인 EX9_EMPLOYEE_IDX3 같은 경우 인덱스 테이블 1건 찾고 그 한 건으로 바로 본 테이블에서 한 개를 찾아낸 것이다
이 실행계획에서 알 수 있는 내용들은 인덱스 컬럼의 추가가 무조건 적인 성능 향상을 보여주지는 않습니다 오히려 랜덤 I/O를 증가시켜 Table Full Scan 보다 성능이 떨어지는 계획도 보고 있습니다 하지만 적절한 인덱스 설정은 수많은 데이터를 찾는 데 도움을 주는 것은 확실합니다 하지만 그렇다고 테이블에 각 컬럼별로 인덱스를 설정을 해버리면 옵티마이저 해당 테이블의 어떤 인덱스가 가장 적절한지 판단을 하는 비용도 소모가 되기 때문에 하나의 테이블에 테이블 컬럼당 인덱스를 만들어 두는 것도 좋지 않은 선택입니다