一条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'))