参考资料:
本系列博客主要参考资料有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