对fiter操作的解释。

SQL> SELECT /*+ gather_plan_statistics */ *
  2  FROM emp
  3  WHERE NOT EXISTS (SELECT /*+ no_unnest */ 0
  4                    FROM dept
  5                    WHERE dept.dname = 'SALES' AND dept.deptno = emp.deptno)
  6  AND NOT EXISTS (SELECT /*+ no_unnest */ 0
  7                  FROM bonus
  8                  WHERE bonus.ename = emp.ename);

SQL> select * from table(dbms_xplan.display_cursor(null,null,'iostats last'));

 

--------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------
|*  1 |  FILTER                      |         |      1 |        |      8 |00:00:00.01 |      35 |
|   2 |   TABLE ACCESS FULL          | EMP     |      1 |     14 |     14 |00:00:00.01 |       8 |
|*  3 |   TABLE ACCESS BY INDEX ROWID| DEPT    |      3 |      1 |      1 |00:00:00.01 |       6 |
|*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |      3 |      1 |      3 |00:00:00.01 |       3 |
|*  5 |   TABLE ACCESS FULL          | BONUS   |      7 |      1 |      0 |00:00:00.01 |      21 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter( NOT EXISTS (SELECT 0 FROM "DEPT" "DEPT" WHERE "DEPT"."DEPTNO"=:B1
       AND "DEPT"."DNAME"='SALES') AND NOT EXISTS (SELECT 0 FROM "BONUS"
       "BONUS" WHERE "BONUS"."ENAME"=:B2))
   3 - filter("DEPT"."DNAME"='SALES')
   4 - access("DEPT"."DEPTNO"=:B1)
   5 - filter("BONUS"."ENAME"=:B1)

The particular characteristic of this operation is that it supports a varying number of children.
If it has a single child, it is considered a stand-alone operation. If it has two or more children,
its function is similar to the operation NESTED LOOPS. The first child drives the execution of the
other children.------------摘录自 troubleshooting oracle performance

1.首先操作2先执行，返回14条记录给父操作1.

2.操作2返回的每条记录，过滤操作的第二和第三个孩子都要执行一次。In reality, a kind of caching is implemented to reduce executions to a minimum. This is confirmed by comparing the column A-Rows of operation 2 with the column Starts of operations 3 and 5. Operation 3 is executed three times, once for each distinct value in the column deptno in the table emp. Operation 5 is executed eight times, once for each distinct value in the column empno in the table emp after applying the filter imposed by the operation 3.---英文部分摘录自troubleshooting oracle performance。我认为红色字体为书中的错误，应该是经过操作3过滤后，emp表的ename字段有几个不同值就执行几次，而不是empno有几个不同值就执行几次。

3.According to the rules for stand-alone operations, operation 4, which is executed before operation 3, scans the index dept_pk by applying the access predicate "DEPT"."DEPTNO"=:B1. The bind variable (B1) is used to pass the value that is to be checked by the subquery. By doing so over the three executions, it extracts three rowids from the index and passes them to its parent operation (3).

4. Operation 3 accesses the table dept through the rowids passed from its child operation(4) and applies the filter predicate "DEPT"."DNAME"='SALES'. Since this operation is used only to apply a restriction, it returns no data to its parent operation (1). In any case, it is important to note that only one row satisfying the filter predicate was found. Since a NOT EXISTS is used, this matching row is discarded.

5. Operation 5 scans the table bonus and applies the filter predicate "BONUS"."ENAME"=:B1. The bind variable (B1) is used to pass the value to be checked by the subquery. Since this operation is used only to apply a restriction, it returns no data to its parent operation (1). It is, however, important to notice that no row satisfying the filter predicate was found. Since a NOT EXISTS is used, no rows are discarded.

6. Operation 1, after applying the filter predicate implemented with operations 3 and 5, sends the data of eight rows to the caller.

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22034023/viewspace-672064/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22034023/viewspace-672064/