짜여진 쿼리를 뜯어 고치고 있는데요...
구문 분석결과
아래쿼리처럼 줄였습니다.
제 생각엔 With를 쓰지 않고 LEFT OUTJER JOIN 으로만으로도 더 빠르게 할 수 있을꺼같은데..
어떻게 고쳐나가야할지 조언좀 부탁드립니다. ㅠㅠ
서버코딩만했는데...디비로 튜닝하려니 머리에 쥐나네요..
원래 CASE문이 많던걸 고쳐서 13초대 까지 는 만들었는데 .ㅠㅠ
조언좀 부탁드립니다.
감사합니다.
WITH SALE AS (
SELECT TA.JUMCD, SALEDT, SPCODE, REFNO, BRANDCD, CATECD, PMCODE
, SUM((NETSALEAMTD + HSAMTD1 + DCAMTD2 + DCAMTD3) * RET_FLAG) AS G_SALED
, SUM((HSAMTD1 + DCAMTD2) * RET_FLAG) AS H_AMTD
, SUM((NETSALEAMTD + DCAMTD3) * RET_FLAG) AS N_SALED
, SUM((DCAMTD3) * RET_FLAG) AS ENURID
, SUM((NETSALEAMTD) * RET_FLAG) AS S_SALED
, SUM((NETSALEAMTW + HSAMTW1 + DCAMTW2 + DCAMTW3) * RET_FLAG) AS G_SALEW
, SUM((HSAMTW1 + DCAMTW2) * RET_FLAG) AS H_AMTW
, SUM((NETSALEAMTW + DCAMTW3) * RET_FLAG) AS N_SALEW
, SUM((DCAMTW3) * RET_FLAG) AS ENURIW
, SUM((NETSALEAMTW) * RET_FLAG) AS S_SALEW
, SUM(SALEQTY * RET_FLAG) AS SALEQTY
, SUM(RET_FLAG) AS CUS_CNT
, (SELECT C.WJUMRATE FROM (SELECT * FROM SEXCHNG S WHERE S.CURCD = 'USD' ORDER BY EXCH_DT DESC) C WHERE ROWNUM = 1) AS DWRATE
, SUM((NETSALEAMTD + HSAMTD1 + DCAMTD2 + DCAMTD3) * RET_FLAG) AS COMP_G_SALED
, SUM((NETSALEAMTD) * RET_FLAG) AS COMP_S_SALED
, SUM((NETSALEAMTW + HSAMTW1 + DCAMTW2 + DCAMTW3) * RET_FLAG) AS COMP_G_SALEW
, SUM((NETSALEAMTW) * RET_FLAG) AS COMP_S_SALEW
, SUM(SALEQTY * RET_FLAG) AS COMP_SALEQTY
, MAX(TA.JS_WONGA_D) AS JS_WONGA_D
, MAX(TA.JS_WONGA_W) AS JS_WONGA_W
, SUM(JS_WONGA_D_AMT * RET_FLAG) AS JS_WONGA_D_AMT
, SUM(JS_WONGA_W_AMT * RET_FLAG) AS JS_WONGA_W_AMT
FROM (
SELECT A.JUMCD, A.SALEDT, B.SPCD AS SPCODE, B.REFNO, B.BRANDCD, B.CATECD, B.PMCODE,
CASE WHEN A.TRANKIND = '00' THEN 1 ELSE -1 END AS RET_FLAG,
B.NETSALEAMTD, B.HSAMTD1, B.DCAMTD2, B.DCAMTD3,
B.NETSALEAMTW, B.HSAMTW1, B.DCAMTW2, B.DCAMTW3,
B.SALEQTY,
A.EXCHNO, A.INETORDERNO ,
E.JS_WONGA_D, E.JS_WONGA_W,
E.JS_WONGA_D * B.SALEQTY AS JS_WONGA_D_AMT, E.JS_WONGA_W * B.SALEQTY AS JS_WONGA_W_AMT
FROM SSALHDR A
,(SELECT S.*
, (SELECT REFER_CODE
FROM COMMON.CCOMMON C
WHERE C.SYS_PART = 'S'
AND C.COMM_PART = 'S022'
AND C.USE_YN = 'Y'
AND C.COMM_CODE = S.CATECD) AS REFER_CODE
FROM SSALDTL S) B
, SJUMCDM C, SCOMBRD D
, SPRCHIS E
WHERE A.JUMCD LIKE '' ||'%'
AND A.EXCHNO = B.EXCHNO
AND A.JUMCD = C.JUMCD
AND C.AIRGB = 'Y'
AND B.BRANDCD = D.BRANDCD
AND A.JUMCD = E.JUMCD
AND B.SPCD = E.SPCODE
AND E.HS_CD = '00000000000'
AND TO_CHAR(SYSDATE, 'YYYYMMDD') BETWEEN E.STRYMD AND E.STPYMD
AND A.SALEDT IN ( SELECT YMD
FROM SALE.COPY_YMD
WHERE YMD BETWEEN '20170101' AND '20171231'
OR YMD BETWEEN '20180126' AND '20180126'
)
AND B.CATECD LIKE '06' ||'%'
AND D.MAEIPGB LIKE '' ||'%' -- 0:수입, 1:환급, 2:내국물품
AND D.BRANDCD LIKE '' ||'%'
AND A.JUMCD IN (SELECT JUMCD FROM SALE.SJUMCDM WHERE AIRCOMP_CD = '01') -- 항공사 구분
AND B.REFER_CODE LIKE '3' ||'%' -- 상품대분류
--AND A.TRANTYPE LIKE DECODE('', '2', '01', '1','', '00') || '%' --ON/OFF LINE 구분
) TA,
(SELECT JUMCD
, INETORDERNO
, ORDER_GB
FROM SARSALH
WHERE JUMCD LIKE '' ||'%'
AND (FLIGHT_DT BETWEEN '20170101' AND '20171231'
OR FLIGHT_DT BETWEEN '20180126' AND '20180126')
) TB
WHERE TA.JUMCD= TB.JUMCD(+)
AND TA.INETORDERNO = TB.INETORDERNO(+)
AND NVL(ORDER_GB,'3') LIKE DECODE('0', '0','', '0') || '%'
GROUP BY TA.JUMCD, SALEDT, SPCODE, REFNO, BRANDCD, CATECD, PMCODE
)
SELECT A.BRANDCD AS GUBUN1, B.BRANDNM AS GUBUN2, '' AS GUBUN3, '' AS GUBUN4, '' AS GUBUN5, '' AS GUBUN6
, SUM(A.G_SALED) AS G_SALED
, SUM(A.H_AMTD) AS H_AMTD
, SUM(A.N_SALED) AS N_SALED
, SUM(A.ENURID) AS ENURID
, SUM(A.S_SALED) AS S_SALED
, SUM(A.G_SALEW) AS G_SALEW
, SUM(A.H_AMTW) AS H_AMTW
, SUM(A.N_SALEW) AS N_SALEW
, SUM(A.ENURIW) AS ENURIW
, SUM(A.S_SALEW) AS S_SALEW
, SUM(A.SALEQTY) AS SALEQTY
, SUM(A.CUS_CNT) AS CUS_CNT
, CASE WHEN SUM(CUS_CNT) = 0 THEN 0 ELSE ROUND(SUM(A.S_SALED) / SUM(CUS_CNT),1) END AS CUST_SALED
, CASE WHEN SUM(CUS_CNT) = 0 THEN 0 ELSE ROUND(SUM(A.S_SALEW) / SUM(CUS_CNT),1) END AS CUST_SALEW
, A.DWRATE
, SUM(COMP_G_SALED) AS COMP_G_SALED
, SUM(COMP_S_SALED) AS COMP_S_SALED
, SUM(COMP_G_SALEW) AS COMP_G_SALEW
, SUM(COMP_S_SALEW) AS COMP_S_SALEW
, SUM(COMP_SALEQTY) AS COMP_SALEQTY
, SUM(JS_WONGA_D_AMT) AS JS_WONGA_D_AMT
, SUM(JS_WONGA_W_AMT) AS JS_WONGA_W_AMT
, 0 AS JG_QTY01
, 0 AS JG_QTY03
FROM SALE A, SCOMBRD B
WHERE A.BRANDCD = B.BRANDCD
AND 7 = '7'
GROUP BY A.BRANDCD, B.BRANDNM, A.DWRATE
;
1. 정렬 부하 최소화 => * 대신 컬럼명을 명시하세요.
, (SELECT c.wjumrate FROM (SELECT * FROM sexchng s WHERE s.curcd = 'USD' ORDER BY exch_dt DESC) c WHERE ROWNUM = 1) AS dwrate
, (SELECT c.wjumrate FROM (SELECT wjumrate FROM sexchng s WHERE s.curcd = 'USD' ORDER BY exch_dt DESC) c WHERE ROWNUM = 1) AS dwrate
2. 불필요한 서브쿼리 조인 제거 1
AND a.saledt IN (SELECT ymd FROM sale.copy_ymd WHERE ymd BETWEEN '20170101' AND '20171231' OR ymd BETWEEN '20180126' AND '20180126')
AND (a.saledt BETWEEN '20170101' AND '20171231' OR a.saledt BETWEEN '20180126' AND '20180126')
3. 불필요한 서브쿼리 조인 제거 2
AND a.jumcd IN (SELECT jumcd FROM sale.sjumcdm WHERE aircomp_cd = '01')
AND c.aircomp_cd = '01'