SELECT A.* FROM ( SELECT COUNT(*) AS TOTAL_ORDER_COUNT, SUM(M.ORDER_AMT) AS TOTAL_ORDER_AMT FROM TBL_ORDER_MASTER M -- 주문내역마스터 LEFT JOIN ( SELECT STORE_CD, ORDER_DATE, ORDER_NO, COUNT(CASE WHEN MENU_TYPE = 'A' THEN 1 END) AS A_MENU_TYPE_CNT FROM TBL_ORDER_MENU --주문메뉴내역 WHERE 1 = 1 -- <if(storeGrpCd != null and storeCd == null)> AND STORE_CD IN ( SELECT STORE_CD FROM TBL_STORE -- 매장 WHERE USE_YN = 'Y' AND STORE_GRP_CD = '#{storeGrpCd}' AND (DEL_YN IS NULL OR DEL_YN != 'Y') ) --</if> -- <if(storeCd != null)> AND STORE_CD IN (select regexp_substr(#{storeCd}, '[^,]+', 1, level) from dual connect BY regexp_substr(#{storeCd}, '[^,]+', 1, level) is not null) --</if> AND ORDER_DATE >= '#{strtOrderDate}' AND ORDER_DATE <= '#{endOrderDate}' GROUP BY STORE_CD, ORDER_DATE, ORDER_NO ) TI ON M.STORE_CD = TI.STORE_CD AND M.ORDER_DATE = TI.ORDER_DATE AND M.ORDER_NO = TI.ORDER_NO WHERE 1 = 1 -- <if(storeGrpCd != null and storeCd == null)> AND M.STORE_CD IN ( SELECT STORE_CD FROM TBL_STORE WHERE USE_YN = 'Y' AND STORE_GRP_CD = '#{storeGrpCd}' AND (DEL_YN IS NULL OR DEL_YN != 'Y') ) --</if> -- <if(storeCd != null)> AND STORE_CD IN (select regexp_substr(#{storeCd}, '[^,]+', 1, level) from dual connect BY regexp_substr(#{storeCd}, '[^,]+', 1, level) is not null) --</if> AND M.ORDER_DATE >= '#{strtOrderDate}' AND M.ORDER_DATE <= '#{endOrderDate}' AND M.ORDER_TYPE IN ('A' , 'B') AND TI.A_MENU_TYPE_CNT > 0 ) A;
속도가 너무 느린 쿼리가 있어서
봤더니 똑같은 in절을 2번씩 쓰고있고
의도를 모르겠습니다..
튜닝 가능할까요?
주문통계 뽑는 쿼리이고
매장마다 메뉴가 다릅니다.
1개의그룹에(STORE_GRP_CD)
여러개의 매장이있습니다(STORE_CD)
SELECT A.* FROM (SELECT COUNT(*) AS TOTAL_ORDER_COUNT, SUM(M.ORDER_AMT) AS TOTAL_ORDER_AMT, FROM TBL_ORDER_MASTER M LEFT OUTER JOIN (SELECT STORE_CD, ORDER_DATE, ORDER_NO, COUNT(CASE WHEN MENU_TYPE = 'A' THEN 1 END) AS A_MENU_TYPE_CNT FROM TBL_ORDER_MENU --주문메뉴내역 WHERE 1 = 1 AND STORE_CD IN (SELECT STORE_CD FROM TBL_STORE WHERE USE_YN = 'Y' AND STORE_GRP_CD = '#{storeGrpCd}' AND (DEL_YN IS NULL OR DEL_YN != 'Y') ) AND STORE_CD IN (select regexp_substr(#{storeCd}, '[^,]+', 1, level) from dual connect BY regexp_substr(#{storeCd}, '[^,]+', 1, level) is not null) AND ORDER_DATE >= '#{strtOrderDate}' AND ORDER_DATE <= '#{endOrderDate}' GROUP BY STORE_CD, ORDER_DATE, ORDER_NO ) TI ON M.STORE_CD = TI.STORE_CD AND M.ORDER_DATE = TI.ORDER_DATE AND M.ORDER_NO = TI.ORDER_NO WHERE 1 = 1 AND M.STORE_CD IN (SELECT STORE_CD FROM TBL_STORE WHERE USE_YN = 'Y' AND STORE_CD IN (SELECT STORE_CD FROM TBL_STORE WHERE USE_YN = 'Y' AND STORE_GRP_CD = '#{storeGrpCd}' AND (DEL_YN IS NULL OR DEL_YN != 'Y') ) AND STORE_CD IN (select regexp_substr(#{storeCd}, '[^,]+', 1, level) from dual connect BY regexp_substr(#{storeCd}, '[^,]+', 1, level) is not null) ) AND M.ORDER_DATE >= '#{strtOrderDate}' AND M.ORDER_DATE <= '#{endOrderDate}' AND M.ORDER_TYPE IN ('A' , 'B') AND TI.A_MENU_TYPE_CNT > 0 ) A;
수정해서 올리긴 했는데....
다시 올리주신 쿼리랑 같은 쿼리네요.
우주민님! 답변감사합니다
in절 2번들어가는건 제가 편집을 잘못한것입니다.
현재는 수정버전으로 수정해놨습니다.
본문의 질문은
AND M.STORE_CD IN (
SELECT
STORE_CD
FROM
TBL_STORE
WHERE
USE_YN = 'Y'
AND STORE_GRP_CD = '#{storeGrpCd}'
AND (DEL_YN IS NULL
OR DEL_YN != 'Y')
)
--</if>
-- <if(storeCd != null)>
AND STORE_CD IN (select regexp_substr(#{storeCd}, '[^,]+', 1, level) from dual connect BY regexp_substr(#{storeCd}, '[^,]+', 1, level) is not null)
--</if>
이부분이 2번 들어갈 필요가 있느냐에 질문이였습니다.
튜닝이 더 가능하지않을까해서 질문올렸습니다
SELECT A.* FROM (SELECT COUNT(*) AS TOTAL_ORDER_COUNT, SUM(M.ORDER_AMT) AS TOTAL_ORDER_AMT, FROM TBL_ORDER_MASTER M INNER JOIN (SELECT STORE_CD, ORDER_DATE, ORDER_NO, COUNT(CASE WHEN MENU_TYPE = 'A' THEN 1 END) AS A_MENU_TYPE_CNT FROM TBL_ORDER_MENU --주문메뉴내역 WHERE 1 = 1 AND STORE_CD IN (SELECT STORE_CD FROM TBL_STORE WHERE USE_YN = 'Y' AND STORE_GRP_CD = '#{storeGrpCd}' AND (DEL_YN IS NULL OR DEL_YN != 'Y') ) AND STORE_CD IN (select regexp_substr(#{storeCd}, '[^,]+', 1, level) from dual connect BY regexp_substr(#{storeCd}, '[^,]+', 1, level) is not null) AND ORDER_DATE >= '#{strtOrderDate}' AND ORDER_DATE <= '#{endOrderDate}' GROUP BY STORE_CD, ORDER_DATE, ORDER_NO ) TI ON M.STORE_CD = TI.STORE_CD AND M.ORDER_DATE = TI.ORDER_DATE AND M.ORDER_NO = TI.ORDER_NO WHERE 1 = 1 AND M.ORDER_TYPE IN ('A' , 'B') AND TI.A_MENU_TYPE_CNT > 0 ) A;
마지막에 T1.A_MENU_TYPE_CNT > 0 라는 조건으로 LEFT OUTER JOIN 을 INNER 로 변경하면 위와 같은 쿼리가 될듯 합니다.