데이터베이스 질문과 답변
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 로 변경하면 위와 같은 쿼리가 될듯 합니다.
대댓글이안되서 여기다 답글 올립니다
in절이 문제일까요?
DEL_YN IS NULL OR DEL_YN != 'Y' 이부분도 성능에 많이 문제일까요?
TBL_STORE 테이블만 읽는데도 속도가 많이 느립니다ㅠㅠ
단순하게 쿼리 만으로 파악하기는 힘든 부분이 많습니다.
테이블에 있는 데이터양이나 구성, 인덱스, 실행계획이 어떻게 구성되는지 등등 복합적으로 파악하셔야 할듯 합니다.
단 이번의 경우는 아마 OUTER JOIN 을 INNER JOIN 으로 변경하면서 그래도 조금은 나은 성능이 되지 않을까 추측해봅니다.
답변감사합니다!
58라인의 괄호닫기가 62라인쪽으로 내려가야 맞을 듯 하네요.
쿼리 정리중에 테이블 별칭 때문에 저도 그렇게 생각하긴 했는데....
주문메뉴내역 테이블에 적용한 스타일 보니 그냥 복사 해서 붙여넣느라 그렇게 된듯하네요.
정상적인 위치가 맞아 보입니다.
조건에 따라 분기타는 부분입니다!
58라인의 괄호 닫기가 62라인 아래쪽에 있어야 오류가 안날 듯 하네요.
- 현재 : ) AND store_cd IN () -- store_cd 앞에 m. 이 없어서 오류( m. 인지? ti. 인지? 알수 없어서 오류)
- 수정 : AND store_cd IN () )
아... 두 테이블 모두 동일한 컬럼명을 가지고 있어서 오류네요.
아마 동일한 결과값을 가지는 쿼리가 되겠지만....
저는 패턴을 동일하게 맞추는 쪽 성향이 커서... ㅎㅎ
테이블 별칭을 추가 하는것을 추천해봅니다.
) 버튼을 클릭하여 작성 하시면 됩니다.