위의 그림은 인덱스 B-Tree를 그림으로 표현한 내용인데, INDEX FULL SCAN은 LEAF 블록을 처음부터 끝까지 모두 읽는 ‘수평적 탐색’을 의미합니다. 이 중 ’INDEX FULL SCAN (MIN/MAX)’ operation은 MIN() 또는 MAX() 함수로 데이터 추출 시 INDEX LEAF BLOCK의 양 끝(MIN/MAX)의 데이터를 읽어 빠르게 데이터를 추출하는 방법입니다. 이렇게 데이터를 추출할 경우 전체 데이터를 모두 읽지 않고 MIN/MAX 데이터를 빠르게 가져올 수 있어 성능을 극대화 시킬 수 있습니다.
‘INDEX FULL SCAN (MIN/MAX)’ operation이 어떻게 데이터를 액세스하는지 예제를 통해 살펴보도록 하겠습니다.
1. 테스트 데이터 생성
(MIN/MAX) operation을 살펴보기 위해 테스트 데이터를 만들도록 하겠습니다. 테스트 데이터는 ‘PARENT_T : CHILD_T = 1 : M’ 관계의 테이블을 만들고 CHILD_T 테이블에는 약 100만건의 데이터를 만들도록 하겠습니다.
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
|
-- 0. 오라클 버전 확인
SELECT *
FROM V$VERSION
;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
;
-- 1. 테이블 생성
DROP TABLE PARENT_T;
CREATE TABLE PARENT_T AS
SELECT LEVEL CNT,
CHR(65 + LEVEL - 1) GUBN
FROM DUAL
CONNECT BY LEVEL <= 7
;
DROP TABLE CHILD_T;
CREATE TABLE CHILD_T AS
SELECT LENGTH(LEVEL) CNT,
TO_DATE('0001-01-01', 'YYYY-MM-DD') + LEVEL - 1 CHILD_DATE
FROM DUAL
CONNECT BY LEVEL <= 1000000
;
-- 2. 인덱스 생성
CREATE INDEX PARENT_T_N01 ON PARENT_T (CNT);
CREATE INDEX CHILD_T_N01 ON CHILD_T (CHILD_DATE) PARALLEL 8;
ALTER INDEX CHILD_T_N01 NOPARALLEL;
CREATE INDEX CHILD_T_N02 ON CHILD_T (CNT, CHILD_DATE) PARALLEL 8;
ALTER INDEX CHILD_T_N02 NOPARALLEL;
|
테스트 환경은 오라클 11g이며, PARENT_T 테이블에는 7건의 코드성 데이터를, CHILD_T 테이블에는 숫자 자리수를 하나의 그룹으로 하고 여기에 날짜값이 순차적으로 증가하도록 CHILD_DATE 값을 넣었습니다. 또한, PARENT_T, CHILD_T 테이블에 각각 인덱스를 생성하였으며, CHILD_T의 경우 ‘CHILD_DATE’, ‘CNT + CHILD_DATE’ 2개 인덱스를 생성하였습니다. 아래는 각각의 테이블 데이터 출력 결과입니다.
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
|
SELECT *
FROM PARENT_T
;
CNT GUBN
---------- ----
1 A
2 B
3 C
4 D
5 E
6 F
7 G
7 rows selected
;
SELECT *
FROM CHILD_T
WHERE ROWNUM <= 20
;
CNT CHILD_DATE
---------- -----------
1 0001-01-01
1 0001-01-02
1 0001-01-03
1 0001-01-04
1 0001-01-05
1 0001-01-06
1 0001-01-07
1 0001-01-08
1 0001-01-09
2 0001-01-10
2 0001-01-11
2 0001-01-12
2 0001-01-13
2 0001-01-14
2 0001-01-15
2 0001-01-16
2 0001-01-17
2 0001-01-18
2 0001-01-19
2 0001-01-20
20 rows selected
;
|
2. (MIN/MAX) operation
위에서 만든 샘플 데이터를 통해 (MIN/MAX) operation을 살펴보도록 하겠습니다. 우선 일반 그룹함수인 SUM() 함수를 이용하여 MIN/MAX() 함수와 어떤 차이가 있는지 보겠습니다.
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
|
-- 1. SUM() 함수
SELECT SUM(CNT) SUM_CHILD_DATE
FROM CHILD_T
;
SUM_CHILD_DATE
--------------
5888896
;
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.010 0.016 0 102 2 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 2 0.280 2.147 2131 2230 0 1
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 4 0.290 2.163 2131 2332 2 1
Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
1 SORT AGGREGATE (cr=2230 pr=2131 pw=0 time=2147299 us)
1000000 TABLE ACCESS FULL CHILD_T (cr=2230 pr=2131 pw=0 time=9845638 us cost=633 size=17254172 card=1327244)
;
-- 2. MAX() 함수
SELECT MAX(CHILD_DATE) MAX_CHILD_DATE
FROM CHILD_T
;
MAX_CHILD_DATE
--------------
2738-11-26
;
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.000 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 2 0.000 0.000 0 3 0 1
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 4 0.000 0.000 0 3 0 1
Misses in library cache during parse: 0
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
1 SORT AGGREGATE (cr=3 pr=0 pw=0 time=50 us)
1 INDEX FULL SCAN (MIN/MAX) CHILD_T_N01 (cr=3 pr=0 pw=0 time=37 us cost=3 size=9 card=1)(Object ID 176297043)
;
|
보시는 것처럼 100만건이 존재하는 CHILD_T 테이블에 SUM() 함수를 사용할 경우 당연히 100만건을 모두 액세스하고 SUM을 구해야 합니다. 하지만 MAX() 함수의 경우 인덱스에서 딱 1건만 액세스하고 바로 데이터를 가져오고 있으며, 수행시간 및 I/O도 약 1,000배 감소된 걸 볼 수 있습니다.
하지만 (MIN/MAX) operation은 MIN, MAX 값을 동시에 가져올 수는 없습니다. 만약 MIN, MAX 함수를 동시에 사용할 경우 어떻게 되는지 살펴보겠습니다.
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
|
SELECT MIN(CHILD_DATE) MIN_CHILD_DATE,
MAX(CHILD_DATE) MAX_CHILD_DATE
FROM CHILD_T
;
MIN_CHILD_DATE MAX_CHILD_DATE
-------------- --------------
0001-01-01 2738-11-26
;
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.010 0.013 0 102 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 2 0.340 0.666 2015 2230 0 1
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 4 0.350 0.679 2015 2332 0 1
Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
1 SORT AGGREGATE (cr=2230 pr=2015 pw=0 time=665887 us)
1000000 TABLE ACCESS FULL CHILD_T (cr=2230 pr=2015 pw=0 time=1135074 us cost=636 size=11945196 card=1327244)
;
|
위의 결과처럼 MIN, MAX 함수를 동시에 사용할 경우 (MIN/MAX) operation은 실행계획에 나타나지 않으며, TABLE FULL SCAN으로 전체 데이터를 액세스하여 가져오는 것을 볼 수 있습니다. 그럼 MIN, MAX 값을 동시에 가져오면서 (MIN/MAX) operation을 사용하려면 어떻게 해야 할까요?
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
|
SELECT MIN(MIN_CHILD_DATE) MIN_CHILD_DATE,
MAX(MAX_CHILD_DATE) MAX_CHILD_DATE
FROM (SELECT MIN(CHILD_DATE) MIN_CHILD_DATE,
NULL MAX_CHILD_DATE
FROM CHILD_T
UNION ALL
SELECT NULL MIN_CHILD_DATE,
MAX(CHILD_DATE) MAX_CHILD_DATE
FROM CHILD_T
)
;
MIN_CHILD_DATE MAX_CHILD_DATE
-------------- --------------
0001-01-01 2738-11-26
;
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.020 0.037 0 204 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 2 0.000 0.008 5 6 0 1
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 4 0.020 0.045 5 210 0 1
Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
1 SORT AGGREGATE (cr=6 pr=5 pw=0 time=7838 us)
2 VIEW (cr=6 pr=5 pw=0 time=7822 us cost=1271 size=36 card=2)
2 UNION-ALL (cr=6 pr=5 pw=0 time=7817 us)
1 SORT AGGREGATE (cr=3 pr=3 pw=0 time=4661 us)
1 INDEX FULL SCAN (MIN/MAX) CHILD_T_N01 (cr=3 pr=3 pw=0 time=4646 us)(Object ID 176297043)
1 SORT AGGREGATE (cr=3 pr=2 pw=0 time=3139 us)
1 INDEX FULL SCAN (MIN/MAX) CHILD_T_N01 (cr=3 pr=2 pw=0 time=3121 us)(Object ID 176297043)
;
|
방법은 바로 MIN과 MAX를 가져오는 SQL을 각각 구현한 후 이를 UNION ALL로 묶어 데이터를 추출할 수 있습니다. 즉, MIN/MAX는 동시에 추출할 수 없다는 것을 이 테스트를 통해 확인할 수 있습니다.
(MIN/MAX) operation의 장점은 테이블에 조건이 없어도 INDEX FULL SCAN을 통해 데이터를 빠르게 가져온다는 장점이 있는데, 만약 조건이 들어갈 경우 operation에 어떤 변화가 있는지 살펴보겠습니다. 아래 예제는 CHILD_T 테이블에 CNT 조건을 추가하여 MAX(CHILD_DATE) 값을 가져오는 SQL입니다.
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
|
SELECT MAX(CHILD_DATE) MAX_CHILD_DATE
FROM CHILD_T
WHERE CNT = 5
;
MAX_CHILD_DATE
--------------
0274-10-13
;
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.020 0.020 0 113 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 2 0.000 0.000 0 3 0 1
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 4 0.020 0.020 0 116 0 1
Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
1 SORT AGGREGATE (cr=3 pr=0 pw=0 time=35 us)
1 FIRST ROW (cr=3 pr=0 pw=0 time=28 us cost=3 size=22 card=1)
1 INDEX RANGE SCAN (MIN/MAX) CHILD_T_N02 (cr=3 pr=0 pw=0 time=27 us cost=3 size=22 card=1)(Object ID 176297044)
;
|
CHILD_T 테이블에 CNT=5 조건을 추가하여 MAX(CHILD_DATE) 값을 구할 경우에도 마찬가지로 빠르게 한건을 가져오고 있는데, 조건이 안 들어간 경우와 비교하여 실행계획에 변화가 생겼습니다. 변화가 생긴 operation은 바로 ‘INDEX FULL SCAN (MIN/MAX)’에서 ‘INDEX RANGE SCAN (MIN/MAX)’으로 바뀐 점입니다. 즉, MIN/MAX operation의 경우 조건이 없을 경우 INDEX FULL SCAN을 통해 양 끝의 데이터를 가져올 수 있으며, 조건이 있을 경우 해당 조건을 INDEX RANGE SCAN으로 액세스 한 뒤 해당 인덱스의 양 끝 데이터를 또한 MIN/MAX로 가져올 수 있다는 것을 의미합니다.
3. (MIN/MAX) operation 튜닝
그럼 CNT 값을 조건으로 넣기 않고 CNT 값에 따른 MAX_CHILD_DATE 값을 가져오려고 하면 어떻게 될까요? 아래의 예제에서 살펴보겠습니다.
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
|
SELECT CNT,
MAX(CHILD_DATE)
FROM CHILD_T
GROUP BY CNT
ORDER BY CNT
;
CNT MAX(CHILD_DATE)
---------- ---------------
1 0001-01-09
2 0001-04-09
3 0003-09-26
4 0028-05-17
5 0274-10-13
6 2738-11-25
7 2738-11-26
7 rows selected
;
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.014 0 102 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 2 0.560 1.365 2131 2230 0 7
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 4 0.560 1.380 2131 2332 0 7
Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
7 HASH GROUP BY (cr=2230 pr=2131 pw=0 time=1364850 us cost=756 size=29199368 card=1327244)
1000000 TABLE ACCESS FULL CHILD_T (cr=2230 pr=2131 pw=0 time=3307440 us cost=636 size=29199368 card=1327244)
;
|
보시는 것처럼, TABLE FULL SCAN을 통해 100만건을 모두 액세스한 후 데이터를 추출하는 것을 볼 수 있습니다. 즉 CHILD_T_N02(CNT, CHILD_DATE) 인덱스를 활용하여 MIN/MAX operation을 사용하지 못하는 것을 확인할 수 있습니다.
그럼 CNT 값에 값을 직접 상수로 입력할 경우 MIN/MAX operation을 사용할 수 있는지 살펴보겠습니다.
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 CNT,
MAX(CHILD_DATE)
FROM CHILD_T
WHERE CNT IN (1, 2, 3, 4, 5, 6, 7)
GROUP BY CNT
ORDER BY CNT
;
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.020 0.023 0 102 2 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 2 1.010 12.247 3073 3087 0 7
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 4 1.030 12.269 3073 3189 2 7
Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
7 SORT GROUP BY NOSORT (cr=3087 pr=3073 pw=0 time=29819 us cost=16 size=29199368 card=1327244)
1000000 INLIST ITERATOR (cr=3087 pr=3073 pw=0 time=13385581 us)
1000000 INDEX RANGE SCAN CHILD_T_N02 (cr=3087 pr=3073 pw=0 time=13015854 us cost=16 size=29199368 card=1327244)(Object ID 176297044)
;
|
보시는 것처럼 CNT 값을 상수로 넣을 경우 TABLE FULL SCAN이 아닌 INDEX RANGE SCAN으로 operation이 변경되었지만 여전히 MIN/MAX operation으로 데이터를 가져오지 않기 때문에 100만건을 액세스하는 것은 변함이 없습니다. 여기서 두 번째 특징을 찾을 수 있는데 MIN/MAX operation은 반드시 특정값에 속한 경우에만 MIN/MAX 값을 가져올 수 있다는 것입니다. CHILD_T 테이블에서 조건없이MAX(CHILD_DATE) 값을 추출할 경우 ‘CHILD_T_N01(CHILD_DATE)’ 인덱스를 통해 ‘INDEX FULL SCAN (MIN/MAX)’ operation을 사용할 수 있으며, CNT 값이 들어갈 경우 ‘CHILD_T_N02(CNT, CHILD_DATE)’ 인덱스를 통해 특정 선두값(CNT)에 대해서만 ‘INDEX RANGE SCAN (MIN/MAX)’을 사용할 수 있습니다. 하지만 선두값이 여러개일 경우 MIN/MAX operation을 사용할 수 없으며 전체 데이터를 모두 읽어야 하는 한계가 있습니다.
이와 같이 선두 컬럼을 여러개 추출해야 할 경우 MIN/MAX operation을 이용하여 빠르게 데이터를 추출할 수 있는 방법은 없는것일까요? 방법이 있습니다. 그것은 바로 위에서 나열한 특정을 활용하는 것인데, ‘INDEX RANGE SCAN (MIN/MAX)’을 사용하려면 특정 선두값을 반드시 한건만 입력해야 한다는 것을 알았으므로 이 특징을 활용하도록 SQL을 변경해주면 됩니다. 아래의 예제에서 살펴보겠습니다.
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
|
SELECT P.CNT,
(SELECT MAX(C.CHILD_DATE)
FROM CHILD_T C
WHERE C.CNT = P.CNT) MAX_CHILD_DATE
FROM PARENT_T P
ORDER BY CNT
;
CNT MAX_CHILD_DATE
---------- --------------
1 0001-01-09
2 0001-04-09
3 0003-09-26
4 0028-05-17
5 0274-10-13
6 2738-11-25
7 2738-11-26
7 rows selected
;
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.020 0.025 0 107 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 2 0.000 0.001 2 20 0 7
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 4 0.020 0.026 2 127 0 7
Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
7 SORT AGGREGATE (cr=16 pr=2 pw=0 time=1194 us)
7 FIRST ROW (cr=16 pr=2 pw=0 time=1143 us cost=3 size=22 card=1)
7 INDEX RANGE SCAN (MIN/MAX) CHILD_T_N02 (cr=16 pr=2 pw=0 time=1129 us cost=3 size=22 card=1)(Object ID 176297044)
7 TABLE ACCESS FULL PARENT_T (cr=4 pr=0 pw=0 time=28 us cost=3 size=91 card=7)
;
|
CHILD_T 테이블에 선두컬럼을 넣으려면, ’PARENT_T : CHILD_T = 1 : M’ 관계를 활용하면 되며 이를 1:1 관계로 만들 경우, 즉 CHILD_T 테이블을 스칼라 서브쿼리로 변경할 경우 성능 선두컬럼(CNT)을 항상 받을 수 있기 때문에 MIN/MAX operation을 이용할 수 있으며, 이와 같이 극대화된 성능 개선이 가능합니다.
4. MIN/MAX operation 정리
이처럼 MIN/MAX operation 특징을 정확히 이해하고 활용할 경우 성능개선이 가능하며 이 기능을 정리한 내용은 아래와 같습니다.
|
|
1) 인덱스 LEAF 블록의 양 끝을 읽어 가져오기 때문에 데이터를 빠르게 가져올 수 있다.
2) MIN(), MAX() 함수를 동시에 사용할 경우 나타나지 않으며, 동시에 값을 가져와야 할 경우
이를 분리해서 가져와야 한다.
3) INDEX FULL SCAN 뿐만이 아니라 INDEX RANGE SCAN을 통해서도 구현이 가능하다.
4) INDEX RANGE SCAN (MIN/MAX) operation을 사용하기 위해서는 반드시 선두컬럼의 값이 한건만
액세스되도록 해야 한다. 이를 위해 CHILD 테이블에서 선두컬럼을 얻을 수 없을 경우 PARENT
테이블에서 데이터를 추출 후 CHILD 테이블을 스칼라 서브쿼리로 활용하여 INDEX RANGE SCAN
(MIN/MAX) operation을 사용할 수 있다.
|
