SQL优化 第九章 With改写优化SQL

参考资料：
本系列博客主要参考资料有CUUG冉乃纲老师数据库教学笔记，《SQL优化核心思想》（罗炳森，黄超，钟侥著），《PostgreSQL技术内幕：查询优化深度探索》（张树杰著），排名不分先后。

 

9.1 提取公共项

9.1.1. 数据脚本

drop table test1;

drop table test2;

create table test1 as select * from dba_objects;

create table test2 as select * from dba_objects;

insert into test2 select * from test2;

--反复执行test2的自插操作，直到行数足够多

commit;

drop index ix_test1;

drop index ix_test2;

create index ix_test1_01 on test1(owner);

create index ix_test1_02 on test1(object_id);

9.1.2 改写过程

（1）改写前

select '1' as seq, count(t1.OBJECT_ID),count(t2.OBJECT_ID)

from test1 t1

  left join (select * from test2 t where t.OBJECT_ID<1000 and t.OWNER='OUTLN') t2

    on t1.OBJECT_ID=t2.OBJECT_ID

where t1.OBJECT_ID<1000

union

select  '2', count(t1.OBJECT_ID),count(t2.OBJECT_ID)

from test1 t1

  left join (select * from test2 t where t.OBJECT_ID<1000 and t.OBJECT_TYPE='EDITION') t2

    on t1.OBJECT_ID=t2.OBJECT_ID

where  t1.OBJECT_ID<1000;