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
(select 1 from t2 where t2.owner = t1.owner
group by t2.object_type having count(*) >= 10) and
t1.object_type = 'TABLE'
Plan hash value: 2091197476
-----------------------------------------------------------------------------------------------
--------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem
| 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------
--------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 268 |
| | |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 268 |
| | |
|* 2 | FILTER | | 1 | | 519 |00:00:00.01 | 268 |
| | |
|* 3 | TABLE ACCESS FULL | T1 | 1 | 587 | 522 |00:00:00.01 | 67 |
| | |
|* 4 | FILTER | | 3 | | 2 |00:00:00.01 | 201 |
| | |
| 5 | HASH GROUP BY | | 3 | 50 | 8 |00:00:00.01 | 201 | 1301K| 1301K| 1074K (0)|
|* 6 | TABLE ACCESS FULL| T2 | 3 | 50 | 3449 |00:00:00.01 | 201 |
| | |
-----------------------------------------------------------------------------------------------
--------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter( IS NOT NULL)
3 - filter("T1"."OBJECT_TYPE"='TABLE')
4 - filter(COUNT(*)>=10)
6 - filter("T2"."OWNER"=:B1)
Note
-----
- dynamic sampling used for this statement (level=2)
已选择33行。
①Starts的意思是执行了几次，首先2这个地方执行了1次，是对t1进行全表扫描，返回了522行记录，
测试如下:
SQL> select count(*) from t1 where t1.object_type = 'TABLE';
COUNT(*)
----------
522
②看 一下4这个结点，执行了3次， 测试：
SQL> select distinct owner from t1 where t1.object_type = 'TABLE';
OWNER
------------------------------
OUTLN
SYSTEM
SYS
总结：
1.Filter就是外部查询返回多少个唯一值，内部查询就访问多少次。
2.产生Filter的方式：
①exists里有group by xx having xx
②exists里有rownum 或者是start with时会产生filter.
3.filter的执行计划一般是不好的，因为外部返回的数据很大的话，就会导致内部查询扫描很多次。
改写exists：
这样改写的原因是owner是传值，其实也就是相当于对owner分组
select /*+ gather_plan_statistics */
count(*)
from t1
where owner in (select owner
from t2
group by t2.object_type,owner
having count(*) >= 10)
and t1.object_type = 'TABLE';
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
SQL_ID c07dgxq09xqga, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from t1 where owner
in (select owner from t2 group by
t2.object_type,owner having count(*) >= 10) and
t1.object_type = 'TABLE'
Plan hash value: 3015395397
-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 134 | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 134 | | | |
|* 2 | HASH JOIN SEMI | | 1 | 587 | 519 |00:00:00.01 | 134 | 2293K| 2293K| 561K (0)|
| 3 | JOIN FILTER CREATE | :BF0000 | 1 | 587 | 522 |00:00:00.01 | 67 | | | |
|* 4 | TABLE ACCESS FULL | T1 | 1 | 587 | 522 |00:00:00.01 | 67 | | | |
| 5 | VIEW | VW_NSO_1 | 1 | 5002 | 10 |00:00:00.01 | 67 | | | |
|* 6 | FILTER | | 1 | | 10 |00:00:00.01 | 67 | | | |
| 7 | HASH GROUP BY | | 1 | 5002 | 21 |00:00:00.01 | 67 | 1214K| 1214K| 1249K (0)|
| 8 | JOIN FILTER USE | :BF0000 | 1 | 5002 | 3449 |00:00:00.01 | 67 | | | |
|* 9 | TABLE ACCESS FULL| T2 | 1 | 5002 | 3449 |00:00:00.01 | 67 | | | |
-------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"="OWNER")
4 - filter("T1"."OBJECT_TYPE"='TABLE')
6 - filter(COUNT(*)>=10)
9 - filter(SYS_OP_BLOOM_FILTER(:BF0000,"OWNER"))
Note
-----
- dynamic sampling used for this statement (level=2)
已选择36行。
