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