========================고치기전 쿼리는 3초나 걸렸씁니다.=================
SELECT SA4.ORDER_NO,
SA4.DOC_NO, SA1.AS_NO,
SA1.AS_KOR_NM,
SA1.ITEM_STATE ,
SA1.SPEC,
SA1.ACP_DT,
SA1.QTY,
SA1.COST,
SA1.BOOK_AMT,
SA1.SETUP_LOC,
SA4.ORDER_DEPT_CD,
CM1.DPT_NM_KOR,
SA4.ORDER_EMP_NO,
(SELECT NM FROM SHM010 WHERE MEMBER_NO = SA4.ORDER_EMP_NO) AS ORDER_EMP_NM,
SA4.ORDER_DT,
SA4.RSN,
SA4.REMARK,
SA4.EXP_DT,
SA4.CHG_DIV
FROM SAS004 SA4, SAS001 SA1, COM001 CM1,
(SELECT COM_CD FROM COM003 WHERE COM_CD_DIV = 'SAS001' AND COM_CD <> '$' AND DUMMY_01 = '폐기') CM3
WHERE SA4.AS_NO = SA1.AS_NO
AND SA4.ORDER_DEPT_CD = CM1.DPT_CD
AND SA4.CHG_DIV = CM3.COM_CD
AND NVL(SA1.MNG_DEPT_CD, ' ') LIKE ''||'%'
AND NVL(SA1.SETUP_LOC, ' ') LIKE ''||'%'
AND NVL(SA1.ACCT_CD, ' ') LIKE ''||'%'
AND SUBSTR(NVL(ITEM_CD,' '), 0,1) LIKE ''||'%'
AND SUBSTR(NVL(ITEM_CD,' '), 0,3) LIKE ''||'%'
AND NVL(SA1.ITEM_STATE, ' ') LIKE ''||'%'
ORDER BY SA4.AS_NO
==========================고친후의 쿼리는==============================
SELECT SA4.ORDER_NO,
SA4.DOC_NO, SA1.AS_NO,
SA1.AS_KOR_NM,
SA1.ITEM_STATE ,
SA1.SPEC,
SA1.ACP_DT,
SA1.QTY,
SA1.COST,
SA1.BOOK_AMT,
SA1.SETUP_LOC,
SA4.ORDER_DEPT_CD,
CM1.DPT_NM_KOR,
SA4.ORDER_EMP_NO,
(SELECT NM FROM SHM010 WHERE MEMBER_NO = SA4.ORDER_EMP_NO) AS ORDER_EMP_NM,
SA4.ORDER_DT,
SA4.RSN,
SA4.REMARK,
SA4.EXP_DT,
SA4.CHG_DIV
FROM SAS001 SA1, SAS004 SA4, COM001 CM1,
(SELECT COM_CD FROM COM003 WHERE COM_CD_DIV = 'SAS001' AND COM_CD <> '$' AND DUMMY_01 = '폐기') CM3
WHERE SA4.AS_NO = SA1.AS_NO
AND SA4.ORDER_DEPT_CD = CM1.DPT_CD
AND SA4.CHG_DIV = CM3.COM_CD
AND NVL(SA1.MNG_DEPT_CD, ' ') LIKE ''||'%'
AND NVL(SA1.SETUP_LOC, ' ') LIKE ''||'%'
AND NVL(SA1.ACCT_CD, ' ') LIKE ''||'%'
AND SUBSTR(NVL(ITEM_CD,' '), 0,1) LIKE ''||'%'
AND SUBSTR(NVL(ITEM_CD,' '), 0,3) LIKE ''||'%'
AND NVL(SA1.ITEM_STATE, ' ') LIKE ''||'%'
ORDER BY SA4.AS_NO
바로 나왔는데 여기서 의문은 고친게 단지 FROM 절의 SAS001 과 SAS004
위치만 바꾼거거든요!!
어째서 속도차이가 엄청난거져?? 밑에있는 쿼리는 바로나왔씁니다.
위에껀 3초나 걸리고~~
(SAS001은 9만건있고 SAS004 는 1건있습니다. COM001은 800건있고요)
그리고 WHERE절에서 오라클이 검색하는순서가 밑에 조건문부터인가요
위쪽의 조인부분부터인가요??
|