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

SQL 쿼리 튜닝 도움 부탁드립니다

by 비연 [SQL Query] 쿼리 튜닝 [2023.03.22 16:49:55]

안녕하세요.
요즘 들어 부쩍 늘어나는 데이터로 인해 쿼리 속도가 상당히 느려지고 있어 도움 요청 드립니다.

현재 쿼리

SELECT FROM_PLNT_NO
, ITEM_CODE
, SUM(DECODE(ORD_GESTALT, 'P',P_WGT, 0))+NVL(SUM(MISS_P),0) AS P_WGT
, SUM(DECODE(ORD_GESTALT, 'S',S_WGT, 0))+NVL(SUM(MISS_S),0) AS S_WGT
, 0 AS CFM_WGT
, 0 AS RMN_WGT
FROM (
       SELECT A.FROM_PLNT_NO
   , A.ITEM_CODE
   , DECODE(A.ORD_GESTALT, 'P',DECODE(A.USE_YN,'Y',REQ_WGT,0), 0) AS P_WGT
   , DECODE(A.ORD_GESTALT, 'S',DECODE(A.USE_YN,'Y',REQ_WGT,0), 0) AS S_WGT
   , 0 AS CFM_WGT
   , 0 AS RMN_WGT
   , ( SELECT SUM(S.REQ_WGT) FROM WMS.W_TB_DELIVERY_ORDER_MASTER S WHERE S.P_REQ_NO = A.REQ_NO AND S.USE_YN = 'Y' AND A.USE_YN = 'N' AND S.REQ_STATUS = 'C' AND S.ORD_GESTALT = 'S') MISS_S
   , ( SELECT SUM(S.REQ_WGT) FROM WMS.W_TB_DELIVERY_ORDER_MASTER S WHERE S.P_REQ_NO = A.REQ_NO AND S.USE_YN = 'Y' AND A.USE_YN = 'N' AND S.REQ_STATUS = 'C' AND S.ORD_GESTALT = 'P') MISS_P
       , A.ORD_GESTALT
   FROM W_TB_DELIVERY_ORDER_MASTER A
   WHERE P_REQ_NO LIKE 'OMI%'
)
GROUP BY FROM_PLNT_NO,ITEM_CODE

이 쿼리가 도는게 25초 정도 걸리는 쿼리입니다.

안쪽의 SELECT 쿼리는 1초도 걸리지 않는 상황인데, 둘러싸고 있는 SELECT 부분을 보완할수 있는 방법이 없는지 도움 부탁드립니다.

by 마농 [2023.03.22 18:03:00]

동일 테이블을 여러번 사용하네요.
스칼라서브쿼리 부분이 원인 일 듯 한데.
이를 개선하려면 테이블 및 인덱스 구조, 컬럼들 간의 관계 및 특징, 쿼리의 목적, 데이터 건수 등 정보가 필요합니다.
테이블의 PK 는 뭔지? p_req_no 와 req_no 는 어떤 관계인지?
use_yn, req_status, ord_gestalt 항목 등은 어떤 쓰임새 인지?

by 마농 [2023.03.23 00:58:49]

SELECT from_plnt_no
     , item_code
     , NVL(SUM(p_wgt), 0) + NVL(SUM(miss_p), 0) p_wgt
     , NVL(SUM(s_wgt), 0) + NVL(SUM(miss_s), 0) s_wgt
     , 0 cfm_wgt
     , 0 rmn_wgt
  FROM (SELECT a.from_plnt_no
             , a.item_code
             , a.req_no
             , DECODE(a.use_yn || a.ord_gestalt, 'YP', a.req_wgt) p_wgt
             , DECODE(a.use_yn || a.ord_gestalt, 'YS', a.req_wgt) s_wgt
             , SUM(DECODE(s.ord_gestalt, 'P', s.req_wgt) miss_p
             , SUM(DECODE(s.ord_gestalt, 'S', s.req_wgt) miss_s
          FROM w_tb_delivery_order_master a
          LEFT OUTER JOIN w_tb_delivery_order_master s
            ON s.p_req_no = a.req_no
           AND s.use_yn = 'Y'
           AND a.use_yn = 'N'
           AND s.req_status = 'C'
           AND s.ord_gestalt IN ('S', 'P')
         WHERE a.p_req_no LIKE 'OMI%'
         GROUP BY a.from_plnt_no, a.item_code
             , a.req_no, a.use_yn, a.ord_gestalt, a.req_wgt
        )
 GROUP BY from_plnt_no, item_code
;

by 마농 [2023.03.23 01:12:47]

SELECT from_plnt_no
     , item_code
     , NVL(SUM(p_wgt), 0) p_wgt
     , NVL(SUM(s_wgt), 0) s_wgt
     , 0 cfm_wgt
     , 0 rmn_wgt
  FROM (SELECT CONNECT_BY_ROOT(from_plnt_no) from_plnt_no
             , CONNECT_BY_ROOT(item_code   ) item_code
             , DECODE(use_yn || ord_gestalt, 'YP', req_wgt) p_wgt
             , DECODE(use_yn || ord_gestalt, 'YS', req_wgt) s_wgt
          FROM w_tb_delivery_order_master
         START WITH p_req_no LIKE 'OMI%'
         CONNECT BY PRIOR req_no = p_req_no
           AND PRIOR use_yn = 'N'
           AND use_yn = 'Y'
           AND req_status = 'C'
           AND ord_gestalt IN ('S', 'P')
           AND LEVEL = 2
        )
 GROUP BY from_plnt_no, item_code
;

by 비연 [2023.03.23 08:29:32]

마농님 답변 감사드립니다.

w_tb_delivery_order_master 의 pk는 REQ_NO, REQ_ITEM_NO 입니다.

P_REQ_NO는 REQ_NO의 부모키이구요.

그외 use_yn, req_status, ord_gestalt 은 기본 검색조건절에 들어가는 컬럼입니다.

현재 마농님께서 변경해주신 쿼리로 돌리니 속도도 상당히 개선되었고, 속도도 20초이상 단축되는걸 확인하였습니다.

제공해주신 쿼리 분석하여 다시한번 적용해 볼 예정입니다.

정말 감사드립니다.

by 마농 [2023.03.23 09:28:29]

req_no 가 유니크하다는 가정하에 작성한 쿼리인데요.
복합키로 되어 있는 것이 좀 찜찜한데요.
req_no 에 중복값이 있다면? 결과가 좀 잘못될 것 같은 느낌이 듭니다.
원본 쿼리도 마찬가지로 문제가 있을 것 같은 느낌입니다.

by 마농 [2023.03.23 11:18:00]

WITH t AS
(
SELECT 1 req_no, 11 req_item_no, 100 req_wgt, 'Y' use_yn, 0 p_req_no FROM dual
UNION ALL SELECT 1, 12, 200, 'Y', 0 FROM dual
UNION ALL SELECT 2, 11, 100, 'N', 0 FROM dual
UNION ALL SELECT 2, 12, 200, 'N', 0 FROM dual
UNION ALL SELECT 3, 11, 100, 'Y', 2 FROM dual
UNION ALL SELECT 3, 12, 200, 'Y', 2 FROM dual
UNION ALL SELECT 3, 13, 300, 'Y', 2 FROM dual
)
SELECT a.req_no
     , NVL(SUM(a.req_wgt), 0) wgt_a
     , NVL(SUM(s.req_wgt), 0) wgt_s
  FROM t a
  LEFT OUTER JOIN t s
    ON a.req_no = s.p_req_no
   AND a.use_yn = 'N'
   AND s.use_yn = 'Y'
 WHERE a.p_req_no = 0
 GROUP BY a.req_no
;
-- Result --
1 300    0
2 900 1200
-- req_no 중복 발생시 값이 뻥튀기 되는 현상 발생(카티션곱)
-- req_no 2번의 자료는 합계가 300, 600 이 나와야 하는데 900, 1200 이 나옴

by 비연 [2023.03.24 09:33:44]

아..추가 조언 감사드립니다.

REQ_NO는 중복값이 발생할수 없는 상황입니다. ^^

응용 쿼리 다시 한번 감사드립니다.

댓글등록

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

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

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

데이터베이스 질문과 답변