이번 시간에는 ROWID를 통해 데이터를 가져오는 고비용에 대해서 어떻게 대처할 것인지에 대한 테이블 설계에 대해서 알아보겠습니다
일반적인 테이블
옵티마이저가 인덱스 테이블을 서칭해서 ROWID를 가져와서 버퍼캐시를 먼저 보는데 이때 버퍼캐시에 원하는 데이터가 있으면 고비용으로 처리되는 게 아닙니다 하지만 버퍼캐시에 없어서 디스크로 가서 데이터를 찾아오는 경우 상당히 고비용입니다 그럼 여기서 출발하는 의문이 인덱스 서칭하는 테이블 자체를 그냥 다이렉트로 가져올 수 있게 만들면 되지 않으냐 하는 질문이 있습니다
소스
https://gitlab.com/kimdongy1000/sqltuningproject
Ex10_Student , EX11_EMPLOYEE , EX12_EMPLOYEE 찹아보시면될꺼 같습니다
인덱스 테이블
테이블 자체를 인덱스 테이블로 만들 수 있습니다 만드는 구문은 다음과 같습니다
1
2
3
4
5
6
7
8
9
10
11
DROP TABLE Ex10_Student
CREATE TABLE Ex10_Student(
student_id NUMBER ,
student_name varchar(10),
CONSTRAINT index_ex10_student_pk PRIMARY KEY (student_id)
)
organization INDEX;
이때 create table 아래에 organization INDEX;를 붙여주는데 이러한 작업을 인덱스 조직화 테이블 (IOT)라고 합니다 이것이 일반적인 테이블과 다른 이유는 데이터가 저장되는 디스크는 동일하지만 일반 테이블은 별도의 인덱스가 새롭게 생겨나는 반면에 이 IOT 테이블은 인덱스 테이블 자체가 데이터를 저장하기 때문에 보통의 ROWID를 찾아와서 본 테이블에 가서 데이터를 찾아오는 고비용 작업을 따로 하지 않습니다
IOT 장점
위에서 말했다 싶히 인덱스 데이터 자체가 본 테이블 데이터이기 떄문에 인덱스 서칭해서 얻은 ROWID 를 본 테이블가서 가져와야 하는 고비용 작업을 안해도 된다
IOT 단점
주로 읽기 위주의 테이블이고 쓰기 작업에는 불리한 테이블입니다 데이터가 갱신될때 인덱스도 같이 갱신되기 떄문입니다
쿼리
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
EXPLAIN PLAN FOR
SELECT * FROM Ex10_Student
WHERE student_id BETWEEN 250 and 350
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT |
------------------------------------------------------------------------------------------+
Plan hash value: 1279150182 |
|
------------------------------------------------------------------------------------------|
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time ||
------------------------------------------------------------------------------------------|
| 0 | SELECT STATEMENT | | 101 | 2020 | 2 (0)| 00:00:01 ||
|* 1 | INDEX RANGE SCAN| INDEX_EX10_STUDENT_PK | 101 | 2020 | 2 (0)| 00:00:01 ||
------------------------------------------------------------------------------------------|
|
Predicate Information (identified by operation id): |
--------------------------------------------------- |
|
1 - access("STUDENT_ID">=250 AND "STUDENT_ID"<=350) |
|
Note |
----- |
- dynamic statistics used: dynamic sampling (level=2) |
지금 이 실행계획을 보면 빠져 있는 게 있다 TABLE ACCESS BY INDEX ROWID BATCHED 다른 일반적인 테이블에서 볼 수 있는 이와 같이 ROWID를 이용한 Table ACCESS 오퍼레이션이 빠져 있는 것을 볼 수 있다 IOT 자체가 결과물이기 때문에 본 테이블을 찾아가는 번거로움이 없어지는 것이다
클러스터 테이블
여러 테이블을 물리적으로 같은 위치에 저장하는 방법을 말합니다 즉 예를 들어서 어떤 KEY 가 있으면 그 KEY를 기준으로 최대한 하나의 블록에 담으려고 하는 것을 말합니다 만약 블록의 값을 초과하는 경우에는 하나의 블록을 만들고 클러스터링 키가 동일한 블록은 클러스터 체인으로 연결하는 것입니다
이렇게 클러스터 KEY 가 같은것끼리 같은 블록에 최대한 모여 있고 만약 블록의 크키가 넘어가게 되면 다른 인접한 블록에 데이터를 채워넣되 클러스터 key 가 같은 들은 이렇게 체인을 이용해서 서로를 연결해서 빠르게 가져올 수 있게 만들어집니다
모든 통계정보 갱신
1
2
3
4
5
6
7
8
9
EXEC DBMS_STATS.GATHER_TABLE_STATS('SQLTUNING', 'EX11_EMPLOYEE');
EXEC DBMS_STATS.GATHER_TABLE_STATS('SQLTUNING', 'EX12_EMPLOYEE');
EXEC DBMS_STATS.GATHER_INDEX_STATS('SQLTUNING', 'EX11_EMPLOYEE_CLUSTER_DEPT_NO_IDX');
EXEC DBMS_STATS.GATHER_INDEX_STATS('SQLTUNING', 'Ex12_Employee_idx');
ALTER INDEX EX11_EMPLOYEE_CLUSTER_DEPT_NO_IDX REBUILD;
ALTER INDEX Ex12_Employee_idx REBUILD;
일반 테이블과 비교
이번엔 클러스터 테이블과 비교를 해보겠습니다 하나는 클러스터 테이블과, 다른 하나는 일반 테이블이 같은 스키마에 데이터 백만 개를 insert 하고 동일한 쿼리의 계획을 뽑아보겠습니다
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
PLAN_TABLE_OUTPUT |
----------------------------------------------------------------------------------------------------------+
Plan hash value: 784139901 |
|
----------------------------------------------------------------------------------------------------------|
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time ||
----------------------------------------------------------------------------------------------------------|
| 0 | SELECT STATEMENT | | 219K| 4508K| 723 (0)| 00:00:01 ||
| 1 | TABLE ACCESS CLUSTER| EX11_EMPLOYEE | 219K| 4508K| 723 (0)| 00:00:01 ||
|* 2 | INDEX UNIQUE SCAN | EX11_EMPLOYEE_CLUSTER_DEPT_NO_IDX | 1 | | 0 (0)| 00:00:01 ||
----------------------------------------------------------------------------------------------------------|
|
Predicate Information (identified by operation id): |
--------------------------------------------------- |
|
2 - access("EMPLOYEE_DEPT_NO"=10) |
PLAN_TABLE_OUTPUT |
---------------------------------------------------------------------------------------------------------+
Plan hash value: 442890301 |
|
---------------------------------------------------------------------------------------------------------|
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time ||
---------------------------------------------------------------------------------------------------------|
| 0 | SELECT STATEMENT | | 200K| 4101K| 3949 (1)| 00:00:01 ||
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EX12_EMPLOYEE | 200K| 4101K| 3949 (1)| 00:00:01 ||
|* 2 | INDEX RANGE SCAN | EX12_EMPLOYEE_IDX | 200K| | 394 (1)| 00:00:01 ||
---------------------------------------------------------------------------------------------------------|
|
Predicate Information (identified by operation id): |
--------------------------------------------------- |
|
2 - access("EMPLOYEE_DEPT_NO"=10) |
PLAN_TABLE_OUTPUT |
-----------------------------------------------------------------------------------+
Plan hash value: 2044896842 |
|
-----------------------------------------------------------------------------------|
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time ||
-----------------------------------------------------------------------------------|
| 0 | SELECT STATEMENT | | 200K| 4101K| 995 (1)| 00:00:01 ||
|* 1 | TABLE ACCESS FULL| EX12_EMPLOYEE | 200K| 4101K| 995 (1)| 00:00:01 ||
-----------------------------------------------------------------------------------|
|
Predicate Information (identified by operation id): |
--------------------------------------------------- |
|
1 - filter("EMPLOYEE_DEPT_NO"=10) |
EX12_EMPLOYEE는 INDEX 힌트를 준 것과 비교를 했습니다 데이터가 많으니 옵티마이저는 인덱스를 태우는 것보다 Full Scan 을 한 것이 유리하다고 판단해서 Full Scan으로 방향을 돌립니다 혹시나 싶어서 Index로 강제를 주었을 때는 Cost 가 폭발하는 것을 보았습니다 이때는 무수히 많은 랜덤 I/O 가 발생하는 것을 볼 수 있습니다
반면에 클러스팅 테이블은 INDEX UNIQUE SCAN 을 타긴 하지만 그 값을 기준으로 랜덤 I/O는 일반 인덱스에 비해서 엄청 작게 나옵니다 이것이 클러스터 테이블의 장점입니다 이렇게 되는 이유는 일반적인 테이블은 랜덤인 블록에 데이터를 저장하는 반면에 클러스터 테이블은 최대한 하나의 블록에서 저장하려고 노력하고 그것이 초과하면서 서로 체인을 걸어서 바로 다음 동일한 블록을 찾아갈 수 있게 만들어진 것입니다 그리고 일단 인덱스는 랜덤 액세스로 동작하지만 클러스터 인덱스는 시퀄셜 인덱스로 인접한 블록 모두를 가져올 수 있습니다