데이터베이스 질문과 답변
where not 조회속도 향상 조언 부탁드립니다.
0
3
1,334
by 치비 [2019.01.21 15:11:21]
반갑습니다.
이번에 특정기간사이 품명별 외주량,출하량,입고량 등의 내역을 출력하는 쿼리를 만들게 되었는데요.
일자별 정보를 출력해야 하므로 울며 겨자먹기로 달력 PP001C 테이블과 아이템 정보를 표시하는 GL001M 테이블을 크로스 조인 하였는데요.
여기서 문제인게 외주량,출하량,입고량이 0인 경우는 where not 절로 제외 시켜버리니까 조회 하는 시간이 어마어마 하게 증가해버리더군요
여기서 ORDER BY 까지 붙여버리니까 SQL에서 뻗어버리는것 같아서... 크로스 조인시 공통키워드가 없는경우 분류방법에 대해서
형님들의 조언을 얻고자 합니다.
아래는 SQL 구문 입니다.
SELECT 0 ID, GL001M.OCCR_DATE,
GL001M.ITEM_CODE,
GL001M.ITEM_DESC,
GL001M.INV_QTY_YESTERDAY + GL001M.ACPT_QTY_TODAY - GL001M.ISS_QTY_TODAY + GL001M.AMND_QTY_TODAY INV_QTY_TODAY,
GL001M.INV_QTY_YESTERDAY,
GL001M.ACPT_QTY_TODAY,
GL001M.ISS_QTY_TODAY,
GL001M.AMND_QTY_TODAY,
GL001M.INV_QTY,
GL001M.AWAT_QTY,
GL001M.RSV_QTY,
GL001M.ISS_QTY,
GL001M.ACPT_QTY,
GL001M.MAJ_GRP_CODE
FROM (SELECT PP001C.OCCR_DATE,
NVL((SELECT GL010M.ST_QTY FROM HM.GL010M
WHERE GL010M.STTL_MNTH = SUBSTR(PP001C.OCCR_DATE,1,6)
AND GL010M.MAJ_ORD_TYPE = DECODE(GL001M.PRCH_GUBUN,'O','D','I','D',GL001M.PRCH_GUBUN)
AND GL010M.ITEM_CODE = GL001M.ITEM_CODE),0)
+
NVL((SELECT SUM(ACPT_QTY) ACPT_QTY FROM HM.GL011M
WHERE GL011M.STTL_MNTH = SUBSTR(PP001C.OCCR_DATE,1,6)
AND GL011M.MAJ_ORD_TYPE = DECODE(GL001M.PRCH_GUBUN,'O','D','I','D',GL001M.PRCH_GUBUN)
AND GL011M.ITEM_CODE = GL001M.ITEM_CODE
AND GL011M.CRNT_DATE BETWEEN SUBSTR(PP001C.OCCR_DATE,1,6)||'01' AND to_char(to_date(PP001C.OCCR_DATE,'yyyymmdd')-1,'yyyymmdd') ),0)
-
NVL((SELECT SUM(ISS_QTY) ISS_QTY FROM HM.GL012M
WHERE GL012M.STTL_MNTH = SUBSTR(PP001C.OCCR_DATE,1,6)
AND GL012M.MAJ_ORD_TYPE = DECODE(GL001M.PRCH_GUBUN,'O','D','I','D',GL001M.PRCH_GUBUN)
AND GL012M.ITEM_CODE = GL001M.ITEM_CODE
AND GL012M.CRNT_DATE BETWEEN SUBSTR(PP001C.OCCR_DATE,1,6)||'01' AND to_char(to_date(PP001C.OCCR_DATE,'yyyymmdd')-1,'yyyymmdd') ),0)
+
NVL((SELECT NVL(SUM(INV_AMND_QTY),0) INV_AMND_QTY FROM HM.GL013M
WHERE GL013M.STTL_MNTH = SUBSTR(PP001C.OCCR_DATE,1,6)
AND GL013M.MAJ_ORD_TYPE = DECODE(GL001M.PRCH_GUBUN,'O','D','I','D',GL001M.PRCH_GUBUN)
AND GL013M.ITEM_CODE = GL001M.ITEM_CODE
AND GL013M.CRNT_DATE BETWEEN SUBSTR(PP001C.OCCR_DATE,1,6)||'01' AND to_char(to_date(PP001C.OCCR_DATE,'yyyymmdd')-1,'yyyymmdd')),0)
inv_qty_yesterday,
NVL((SELECT SUM(ACPT_QTY) ACPT_QTY FROM HM.GL011M
WHERE GL011M.STTL_MNTH = SUBSTR(PP001C.OCCR_DATE,1,6)
AND GL011M.MAJ_ORD_TYPE = DECODE(GL001M.PRCH_GUBUN,'O','D','I','D',GL001M.PRCH_GUBUN)
AND GL011M.ITEM_CODE = GL001M.ITEM_CODE
AND GL011M.CRNT_DATE = PP001C.OCCR_DATE),0)
acpt_qty_today,
NVL((SELECT SUM(ISS_QTY) ISS_QTY FROM HM.GL012M
WHERE GL012M.STTL_MNTH = SUBSTR(PP001C.OCCR_DATE,1,6)
AND GL012M.MAJ_ORD_TYPE = DECODE(GL001M.PRCH_GUBUN,'O','D','I','D',GL001M.PRCH_GUBUN)
AND GL012M.ITEM_CODE = GL001M.ITEM_CODE
AND GL012M.CRNT_DATE = PP001C.OCCR_DATE),0)
iss_qty_today,
NVL((SELECT NVL(SUM(INV_AMND_QTY),0) INV_AMND_QTY FROM HM.GL013M
WHERE GL013M.STTL_MNTH = SUBSTR(PP001C.OCCR_DATE,1,6)
AND GL013M.MAJ_ORD_TYPE = DECODE(GL001M.PRCH_GUBUN,'O','D','I','D',GL001M.PRCH_GUBUN)
AND GL013M.ITEM_CODE = GL001M.ITEM_CODE
AND GL013M.CRNT_DATE = PP001C.OCCR_DATE),0)
amnd_qty_today ,
GL001M.INV_QTY,
GL001M.AWAT_QTY,
GL001M.RSV_QTY,
GL001M.ITEM_CODE,
GL001M.ITEM_DESC,
NVL((SELECT MM036M.ISS_QTY
FROM HM.MM036M
WHERE MM036M.ISS_DATE = PP001C.OCCR_DATE
AND MM036M.ITEM_CODE = GL001M.ITEM_CODE
AND EXISTS
(SELECT 1 FROM HM.PP031M,HM.GL001M
WHERE PP031M.ITEM_CODE = MM036M.ITEM_CODE
AND PP031M.PRE_ITEM_CODE = GL001M.ITEM_CODE
AND GL001M.PRCH_GUBUN = 'O') ),0)
ISS_QTY,
NVL((SELECT MM036M.ACPT_QTY
FROM HM.MM036M
WHERE MM036M.ACPT_DATE = PP001C.OCCR_DATE
AND MM036M.ITEM_CODE = GL001M.ITEM_CODE
AND EXISTS
(SELECT 1 FROM HM.PP031M,HM.GL001M
WHERE PP031M.ITEM_CODE = MM036M.ITEM_CODE
AND PP031M.PRE_ITEM_CODE = GL001M.ITEM_CODE
AND GL001M.PRCH_GUBUN = 'O') ),0)
ACPT_QTY,
GL001M.MAJ_GRP_CODE
FROM HM.GL001M(아이템정보),
HM.PP001C(공장달렭)
WHERE PP001C.OCCR_DATE BETWEEN '20181111' AND '20181126' AND GL001M.MAJ_GRP_CODE = '1' ) GL001M
WHERE NOT(GL001M.ACPT_QTY_TODAY(입고량) = 0
AND GL001M.ISS_QTY_TODAY(출하량) = 0
AND GL001M.AMND_QTY_TODAY(재고량) = 0
AND GL001M.ISS_QTY(외주량) = 0
AND GL001M.ACPT_QTY(외주입고량) = 0) ;
by 우리집아찌
[2019.01.21 15:18:34]
서브쿼리가 너무 많아요.
OUTER JOIN으로 바꾸어보세요.
by 마농
[2019.01.24 15:07:44]
SELECT 0 id
, a.occr_date
, a.item_code
, a.item_desc
, NVL(b.st_qty, 0)
+ NVL(e.acpt_qty_yesterday, 0)
- NVL(f.iss_qty_yesterday , 0)
+ NVL(g.amnd_qty_yesterday, 0)
+ NVL(e.acpt_qty_today , 0)
- NVL(f.iss_qty_today , 0)
+ NVL(g.amnd_qty_today , 0) AS inv_qty_today
, NVL(b.st_qty, 0)
+ NVL(e.acpt_qty_yesterday, 0)
- NVL(f.iss_qty_yesterday , 0)
+ NVL(g.amnd_qty_yesterday, 0) AS inv_qty_yesterday
, NVL(e.acpt_qty_today , 0) AS acpt_qty_today
, NVL(f.iss_qty_today , 0) AS iss_qty_today
, NVL(g.amnd_qty_today , 0) AS amnd_qty_today
, a.inv_qty
, a.awat_qty
, a.rsv_qty
, NVL(c.iss_qty , 0) AS c.iss_qty
, NVL(d.acpt_qty , 0) AS d.acpt_qty
, a.maj_grp_code
FROM (SELECT SUBSTR(b.occr_date, 1, 6) AS sttl_mnth
, b.occr_date
, a.item_code
, a.item_desc
, a.inv_qty
, a.awat_qty
, a.rsv_qty
, a.maj_grp_code
, DECODE(a.prch_gubun, 'O', 'D', 'I', 'D', a.prch_gubun) AS maj_ord_type
FROM hm.gl001m a
, hm.pp001c b
WHERE b.occr_date BETWEEN '20181111' AND '20181126'
AND a.maj_grp_code = '1'
) a
LEFT OUTER JOIN hm.gl010m b
ON a.sttl_mnth = b.sttl_mnth
AND a.maj_ord_type = b.maj_ord_type
AND a.item_code = b.item_code
LEFT OUTER JOIN hm.mm036m c
ON a.occr_date = c.iss_date
AND a.item_code = c.item_code
AND EXISTS (SELECT 1
FROM hm.pp031m x
, hm.gl001m y
WHERE x.item_code = c.item_code
AND x.pre_item_code = y.item_code
AND y.prch_gubun = 'O'
)
LEFT OUTER JOIN hm.mm036m d
ON a.occr_date = d.acpt_date
AND a.item_code = d.item_code
AND EXISTS (SELECT 1
FROM hm.pp031m x
, hm.gl001m y
WHERE x.item_code = d.item_code
AND x.pre_item_code = y.item_code
AND y.prch_gubun = 'O'
)
LEFT OUTER JOIN
(SELECT sttl_mnth, maj_ord_type, item_code, crnt_date
, SUM(acpt_qty) acpt_qty_today
, SUM(SUM(acpt_qty)) OVER(
PARTITION BY sttl_mnth, maj_ord_type, item_code, SUBSTR(crnt_date, 1, 6)
ORDER BY crnt_date)
- SUM(acpt_qty) acpt_qty_yesterday
FROM gl011m
WHERE sttl_mnth BETWEEN SUBSTR('20181111', 1, 6) AND SUBSTR('20181126', 1, 6)
AND crnt_date BETWEEN SUBSTR('20181111', 1, 6) AND '20181126'
GROUP BY sttl_mnth, maj_ord_type, item_code, crnt_date
) e
ON a.sttl_mnth = e.sttl_mnth
AND a.maj_ord_type = e.maj_ord_type
AND a.item_code = e.item_code
AND a.occr_date = e.crnt_date
LEFT OUTER JOIN
(SELECT sttl_mnth, maj_ord_type, item_code, crnt_date
, SUM(iss_qty) iss_qty_today
, SUM(SUM(iss_qty)) OVER(
PARTITION BY sttl_mnth, maj_ord_type, item_code, SUBSTR(crnt_date, 1, 6)
ORDER BY crnt_date)
- SUM(iss_qty) iss_qty_yesterday
FROM gl012m
WHERE sttl_mnth BETWEEN SUBSTR('20181111', 1, 6) AND SUBSTR('20181126', 1, 6)
AND crnt_date BETWEEN SUBSTR('20181111', 1, 6) AND '20181126'
GROUP BY sttl_mnth, maj_ord_type, item_code, crnt_date
) f
ON a.sttl_mnth = f.sttl_mnth
AND a.maj_ord_type = f.maj_ord_type
AND a.item_code = f.item_code
AND a.occr_date = f.crnt_date
LEFT OUTER JOIN
(SELECT sttl_mnth, maj_ord_type, item_code, crnt_date
, SUM(inv_amnd_qty) amnd_qty_today
, SUM(SUM(inv_amnd_qty)) OVER(
PARTITION BY sttl_mnth, maj_ord_type, item_code, SUBSTR(crnt_date, 1, 6)
ORDER BY crnt_date)
- SUM(inv_amnd_qty) amnd_qty_yesterday
FROM gl013m
WHERE sttl_mnth BETWEEN SUBSTR('20181111', 1, 6) AND SUBSTR('20181126', 1, 6)
AND crnt_date BETWEEN SUBSTR('20181111', 1, 6) AND '20181126'
GROUP BY sttl_mnth, maj_ord_type, item_code, crnt_date
) g
ON a.sttl_mnth = g.sttl_mnth
AND a.maj_ord_type = g.maj_ord_type
AND a.item_code = g.item_code
AND a.occr_date = g.crnt_date
WHERE 1=1
AND 0 != ANY ( NVL(e.acpt_qty_today, 0)
, NVL(f.iss_qty_today , 0)
, NVL(g.amnd_qty_today, 0)
, NVL(c.iss_qty , 0)
, NVL(d.acpt_qty , 0)
)
;
by 치비
[2019.04.17 11:28:03]
존경합니다!
질문한 쿼리정도 DATA만 뽑아냈다고 으쓱거렸던
제자신이 부끄러울 정도네요... 정말 감사드리며 정진하겠습니다
) 버튼을 클릭하여 작성 하시면 됩니다.