국가공인 SQLP 자격검정 핵심노트 1 - 48. INDEX 추가, SQL 변경
- 인덱스 튜닝
- 인덱스 기본 원리
- 48. 인덱스 추가, SQL 변경
- 인덱스 기본 원리
실습 준비
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 쿼리 수행데이터 수집
/*+ GATHER_PLAN_STATISTICS */
-- 옵티마이저 쿼리변환 방지
/*+ NO_QUERY_TRANSFORMATION */
-- 수행 쿼리 SQL_ID 조회
SELECT SQL_ID, SQL_TEXT
FROM V$SQL
WHERE SQL_TEXT LIKE '%GATHER_PLAN_STATISTICS%'
AND SQL_TEXT NOT LIKE '%V$SQL%'
ORDER BY LAST_ACTIVE_TIME DESC
-- 쿼리 수행데이터 출력
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('SQL_ID', 0, 'ALLSTATS LAST'));
실습 스크립트
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
-- 1) 테이블 생성
CREATE TABLE 거래 (
증서번호 VARCHAR2(10),
이체사유발생일자 VARCHAR2(8),
거래코드 VARCHAR2(4),
순번 NUMBER,
투입인출구분코드 VARCHAR2(1),
기본이체금액 NUMBER,
정산이자 NUMBER,
CONSTRAINT PK_거래 PRIMARY KEY (증서번호, 이체사유발생일자, 거래코드, 순번)
);
-- 2) 샘플 데이터 적재 (증서번호 1000건 x 각 증서당 거래 40건)
INSERT INTO 거래
SELECT
LPAD(TO_CHAR(MOD(LEVEL - 1, 1000)), 10, '0') AS 증서번호,
TO_CHAR(DATE '2020-01-01' + TRUNC((LEVEL - 1) / 1000), 'YYYYMMDD') AS 이체사유발생일자,
TO_CHAR(7000 + MOD(TRUNC((LEVEL - 1) / 1000), 20) * 10) AS 거래코드,
TRUNC((LEVEL - 1) / 1000) + 1 AS 순번,
DECODE(MOD(TRUNC((LEVEL - 1) / 1000), 2), 0, 'G', 'S') AS 투입인출구분코드,
TRUNC(DBMS_RANDOM.VALUE(10000, 1000000)) AS 기본이체금액,
TRUNC(DBMS_RANDOM.VALUE(0, 5000)) AS 정산이자
FROM DUAL
CONNECT BY LEVEL <= 40000;
COMMIT;
튜닝 전
- 투입인출구분코드가 인덱스 구성요소가 아님 → 인덱스 스캔에 비효율 발생
- 투입인출구분코드를 제외한 조건이 동일한 2개의 인라인 뷰 사용 → 인덱스 스캔~테이블 액세스 과정이 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
SELECT /*+ GATHER_PLAN_STATISTICS NO_QUERY_TRANSFORMATION */
NVL((A.기본이체금액+A.정산이자) - (B.기본이체금액+B.정산이자), 0) AS 차액
FROM (
SELECT NVL(SUM(기본이체금액), 0) 기본이체금액
, NVL(SUM(정산이자), 0) 정산이자
FROM 거래
WHERE 증서번호 = '0000000500'
AND 이체사유발생일자 <= '20240101'
AND 거래코드 NOT IN ('7411','7412','7503','7504')
AND 투입인출구분코드 = 'G'
) A, (
SELECT NVL(SUM(기본이체금액), 0) 기본이체금액
, NVL(SUM(정산이자), 0) 정산이자
FROM 거래
WHERE 증서번호 = '0000000500'
AND 이체사유발생일자 <= '20240101'
AND 거래코드 NOT IN ('7411','7412','7503','7504')
AND 투입인출구분코드 = 'S'
) B;
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 84 |
| 1 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | 84 |
| 2 | VIEW | | 1 | 1 | 1 |00:00:00.01 | 42 |
| 3 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 42 |
|* 4 | TABLE ACCESS BY INDEX ROWID BATCHED| 거래 | 1 | 33 | 20 |00:00:00.01 | 42 |
|* 5 | INDEX RANGE SCAN | PK_거 | 1 | 66 | 40 |00:00:00.01 | 2 |
| 6 | VIEW | | 1 | 1 | 1 |00:00:00.01 | 42 |
| 7 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 42 |
|* 8 | TABLE ACCESS BY INDEX ROWID BATCHED| 거래 | 1 | 33 | 20 |00:00:00.01 | 42 |
|* 9 | INDEX RANGE SCAN | PK_거 | 1 | 66 | 40 |00:00:00.01 | 2 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("투입인출구분코드"='G')
5 - access("증서번호"='0000000500' AND "이체사유발생일자"<='20240101')
filter(("거래코드"<>'7411' AND "거래코드"<>'7412' AND "거래코드"<>'7503' AND "거래코드"<>'7504'))
8 - filter("투입인출구분코드"='S')
9 - access("증서번호"='0000000500' AND "이체사유발생일자"<='20240101')
filter(("거래코드"<>'7411' AND "거래코드"<>'7412' AND "거래코드"<>'7503' AND "거래코드"<>'7504'))
튜닝(1) - 인덱스 추가
투입인출구분코드를 구성요소로 포함하는 인덱스 추가 → 투입인출구분코드가 access 조건으로 작동 ※ 인덱스 스캔 효율은 향상되었으나, 여전히 인덱스 스캔~테이블 액세스 과정을 반복 수행
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
CREATE INDEX 거래_X01 ON 거래 (증서번호, 투입인출구분코드, 이체사유발생일자);
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 44 | 1 |
| 1 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | 44 | 1 |
| 2 | VIEW | | 1 | 1 | 1 |00:00:00.01 | 22 | 1 |
| 3 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 22 | 1 |
|* 4 | TABLE ACCESS BY INDEX ROWID BATCHED| 거래 | 1 | 33 | 20 |00:00:00.01 | 22 | 1 |
|* 5 | INDEX RANGE SCAN | 거래_X0| 1 | 33 | 20 |00:00:00.01 | 2 | 1 |
| 6 | VIEW | | 1 | 1 | 1 |00:00:00.01 | 22 | 0 |
| 7 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 22 | 0 |
|* 8 | TABLE ACCESS BY INDEX ROWID BATCHED| 거래 | 1 | 33 | 20 |00:00:00.01 | 22 | 0 |
|* 9 | INDEX RANGE SCAN | 거래_X0| 1 | 33 | 20 |00:00:00.01 | 2 | 0 |
--------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter(("거래코드"<>'7411' AND "거래코드"<>'7412' AND "거래코드"<>'7503' AND "거래코드"<>'7504'))
5 - access("증서번호"='0000000500' AND "투입인출구분코드"='G' AND "이체사유발생일자"<='20240101')
8 - filter(("거래코드"<>'7411' AND "거래코드"<>'7412' AND "거래코드"<>'7503' AND "거래코드"<>'7504'))
9 - access("증서번호"='0000000500' AND "투입인출구분코드"='S' AND "이체사유발생일자"<='20240101')
튜닝(2) - SQL 변경
인라인 뷰를 한개로 합치고, CASE로 집계 분기 → 인덱스 스캔~테이블 액세스 과정을 한 번만 수행
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
SELECT /*+ GATHER_PLAN_STATISTICS */
(G_기본이체금액 + G_정산이자) - (S_기본이체금액 + S_정산이자) AS 차액
FROM (
SELECT NVL(SUM(CASE WHEN 투입인출구분코드 = 'G' THEN 기본이체금액 END), 0) G_기본이체금액
, NVL(SUM(CASE WHEN 투입인출구분코드 = 'G' THEN 정산이자 END), 0) G_정산이자
, NVL(SUM(CASE WHEN 투입인출구분코드 = 'S' THEN 기본이체금액 END), 0) S_기본이체금액
, NVL(SUM(CASE WHEN 투입인출구분코드 = 'S' THEN 정산이자 END), 0) S_정산이자
FROM 거래
WHERE 증서번호 = '0000000500'
AND 이체사유발생일자 <= '20240101'
AND 거래코드 NOT IN ('7411','7412','7503','7504')
AND 투입인출구분코드 IN ('G', 'S')
);
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 42 |
| 1 | VIEW | | 1 | 1 | 1 |00:00:00.01 | 42 |
| 2 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 42 |
|* 3 | TABLE ACCESS BY INDEX ROWID BATCHED| 거래 | 1 | 66 | 40 |00:00:00.01 | 42 |
|* 4 | INDEX RANGE SCAN | PK_거 | 1 | 66 | 40 |00:00:00.01 | 2 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(("투입인출구분코드"='G' OR "투입인출구분코드"='S'))
4 - access("증서번호"='0000000500' AND "이체사유발생일자"<='20240101')
filter(("거래코드"<>'7411' AND "거래코드"<>'7412' AND "거래코드"<>'7503' AND "거래코드"<>'7504'))
튜닝(3) - SQL 변경 + 추가 INDEX 유도
- 인라인 뷰를 한개로 합치고, CASE로 집계 분기 → 인덱스 스캔~테이블 액세스 과정을 한 번만 수행
- 추가한 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
28
29
30
31
32
33
SELECT /*+ GATHER_PLAN_STATISTICS */
(G_기본이체금액 + G_정산이자) - (S_기본이체금액 + S_정산이자) AS 차액
FROM (
SELECT /*+ INDEX(거래 거래_X01) */
NVL(SUM(CASE WHEN 투입인출구분코드 = 'G' THEN 기본이체금액 END), 0) G_기본이체금액
, NVL(SUM(CASE WHEN 투입인출구분코드 = 'G' THEN 정산이자 END), 0) G_정산이자
, NVL(SUM(CASE WHEN 투입인출구분코드 = 'S' THEN 기본이체금액 END), 0) S_기본이체금액
, NVL(SUM(CASE WHEN 투입인출구분코드 = 'S' THEN 정산이자 END), 0) S_정산이자
FROM 거래
WHERE 증서번호 = '0000000500'
AND 이체사유발생일자 <= '20240101'
AND 거래코드 NOT IN ('7411','7412','7503','7504')
AND 투입인출구분코드 IN ('G', 'S')
);
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 44 |
| 1 | VIEW | | 1 | 1 | 1 |00:00:00.01 | 44 |
| 2 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 44 |
| 3 | INLIST ITERATOR | | 1 | | 40 |00:00:00.01 | 44 |
|* 4 | TABLE ACCESS BY INDEX ROWID BATCHED| 거래 | 2 | 66 | 40 |00:00:00.01 | 44 |
|* 5 | INDEX RANGE SCAN | 거래_X0| 2 | 66 | 40 |00:00:00.01 | 4 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter(("거래코드"<>'7411' AND "거래코드"<>'7412' AND "거래코드"<>'7503' AND "거래코드"<>'7504'))
5 - access("증서번호"='0000000500' AND (("투입인출구분코드"='G' OR "투입인출구분코드"='S')) AND
"이체사유발생일자"<='20240101')
This post is licensed under CC BY 4.0 by the author.