参考资料:
本系列博客主要参考资料有CUUG冉乃纲老师数据库教学笔记,《SQL优化核心思想》(罗炳森,黄超,钟侥著),《PostgreSQL技术内幕:查询优化深度探索》(张树杰著),排名不分先后。
虽然Filter连接大部分时间不太讨喜,但是,鉴于存在即是合理的这种高大上的哲学思想,我们首先给Filter在局部范围平个反。
10.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;
insert into test2 select * from test2;
commit;
create index ix_test1_01 on test1(object_id);
create index ix_test2_01 on test2(object_id);
10.2 Filter连接的好
(1)改写前
select count(*)
from test1 t1
where exists (select 1
from test2 t2
where t1.owner=t2.owner
and t1.object_type = t2.object_type
group by t2.owner,t2.object_type
having count(*)>300)
and t1.object_id<1000;