꿈꾸는 개발자, DBA 커뮤니티 구루비

쿼리질문드립니다

by mbj1 [Oracle Tuning] [2025.01.16 12:23:44]

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)

by 마농 [2025.01.16 14:28:52]

괄호, 컴마 등이 짝이 안맞네요.

by mbj1 [2025.01.16 14:44:35]

수정해서 다시올렸습니다!

컬럼이 많아 축소해서 올리느라 콤마가 빠졌습니다

by 우주민 [2025.01.16 14:48:27]

... 수정된 쿼리는 정상적인 쿼리 아닌가요?

by mbj1 [2025.01.16 14:44:53]

쿼리 수정하였습니다!

by 우주민 [2025.01.16 14:46:03]

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;
                

수정해서 올리긴 했는데....

다시 올리주신 쿼리랑 같은 쿼리네요.

by mbj1 [2025.01.16 14:51:14]

우주민님! 답변감사합니다

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번 들어갈 필요가 있느냐에 질문이였습니다.

튜닝이 더 가능하지않을까해서 질문올렸습니다

by 우주민 [2025.01.16 14:54:09]

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 로 변경하면 위와 같은 쿼리가 될듯 합니다.

by mbj1 [2025.01.16 15:04:17]

대댓글이안되서 여기다 답글 올립니다

in절이 문제일까요?

DEL_YN IS NULL OR DEL_YN != 'Y' 이부분도 성능에 많이 문제일까요?

TBL_STORE 테이블만 읽는데도 속도가 많이 느립니다ㅠㅠ

by 우주민 [2025.01.16 15:07:28]

단순하게 쿼리 만으로 파악하기는 힘든 부분이 많습니다.

테이블에 있는 데이터양이나 구성, 인덱스, 실행계획이 어떻게 구성되는지 등등 복합적으로 파악하셔야 할듯 합니다.

단 이번의 경우는 아마 OUTER JOIN 을 INNER JOIN 으로 변경하면서 그래도 조금은 나은 성능이 되지 않을까 추측해봅니다.

by mbj1 [2025.01.16 15:22:11]

답변감사합니다!

by 마농 [2025.01.16 15:10:15]

58라인의 괄호닫기가 62라인쪽으로 내려가야 맞을 듯 하네요.

by 우주민 [2025.01.16 15:15:09]

쿼리 정리중에 테이블 별칭 때문에 저도 그렇게 생각하긴 했는데....

주문메뉴내역 테이블에 적용한 스타일 보니 그냥 복사 해서 붙여넣느라 그렇게 된듯하네요.

정상적인 위치가 맞아 보입니다.

by mbj1 [2025.01.16 15:23:18]

조건에 따라 분기타는 부분입니다!

by 마농 [2025.01.16 16:11:11]

58라인의 괄호 닫기가 62라인 아래쪽에 있어야 오류가 안날 듯 하네요.
- 현재 : ) AND store_cd IN () -- store_cd 앞에 m. 이 없어서 오류( m. 인지? ti. 인지? 알수 없어서 오류)
- 수정 : AND store_cd IN () )

by 우주민 [2025.01.16 16:40:38]

아... 두 테이블 모두 동일한 컬럼명을 가지고 있어서 오류네요.

아마 동일한 결과값을 가지는 쿼리가 되겠지만....

저는 패턴을 동일하게 맞추는 쪽 성향이 커서... ㅎㅎ

테이블 별칭을 추가 하는것을 추천해봅니다.

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 질문과 답변