FILTER的适用范围:
1. 主表返回的记录数较少
2.子查询返回记录数较小
下面做实验证明:
select department_name
from hr.dept_1 dept
where department_id IN (select department_id from hr.employees_1 emp);
SQL> select count(*) from dept_1;
COUNT(*)
----------
2
SQL> select count(*) from employees_1;
COUNT(*)
----------
3506176
SQL> select department_name
from hr.dept_1 dept
where department_id IN (select department_id from hr.employees_1 emp); 2 3
Execution Plan
----------------------------------------------------------
Plan hash value: 3257593059
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 40 | 7 (0)| 00:00:01 |
| 1 | NESTED LOOPS SEMI | | 2 | 40 | 7 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT_1 | 2 | 32 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | EMPLOYEES_1_IDX1 | 3508K| 13M| 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("DEPARTMENT_ID"="DEPARTMENT_ID")
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
10 consistent gets
0 physical reads
0 redo size
486 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
那么走FILTER呢？
SQL> select department_name
from hr.dept_1 dept
where department_id IN (select /*+ NO_UNNEST */ department_id from hr.employees_1 emp); 2 3
Execution Plan
----------------------------------------------------------
Plan hash value: 1766326341
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 16 | 6 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL| DEPT_1 | 2 | 32 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | EMPLOYEES_1_IDX1 | 2 | 8 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "HR"."EMPLOYEES_1" "EMP"
WHERE "DEPARTMENT_ID"=:B1))
3 - access("DEPARTMENT_ID"=:B1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
10 consistent gets
0 physical reads
0 redo size
486 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
此时效率一样,说明当from ...主表 返回数据 只返回几条 走FILTER没问题(主表小是之返回行数少)
那么还有什么情况,可以走FILTER呢？
SQL> select * from test a where object_id in (select department_id
from hr.dept_1 dept
where department_id IN (select department_id from hr.employees_1 emp)); 2 3
64 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 717021958
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 207 | 9747 (7)| 00:01:57 |
| 1 | VIEW | VM_NWVW_2 | 1 | 207 | 9747 (7)| 00:01:57 |
| 2 | HASH UNIQUE | | 1 | 226 | 9747 (7)| 00:01:57 |
|* 3 | HASH JOIN | | 14M| 3134M| 9264 (2)| 00:01:52 |
|* 4 | HASH JOIN | | 46 | 10212 | 7400 (1)| 00:01:29 |
| 5 | TABLE ACCESS FULL | DEPT_1 | 2 | 6 | 3 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL | TEST | 2511K| 524M| 7389 (1)| 00:01:29 |
| 7 | INDEX FAST FULL SCAN| EMPLOYEES_1_IDX1 | 3508K| 13M| 1819 (1)| 00:00:22 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("DEPARTMENT_ID"="DEPARTMENT_ID")
4 - access("OBJECT_ID"="DEPARTMENT_ID")
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
57221 consistent gets
41372 physical reads
0 redo size
2850 bytes sent via SQL*Net to client
463 bytes received via SQL*Net from client
6 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
64 rows processed
这里子查询被展开了,导致test和dept_1先关联了,但是子查询
SQL> (select department_id
from hr.dept_1 dept
where department_id IN (select department_id from hr.employees_1 emp)) 2 3 ;
DEPARTMENT_ID
-------------
10
20
只返回了2条记录,可以让它走FILTER。
SQL> select * from test a where object_id in (select department_id
from hr.dept_1 dept
where department_id IN (select /*+ NO_UNNEST */ department_id from hr.employees_1 emp)); 2 3
64 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1506439343
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 23 | 5060 | 7403 (1)| 00:01:29 |
|* 1 | HASH JOIN RIGHT SEMI| | 23 | 5060 | 7403 (1)| 00:01:29 |
| 2 | VIEW | VW_NSO_1 | 1 | 13 | 6 (0)| 00:00:01 |
|* 3 | FILTER | | | | | |
| 4 | TABLE ACCESS FULL| DEPT_1 | 2 | 6 | 3 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | EMPLOYEES_1_IDX1 | 2 | 8 | 3 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL | TEST | 2511K| 495M| 7389 (1)| 00:01:29 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("OBJECT_ID"="DEPARTMENT_ID")
3 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "HR"."EMPLOYEES_1" "EMP"
WHERE "DEPARTMENT_ID"=:B1))
5 - access("DEPARTMENT_ID"=:B1)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
48871 consistent gets
33065 physical reads
0 redo size
4119 bytes sent via SQL*Net to client
463 bytes received via SQL*Net from client
6 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
64 rows processed
走FILTER后逻辑读下降到了 48871 。
总结如果子查询返回数据很少，那么不妨让它走filter,这种情况如果子查询被展开,那么会导致执行计划混乱,导致大表先和子查询里的大表关联。
