一条SQL语句的优化,在线等(急),多谢各位了.
SELECT A.WAREHOUSECD WAREHOUSECD,
A.COMPANYCD COMPANYCD,
A.SHIPDATE SHIPDATE,
A.DOCUMENTNUMBER DOCUMENTNUMBERA,
A.DELIVNAME DELIVNAME,
A.SSZIPCODE SSZIPCODE,
(SELECT T.TRANSAMT
FROM EDIFREINVRATEMASTERRDC T
WHERE T.BUSI = TRIM(A.COMPANYCD) AND
T.RATEYY = TO_CHAR(A.SHIPDATE, 'YYYY ') AND
T.SSZIPCODE = TRIM(A.SSZIPCODE) AND ROWNUM = 1) TRANSAMT,
A.NOTES NOTES,
A.SOTYPE SOTYPE,
DECODE(B.URGENT, 'Y ', B.URGENT, 'N ') URGENT,
B.LIACODE LIABILITYCD,
B.REACODE REASONCD,
B.MATERIAL ITEMCD,
B.ACTQUANTITY ACTQUANTITY,
B.ACCQUANTITY ACCQUANTITY,
FX_GETTOTMEASUREMENT(B.MESSAGERECEIVERIDENTIFIER,
B.MATERIAL,
B.ACTQUANTITY) CBM,
FX_GETTOTMEASUREMENT(B.MESSAGERECEIVERIDENTIFIER,
B.MATERIAL,
B.ACCQUANTITY) ACC_CBM,
Z.BASE_AMT BASE_AMT,
Z.BASE_AMT /
nvl((select count(*)
FROM EDIPODDLYRDC W, EDIPODDLYMATLISTRDC X
WHERE W.MESSAGENUMBER = X.MESSAGENUMBER AND W.ACTFLG = '1 ' AND
W.STS = 900 and W.RDREFERENCENUMBER = A.otherrefno1),
1) as avg_baseamt,
Z.ADD_AMT ADD_AMT,
MFWAREHOUSE.SNAME mfwarehouse_sname,
FX_GETCOMPANYNAME(A.COMPANYCD) mfcompany_sname,
B.lot1 lot1,
z.Documentdate Documentdate,
Z.DOCUMENTNUMBER DOCUMENTNUMBERZ
FROM TRSO A,
EDIOUTDLYRDC D,
V_EDIFRINVAMT Z,
(SELECT W.MESSAGERECEIVERIDENTIFIER,
W.RDREFERENCENUMBER,
W.URGENT,
W.LIACODE,
W.REACODE,
X.MATERIAL,
X.ACTQUANTITY,
X.ACCQUANTITY,
X.NAME lot1
FROM EDIPODDLYRDC W, EDIPODDLYMATLISTRDC X
WHERE W.MESSAGENUMBER = X.MESSAGENUMBER AND W.ACTFLG = '1 ' AND
W.STS = 900) B,
MFWAREHOUSE
WHERE A.otherrefno1 = D.DOCUMENTNUMBER AND
A.otherrefno1 = B.RDREFERENCENUMBER AND
A.WAREHOUSECD = MFWAREHOUSE.WAREHOUSECD AND
A.otherrefno1 = Z.SAMSUNGREFERENCENUMBER AND Z.GUBUN = 'FR ' AND
A.ACTFLG = '1 ' AND A.STS = 900 AND D.ACTFLG = '1 ' AND D.STS = 900 AND
A.SOTYPE IN ( 'NOR ', 'NOF ', 'SMP ', 'TEI ') AND
A.WAREHOUSECD IN ( 'SH001 ', 'BJ001 ') AND
(a.warehousecd = 'BJ001 ' AND
a.shipdate > = to_date( '2007/08/11 00:00 ', 'YYYY/MM/DD HH24:MI ') AND
a.shipdate <= to_date( '2007/08/21 00:00 ', 'YYYY/MM/DD HH24:MI ') AND
to_date(Z.DOCUMENTDATE, 'YYYYMMDD ') > =
to_date( '2007/08/11 00:00 ', 'YYYY/MM/DD HH24:MI ') AND
to_date(Z.DOCUMENTDATE, 'YYYYMMDD ') <=
to_date( '2007/08/21 00:00 ', 'YYYY/MM/DD HH24:MI '))
[解决办法]
使用的子查询太多，可以进行分撤查询，还有在查询时不要对字段进行计算
,如：
to_date(Z.DOCUMENTDATE, 'YYYYMMDD ') <=
to_date( '2007/08/21 00:00 ', 'YYYY/MM/DD HH24:MI '))
改为
Z.DOCUMENTDATE <=timestamp '2007-08-21 00:00:00 '
[解决办法]
这个sql语句跑了多久？好像一般复杂的报表语句都跑得不快的 ...
[解决办法]
晕,10天的数据跑3-5分钟.
执行一下分析计划在看看..
