SQL 튜닝의 시작 (2013년)
CONNECT BY LEVEL 활용하기
데이터 변환시 CONNECT BY LEVEL 사용하기
-- 아래 SQL을 수행할 경우 추출결과는 한 개의 로우로 표현된다.
-- 가독성을 높이기 위해서 가로 열 세로 열 변환하여 추출하고자하는 SQL 생성
SQL> WITH CONNECT_BY_LEVEL_T AS
2 (
3 SELECT '통신회사' AS C1
4 ,'SK텔ㄹ레콤' AS C2
5 ,'KT' AS C3
6 ,'LGU+' AS C4
7 FROM DUAL
8 )
9 SELECT *
10 FROM CONNECT_BY_LEVEL_T
11 ;
C1 C2 C3 C4
-------- ---------- -- ----
통신회사 SK텔ㄹ레콤 KT LGU+
WITH CONNECT_BY_LEVEL_T AS
(
SELECT '통신회사' AS C1
,'SK텔레콤' AS C2
,'KT' AS C3
,'LGU+' AS C4
FROM DUAL
)
SELECT C1 AS "통신회사"
FROM ( SELECT TT.RNO
,MAX(DECODE(TT.RNO,1,DECODE(T.C1,'통신회사',DECODE(T.C2,'SK텔레콤',T.C2))
,2,DECODE(T.C1,'통신회사',DECODE(T.C3,'KT',T.C3))
,3,DECODE(T.C1,'통신회사',DECODE(T.C4,'LGU+',T.C4)))) AS C1
FROM CONNECT_BY_LEVEL_T T,
(SELECT LEVEL AS RNO
FROM DUAL
CONNECT BY LEVEL <= 3) TT
GROUP BY TT.RNO)
통신회사
--------
SK텔레콤
KT
LGU+
데이터 중복 조회 제거하기
스크립트생성
DROP TABLE CONNECT_BY_LEVEL_SUM PURGE;
SQL> CREATE TABLE CONNECT_BY_LEVEL_SUM AS
2 SELECT LEVEL AS C1
3 ,TO_CHAR(TO_DATE('19940101','YYYYMMDD')+LEVEL-1,'YYYYMMDD') AS C2
4 FROM DUAL
5 CONNECT BY LEVEL <= 100000;
SQL[1] 동일데이터 중복조회로 인한 비효율 발생
-- 월(계)와 일(계) 데이터를 UNION ALL 을 이용하여, 동일 데이터를 두번 읽어 UNION ALL의 상단에는 월(계), 하단에는 일(계)를 추출하는 SQL이다.
-- 여기서 문제는 동일한 데이터를 중복으로 읽는 문제가 발생된다.
SELECT '월(계)' AS C1
,SUBSTR(C2,1,6) AS C2
,SUM(C1) AS C3
FROM CONNECT_BY_LEVEL_SUM
WHERE C2 BETWEEN TO_CHAR(TRUNC(SYSDATE,'MONTH'),'YYYYMMDD')
AND TO_CHAR(SYSDATE,'YYYYMMDD')
GROUP BY SUBSTR(C2,1,6)
UNION ALL
SELECT '일(계)'
,C2
,SUM(C1)
FROM CONNECT_BY_LEVEL_SUM
WHERE C2 BETWEEN TO_CHAR(TRUNC(SYSDATE,'MONTH'),'YYYYMMDD')
AND TO_CHAR(SYSDATE,'YYYYMMDD')
GROUP BY C2
C1 C2 C3
------ ------------ ----------
월(계) 201310 158939
일(계) 20131007 7220
일(계) 20131010 7223
일(계) 20131019 7232
일(계) 20131020 7233
일(계) 20131021 7234
일(계) 20131014 7227
일(계) 20131015 7228
일(계) 20131017 7230
일(계) 20131011 7224
일(계) 20131012 7225
C1 C2 C3
------ ------------ ----------
일(계) 20131016 7229
일(계) 20131001 7214
일(계) 20131005 7218
일(계) 20131006 7219
일(계) 20131009 7222
일(계) 20131002 7215
일(계) 20131004 7217
일(계) 20131008 7221
일(계) 20131013 7226
일(계) 20131018 7231
일(계) 20131003 7216
C1 C2 C3
------ ------------ ----------
일(계) 20131022 7235
Plan hash value: 1082975143
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 188 | 3572 | 157 (53)| 00:00:02 |
| 1 | UNION-ALL | | | | | |
| 2 | HASH GROUP BY | | 94 | 1786 | 79 (6)| 00:00:01 |
|* 3 | FILTER | | | | | |
|* 4 | TABLE ACCESS FULL| CONNECT_BY_LEVEL_SUM | 94 | 1786 | 78 (4)| 00:00:01 |
| 5 | HASH GROUP BY | | 94 | 1786 | 79 (6)| 00:00:01 |
|* 6 | FILTER | | | | | |
|* 7 | TABLE ACCESS FULL| CONNECT_BY_LEVEL_SUM | 94 | 1786 | 78 (4)| 00:00:01 |
---------------------------------------------------------------------------------------------
대상 SQL의 비효율적인 데이터를 두번읽는 비효율을 제거 할 수 있는 방법은
SQL의 두 테이블이 조인 할 때, 조인 조건이 존재하지 않을 경우 CARTESIAN PRODUCT가 발생한다.
이때 데이터가 M*M으로 복제가 되는데, 바로 이러한 점을 이용하여, 원래의 데이터를 원하는 만큼 복제하여,
동일한 데이터를 여러 번 반복해 읽지 않고 처리할 수 있다.
CARTESIAN PRODUCT가 발생하는 경우 데이터가 어떻게 복제되는지 간단히 확인은
책 286~287을 참조한다.
SQL[2] 동일 데이터 중복조회를 제거한 효율적인 SQL
SELECT DECODE(Y.RNO,1,'일(계)','월(계)') AS C1
,DECODE(Y.RNO,1,C2,SUBSTR(C2,1,6)) AS C2
,SUM(DECODE(Y.RNO,1,C3,C3)) AS C3
FROM ( SELECT C2, SUM(C1) AS C3
FROM CONNECT_BY_LEVEL_SUM
WHERE C2 BETWEEN TO_CHAR(TRUNC(SYSDATE,'MONTH'),'YYYYMMDD')
AND TO_CHAR(SYSDATE,'YYYYMMDD')
GROUP BY C2
) X,
(SELECT LEVEL AS RNO
FROM DUAL
CONNECT BY LEVEL <=2) Y
GROUP BY DECODE(Y.RNO,1,'일(계)','월(계)')
,DECODE(Y.RNO,1,C2,SUBSTR(C2,1,6))
C1 C2 C3
------ ------------ ----------
일(계) 20131010 7223
일(계) 20131020 7233
일(계) 20131007 7220
일(계) 20131012 7225
월(계) 201310 158939
일(계) 20131016 7229
일(계) 20131002 7215
일(계) 20131017 7230
일(계) 20131009 7222
일(계) 20131019 7232
일(계) 20131015 7228
C1 C2 C3
------ ------------ ----------
일(계) 20131001 7214
일(계) 20131008 7221
일(계) 20131018 7231
일(계) 20131021 7234
일(계) 20131014 7227
일(계) 20131011 7224
일(계) 20131013 7226
일(계) 20131003 7216
일(계) 20131005 7218
일(계) 20131006 7219
일(계) 20131004 7217
C1 C2 C3
------ ------------ ----------
일(계) 20131022 7235
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 94 | 3008 | 82 (7)| 00:00:01 |
| 1 | HASH GROUP BY | | 94 | 3008 | 82 (7)| 00:00:01 |
| 2 | MERGE JOIN CARTESIAN | | 94 | 3008 | 81 (5)| 00:00:01 |
| 3 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
|* 4 | CONNECT BY WITHOUT FILTERING| | | | | |
| 5 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 6 | BUFFER SORT | | 94 | 1786 | 82 (7)| 00:00:01 |
| 7 | VIEW | | 94 | 1786 | 79 (6)| 00:00:01 |
| 8 | HASH GROUP BY | | 94 | 1786 | 79 (6)| 00:00:01 |
|* 9 | FILTER | | | | | |
|* 10 | TABLE ACCESS FULL | CONNECT_BY_LEVEL_SUM | 94 | 1786 | 78 (4)| 00:00:01 |
--------------------------------------------------------------------------------------------------------
데이터 중복 조회 제거하기
스크립트생성
SQL> CREATE TABLE DUMMY_TABLE_DATE
2 (
3 C1 NUMBER,
4 C2 CHAR(1),
5 C3 VARCHAR2(8)
6 );
테이블이 생성되었습니다.
SQL> ALTER SESSION SET NLS_DATE_FORMAT='YYYYMMDD';
세션이 변경되었습니다.
SQL> INSERT INTO DUMMY_TABLE_DATE
2 SELECT LEVEL
3 ,CHR(65+MOD(LEVEL,26))
4 ,DECODE(MOD(LEVEL,5),0,'20110101',
5 1,'20110102',
6 2,'20110103',
7 3,'20110104',
8 4,'20110105')
9 FROM DUAL
10 CONNECT BY LEVEL <= 500000;
500000 개의 행이 만들어졌습니다.
SQL>
SQL> COMMIT;
커밋이 완료되었습니다.
SQL> CREATE INDEX DUMMY_TABLE_DATE_IDX01 ON DUMMY_TABLE_DATE(C3,C2,C1);
인덱스가 생성되었습니다.
비교테스트(BETWEEN vs CONNECT BY LEVEL 활용)
SQL> SELECT C3, COUNT(*)
2 FROM DUMMY_TABLE_DATE
3 GROUP BY C3
4 ORDER BY C3;
C3 COUNT(*)
-------- ----------
20110101 100000
20110102 100000
20110103 100000
20110104 100000
20110105 100000
SQL결과는 C3의 분포도를 의미한다.
각각의 100000건씩 존재
테이블인덱스는 C3+C2의 순서로 된 결합인덱스 존재
인덱스 선두 컬럼인 C3컬럼을 BETWEEN으로 조회할 때와
CONNECT BY LEVEL을 활용하여 조인으로 처리하는 경우에 대한 성능 차이를 확인해본다.
-- 테스트[1] BETWEEN 조회
VAR B1 VARCHAR2(10)
VAR B2 VARCHAR2(10)
VAR B3 VARCHAR2(10)
EXEC :B1 := '20110101'
EXEC :B2 := '20110105'
EXEC :B3 := 'A'
SELECT /*+ INDEX(X DUMMY_TABLE_DATE_IDX01) */
X.*
FROM DUMMY_TABLE_DATE X
WHERE X.C3 BETWEEN :B1 AND :B2
AND X.C2 = :B3
call count cpu elapsed disk query current rows
------- ---------- ---------- ---------- ----------- ----------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.02 0.01 0 71 0 0
Fetch 771 0.11 0.24 1538 2288 0 19230
------- ---------- ---------- ---------- ----------- ----------- ---------- ----------
Total 773 0.12 0.25 1538 2359 0 19230
Rows Row Source Operation
------- ---------------------------------------------------
19230 FILTER (cr=2288 pr=1538 pw=0 time=8204 us)
19230 INDEX RANGE SCAN DUMMY_TABLE_DATE_IDX01 (cr=2288 pr=1538 pw=0 time=5384 us cost=1925 size=428164 card=19462)(object id 80913)
-- 테스트[2] CONNECT BY LEVEL을 활용한 조회
SELECT /*+ LEADING(D) USE_NL(D X) INDEX(X DUMMY_TABLE_DATE_IDX01) */
X.*
FROM DUMMY_TABLE_DATE X,
(
SELECT TO_CHAR(TO_DATE(:B1,'YYYYMMDD')+LEVEL-1,'YYYYMMDD') AS C3
FROM DUAL
CONNECT BY LEVEL <= (TO_DATE(:B2,'YYYYMMDD')-TO_DATE(:B1,'YYYYMMDD')+1)
) D
WHERE D.C3 = X.C3
AND X.C2 = :B3
call count cpu elapsed disk query current rows
------- ---------- ---------- ---------- ----------- ----------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.02 0.01 0 71 0 0
Fetch 771 0.00 0.04 2 855 0 19230
------- ---------- ---------- ---------- ----------- ----------- ---------- ----------
Total 773 0.02 0.05 2 926 0 19230
Rows Row Source Operation
------- ---------------------------------------------------
19230 NESTED LOOPS (cr=855 pr=2 pw=0 time=8332 us cost=5 size=108976 card=3892)
5 VIEW (cr=0 pr=0 pw=0 time=80 us cost=2 size=6 card=1)
5 CONNECT BY WITHOUT FILTERING (cr=0 pr=0 pw=0 time=64 us)
1 FAST DUAL (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)
19230 INDEX RANGE SCAN DUMMY_TABLE_DATE_IDX01 (cr=855 pr=2 pw=0 time=5492 us cost=3 size=85624 card=3892)(object id 80913)
기존 쿼리블록 2359 -> 926로 2.5배 가량 향상된걸 확인 할 수 있다.
"데이터베이스 스터디모임" 에서 2013년에 "SQL튜닝의시작
" 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/3816
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.