关于如下语句的调优！

[luncashcage]

select * from
(
select
rownum rn , t.* from
(
select handle_date, agent_code,agent_name,dept,business_type, count(distinct account_name),count(distinct business_code)
from cust a where mymonth = '200504'
and not exists
(
select 1 from cust b where mymonth='200503' and a.business_code = b.business_code
)
group by handle_date, agent_code,agent_name,dept,business_type
) t

where rownum < 15137
) where rn > 15117

由于cust那张表数据比较多，所以每执行一次这条语句。需要花费一分半多分钟。请问，有什么好的办法调优呢。多谢了

[小李木耳]

>试试如下：

select handle_date, agent_code,agent_name,dept,business_type, count(distinct account_name),count(distinct business_code)
from cust a where mymonth = '200504'
and not in
(select b.business_code from cust b where mymonth='200503')
and rownum < 15137 and rownum> 15117
group by handle_date, agent_code,agent_name,dept,business_type

[qiaozhiwei]

估计是不能再优化了,楼上的应该是错误的,你考虑建索引吧

[沝林]

qiaozhiwei(乔）说得没错，不过还可以考虑下下面的方法：
oracle自下而上的顺序解析WHERE子句，尽量把可以过滤掉最大数量记录的条件写在WHERE子句的末尾
试着把条件mymonth = '200504'和mymonth='200503'放到各自where子句的最末尾

[小李木耳]

sorry error!
---------->
select handle_date, agent_code,agent_name,dept,business_type, count(distinct account_name),count(distinct business_code)
from cust a where mymonth = '200504'
and not in
(select b.business_code from cust b where mymonth='200503')
and rownum < 15137 and rownum> 15117
group by handle_date, agent_code,agent_name,dept,business_type

[小李木耳]

搂主听qiaozhiwei(乔) 和 duanzilin(寻)的建议吧，
要不就做一个定期的存储过程，建立一个分组和rownum的的table。

[luncashcage]

对的，二楼至少有两个显著错误，第一个，not in前应该加a.business_code，第二，and rownum < 15137 and rownum> 15117这样是不正确的。
虽然，我在之前已经知道，not in与not exists的性能差别，但还是本着实事求是的原则用in的试了一遍，结果发现，用exists大约在75秒左右，用in大约在85秒，差距不是想象的那么大。
---------------------------------------------
三楼的，如果建索引，应该考虑在哪列上建索引呢。