안녕하세요 ERP 초보 개발자 입니다... 아래와 같은 쿼리를 짜서 특정 날짜 사이에 불출,입고,예약,외주의 변동량을 쿼리로 뽑아내는데는 성공했는데요... 문제는 여러개를 조인 하다보니까... 아무 변동량이 없는 0값만 포함하는 행을 없애려고 하다 보니까 where not을 이용해서 특정 값을 제외 시켜버리니까... where not 이 있으면 쿼리조회시간 50초 where not 을 지우고 조회하면 0.5초 걸리네요 좀더 조회속도를 빠르게 해보고 싶은데 방법을 모르겠네요 조언 부탁드립니다 감사합니다. 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) ;
오토트레이싱 결과
----------------------------------------------------------- | |||||
3739 CPU used by this session | |||||
3741 CPU used when call started | |||||
3778 DB time | |||||
2 calls to get snapshot scn: kcmgss | |||||
42153 calls to kcmgcs | |||||
6079489 consistent gets | |||||
1 consistent gets - examination | |||||
6079489 consistent gets from cache | |||||
6079488 consistent gets from cache (fastpath) | |||||
6037335 no work - consistent read gets | |||||
10 non-idle wait count | |||||
2 opened cursors cumulative | |||||
2 opened cursors current | |||||
2 pinned cursors current | |||||
6079489 session logical reads | |||||
11803 table scans (short tables) | |||||
9 user calls |
WHERE NOT 을 제외했을때의 오토트래이싱 값
----------------------------------------------------------- | ||||
113 CPU used by this session | ||||
113 CPU used when call started | ||||
112 DB time | ||||
2 calls to get snapshot scn: kcmgss | ||||
1253 calls to kcmgcs | ||||
180333 consistent gets | ||||
1 consistent gets - examination | ||||
180333 consistent gets from cache | ||||
180332 consistent gets from cache (fastpath) | ||||
179079 no work - consistent read gets | ||||
10 non-idle wait count | ||||
2 opened cursors cumulative | ||||
2 opened cursors current | ||||
2 pinned cursors current | ||||
180333 session logical reads | ||||
351 table scans (short tables) | ||||
9 user calls |