데이터베이스 질문과 답변
안녕하세요.
요즘 들어 부쩍 늘어나는 데이터로 인해 쿼리 속도가 상당히 느려지고 있어 도움 요청 드립니다.
현재 쿼리
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 부분을 보완할수 있는 방법이 없는지 도움 부탁드립니다.
동일 테이블을 여러번 사용하네요.
스칼라서브쿼리 부분이 원인 일 듯 한데.
이를 개선하려면 테이블 및 인덱스 구조, 컬럼들 간의 관계 및 특징, 쿼리의 목적, 데이터 건수 등 정보가 필요합니다.
테이블의 PK 는 뭔지? p_req_no 와 req_no 는 어떤 관계인지?
use_yn, req_status, ord_gestalt 항목 등은 어떤 쓰임새 인지?
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
;
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
;
마농님 답변 감사드립니다.
w_tb_delivery_order_master 의 pk는 REQ_NO, REQ_ITEM_NO 입니다.
P_REQ_NO는 REQ_NO의 부모키이구요.
그외 use_yn, req_status, ord_gestalt 은 기본 검색조건절에 들어가는 컬럼입니다.
현재 마농님께서 변경해주신 쿼리로 돌리니 속도도 상당히 개선되었고, 속도도 20초이상 단축되는걸 확인하였습니다.
제공해주신 쿼리 분석하여 다시한번 적용해 볼 예정입니다.
정말 감사드립니다.
req_no 가 유니크하다는 가정하에 작성한 쿼리인데요.
복합키로 되어 있는 것이 좀 찜찜한데요.
req_no 에 중복값이 있다면? 결과가 좀 잘못될 것 같은 느낌이 듭니다.
원본 쿼리도 마찬가지로 문제가 있을 것 같은 느낌입니다.
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 이 나옴
아..추가 조언 감사드립니다.
REQ_NO는 중복값이 발생할수 없는 상황입니다. ^^
응용 쿼리 다시 한번 감사드립니다.
) 버튼을 클릭하여 작성 하시면 됩니다.