데이터베이스 질문과 답변
안녕하세요.
NVL 사용시 속도가 너무 안나와서 대체할수 있는 방법이 없을지 문의드립니다.
쿼리는 다음과 같이 이루어집니다.
SELECT FROM_PLNT_NO
, ITEM_CODE
, ROUND(P_WGT) as P_WGT
, ROUND(S_WGT) as S_WGT
, ROUND(CFM_WGT) as CFM_WGT
, ROUND(NVL(S_WGT,0)+CFM_WGT) AS RMN_WGT
FROM (
SELECT FROM_PLNT_NO
, ITEM_CODE
, P_WGT - ( SELECT SUM(REQ_WGT) FROM WMS.W_TB_DELIVERY_ORDER_MASTER S WHERE SHIP_GB ='D' AND ORD_GESTALT = 'P' AND REQ_STATUS = 'C' AND USE_YN = 'Y' AND S.FROM_PLNT_NO = A.FROM_PLNT_NO AND S.ITEM_CODE = A.ITEM_CODE ) AS P_WGT
, S_WGT - ( SELECT SUM(REQ_WGT) FROM WMS.W_TB_DELIVERY_ORDER_MASTER S WHERE SHIP_GB ='D' AND ORD_GESTALT = 'S' AND REQ_STATUS = 'C' AND USE_YN = 'Y' AND S.FROM_PLNT_NO = A.FROM_PLNT_NO AND S.ITEM_CODE = A.ITEM_CODE ) AS S_WGT
, CFM_WGT
, RMN_WGT
FROM (
--생산.공용 승인중량
SELECT FROM_PLNT_NO
, ITEM_CODE
, SUM(P_WGT) AS P_WGT
, SUM(S_WGT) AS S_WGT
, SUM(CFM_WGT) AS CFM_WGT
, SUM(RMN_WGT) AS RMN_WGT
FROM (..............................
위 쿼리중 빨간색 부분이 NULL 일 확률이 있는데 저 부분을 NVL을 써서 NULL 이면 0 으로 셋팅하면 계산값은 잘 나오는데 속도가 너무 느려지는게 문제입니다.
( SELECT SUM(REQ_WGT) FROM WMS.W_TB_DELIVERY_ORDER_MASTER S WHERE SHIP_GB ='D' AND ORD_GESTALT = 'P' AND REQ_STATUS = 'C' AND USE_YN = 'Y' AND S.FROM_PLNT_NO = A.FROM_PLNT_NO AND S.ITEM_CODE = A.ITEM_CODE ) 적용시 3초대
NVL(( SELECT SUM(REQ_WGT) FROM WMS.W_TB_DELIVERY_ORDER_MASTER S WHERE SHIP_GB ='D' AND ORD_GESTALT = 'P' AND REQ_STATUS = 'C' AND USE_YN = 'Y' AND S.FROM_PLNT_NO = A.FROM_PLNT_NO AND S.ITEM_CODE = A.ITEM_CODE ),0) 적용시 30초대
NVL을 안쓰고 NULL 일때 값이 0 을 강제로 셋팅할수 있는 방법이 없을지 문의드립니다.
1. NVL 을 서브쿼리 안으로 넣어보면 어떨런지? (SELECT NVL(SUM(REQ_WGT), 0) ...
2. 전체 쿼리가 복잡하네요. 전체 쿼리 개선의 여지는 없는지?
- 전체 쿼리를 올려주실 수 있는지?
마농님 안녕하세요.
말씀하신데로 서브쿼리에도 넣어보았습니다만 속도가 개선되지는 않네요.
전체쿼리 올려봅니다. 도움 부탁드립니다.
--------------------------------------전체 쿼리 ----------------------------------------------------
SELECT FROM_PLNT_NO
, ITEM_CODE
, ROUND(P_WGT) as P_WGT
, ROUND(S_WGT) as S_WGT
, ROUND(CFM_WGT) as CFM_WGT
, ROUND(S_WGT+CFM_WGT) AS RMN_WGT
FROM (
SELECT FROM_PLNT_NO
, ITEM_CODE
, P_WGT - ( SELECT NVL(SUM(REQ_WGT),0) FROM WMS.W_TB_DELIVERY_ORDER_MASTER S WHERE SHIP_GB ='D' AND ORD_GESTALT = 'P' AND REQ_STATUS = 'C' AND USE_YN = 'Y' AND S.FROM_PLNT_NO = A.FROM_PLNT_NO AND S.ITEM_CODE = A.ITEM_CODE ) AS P_WGT
, S_WGT - ( SELECT NVL(SUM(REQ_WGT),0) FROM WMS.W_TB_DELIVERY_ORDER_MASTER S WHERE SHIP_GB ='D' AND ORD_GESTALT = 'S' AND REQ_STATUS = 'C' AND USE_YN = 'Y' AND S.FROM_PLNT_NO = A.FROM_PLNT_NO AND S.ITEM_CODE = A.ITEM_CODE ) AS S_WGT
, CFM_WGT
, RMN_WGT
FROM (
--생산.공용 승인중량
SELECT FROM_PLNT_NO
, ITEM_CODE
, SUM(P_WGT) AS P_WGT
, SUM(S_WGT) AS S_WGT
, SUM(CFM_WGT) AS CFM_WGT
, SUM(RMN_WGT) AS RMN_WGT
FROM (
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 wms.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
UNION
--공용요청잔량
SELECT WERKS
, MATNR
, 0 AS P_WGT
, 0 AS S_WGT
, SUM(REQ_WGT) AS CFM_WGT
, 0 AS RMN_WGT
FROM W_TB_WOS_ORDER_MASTER
WHERE order_gestalt = 'S' AND STATUS = 'D'
GROUP BY WERKS,MATNR
) GROUP BY FROM_PLNT_NO
, ITEM_CODE
ORDER BY FROM_PLNT_NO
, ITEM_CODE
) A
) M
1) 입력파라미터로 받아서 null인 경우 다른 쿼리를 타도록 분기처리하기
1. UNION -> UNION ALL
2. 스칼라 서브쿼리 -> 아우터 조인
3. 중간 과정 미사용 항목 제거 -> rmn_wgt
SELECT from_plnt_no
, item_code
, ROUND(p_wgt) AS p_wgt
, ROUND(s_wgt) AS s_wgt
, ROUND(cfm_wgt) AS cfm_wgt
, ROUND(s_wgt + cfm_wgt) AS rmn_wgt
FROM (SELECT a.from_plnt_no
, a.item_code
, a.p_wgt - NVL(SUM(DECODE(s.ord_gestalt, 'P', s.req_wgt)), 0) AS p_wgt
, a.s_wgt - NVL(SUM(DECODE(s.ord_gestalt, 'S', s.req_wgt)), 0) AS s_wgt
, a.cfm_wgt
FROM (--생산.공용 승인중량
SELECT from_plnt_no
, item_code
, SUM(p_wgt) AS p_wgt
, SUM(s_wgt) AS s_wgt
, SUM(cfm_wgt) AS cfm_wgt
FROM (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
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 wms.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
UNION ALL
--공용요청잔량
SELECT werks
, matnr
, 0 AS p_wgt
, 0 AS s_wgt
, SUM(req_wgt) AS cfm_wgt
FROM w_tb_wos_order_master
WHERE order_gestalt = 'S'
AND status = 'D'
GROUP BY werks, matnr
)
GROUP BY from_plnt_no, item_code
) a
LEFT OUTER JOIN wms.w_tb_delivery_order_master s
ON s.ship_gb = 'D'
AND s.req_status = 'C'
AND s.use_yn = 'Y'
AND s.ord_gestalt IN ('P', 'S')
AND s.from_plnt_no = a.from_plnt_no
AND s.item_code = a.item_code
GROUP BY a.from_plnt_no, a.item_code
, a.p_wgt, a.s_wgt, a.cfm_wgt
) m
ORDER BY from_plnt_no, item_code
;
마농님 많은 공부가 되고 있습니다. 정말 감사드립니다.
다른 케이스인데, 심플한 쿼리인데도 이 부분도 속도가 안나오는 이유는 무엇인지 조언 부탁드립니다.
유사 케이스라 생각을 했는데, 이건 또 다른 방향으로의 개선이 필요한 것인지요..
SELECT A.req_no AS vbelv --오더번호(출하요청)
,A.req_item_no AS posnv --싱위오더번호아이템 순번
,A.p_req_no --오더번호
,sap_order_no AS vbeln --재고오더
,sap_order_item_no AS posnr --품목번호
,To_char(To_date(A.ord_dt), 'YYYY-MM-DD') AS ORD_DT --오더입력일
,'R' AS status --진행상태
,W_fn_code_filter_nm('MC1102', 1, A.req_status, NULL) AS REQ_STATUS_NM
,'R' AS gi_status --진행상태
,A.steel_kind AS skind --강종
,W_fn_code_filter_nm('SKIND', 1, A.steel_kind, NULL) AS skind_nm --강종명
,A.steel_standard AS sstan --규격
,A.steel_length AS slength --길이
,A.ton_wgt --결속구분
,W_fn_code_filter_nm('MC1101', 1, A.ton_wgt, 'KO') AS TON_WGT_NM --결속구분명
,from_plnt_no AS WERKS_CD --플랜트
,rmn_qty - (SELECT Nvl(SUM(req_qty), 0)
FROM (SELECT p_req_no,
req_qty
FROM sales.w_tb_delivery_order_master ST
WHERE ST.p_req_no = A.req_no
AND req_status = 'C'
AND ST.cust_gb = CASE
WHEN Length(temp1) > 13 THEN
'70'
ELSE ST.cust_gb
END
AND use_yn = 'Y')) AS RMN_QTY
,A.item_type
FROM w_tb_wos_order_master M inner join sales.w_tb_delivery_order_master a
ON M.order_master_id = A.p_req_no
left outer join w_tb_wos_customer_delivery B ON ( A.arrive_code = B.seq )
left outer join w_vw_sy_customer_master C ON ( A.cust_no = C.kunag AND A.site_no = C.kunwe
AND A.comp_cd = C.company_cd )
left outer join w_vw_sy_customer_master C2 ON ( A.reg_user = C2.kunwe AND A.comp_cd = C2.company_cd )
left outer join w_tb_sy_member_master E ON ( A.reg_user = E.member_id )
WHERE A.rmn_qty > 0
AND A.p_req_no LIKE 'OMI%'
AND A.use_yn = 'Y'
AND A.dlv_req_dt BETWEEN '20230702' AND '20230708'
ORDER BY A.req_no DESC
제일 좋은 답변은 쿼리의 실행계획을 보여주시면 가능할 것 같습니다만..
a 테이블이 드라이빙 테이블로 먼저 조회하는 것으로 보이는데
a 테이블의 데이터를 조화할 때마다 , ST ( a 와 같은 테이블 맞죠?) 에서 다시 읽어오기 때문에
느려지는 것으로 보입니다.
a 테이블을 조회하는 컬럼에서 인덱스 의 성능(손익분기점) 이 안 좋다면
full scan 을 해서 ( 조회컬럼의 조건으로 보아 안 좋을 가능성 많음. 만약 좋다면 인덱스 스캔을 하고).
a 테이블로 출력하는 컬럼기준으로 group by 하고, st 테이블에서 where 에 해당하는 부분을
sum (case when st.req_status = 'C' and ... then req_qty else null end ) 로 처리하는 하여여조인을 하는 게 어떻까 합니다.
안녕하세요
좀 이해가 안됩니다. groupby 를 해야할 이유가 없는 레코드들입니다.
말씀하신 ST 테이블(a 와 같은 테이블은 맞습니다 )
즉 전체 데이터의 합계를 가져와서 확정처리한 수량을 뺀 나머지를 계산하는 부분이라서요.
부모레코드(p_req_No) 및에 자식 레코드(req_no) 방식이라 저렇게 표현을 했던건데 저게 속도를 잡고 있는지는 몰랐습니다.
조언 감사드립니다.
) 버튼을 클릭하여 작성 하시면 됩니다.