Filter消除-20190928-01

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 where rownum<100;

insert into test2 select * from test2 where rownum<2000;

commit;

 

2 业务需求

检索test2和test1表相同object_id，并且要求test2中object_id出现过3次。

 

3 性能改善

3.1 写法一

EXPLAIN PLAN FOR

select t1.object_id,t1.object_name,t1.owner

from test1 t1

where exists (select 1