DATOR


조건절 값의 구분에 의한 UNION ALL 문장의 비효율 줄이기 튜닝[SQL]


아래와 같은 유형의 SQL 문장을 튜닝 프로젝트에서 자주 접하게 됩니다.

 똑같은 경우도 있고 비슷한 유형도 많이 있습니다.

 

EX)

 

SELECT A.COL1, A.COL2

  FROM TAB1 A

WHERE A.CON1 = '1'

UNION ALL

SELECT A.COL1, A.COL2

  FROM TAB1 A

WHERE A.CON1 = '2'

 

인덱스는 CON1 칼럼에 생성되어 있습니다.

 

여기서는 칼럼의 히스토그램 및 그에 따른 INDEX RANGE SCAN, FULL TABLE SCAN 의 논의는 제외 하고 INDEX RANGE SCAN 을 한다고 생각합니다.

 

단순히 테이블 하나만 ACCESS 한다면 위 SQL은 통합을 하는것이 유리할까요? 아니면 같을까요?

 

별다른 이변이 없다면 통합을 하던 분리를 하던 성능은 같을 것입니다.

 

여러 다른 조건과의 조합에 따라 분리를 하는것이 성능상 더 유리할수도 있을 것입니다.

 

분리에 따른 성능향상의 유형은 다음에 보여드리도록 하고. 아래와 같은 경우는 통합을 하여 성능을 향상시킨 경우 입니다.

 

EX)

 

SELECT A.COL1, A.COL2, '' COL3 

  FROM TAB1 A

WHERE A.CON1 = '1'

UNION ALL

SELECT A.COL1, A.COL2 , B.COL1

  FROM TAB1 A , TAB2 B

WHERE A.COL1 = B.COL1

    AND  A.CON1 = '2'

 

위의 SQL 에서 보면 조건이 '2' 인 경우는 TAB2 와 조인을 하여 값을 가져오고 있습니다.

 

이런경우라면 아래와 같이 통합을 하고 조건이 '2' 인경우는 SCALA SUBQUERY 로 처리해주면 최종결과 집합과 JOIN 함으로 비효율을 줄일수 있습니다.

 

SELECT A.COL1, A.COL2,

              CASE WHEN A.CON1 = '2'  THEN

                        (SELECT COL3

                           FROM TAB2 X

                         WHERE A.COL1 = X.COL1) 

               ELSE '' END  COL3

  FROM TAB1 A

 

아래는 실제 수행된 SQL Trace결과 입니다.

 

=======================================================================================================================


SELECT EM.CO_DVCD,
                         EM.ETPS_IO_YMD IO_YMD,
                         EM.ETPS_IO_NO IO_NO,
                         EM.SORD_IPTY_NO,
                         EM.SORD_TRCO_MDEPT_SQ,
                         EM.SORD_IO_TPI_DVCD,
                         EM.RORD_IPTY_NO,
                         EM.RORD_TRCO_MDEPT_SQ,
                         EM.ETPS_IO_DVCD,
                         EM.RORD_IO_TPI_DVCD,
                         EM.DPP_NODE_NO DPP_NODE_NO,
                         EM.DPP_NODE_DVCD,
                         EM.ARVL_NODE_NO ARVL_NODE_NO,
                         EM.ARVL_NODE_DVCD,
                         EM.RMK,
                         MDEPT_NO
                    FROM AA_ETPS_IO_MST EM, AA_CD_REL CR
                   WHERE     CR.CD_REL_DVCD = '0008'
                         AND CR.OPNT_HDCD = 'WM100004'
                         AND CR.STD_HDCD = 'SA700021'
                         AND '2' = :AS_OPNT_DTCD
                         AND CR.OPNT_DTCD = :AS_OPNT_DTCD
                         AND CR.STD_DTCD LIKE :AS_STD_DTCD
                         AND EM.ETPS_IO_DVCD = CR.STD_DTCD
                         AND EM.CO_DVCD = :AS_CO_DVCD
                         AND EM.ETPS_IO_YMD BETWEEN :AS_ETPS_IO_FYMD
                                                AND :AS_ETPS_IO_TYMD
                         AND EM.DEL_YN = 'N'
                         AND EM.DPP_NODE_GHQ_NO = :AS_NODE_GHQ_NO
                         AND EM.DPP_NODE_NO = :AS_NODE_NO
                         AND EM.SORD_IPTY_NO LIKE :AS_SORD_IPTY_NO
                         AND NVL (EM.SORD_TRCO_MDEPT_SQ, ' ') LIKE
                                :AS_SORD_TRCO_MDEPT_SQ
                         AND EM.SORD_IO_TPI_DVCD <> '007'  <== 이조건이 다름
                  UNION ALL
                  SELECT EM.CO_DVCD,
                         EM.ETPS_IO_YMD IO_YMD,
                         EM.ETPS_IO_NO IO_NO,
                         EM.SORD_IPTY_NO,
                         EM.SORD_TRCO_MDEPT_SQ,
                         EM.SORD_IO_TPI_DVCD,
                         EM.RORD_IPTY_NO,
                         EM.RORD_TRCO_MDEPT_SQ,
                         EM.ETPS_IO_DVCD,
                         EM.RORD_IO_TPI_DVCD,
                         EM.DPP_NODE_NO DPP_NODE_NO,
                         EM.DPP_NODE_DVCD,
                         EM.ARVL_NODE_NO ARVL_NODE_NO,
                         EM.ARVL_NODE_DVCD,
                         EM.RMK,
                         A.MDEPT_NO MDEPT_NO
                    FROM AA_ETPS_IO_MST EM, AA_CD_REL CR, AA_DEPT_EMP_CHG_HST A   <===  조건에 따라 테이블 추가로 읽음.
                   WHERE     CR.CD_REL_DVCD = '0008'
                         AND CR.OPNT_HDCD = 'WM100004'
                         AND CR.STD_HDCD = 'SA700021'
                         AND EM.CO_DVCD = A.CO_DVCD
                         AND EM.SORD_IPTY_NO = A.TRNO
                         AND EM.SORD_TRCO_MDEPT_SQ = A.TRCO_MDEPT_SQ
                         AND EM.ETPS_IO_YMD BETWEEN A.BYMD AND A.EYMD
                         AND '2' = :AS_OPNT_DTCD --1 : 입고, 2 : 출고
                         AND CR.OPNT_DTCD = :AS_OPNT_DTCD --1 : 입고, 2 : 출고
                         AND CR.STD_DTCD LIKE :AS_STD_DTCD --%
                         AND EM.ETPS_IO_DVCD = CR.STD_DTCD
                         AND EM.CO_DVCD = :AS_CO_DVCD --10
                         AND EM.ETPS_IO_YMD BETWEEN :AS_ETPS_IO_FYMD --20101201
                                                AND :AS_ETPS_IO_TYMD --20101231
                         AND EM.DEL_YN = 'N'
                         AND EM.DPP_NODE_GHQ_NO = :AS_NODE_GHQ_NO --B102
                         AND EM.DPP_NODE_NO = :AS_NODE_NO --C10S000300
                         AND A.MDEPT_NO LIKE :AS_MDEPT_NO --%         <==추가 테이블에 대한 조건
                         AND EM.SORD_IPTY_NO LIKE :AS_SORD_IPTY_NO --%
                         AND NVL (EM.SORD_TRCO_MDEPT_SQ, ' ') LIKE
                                :AS_SORD_TRCO_MDEPT_SQ --%
                         AND EM.SORD_IO_TPI_DVCD = '007'   <====이조건이 다름

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           58        0.06       0.05           0        8080          0        1416
------- ----------  ---------- ---------- ----------- ----------- ----------  ----------
Total           60        0.06       0.05           0        8080          0        1416

Misses in library cache during parse: 0
Optimizer goal: All_Rows
Parsing user id: 62  (UHATIS)

Rows     Row Source Operation
-------  ---------------------------------------------------
   1416  UNION-ALL  (cr=8080 pr=0 pw=0 time=11052 us)
    120   FILTER  (cr=1619 pr=0 pw=0 time=1138 us)
    120    NESTED LOOPS  (cr=1619 pr=0 pw=0 time=1131 us)
    120     TABLE ACCESS BY INDEX ROWID AA_ETPS_IO_MST (cr=1493 pr=0 pw=0 time=2654 us)
   1416      INDEX RANGE SCAN AA_ETPS_IO_MST_IX02 (cr=98 pr=0 pw=0 time=3914 us)(object id 412916)
    120     INDEX UNIQUE SCAN BA_CD_REL_PK (cr=126 pr=0 pw=0 time=437 us)(object id 413249)
   1296   FILTER  (cr=6461 pr=0 pw=0 time=80368 us)
   1296    TABLE ACCESS BY INDEX ROWID AA_DEPT_EMP_CHG_HST (cr=6461 pr=0 pw=0 time=80366 us)
   2593     NESTED LOOPS  (cr=5594 pr=0 pw=0 time=152939 us)
   1296      NESTED LOOPS  (cr=2890 pr=0 pw=0 time=18029 us)
   1296       TABLE ACCESS BY INDEX ROWID AA_ETPS_IO_MST (cr=1540 pr=0 pw=0 time=10251 us)
   1416        INDEX RANGE SCAN AA_ETPS_IO_MST_IX02 (cr=144 pr=0 pw=0 time=2286 us)(object id 412916)
   1296       INDEX UNIQUE SCAN AA_CD_REL_PK (cr=1350 pr=0 pw=0 time=4927 us)(object id 413249)
   1296      INDEX RANGE SCAN AA_DEPT_EMP_CHG_HST_PK (cr=2704 pr=0 pw=0 time=11602 us)(object id 413623)  <== 대상집합 전체와 JOIN 하고 있음.

====================================================================================================================
   
SELECT EM.CO_DVCD,
                         EM.IO_YMD,
                         EM.IO_NO,
                         EM.SORD_IPTY_NO,
                         EM.SORD_TRCO_MDEPT_SQ,
                         EM.SORD_IO_TPI_DVCD,
                         EM.RORD_IPTY_NO,
                         EM.RORD_TRCO_MDEPT_SQ,
                         EM.ETPS_IO_DVCD,
                         EM.RORD_IO_TPI_DVCD,
                         EM.DPP_NODE_NO DPP_NODE_NO,
                         EM.DPP_NODE_DVCD,
                         EM.ARVL_NODE_NO ARVL_NODE_NO,
                         EM.ARVL_NODE_DVCD,
                         EM.RMK,
                         EM.MDEPT_NO
                  FROM
                        (
                        SELECT EMI.CO_DVCD,
                               EMI.ETPS_IO_YMD IO_YMD,
                               EMI.ETPS_IO_NO IO_NO,
                               EMI.SORD_IPTY_NO,
                               EMI.SORD_TRCO_MDEPT_SQ,
                               EMI.SORD_IO_TPI_DVCD,
                               EMI.RORD_IPTY_NO,
                               EMI.RORD_TRCO_MDEPT_SQ,
                               EMI.ETPS_IO_DVCD,
                               EMI.RORD_IO_TPI_DVCD,
                               EMI.DPP_NODE_NO DPP_NODE_NO,
                               EMI.DPP_NODE_DVCD,
                               EMI.ARVL_NODE_NO ARVL_NODE_NO,
                               EMI.ARVL_NODE_DVCD,
                               EMI.RMK,
                              CASE WHEN EM.SORD_IO_TPI_DVCD = '007' THEN

                                       (SELECT  MDEPT_NO FROM  SA_DEPT_EMP_CHG_HST A
                                        WHERE EMI.CO_DVCD = A.CO_DVCD
                                             AND EMI.SORD_IPTY_NO = A.TRNO
                                             AND EMI.SORD_TRCO_MDEPT_SQ = A.TRCO_MDEPT_SQ
                                             AND EMI.ETPS_IO_YMD BETWEEN A.BYMD AND A.EYMD

                           ELSE '' END  MDEPT_NO
                          FROM AA_ETPS_IO_MST EMI, AA_CD_REL CR
                         WHERE     CR.CD_REL_DVCD = '0008'
                               AND CR.OPNT_HDCD = 'WM100004'
                               AND CR.STD_HDCD = 'SA700021'
                               AND '2' = :AS_OPNT_DTCD
                               AND CR.OPNT_DTCD = :AS_OPNT_DTCD
                               AND CR.STD_DTCD LIKE :AS_STD_DTCD
                               AND EMI.ETPS_IO_DVCD = CR.STD_DTCD
                               AND EMI.CO_DVCD = :AS_CO_DVCD
                               AND EMI.ETPS_IO_YMD BETWEEN :AS_ETPS_IO_FYMD
                                                      AND :AS_ETPS_IO_TYMD
                               AND EMI.DEL_YN = 'N'
                               AND EMI.DPP_NODE_GHQ_NO = :AS_NODE_GHQ_NO
                               AND EMI.DPP_NODE_NO = :AS_NODE_NO
                               AND EMI.SORD_IPTY_NO LIKE :AS_SORD_IPTY_NO
                               AND NVL (EMI.SORD_TRCO_MDEPT_SQ, ' ') LIKE
                                      :AS_SORD_TRCO_MDEPT_SQ
                         )EM
                         WHERE  NVL(EM.MDEPT_NO, '%') LIKE :AS_MDEPT_NO

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           58        0.04       0.04           0        5519          0        1416
------- ----------  ---------- ---------- ----------- ----------- ----------  ----------
Total           60        0.04       0.04           0        5519          0        1416

Misses in library cache during parse: 0
Optimizer goal: All_Rows
Parsing user id: 62  (UHATIS)

Rows     Row Source Operation
-------  ---------------------------------------------------
    813  TABLE ACCESS BY INDEX ROWID AA_DEPT_EMP_CHG_HST (cr=2499 pr=0 pw=0 time=12424 us)
    813   INDEX RANGE SCAN AA_DEPT_EMP_CHG_HST_PK (cr=1686 pr=0 pw=0 time=7846 us)(object id 413623) <==  조건에 해당되는 건만 읽음(CR 감소).
   1416  VIEW  (cr=5519 pr=0 pw=0 time=33755 us)
   1416   FILTER  (cr=3020 pr=0 pw=0 time=16738 us)
   1416    NESTED LOOPS  (cr=3020 pr=0 pw=0 time=16730 us)
   1416     TABLE ACCESS BY INDEX ROWID AA_ETPS_IO_MST (cr=1546 pr=0 pw=0 time=22365 us)
   1416      INDEX RANGE SCAN AA_ETPS_IO_MST_IX02 (cr=149 pr=0 pw=0 time=12440 us)(object id 412916)
   1416     INDEX UNIQUE SCAN AA_CD_REL_PK (cr=1474 pr=0 pw=0 time=5223 us)(object id 413249)

 

※ UNION ALL 의 분리와 통합에 관련된 부분은  조건절에 따라 그리고 테이블의 조인방식에 따라 유리한 경우와 불리한 경우가 있음으로

   실행계획과 COST를 충분히 검토후 적용하셔야 하겠습니다. 

Tag :

Leave Comments