SQL优化-避免多余Group By

 

1 数据准备

drop table test1;

drop table test2;

create table test1 as select * from dba_objects;

alter table test1 add constraint pk_test1 primary key (object_id);

create table test2 as select * from dba_objects;

insert into test2 select * from test2;

insert into test2 select * from test2;

commit;

2 优化分析

2.1 写法一

select t1.object_id,t1.object_name,t1.OWNER,

              t1.object_type,t1.namespace,t1.created,

              sum(t2.data_object_id) as sum_id

 from test1 t1

   inner join test2 t2

     on t1.object_id=t2.object_id

 group by t1.object_id,t1.object_name,t1.OWNER,

                t1.object_type,t1.namespace,t1.created;

 

因为test1的主键是object_id,并且test2表的字段只有聚合运算，也就意味着只要按照test1的object_id进行聚合就好了，其他的聚合字段是没要的，但是，基于SQL的语法规则，必须要加上其他检索字段，造成了不必要的分组运算。

2.2 写法二

with tmp as

(select  t1.object_id,

              sum(t2.data_object_id) as sum_id

 from test1 t1

   inner join test2 t2

     on t1.object_id=t2.object_id

 group by t1.object_id)

select  tt.object_id,tt.object_name,tt.OWNER,

              tt.object_type,tt.namespace,tt.created,

              t.sum_id

from tmp t

    ,test1 tt

where t.object_id=tt.OBJECT_ID;

 

这一次改写，聚合运算明显减少，但是，执行计划走了嵌套循环，不适合当前的检索，我们下面用hint稍加调整。

2.3 写法三

with tmp as

(select /*+ use_hash(t1 t2)*/  t1.object_id,

              sum(t2.data_object_id) as sum_id

 from test1 t1

   inner join test2 t2

     on t1.object_id=t2.object_id

 group by t1.object_id)

select  tt.object_id,tt.object_name,tt.OWNER,

              tt.object_type,tt.namespace,tt.created,

              t.sum_id

from tmp t

    ,test1 tt

where t.object_id=tt.OBJECT_ID;

hint调整之后，性能还是没有提升，原因是test1表要多扫描一次，IO成本增加。

2.4 写法四

select t1.object_id, t1.object_name,t1.OWNER,

              t1.object_type,t1.namespace,t1.created,

              t2.sum_id

 from test1 t1

   inner join (select t.object_id,sum(t.data_object_id) as sum_id

               from test2 t

                        group by t.object_id) t2

     on t1.object_id=t2.object_id;

这一次改写，先聚合，再连接，时间大幅减少。