sql優化

dingjiahappy 2010-08-03 05:04:40

這段sql查詢時速度巨慢，請教一下該如何優化，謝謝了！
數據庫為DB2

SELECT

DISTINCT SUBSTR(TIME.EVENTTIME,1,19) AS EVENTDATETIME,

RUN.LOTNO, 

RUN.WO, 

RUN.MATE,

LOT_BRANCHNO.P_LOT_BRANCHNO AS BRANCHNO,

CASE WHEN ENGUSEDNUM.USED!=0 THEN (MAXNO.MAX+ENGUSEDNUM.USED)   ELSE 

MAXNO.MAX END  AS PROD,

MINNUM.MIN AS OKPROD,

(MAXNO.MAX-MINNUM.MIN) AS BPROD,

ENGUSEDNUM.USED AS USEDNUM,



RTRUSED.ENGUSED AS RTR,

RTCUSED.ENGUSED AS RTC,

US4USED.ENGUSED AS US4,

US5USED.ENGUSED AS US5,

US6USED.ENGUSED AS US6,

US7USED.ENGUSED AS US7,

US8USED.ENGUSED AS US8,

US9USED.ENGUSED AS US9,

US11USED.ENGUSED AS US11,

US12USED.ENGUSED AS US12,

MKJUSED.ENGUSED AS MKJ,

TOMUSED.ENGUSED AS TOM,

SUPUSED.ENGUSED AS SUP,

LINGYONG.USED AS NEWUSED,



ROUND(US5USED.ENGUSED*SIZE.SQR_ITEM,3) AS US5USEDSIZE,

ROUND(US6USED.ENGUSED*SIZE.SQR_ITEM,3) AS US6USEDSIZE,

ROUND(US7USED.ENGUSED*SIZE.SQR_ITEM,3) AS US7USEDSIZE,

ROUND(US8USED.ENGUSED*SIZE.SQR_ITEM,3) AS US8USEDSIZE,

ROUND(US9USED.ENGUSED*SIZE.SQR_ITEM,3) AS US9USEDSIZE,

ROUND(US11USED.ENGUSED*SIZE.SQR_ITEM,3) AS US11USEDSIZE,

ROUND(US12USED.ENGUSED*SIZE.SQR_ITEM,3) AS US12USEDSIZE,



ROUND(ENGUSEDNUM.USED*SIZE.SQR_ITEM,3) AS USEDSIZE,

CASE WHEN ENGUSEDNUM.USED>0 THEN  ROUND((MAXNO.MAX+ENGUSEDNUM.USED)*SIZE.SQR_ITEM,3) 

ELSE ROUND(MAXNO.MAX*SIZE.SQR_ITEM,3) END AS PRODSIZE,



CASE WHEN ENGUSEDNUM.USED!=0 THEN ROUND((ENGUSEDNUM.USED*SIZE.SQR_ITEM) /((MAXNO.MAX+ENGUSEDNUM.USED)*SIZE.SQR_ITEM)*100,3) 

ELSE ROUND((ENGUSEDNUM.USED*SIZE.SQR_ITEM) /(MAXNO.MAX*SIZE.SQR_ITEM)*100,3) END AS PRODSIZEUSED,



GCLY.QTY_PROD  AS PROD1,

GCLY.REASONDESC AS REASON 



FROM FHLOTOPERATION  AS RUN 



LEFT JOIN (

SELECT LOTNO,ROUND(SUM(QTY_PROD),1) AS QTY_PROD,REASONDESC FROM FHLOTOPERATION WHERE 

EVENTID='ENGUSED' AND LOTNO NOT LIKE 'X_%' AND REASONDESC NOT LIKE '10%'  AND WO NOT LIKE '%B'

GROUP BY REASONDESC,LOTNO ORDER BY LOTNO)  AS GCLY



ON GCLY.LOTNO=RUN.LOTNO



LEFT JOIN (SELECT DISTINCT LOTNO,P_LOT_BRANCHNO FROM FHLOTOPERATION WHERE P_LOT_BRANCHNO NOT IN (''))  AS LOT_BRANCHNO



ON LOT_BRANCHNO.LOTNO=RUN.LOTNO



LEFT JOIN (SELECT DISTINCT LOTNO,TOOLID FROM FHLOTOPERATION) AS TOOL



ON TOOL.LOTNO=RUN.LOTNO



LEFT JOIN (SELECT LOTNO,ROUND(SUM(QTY_PROD),1) AS ENGUSED FROM FHLOTOPERATION WHERE EVENTID='ENGUSED' AND TOOLID LIKE 'RTR%'  AND LOTNO NOT LIKE 'X_%' GROUP BY LOTNO)  AS RTRUSED



ON RTRUSED.LOTNO=RUN.LOTNO 



LEFT JOIN (SELECT LOTNO,ROUND(SUM(QTY_PROD),1) AS ENGUSED FROM FHLOTOPERATION WHERE EVENTID='ENGUSED' AND TOOLID IN ('RTC8M-09','US20') AND LOTNO NOT LIKE 'X_%' GROUP BY LOTNO)  AS RTCUSED



ON RTCUSED.LOTNO=RUN.LOTNO 



LEFT JOIN (SELECT LOTNO,ROUND(SUM(QTY_PROD),1) AS ENGUSED FROM FHLOTOPERATION WHERE EVENTID='ENGUSED' AND TOOLID LIKE 'CLM8M%'  AND LOTNO NOT LIKE 'X_%' GROUP BY LOTNO)  AS US4USED



ON US4USED.LOTNO=RUN.LOTNO 



LEFT JOIN (SELECT LOTNO,ROUND(SUM(QTY_PROD),1) AS ENGUSED FROM FHLOTOPERATION WHERE EVENTID='ENGUSED' AND TOOLID IN ('PML8M-19','PMS8M-08','PMS8M-07','US5','US6','PML8M-03','US68C','PML8M','PMS8M')  AND LOTNO NOT LIKE 'X_%' AND REASONDESC NOT LIKE '10%' AND WO NOT LIKE '%B' GROUP BY LOTNO)  AS US5USED



ON US5USED.LOTNO=RUN.LOTNO 



LEFT JOIN (SELECT LOTNO,ROUND(SUM(QTY_PROD),1) AS ENGUSED FROM FHLOTOPERATION WHERE EVENTID='ENGUSED' AND TOOLID IN ('US7','US78C','US68C','US68C-01','US68C-02','US68C-03','US68C-04','US68C-05','US68C-06','US68C-07','US68C-08','US68C-09','US68C-10','US68C-11','US68C-12','US68C-13','US68C-14','US68C-15','US68C-16') AND WO NOT LIKE '%B' AND  LOTNO NOT LIKE 'X_%' AND REASONDESC NOT LIKE '10%' GROUP BY LOTNO)  AS US6USED



ON US6USED.LOTNO=RUN.LOTNO 



LEFT JOIN (SELECT LOTNO,ROUND(SUM(QTY_PROD),1) AS ENGUSED FROM FHLOTOPERATION WHERE EVENTID='ENGUSED' AND TOOLID LIKE 'US78C-01%'  AND LOTNO NOT LIKE 'X_%' AND REASONDESC NOT LIKE '10%' AND WO NOT LIKE '%B' GROUP BY LOTNO)  AS US7USED



ON US7USED.LOTNO=RUN.LOTNO 



LEFT JOIN (SELECT LOTNO,ROUND(SUM(QTY_PROD),1) AS ENGUSED FROM FHLOTOPERATION WHERE EVENTID='ENGUSED' AND TOOLID LIKE 'US8%'  AND LOTNO NOT LIKE 'X_%' AND REASONDESC NOT LIKE '10%' AND WO NOT LIKE '%B' GROUP BY LOTNO)  AS US8USED



ON US8USED.LOTNO=RUN.LOTNO 



LEFT JOIN (SELECT LOTNO,ROUND(SUM(QTY_PROD),1) AS ENGUSED FROM FHLOTOPERATION WHERE EVENTID='ENGUSED' AND TOOLID IN ('US9','CLM8P','CLM8P-10','CLM8P-11','CLM8P-44','US11','US118C')  AND LOTNO NOT LIKE 'X_%' AND REASONDESC NOT LIKE '10%' AND WO NOT LIKE '%B' GROUP BY LOTNO)  AS US9USED



ON US9USED.LOTNO=RUN.LOTNO 



LEFT JOIN (SELECT LOTNO,ROUND(SUM(QTY_PROD),1) AS ENGUSED FROM FHLOTOPERATION WHERE EVENTID='ENGUSED' AND TOOLID IN ('US118C-01')  AND LOTNO NOT LIKE 'X_%' AND REASONDESC NOT LIKE '10%' AND WO NOT LIKE '%B' GROUP BY LOTNO)  AS US11USED



ON US11USED.LOTNO=RUN.LOTNO 



LEFT JOIN (SELECT LOTNO,ROUND(SUM(QTY_PROD),1) AS ENGUSED FROM FHLOTOPERATION WHERE EVENTID='ENGUSED' AND TOOLID LIKE 'US12%'  AND LOTNO NOT LIKE 'X_%' AND REASONDESC NOT LIKE '10%' AND WO NOT LIKE '%B' GROUP BY LOTNO)  AS US12USED



ON US12USED.LOTNO=RUN.LOTNO 



LEFT JOIN (SELECT LOTNO,ROUND(SUM(QTY_PROD),1) AS ENGUSED FROM FHLOTOPERATION WHERE EVENTID='ENGUSED' AND TOOLID LIKE 'TOM%'  AND LOTNO NOT LIKE 'X_%' AND REASONDESC NOT LIKE '10%' GROUP BY LOTNO)  AS TOMUSED



ON TOMUSED.LOTNO=RUN.LOTNO 



LEFT JOIN (SELECT LOTNO,ROUND(SUM(QTY_PROD),1) AS ENGUSED FROM FHLOTOPERATION WHERE EVENTID='ENGUSED' AND TOOLID LIKE 'SUP%'  AND LOTNO NOT LIKE 'X_%' AND REASONDESC NOT LIKE '10%' GROUP BY LOTNO)  AS SUPUSED



ON SUPUSED.LOTNO=RUN.LOTNO 



LEFT JOIN (SELECT LOTNO,ROUND(SUM(QTY_PROD),1) AS ENGUSED FROM FHLOTOPERATION WHERE EVENTID='ENGUSED' AND TOOLID LIKE 'MKJ%'  AND LOTNO NOT LIKE 'X_%' AND REASONDESC NOT LIKE '10%' GROUP BY LOTNO)  AS MKJUSED



ON MKJUSED.LOTNO=RUN.LOTNO 







LEFT JOIN (SELECT DISTINCT  LOTNO,USED_QTY_1 AS USED  FROM FHLOTMATEUSG WHERE LOTNO NOT LIKE 'X_%' AND EVENTID='MTL_REQUEST' ) AS LINGYONG



ON LINGYONG.LOTNO=RUN.LOTNO







LEFT JOIN (SELECT LOTNO,MAX(QTY_PROD) AS MAX FROM FHLOTOPERATION GROUP BY LOTNO) AS MAXNUM



ON MAXNUM.LOTNO=RUN.LOTNO



LEFT JOIN (SELECT LOTNO,OKQTY_PROD AS MIN FROM FHLOTOPERATION WHERE EVENTID='BKIN') AS MINNUM



ON MINNUM.LOTNO=RUN.LOTNO



LEFT JOIN (SELECT LOTNO,QTY_PROD AS MAX FROM FHLOTOPERATION WHERE EVENTID='BKIN') AS MAXNO



ON MAXNO.LOTNO=RUN.LOTNO



LEFT JOIN (SELECT LOTNO,MIN(QTY_PROD) AS SPLIT FROM FHLOTOPERATION WHERE EVENTID='SPLIT' GROUP BY LOTNO)  AS SPLITNUM



ON SPLITNUM.LOTNO=RUN.LOTNO



LEFT JOIN (SELECT LOTNO,ROUND(SUM(QTY_PROD),1) AS USED FROM FHLOTOPERATION WHERE EVENTID='ENGUSED' AND REASONDESC NOT LIKE '10%'  AND WO NOT LIKE '%B' GROUP BY LOTNO

)  AS ENGUSEDNUM



ON ENGUSEDNUM.LOTNO=RUN.LOTNO



LEFT JOIN (SELECT  LOTNO,MIN(EVENTDATETIME) AS EVENTTIME FROM FHLOTOPERATION WHERE EVENTID IN ('BKIN') GROUP BY LOTNO)  AS TIME



ON TIME.LOTNO=RUN.LOTNO



LEFT JOIN (SELECT ROUND(SQR_ITEM/1000000,4) AS SQR_ITEM,COD_ITEM FROM CIM_ITEM_V) AS SIZE



ON SIZE.COD_ITEM=RUN.MATE



WHERE 1=1 AND RUN.LOTNO NOT LIKE 'X_%'

AND EVENTTIME>'20100701' AND EVENTTIME<'20100730'