對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.