Database Reference
In-Depth Information
The strange thing in this execution plan is that operation 0 has several children. If you look carefully at the
Starts
column, you'll notice that although operations 1 and 2 are executed three times, operation 3 is executed only
once. Also notice that operations 1 and 2, since they reference the
dept
table, implement the subquery. This unusual
execution plan carries out the operations as follows:
1.
Operation 3, which is the first one being executed, scans the
emp
table and returns all rows
to its parent (0).
2.
For each row returned by the operation 3, the subquery should be executed once.
However, also in this case the SQL engine caches the results, and therefore the subquery is
executed only once for each distinct value in the
deptno
column.
3.
To execute the subquery, operation 2 scans the
dept_pk
index by applying the
"DEPT"."DEPTNO"=:B1
access predicate, extracts the rowids, and returns them to its parent (1).
The bind variable (
B1
) is used to pass the value to be checked to the subquery. Then
operation 1 accesses the
dept
table with those rowids and passes the data to its parent (0).
4.
Operation 0 sends the data to the caller.
Subquery in the WHERE Clause #1
This example shows a particular execution plan related to a query containing a subquery in the
WHERE
clause.
The query and its execution plan are the following:
SELECT deptno
FROM dept
WHERE deptno NOT IN (SELECT deptno FROM emp)
--------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows |
--------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 |
|* 1 | INDEX FULL SCAN | DEPT_PK | 1 | 1 |
|* 2 | TABLE ACCESS FULL| EMP | 4 | 3 |
--------------------------------------------------------
1 - filter( NOT EXISTS (SELECT 0 FROM "EMP" "EMP" WHERE
LNNVL("DEPTNO"<>:B1)))
2 - filter(LNNVL("DEPTNO"<>:B1))
■
this query is another case where the
v$sql_plan
and
v$sql_plan_statistics_all
views show wrong
information. in this case,
EXPLAIN PLAN
shows the correct predicates shown above. the wrongly displayed predicate is
the one associated with operation 1:
Caution
1- filter(
IS NULL
)
