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
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那张表数据比较多,所以每执行一次这条语句。需要花费一分半多分钟。请问,有什么好的办法调优呢。多谢了
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
oracle自下而上的顺序解析WHERE子句,尽量把可以过滤掉最大数量记录的条件写在WHERE子句的末尾
试着把条件mymonth = '200504'和mymonth='200503'放到各自where子句的最末尾
---------->
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
要不就做一个定期的存储过程,建立一个分组和rownum的的table。
虽然,我在之前已经知道,not in与not exists的性能差别,但还是本着实事求是的原则用in的试了一遍,结果发现,用exists大约在75秒左右,用in大约在85秒,差距不是想象的那么大。
---------------------------------------------
三楼的,如果建索引,应该考虑在哪列上建索引呢。