비교 연산자 종류와 컬럼 순서에따른 군집성
테이블과 달리 인덱스에는 같은 값을 갖는 레코들이 서로 군집해 있다 그렇기에 동등 비교(=) 연산자로 조회하면 조건절을 만족하는 레코드는 모두 모여 있다 만약 하나를 누락하거나, 동등 비교 연산을 하지 않는 경우 만족하는 레코드는 서로 흩어진 상태가 된다 다음 그림과 쿼리를 한번 보자
소스
https://gitlab.com/kimdongy1000/sqltuningproject
EX13_EMPLOYEE
간단 스키마
1
2
3
4
5
6
7
8
9
10
11
DROP TABLE EX13_EMPLOYEE
CREATE TABLE EX13_EMPLOYEE(
emp_level NUMBER(2),
emp_no NUMBER(10)
)
CREATE INDEX Ex13_Employee_idx1 ON EX13_EMPLOYEE(emp_level , emp_no);
CREATE INDEX Ex13_Employee_idx2 ON EX13_EMPLOYEE( emp_no , emp_level );
선행 컬럼이 모두 동등비교(=) 일 경우
예를 들어 다음과 같은 인덱스 테이블에 쿼리가 있다고 하자 이때는 지금처럼 모든 조건을 동등 비교로 비교한 결과 인덱스 결과들이 흩어지지 않고 모두 모여 있다 이때 실행계획을 떠보면 filter 조건 없이 accesss 조건으로만 실행계획이 들어가 있게 된다
제일 마지막 컬럼이 부등호일 경우
지금처럼 앞의 선행 컬럼이 모두 동등 비교이고 제일 뒤에 오는 컬럼이 부등호 또는 between일 경우 마찬가지로 인덱스 결과들이 모여 있다
중간 컬럼이 부등호 1
지금은 앞의 두 개의 선행 컬럼이 동등 비교에 중간의 하나의 컬럼이 비교 컬럼에 마지막 컬럼이 다시 동등 비교라면 이때는 앞의 employee_level 까지는 데이터 셋이 몰려 있지만 뒤에 오는 동등 비교가 와서 지금처럼 서로 인접하지 않은 데이터 셋이 오게 된다 이때는 앞에 세 개의 컬럼은 access 조건이고 나머지 하나 제일 뒤에 오는 조건은 인덱스 filter 조건이 된다
중간 컬럼이 부등호 2
이번엔 선행 컬럼럼중 두 번째 컬럼이 동등 비교가 아닐 때이다 이때도 마찬가지로 인덱스 컬럼 결과 셋이 흩어지게 되는데 이때 employee_dept_no 와 employee_job 은 인덱스 access 조건이고 나머지 두 개의 컬럼은 인덱스 filter 조건이 되는 것이다
정리
선행 컬럼이 모두 동등 조건인 상태에서 첫 번째 나타난 범위 검색 조건까지만 만족하는 레코드들은 군집해 있지만 이 뒤에 오는 컬럼들은 동등 비교 연산자를 사용해서 떨어질 수밖에 없는 규칙성을 볼 수 있다 다만 이러한 규칙성은 대부분의 인덱스 테이블에서 나타나는 특징이고 수많은 데이터로 인해서 우연치 않게 모여 있을 수 있지만 그 확률은 극히 드물다
동등 조건이 아닐때 비효율
우리는 위에서 선행 컬럼의 비교 연산자를 보면서 인덱스 access 조건인지 인덱스 filter 조건인지에 대해서 알아보았다 성능이 제일 좋은 방법은 인덱스 컬럼 모두 동치 조건에 들어가는 것이 좋지만 개발하는 여건상 그렇지 못할 때도 분명히 존재한다 위에서 살펴보았을 때 인덱스 효율성은 다음과 같다
인덱스 컬럼 모두가 동등 비교일 때
인덱스 선행 컬럼이 모두 동등 비교이고 후행 컬럼이 없거나 비교인 경우
반대로 제일 최악의 비효율은 다음과 같다
선행 컬럼이 없는 경우
선행 컬럼이 비교인 경우
이 경우라면 인덱스 컬럼의 세팅은 매우 비효율적으로 스캔하게 된다 만약 이러한 비용이 테이블을 Full SCAN 하는 비용보다 높다면 옵티마이저는 당연하게도 테이블 Full Scan 을 고려하게 된다
다음 쿼리비교
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
EXPLAIN PLAN FOR
SELECT /*+ INDEX(EX13_EMPLOYEE Ex13_Employee_idx1) OPT_PARAM('_optimizer_skip_scan_enabled', 'false') */
*
FROM EX13_EMPLOYEE
WHERE emp_level BETWEEN 1 AND 3
AND emp_no = 1000;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
SELECT /*+ INDEX(EX13_EMPLOYEE Ex13_Employee_idx1)*/
*
FROM EX13_EMPLOYEE
WHERE emp_level IN (1 ,2 , 3)
AND emp_no = 1000;
이 둘의 쿼리는 결과적으로 동일한 쿼리입니다 하지만 각각의 실행계획을 보게 되면 다른데
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
PLAN_TABLE_OUTPUT |
---------------------------------------------------------------------------------------+
Plan hash value: 496178858 |
|
---------------------------------------------------------------------------------------|
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time ||
---------------------------------------------------------------------------------------|
| 0 | SELECT STATEMENT | | 3 | 21 | 40 (0)| 00:00:01 ||
|* 1 | INDEX RANGE SCAN| EX13_EMPLOYEE_IDX1 | 3 | 21 | 40 (0)| 00:00:01 ||
---------------------------------------------------------------------------------------|
|
Predicate Information (identified by operation id): |
--------------------------------------------------- |
|
1 - access("EMP_LEVEL">=1 AND "EMP_NO"=1000 AND "EMP_LEVEL"<=3) |
filter("EMP_NO"=1000) |
----------------------------------------------------------------------------------------+
Plan hash value: 573833215 |
|
----------------------------------------------------------------------------------------|
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time ||
----------------------------------------------------------------------------------------|
| 0 | SELECT STATEMENT | | 3 | 21 | 4 (0)| 00:00:01 ||
| 1 | INLIST ITERATOR | | | | | ||
|* 2 | INDEX RANGE SCAN| EX13_EMPLOYEE_IDX1 | 3 | 21 | 4 (0)| 00:00:01 ||
----------------------------------------------------------------------------------------|
|
Predicate Information (identified by operation id): |
--------------------------------------------------- |
|
2 - access(("EMP_LEVEL"=1 OR "EMP_LEVEL"=2 OR "EMP_LEVEL"=3) AND |
"EMP_NO"=1000) |
이렇게 cost 도 차이가 나고 인덱스 테이블 접근하는 것도 다르기 때문입니다 그 이유는 아래 오퍼레이션 INLIST ITERATOR의 차이가 나는 것인데 둘 다 동일한 결과를 보이고 동일한 인덱스를 사용하지만 비용이 차이가 나고, 오퍼레이션이 다른 이유는 다음과 같습니다 첫 번째 쿼리는 SKIP INDEX 처리 때문에 강제적으로 SKIP INDEX를 지우고 RANGE 스캔으로만 유도를 한 것입니다
첫번째 쿼리의 분석
인덱스 테이블 스캔을 보면 첫 번째 쿼리는 스캔 양에 비해서 너무나도 적은 소량의 데이터를 가져오지만
두번째 쿼리 분석
두 번째 쿼리는 이렇게 스캔을 하게 됩니다 결과도 같고 인덱스도 같은 것을 태웠는데 이런 결과가 나오는 이유는 바로 INLIST ITERATOR
INLIST ITERATOR 의 쿼리변환
두번째 쿼리는 내부적으로 이것과 같아집니다
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
EXPLAIN PLAN FOR
SELECT /*+ INDEX(EX13_EMPLOYEE Ex13_Employee_idx1)*/
*
FROM EX13_EMPLOYEE
WHERE emp_level = 1
AND emp_no = 1000
UNION ALL
SELECT /*+ INDEX(EX13_EMPLOYEE Ex13_Employee_idx1)*/
*
FROM EX13_EMPLOYEE
WHERE emp_level = 2
AND emp_no = 1000
UNION ALL
SELECT /*+ INDEX(EX13_EMPLOYEE Ex13_Employee_idx1)*/
*
FROM EX13_EMPLOYEE
WHERE emp_level = 3
AND emp_no = 1000;
이렇게 되면 이제 동등 비교가 되어서 앞에 있는 BETWEEN의 인덱스 스캔 비효율은 없어지게 됩니다 앞에서 인덱스 비효율이 나게 되는 원인은 선두 컬컬럼이 범위로 나오기 때문에 그 뒤에 오는 컬럼의 군집성이 떨어지기 때문에 뒤에 오는 emp_no는 filter 조건으로만 들어가게 됩니다 그래서 첫 번째 쿼리의 실행계획을 보게 되면
1
2
3
4
5
6
1 - access("EMP_LEVEL">=1 AND "EMP_NO"=1000 AND "EMP_LEVEL"<=3) |
filter("EMP_NO"=1000) |
2 - access(("EMP_LEVEL"=1 OR "EMP_LEVEL"=2 OR "EMP_LEVEL"=3) AND |
"EMP_NO"=1000)
일부는 인덱스 액세스 조건으로 들어가고 나머지는 filter 조건으로 들어가서 비효율이 생기게 됩니다 반면에 두 번째 쿼리는 모든 컬럼이 인덱스 access 조건으로 들어가서 이에 대한 비효율은 없어지게 된 것입니다 만약 첫 번째 쿼리에서 인덱스 스킵 모드를 없애면 옵티마이저는 인덱스 스킵 오퍼레이션으로 아래 쿼리와 거의 비슷하게 결과가 나오게 됩니다
주의사항
그러면 모든 BETWEEN 을 IN 조건으로 변경하면 되는가 그것은 또 아닙니다 예를 들어서
1
2
3
4
5
6
7
SELECT /*+ INDEX(EX13_EMPLOYEE Ex13_Employee_idx1)*/
*
FROM EX13_EMPLOYEE
WHERE emp_level BETWEEN 1 AND 9999
AND emp_no = 1000;
범위가 넓으면 넓을수록 그리고 그 범위 안에 일치하는 데이터가 많으면 많을수록입니다 공식은 이렇게 됩니다 수평 스캔(BETWWEN) 비용보다 수직 스캔 (IN) 비용이 더 크게 되면 IN 조건으로 무조건 변경은 비용의 증가가 있을 수 있습니다 IN 조건의 효용성은 스캔 양에 비해서 소량으로만 선택되는 조건에 한해서만 채택되는 것이 좋습니다
SKIP INDEX
우리는 앞에서 쿼리 하나를 강제로 RANGE SCAN 을 시켰다 사실 힌트가 없으면 옵티마이저는 당연하게도 SKIP INDEX 로 활용을 할것인데 이를 얼마나 이득이 되는지 트레이스 파일을 가지고 한번 확인을 해보겠습니다
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
********************************************************************************
SELECT *
FROM EX13_EMPLOYEE
WHERE emp_no = 1000
AND emp_level BETWEEN 1 AND 4
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 4 0 4
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 4 0 4
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 110
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
4 4 4 INDEX RANGE SCAN EX13_EMPLOYEE_IDX2 (cr=4 pr=0 pw=0 time=16 us starts=1 cost=3 size=32 card=4)(object id 77468)
********************************************************************************
이렇게 데이터가 나오는데 이떄 중요한것은 많은 데이터를 읽었는데도 한번도 디스크에 접근한 흔적 없이 적은 수의 데이터를 읽었다 이때 같은 쿼리에 인덱스를 반대로 줘버리면 어떻게 될까?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
SELECT /*+ INDEX(EX13_EMPLOYEE Ex13_Employee_idx1)*/
*
FROM EX13_EMPLOYEE
WHERE emp_no = 1000
AND emp_level BETWEEN 1 AND 4
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 13 0 4
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 13 0 4
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 110
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
4 4 4 INDEX SKIP SCAN EX13_EMPLOYEE_IDX1 (cr=13 pr=0 pw=0 time=28 us starts=1 cost=8 size=32 card=4)(object id 77467)
이때는 INDEX SKIP SCAN 이 일어나게 된다 이떄는 위의 인덱스보다 데이터를 좀더 읽게 된다 하지만 이때 SKIP INDEX 를 사용하지 못하게 힌트를 주면 어떻게 될까?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
SELECT /*+ INDEX(EX13_EMPLOYEE Ex13_Employee_idx1) OPT_PARAM('_optimizer_skip_scan_enabled', 'false')*/
*
FROM EX13_EMPLOYEE
WHERE emp_no = 1000
AND emp_level BETWEEN 1 AND 4
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.02 0.04 776 793 0 4
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.03 0.04 776 793 0 4
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 110
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
4 4 4 INDEX RANGE SCAN EX13_EMPLOYEE_IDX1 (cr=793 pr=776 pw=0 time=23 us starts=1 cost=1057 size=32 card=4)(object id 77467)
이렇게 되어버리니 메모리와 , 디스크에서 I/O 가 상당히 많이 발생한것을 볼 수 있다 그 이유는 인덱스를 강제로 skip index 를 주게 되었으니 각 emp_level 별 1000은 1개이지만 인덱스 때문에 emp_level 10만개를 전부 읽고 필요 없는 부분을 버리기 때문에 이런 상황이 발생하게 되는 것입니다
이때 in 으로 처리하게 되면
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
SELECT /*+ INDEX(EX13_EMPLOYEE Ex13_Employee_idx1) OPT_PARAM('_optimizer_skip_scan_enabled', 'false')*/
*
FROM EX13_EMPLOYEE
WHERE emp_no = 1000
AND emp_level IN (1 , 2, 3, 4)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 1 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 11 0 4
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 11 1 4
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 110
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
4 4 4 INLIST ITERATOR (cr=11 pr=0 pw=0 time=25 us starts=1)
4 4 4 INDEX RANGE SCAN EX13_EMPLOYEE_IDX1 (cr=11 pr=0 pw=0 time=39 us starts=4 cost=6 size=32 card=4)(object id 77467)
INLIST ITERATOR 오퍼레이션이 발동해서 성능이 크게 향상되는 모습을 볼 수 있습니다