SQL语句调优
select round(decode(sum(te1+te2),0,0,sum(te1)/sum(te1+te2)*100),2),ny from sg t
where data in
(select distinct t1.data from kj t1,hl t2
where t1.data =t2.data and qy='dba' and jb='l' and t1.hsj='qj' or t1.fdf='ju')
and ny between 200902 and 201106 group by ny
上面这个在oracle数据库中,花费时间大约为5秒,用户实在受不了,其中,in 中传入的data数量有70左右,
我试过用一些表连接,时间也没有提高。
请问各位大神如何优化这个sQL语句,十分感激!!!
[解决办法]
where t1.data =t2.data and qy='dba' and jb='l' and t1.hsj='qj' or t1.fdf='ju'
红色部分要不要加括号呀?感觉怪怪的
[解决办法]
试试用minus 来减少in 的结果集,in 是查询出2个结果集交集的部分,如果非交集部分比交集部分少很多,可以用minus将2个select的结果集去掉交集部分,得到非交集部分,再用not in 提高效率。不知道对你有用么
select round(decode(sum(te1 + te2), 0, 0, sum(te1) / sum(te1 + te2) * 100), 2), ny from sg t where data not in (select data from sg minus select distinct t1.data from kj t1, hl t2 where t1.data = t2.data and qy = 'dba' and jb = 'l' and t1.hsj = 'qj' or t1.fdf = 'ju') and ny between 200902 and 201106 group by ny
[解决办法]