小弟新学Oracle,哪位大侠帮忙给个算法,原始SQL如下select a.saler,a.saledate,from saleinfoinner join saveinfo sinfo on sinfo .app_order=a.app_order
and a.salemonth.=201001 and sinfo.factno='6901 4425 765 1'and sinfo.salernot in
(
select a.saler,a.saledate,from saleinfoinner join saveinfo sinfo on sinfo .app_order=a.app_order
and a.salemonth.<201001 and sinfo.factno='6901 4425 765 1'
)这是最笨的方法了,执行效率太低了,库中一共400W的数据量,执行一次大概40分钟左右,求高人指点,万分感谢
and a.salemonth.=201001 and sinfo.factno='6901 4425 765 1'and sinfo.salernot in
(
select a.saler,a.saledate,from saleinfoinner join saveinfo sinfo on sinfo .app_order=a.app_order
and a.salemonth.<201001 and sinfo.factno='6901 4425 765 1'
)这是最笨的方法了,执行效率太低了,库中一共400W的数据量,执行一次大概40分钟左右,求高人指点,万分感谢
不过你的意思我看明白了大概,
1、在saleinfo的app_order字段建一个索引
2、salemonth和factno字段建一个组合索引
3、避免使用in查询,如果能替代换一个方式写
select a.saler,a.saledate,
from saleinfo inner join saveinfo sinfo
on sinfo.app_order = a.app_order
and a.salemonth. = 201001
and sinfo.factno = '6901 4425 765 1'
and sinfo.salernot in (
select a.saler,a.saledate,
from saleinfo inner join saveinfo sinfo
on sinfo.app_order = a.app_order
and a.salemonth < 201001
and sinfo.factno = '6901 4425 765 1'
)
效率会高很多,
where a.salemonth.=201001 and a.factno='6901 4425 765 1'
and a.saler not in
(
select saler from saleinfo
where salemonth<201001 and factno='6901 4425 765 1'
)
---------------- -------------- ---------------- ------
大罗 18-12月-09 1 200912
小罗 07-1月 -10 1 201001
大罗 20-1月 -10 1 201001
小小罗 30-1月 -10 1 201001SQL> select * from saveinfo;APP_ORDER FACTNO
---------------- --------------------------------
1 6901 4425 765 1SQL> select a.saler,a.saledate from saleinfo a,saveinfo b
2 WHERE a.app_order=b.app_order and a.salemonth='201001' and b.factno='6901 4
425 765 1'
3 AND NOT EXISTS
4 (select 1 FROM saleinfo c
5 WHERE a.saler=c.saler and c.salemonth<'201001');SALER SALEDATE
---------------- --------------
小罗 07-1月 -10
小小罗 30-1月 -10SQL>
2、楼主的in条件貌似没有用贴上你正确语句大家看一看