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,这种情况如果子查询被展开,那么会导致执行计划混乱,导致大表先和子查询里的大表关联。