Filter的一个测试

 

Filter的一个测试：
创建测试表 t1 和t2. 
create table t1 as select * from dba_objects where rownum<=5000;
create table t2 as select * from dba_objects where rownum<=5000;

执行该测试SQL，这里使用了提示来收集真实的执行计划
select /*+ gather_plan_statistics */
 count(*)
  from t1
 where exists (select 1
          from t2
         where t2.owner = t1.owner
         group by t2.object_type
        having count(*) >= 10)
   and t1.object_type = 'TABLE';


set autot off;
 
select /*+ gather_plan_statistics */
 count(*)
  from t1
 where exists (select 1
          from t2
         where t2.owner = t1.owner
         group by t2.object_type
        having count(*) >= 10)
   and t1.object_type = 'TABLE';

  COUNT(*)
----------
       519

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------
---------------
SQL_ID  49t0m3dv35346, child number 0
-------------------------------------
select /*+ gather_plan_statistics */  count(*)   from t1  where exists
(