SELECT A.AC_TWNO,
A.AC_TWSEQ,
B.OD_NO,
B.SH_NO,
B.SH_ARITCLENO,
A.EL_NO,
A.EL_CNAME,
A.EL_UNIT,
A.MT_LOTNO,
A.MT_AREANO,
A.TW_NO,
A.TW_SEQ,
A.QA_QTY,
to_char(A.AC_DATE, 'yyyy/mm/dd') AC_DATE,
A.AC_QTY,
A.BK_QTY,
A.AC_PRICE,
A.AC_AMT,
A.AC_CFM,
A.EL_NETU,
A.AC_INVONO,
A.PB_ODID,
A.PB_RATE,
C.SU_NA,
D.PB_FANA,
E.DP_NAME,
A.CU_SALE,
F.MT_STOCKNA
from (select MC12.AC_DATE,
MC12.SU_NO,
MC12.SU_PONO,
MC12.PB_FACTNO,
MC12.AC_DEPT,
MC12.PB_ODID,
MC12.PB_RATE,
MC12.AC_CFM,
MC12.CU_SALE,
MC12.AC_INVONO,
MC13.AC_TWNO,
MC13.AC_TWSEQ,
MC13.EL_NO,
MC13.QA_QTY,
MC13.EL_UNIT,
MC13.MT_LOTNO,
MC13.MT_STOCKNO,
MC13.MT_AREANO,
MC13.TW_NO,
MC13.TW_SEQ,
MC13.AC_QTY,
MC13.BK_QTY,
MC13.AC_PRICE,
MC13.AC_AMT,
MC13.EL_NETU,
EL00.EL_CNAME
from DSMC12 MC12, DSMC13 MC13, DSEL00 EL00
where MC12.AC_TWNO = MC13.AC_TWNO
and MC13.EL_NO = EL00.EL_NO(+)
and MC12.AC_TWCL = '2') A,
(select MC08.OD_NO, MC08.TW_NO, OD00.SH_NO, OD00.SH_ARITCLENO
from DSMC08 MC08, DSOD00 OD00
where MC08.OD_NO = OD00.OD_NO) B,
DSSU01 C,
DSPB08 D,
DSPB10 E,
DSMT00 F
where A.TW_NO = B.TW_NO(+)
AND A.SU_NO = C.SU_NO(+)
AND A.PB_FACTNO = D.PB_FANO(+)
AND A.AC_DEPT = E.DP_NO(+)
AND A.MT_STOCKNO = F.MT_STOCKNO(+)
AND A.AC_TWNO >= '1'
and A.AC_TWNO <= 'Z'
AND B.OD_NO = '1'
AND A.EL_NO >= '1'
and A.EL_NO <= 'Z'
AND A.SU_NO = '0000'
AND A.PB_FACTNO = 'FN3'
AND A.MT_STOCKNO = '00021'
AND TO_char(A.AC_DATE, 'YYYY/MM/DD') >= '2010/02/05'
and TO_char(A.AC_DATE, 'YYYY/MM/DD') <= '2010/02/05'
帮忙看看能怎么优化,谢谢!
A.AC_TWSEQ,
B.OD_NO,
B.SH_NO,
B.SH_ARITCLENO,
A.EL_NO,
A.EL_CNAME,
A.EL_UNIT,
A.MT_LOTNO,
A.MT_AREANO,
A.TW_NO,
A.TW_SEQ,
A.QA_QTY,
to_char(A.AC_DATE, 'yyyy/mm/dd') AC_DATE,
A.AC_QTY,
A.BK_QTY,
A.AC_PRICE,
A.AC_AMT,
A.AC_CFM,
A.EL_NETU,
A.AC_INVONO,
A.PB_ODID,
A.PB_RATE,
C.SU_NA,
D.PB_FANA,
E.DP_NAME,
A.CU_SALE,
F.MT_STOCKNA
from (select MC12.AC_DATE,
MC12.SU_NO,
MC12.SU_PONO,
MC12.PB_FACTNO,
MC12.AC_DEPT,
MC12.PB_ODID,
MC12.PB_RATE,
MC12.AC_CFM,
MC12.CU_SALE,
MC12.AC_INVONO,
MC13.AC_TWNO,
MC13.AC_TWSEQ,
MC13.EL_NO,
MC13.QA_QTY,
MC13.EL_UNIT,
MC13.MT_LOTNO,
MC13.MT_STOCKNO,
MC13.MT_AREANO,
MC13.TW_NO,
MC13.TW_SEQ,
MC13.AC_QTY,
MC13.BK_QTY,
MC13.AC_PRICE,
MC13.AC_AMT,
MC13.EL_NETU,
EL00.EL_CNAME
from DSMC12 MC12, DSMC13 MC13, DSEL00 EL00
where MC12.AC_TWNO = MC13.AC_TWNO
and MC13.EL_NO = EL00.EL_NO(+)
and MC12.AC_TWCL = '2') A,
(select MC08.OD_NO, MC08.TW_NO, OD00.SH_NO, OD00.SH_ARITCLENO
from DSMC08 MC08, DSOD00 OD00
where MC08.OD_NO = OD00.OD_NO) B,
DSSU01 C,
DSPB08 D,
DSPB10 E,
DSMT00 F
where A.TW_NO = B.TW_NO(+)
AND A.SU_NO = C.SU_NO(+)
AND A.PB_FACTNO = D.PB_FANO(+)
AND A.AC_DEPT = E.DP_NO(+)
AND A.MT_STOCKNO = F.MT_STOCKNO(+)
AND A.AC_TWNO >= '1'
and A.AC_TWNO <= 'Z'
AND B.OD_NO = '1'
AND A.EL_NO >= '1'
and A.EL_NO <= 'Z'
AND A.SU_NO = '0000'
AND A.PB_FACTNO = 'FN3'
AND A.MT_STOCKNO = '00021'
AND TO_char(A.AC_DATE, 'YYYY/MM/DD') >= '2010/02/05'
and TO_char(A.AC_DATE, 'YYYY/MM/DD') <= '2010/02/05'
帮忙看看能怎么优化,谢谢!
6.1. not exists比not in 效率高
6.2. UNION ALL效率比UNION高
6.3. 避免不必要操作
Select *,Order by,Group by,Distinct, UNION, MINUS,INTERSECT 操作是相当耗时的,能不使用就不要使用。通常, 带有UNION, MINUS , INTERSECT的SQL语句都可以用其它方式重写。
6.4. 联接查询比子查询的效率要高
6.5. 表关联的字段上使用索引
6.6. 在View中尽量不要使用 Package/function
6.7. 合理排列WHERE子句中的连接顺序(大表放后面)
6.8. 用Where子句替换HAVING子句
6.9. 使用索引的一些注意点
而通常情况下,使用索引比全表扫描要块几倍至几千倍,所以对索引要有比较深入的了解。
某些情况下SELECT 语句中的WHERE子句用到索引列,但生成的执行计划却不不使用索引。