一个group by引发的血案

Question

今天遇到一个不可思议的问题，两种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

Answer 1

看看是不是耗时在having count(*) 上面

Answer 2

因为你在建立临时表的时候已加上了条件code=2314这样就过滤了大部分数据，然后再group的数据就少了，

Answer 3

即使在同一个sql里条件加的先后也会影响效率的，一般都会把可以过滤掉大量数据放在前面，楼主可以意会并测试下

Answer 4

code=2314根本就没用上索引
第二种方式就像楼上所说的，数据量少了，索引不索引也无所谓

Answer 5

如果你还疑惑为什么第二种方式总体比较快的话，看看解释计划就明白了
第一种方式那个hash group by消耗极大
而第二种方式就没有这一步

Answer 6

建议in那用exists修改一下试试

Answer 7

select s_id, f_id, r_id 
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;

Answer 8

曾经也遇到过 自己看了下执行计划 发现语句一样在不同版本oracle下面有不同表现 
10.2.0.1 效率很低 是全表扫描
10.2.0.4 应该优化了 很快

Answer 9

嗯是的，hash group by 消耗量最大。不过我觉得这个是优化器的问题。 这么低的效率优化器为什么还采用这种方式。 不如自动的实现先建立临时表的方式。

Answer 10

下面这样写会快不：select s_id, f_id, r_id from COMP_parts where s_id||f_id in
(  select /*+ unnest*/se_id||f_id from COMPREC where code = 2314  ) group by s_id, f_id, r_id having count(*) > 1