DATOR


불필요한 JOIN 제거 튜닝[SQL]


★ 아래의 SQL 문장은 불필요한 JOIN 을 수행하고 있다.

 

AA_INCUSP_BKN  PK : INCU_YMD, INCU_SNO

 

SELECT NVL( ROUND( SUM( B.QTY ) / SUM( A.INCU_CNT ) * 100, 0 ), 0 ), NVL( SUM( B.QTY ), 0 ), NVL( SUM( A.INCU_CNT ), 0 )

FROM AA_INCUSP_BKN A,

   ( SELECT C.INCU_YMD, C.INCU_SNO, SUM( A.FML_SIL_CNT + A.ML_SIL_CNT ) QTY

     FROM AA_CHKOUT_MST A,

            AA_MVOC_BKN B,

            AA_INCUSP_BKN C,

            AA_HTEG_UND_BKN D

     WHERE A.CO_DVCD = '10'

     AND A.SIL_YMD LIKE '200905' || '%'

     AND A.CO_DVCD = B.CO_DVCD

     AND A.MVEG_YMD = B.MVEG_YMD

     AND A.MVEG_SNO = B.MVEG_SNO

     AND B.CO_DVCD = C.CO_DVCD

     AND B.INCU_YMD = C.INCU_YMD

     AND B.INCU_SNO = C.INCU_SNO

     AND C.CO_DVCD = D.CO_DVCD

     AND C.HTEG_UND_YMD = D.HTEG_UND_YMD

     AND C.UND_SNO = D.UND_SNO

     AND D.FARM_RLE_NO = 'FR004429'

     GROUP BY C.INCU_YMD, C.INCU_SNO ) B

WHERE A.CO_DVCD = '10'

  AND A.INCU_YMD = B.INCU_YMD

  AND A.INCU_SNO = B.INCU_SNO

 

INLINE VIEW 내부에서 MAIN QUERY 에서 사용할 컬럼 까지 가져오면 불필요하게 JOIN 을 하지 않아도 됨.

 

 

 SELECT NVL( ROUND( SUM( B.QTY ) / SUM( B.INCU_CNT ) * 100, 0 ), 0 ), NVL( SUM( B.QTY ), 0 ), NVL( SUM( B.INCU_CNT ), 0 )

 FROM

   ( SELECT C.INCU_YMD, C.INCU_SNO, SUM( A.FML_SIL_CNT + A.ML_SIL_CNT ) QTY, MAX( C.INCU_CNT ) INCU_CNT

     FROM                 

    AA_CHKOUT_MST A,

    AA_MVOC_BKN B,

    AA_INCUSP_BKN C,

    AA_HTEG_UND_BKN D

WHERE A.CO_DVCD = '10'

   AND A.SIL_YMD LIKE '200905' || '%'

   AND A.CO_DVCD = B.CO_DVCD

   AND A.MVEG_YMD = B.MVEG_YMD

   AND A.MVEG_SNO = B.MVEG_SNO

   AND B.CO_DVCD = C.CO_DVCD

   AND B.INCU_YMD = C.INCU_YMD

   AND B.INCU_SNO = C.INCU_SNO

   AND C.CO_DVCD = D.CO_DVCD

   AND C.HTEG_UND_YMD = D.HTEG_UND_YMD

   AND C.UND_SNO = D.UND_SNO

   AND D.FARM_RLE_NO = 'FR004429'

   AND C.INCU_YMD = '20090415' AND C.INCU_SNO = '014??

   GROUP BY C.INCU_YMD, C.INCU_SNO

   ) B

 

 

Tag :

Leave Comments