by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

CONNECT BY LEVEL 활용하기




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배 가량 향상된걸 확인 할 수 있다.

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.