Database Reference
In-Depth Information
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)
Figure 10-6.
Parent-child relationships of the
FILTER
operation
■
the
display_cursor
function in the
dbms_xplan
package sometimes shows wrong predicates. the
problem, though, isn't the package. it's actually caused by the
v$sql_plan
and
v$sql_plan_statistics_all
views that
show wrong information. in this case,
EXPLAIN PLAN
shows the correct predicates shown above, but the views show a
wrong predicate for operation 1:
Caution
1 - filter((
IS NULL AND IS NULL
))
note that according to oracle, this isn't a bug. it's just a limitation of the current implementation.
In this execution plan, the three children of the
FILTER
operation are stand-alone operations. Applying the rules
described earlier, you see that the execution plan carries out the operations in the following manner:
1.
Operation 0 has one child (1). It can't be the first one being executed.
2.
Operation 1 has three children (2, 3 and 5), and operation 2 is the first of them in
ascending order. Therefore, the execution starts with operation 2.
3.
Operation 2 scans the
emp
table and returns 14 rows to its parent (1).
4.
For each row returned by operation 2, the second and third children of the
FILTER
operation should be executed once. In reality, a kind of caching is implemented to
reduce executions to a minimum. This is confirmed by comparing the
A-Rows
column of
operation 2 with the
Starts
column of operations 3 and 5. Operation 3 is executed three
times, once for each distinct value in the
deptno
column in the
emp
table. Operation 5 is
executed eight times, once for each distinct value in the
ename
column in the
emp
table
after applying the filter imposed by the operation 3. The following query shows that the
number of starts matches the number of distinct values:
SQL> SELECT deptno, dname, count(*)
2 FROM emp NATURAL JOIN dept
