今天遇到一个不可思议的问题,两种sql语句的写法造成了执行效率极大的不同
select s_id, f_id, r_id from COMP_parts where s_id||f_id in
( select se_id||f_id from COMPREC where code = 2314 ) group by s_id, f_id, r_id having count(*) > 1 查看这条语句的执行计划相当的高,执行速度也是相当的慢,几十万级别的数据,执行了20分钟没执行完
而另一种方法,只是将select s_id, f_id, r_id from COMP_parts where s_id||f_id in
( select se_id||f_id from COMPREC where code = 2314 ) 这部分先建立一个临时表。create table tmp as select s_id, f_id, r_id from COMP_parts where s_id||f_id in
( select se_id||f_id from COMPREC where code = 2314 )
然后 执行select s_id, f_id, r_id from tmp group by s_id, f_id, r_id having count(*) > 1 只需要2秒钟。而建立表也只需要2s。一共4秒。 和之前的二十分钟没有执行完的差距也太大了。
哪位高人能帮忙解答一下,多谢了。我最近在学习sql效率优化这块,希望大家帮忙啊。优化sql
select s_id, f_id, r_id from COMP_parts where s_id||f_id in
( select se_id||f_id from COMPREC where code = 2314 ) group by s_id, f_id, r_id having count(*) > 1 查看这条语句的执行计划相当的高,执行速度也是相当的慢,几十万级别的数据,执行了20分钟没执行完
而另一种方法,只是将select s_id, f_id, r_id from COMP_parts where s_id||f_id in
( select se_id||f_id from COMPREC where code = 2314 ) 这部分先建立一个临时表。create table tmp as select s_id, f_id, r_id from COMP_parts where s_id||f_id in
( select se_id||f_id from COMPREC where code = 2314 )
然后 执行select s_id, f_id, r_id from tmp group by s_id, f_id, r_id having count(*) > 1 只需要2秒钟。而建立表也只需要2s。一共4秒。 和之前的二十分钟没有执行完的差距也太大了。
哪位高人能帮忙解答一下,多谢了。我最近在学习sql效率优化这块,希望大家帮忙啊。优化sql
第二种方式就像楼上所说的,数据量少了,索引不索引也无所谓
第一种方式那个hash group by消耗极大
而第二种方式就没有这一步
from COMP_parts a
where exists(
select 1
from COMPREC b
where code = 2314
and a.s_id||f_id = b.se_id||f_id
)
group by s_id, f_id, r_id
having count(*) > 1;
10.2.0.1 效率很低 是全表扫描
10.2.0.4 应该优化了 很快
( select /*+ unnest*/se_id||f_id from COMPREC where code = 2314 ) group by s_id, f_id, r_id having count(*) > 1