将in改为exists性能会好很多.
c.billno in (select billno from mit_bill_attach where
facno ='WHT03111034' and facid = 'A' )
改成:
exists (select billno from mit_bill_attach where
c.billno = mit_bill_attach.billno
and facno ='WHT03111034' and facid = 'A' )
c.billno in (select billno from mit_bill_attach where
facno ='WHT03111034' and facid = 'A' )
改成:
exists (select billno from mit_bill_attach where
c.billno = mit_bill_attach.billno
and facno ='WHT03111034' and facid = 'A' )
解决方案 »
- 动态增加字段的设计方法
- 查看正在用的回滚段所在的表空间??
- oracle审计
- oracle/sp
- ORACLE以及ADO数据库编程高手请进
- 谁给推荐几本大数据处理和数据挖掘的书,有基础又有实践的
- 求关于一条数据按起止时间截取为多条的select语句写法
- 水晶报表的oracle问题
- 如何将数据从一个数据库增量导入到另外一个数据库的一个表中?
- 用Enterprise manager configuration assistant创建资料档案库时,用户名/密码:system/manager.服务怎么添都不对,索性不添,到对了,这
- 各位大侠,鄙人这厢有礼了。请多指教啊
- Pro*C中使用动态sql定义了两个游标,为什么第2个游标内容取不出来!!
另外,可以适当地建立索引列.
SELECT BILLNO,
ITEMNO,
MATNO,
(SELECT MATNM FROM BMT_MAT B WHERE B.MATNO = A.MATNO),
UNITNM,
PQTY + LQTY AS QTY,
PLUS
FROM MIT_BILL_DETAIL A
WHERE A.MATNO = 'AA0019001010'
AND A.FACNO = 'WHT03111034'
AND FACID = 'A'
UNION ALL
SELECT C.BILLNO,
C.ITEMNO,
C.MATNO,
(SELECT MATNM FROM BMT_MAT D WHERE D.MATNO = C.MATNO),
C.UNITNM,
(SELECT QTY
FROM MIT_BILL_ATTACH
WHERE BILLNO = C.BILLNO
AND SEQ = C.SEQ
AND FACNO = 'WHT03111034'
AND FACID = 'A'),
PLUS
FROM MIT_BILL_DETAIL C
WHERE C.MATNO = 'AA0019001010'
AND EXISTS (SELECT BILLNO
FROM MIT_BILL_ATTACH
WHERE FACNO = 'WHT03111034'
AND FACID = 'A'
AND C.BILLNO = MIT_BILL_ATTACH.BILLNO)
从语句上,只能分两句进行优化!
1:
select billno,itemno,matno,
(select matnm from bmt_mat b where b.matno= a.matno),
unitnm,pqty+lqty as qty,plus
from mit_bill_detail a
where a.matno = 'AA0019001010' and a.facno = 'WHT03111034' and facid = 'A'
2:
select c.billno,c.itemno,c.matno,
(select matnm from bmt_mat d where d.matno= c.matno),
c.unitnm,
(select qty from mit_bill_attach where billno = c.billno and seq = c.seq and facno ='WHT03111034' and facid = 'A' ),plus
from mit_bill_detail c
where c.matno = 'AA0019001010' and c.billno in
(select billno from mit_bill_attach where facno ='WHT03111034' and facid = 'A' )
对第一个语句:只有一处可以优化,就是对条件里,把能过滤最大数据,或者有索引的放到最后。
对第二个语句:
首先看到:
c.billno in
(select billno from mit_bill_attach where facno ='WHT03111034' and facid = 'A' )改为exists
(select billno from mit_bill_attach where c.billno = billno and facno ='WHT03111034' and facid = 'A' )
,接着如果,子查询条件中的facno ='WHT03111034' and facid = 'A' 能外移的话,一定要放到外面,这样可以快很多!条件上,也应该遵从,条件多的放到后面(视具体编译看,可以放最前最后调试看)。如果想最优化,可以把表结构和各表之间的管理贴出来,让大家讨论一下!