인덱스 스캔의 종류
우리는 이제까지 여러 실행계획을 보았지만 아마 인덱스와 관련해서는 다음 2개만 보았을 것이다 Index Range Scan , TABLE ACCESS FULL에 대해서 말이다 이번 시간에는 이 둘을 포함해서 다른 것들에 대해서 정리하는 시간을 가질 것이다
예제소스 및 스키마
https://gitlab.com/kimdongy1000/sqltuningproject
1
2
3
4
5
6
7
8
9
10
11
12
DROP TABLE Ex7_Employee
CREATE TABLE Ex7_Employee (
employee_id number,
name VARCHAR(50),
department_id number,
salary number
);
CREATE INDEX Ex7_Employee_idx ON Ex7_Employee(employee_id , department_id);
Index Range Scan
인덱스의 가장 일반적이고 정상적인 형태의 액세스 형태이다 브랜치 블록에서는 수직 탐색을 하다가 일치하는 부분이 나오면 리프로 내려가서 수평으로 서칭을 하는 인덱스 스캔 방법이다
Index Full Scan
이는 브랜치 블록을 수직적 탐색 없이 수평적 탐색으로 만 해서 데이터 셋을 만들어내는 스캔방식입니다
Index Full Scan 문제점
하지만 이제까지 내가 했던 모든 예제 중에서 Index Full Scan 이 발생하지는 않는데 그 이유는 다음과 같다 옵티마이저는 선두 컬럼이 없으면 먼저 Table Full Scan 을 고려하게 됩니다 하지만 대용량 테이블의 Full Scan의 부담에 크다면 옵티마이저는 다시 한번 인덱스를 고려하게 되는데 만약 Index Full Scan 해서 데이터를 뽑아내는 비용 보다 Table Full Scan 한 비용이 크다면 옵티마이저는 기꺼이 선두 칼럼이 없더라도 다시 한번 index Full Sacn 을 해서 데이터를 뽑는 방식으로 채택할 것입니다
또 다른 문제점
테이블은 어차피 데이터가 쌓이는 구조입니다 아무리 정규화를 하고 테이블을 더욱더 잘게 쪼갠다고 할지라도 서비스가 지속되는 한 테이블의 크기와 데이터의 크기가 커지게 됩니다 그러면 결국 인덱스 테이블 자체도 크기가 커지게 되는데 결국 옵티마이저는 선택을 할 것입니다 인덱스를 스캔해서 얻는 비용 하고 테이블 전체를 스캔해서 얻는 비용 중 둘 중 비용이 낮은 비용으로 선택을 하게 됩니다 결국 인덱스 테이블을 스캔하는 것도 비용에 한 축이기 때문입니다 비용에 관련한 산정을 하므로 생각보다 Index Full Scan이 실행계획에 나오는 것은 극히 드물게 나타납니다
Index Unique Scan
이는 리프 블록 없이 브렌치 블록만으로 데이터를 찾게 될 때 사용하게 됩니다 인덱스가 Unique 하고 동등 비교로 쿼리를 만들 때 사용할 수 있습니다 이전 예제 중에서 ex1_student를 보겠습니다
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
CREATE TABLE ex1_student(
grade_id NUMBER(1),
class_id NUMBER(1),
student_id Number(2),
student_name varchar(10)
)
CREATE UNIQUE INDEX ex1_student_pk_idx ON ex1_student (grade_id, class_id , student_id);
ALTER TABLE ex1_student ADD CONSTRAINT pk_ex1_student PRIMARY KEY (grade_id, class_id , student_id) USING INDEX ex1_student_pk_idx;
EXPLAIN PLAN FOR
SELECT * FROM ex1_student
WHERE grade_id = 1
AND class_id = 1
AND student_id = 1
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT |
--------------------------------------------------------------------------------------------------+
Plan hash value: 3975190650 |
|
--------------------------------------------------------------------------------------------------|
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time ||
--------------------------------------------------------------------------------------------------|
| 0 | SELECT STATEMENT | | 1 | 19 | 2 (0)| 00:00:01 ||
| 1 | TABLE ACCESS BY INDEX ROWID| EX1_STUDENT | 1 | 19 | 2 (0)| 00:00:01 ||
|* 2 | INDEX UNIQUE SCAN | EX1_STUDENT_PK_IDX | 1 | | 1 (0)| 00:00:01 ||
--------------------------------------------------------------------------------------------------|
|
Predicate Information (identified by operation id): |
--------------------------------------------------- |
|
2 - access("GRADE_ID"=1 AND "CLASS_ID"=1 AND "STUDENT_ID"=1) |
간단하게 테이블 스키마하고 인덱스를 살펴보면 우리는 ex1_student_pk_idx라는 unique 인덱스를 만들었습니다 그렇기 때문에 인덱스 테이블은 절대 중복되지 않게끔 DBMS 가 정합성 관리를 해주게 됩니다 이때 인덱스 컬럼을 모두 동등 비교하게 된다면 지금처럼 INDEX UNIQUE SCAN 이 동작하게 됩니다 하지만 이때 인덱스 컬럼을 하나라도 뺀다면 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
EXPLAIN PLAN FOR
SELECT * FROM ex1_student
WHERE grade_id = 1
AND class_id = 1
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT |
----------------------------------------------------------------------------------------------------------+
Plan hash value: 1095574889 |
|
----------------------------------------------------------------------------------------------------------|
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time ||
----------------------------------------------------------------------------------------------------------|
| 0 | SELECT STATEMENT | | 20 | 380 | 3 (0)| 00:00:01 ||
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EX1_STUDENT | 20 | 380 | 3 (0)| 00:00:01 ||
|* 2 | INDEX RANGE SCAN | EX1_STUDENT_PK_IDX | 20 | | 2 (0)| 00:00:01 ||
----------------------------------------------------------------------------------------------------------|
|
Predicate Information (identified by operation id): |
--------------------------------------------------- |
|
2 - access("GRADE_ID"=1 AND "CLASS_ID"=1) |
이렇게 Index Range Scan으로 인덱스 테이블을 뒤지며 또 다른 방식은 동등 비교가 아닌 부등호를 쓸 때도 INDEX UNIQUE 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
EXPLAIN PLAN FOR
SELECT * FROM ex1_student
WHERE grade_id = 1
AND class_id > 1
AND student_id = 1
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT |
----------------------------------------------------------------------------------------------------------+
Plan hash value: 1095574889 |
|
----------------------------------------------------------------------------------------------------------|
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time ||
----------------------------------------------------------------------------------------------------------|
| 0 | SELECT STATEMENT | | 4 | 76 | 3 (0)| 00:00:01 ||
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EX1_STUDENT | 4 | 76 | 3 (0)| 00:00:01 ||
|* 2 | INDEX RANGE SCAN | EX1_STUDENT_PK_IDX | 4 | | 2 (0)| 00:00:01 ||
----------------------------------------------------------------------------------------------------------|
|
Predicate Information (identified by operation id): |
--------------------------------------------------- |
|
2 - access("GRADE_ID"=1 AND "CLASS_ID">1 AND "STUDENT_ID"=1) |
filter("STUDENT_ID"=1) |
Index Skip Scan
인덱스 선두 컬럼을 조건절에 사용하지 않으면 옵티마이저는 기본적으로 Table Full Scan 을 고려합니다 Table Full Scan 보다 I/O를 줄일 수 있거나 정렬된 결과를 쉽게 얻으려면 Index Full Scan 을 사용하기도 합니다 Index Skip Scan 은 선두 컬럼이 없을 때 Table Full Scan 하고 비용적인 측면을 고려했을 때 비용이 적다고 생각하면 고려 대상으로 복합 인덱스에서 선두 컬럼 없이 후속 컬럼으로 스캔했을 때 비용이 적다면 Index Skip Scan 을 사용하게 됩니다
`` EXPLAIN PLAN FOR SELECT /+ INDEX(Ex7_Employee Ex7_Employee_idx)/
* FROM Ex7_Employee WHERE department_id = 50
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT | ——————————————————————————————————–+ Plan hash value: 206828899 | | ——————————————————————————————————–| | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time || ——————————————————————————————————–| | 0 | SELECT STATEMENT | | 100 | 2100 | 13 (0)| 00:00:01 || | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EX7_EMPLOYEE | 100 | 2100 | 13 (0)| 00:00:01 || |* 2 | INDEX SKIP SCAN | EX7_EMPLOYEE_IDX | 100 | | 3 (0)| 00:00:01 || ——————————————————————————————————–| | Predicate Information (identified by operation id): | ————————————————— | | 2 - access(“DEPARTMENT_ID”=50) | filter(“DEPARTMENT_ID”=50) |
```` 여기까지 해서 인덱스 스캔의 종류에 대해서 알아보았습니다.