SQL优化-分析函数优化（不等连接+聚合）

1.数据脚本

drop table test1 ;

drop table test2 ;

drop table test3 ;

create table test1 as

  select * from (select d.*,rownum as rn

                 from dba_objects d

                             where d.owner not in ('SYS','SYSTEM','PUBLIC'))

              where mod(rn,20)=0;

create table test2 as select * from test1;

create table test3 as select * from test1;

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

alter table test2 add constraint pk_test2 primary key (object_id);

alte