Database Reference
In-Depth Information
At first sight, this execution plan is composed from two stand-alone operations. If you carefully look at the
Starts
column, you'll notice something strange. In fact, although the parent (1) is executed only once, the child (2) is
executed four times. Actually, this execution plan carries out the operations as follows:
1.
Operation 1, which is the first being executed, scans the
dept_pk
index. For each value in
the
deptno
column, it executes operation 2. As shown by the filter predicates, operation 2
applies the
NOT EXISTS (SELECT 0 FROM "EMP" "EMP" WHERE LNNVL("DEPTNO"<>:B1))
subquery. Notice that the query optimizer transformed the
NOT IN
into a
NOT EXISTS
. The
bind variable (
B1
) is used to pass the value to be checked to the subquery.
2.
Operation 2 scans the
emp
table, applies the
LNNVL("DEPTNO"<>:B1)
filter predicate,
and returns the data to its parent (1).
3.
For each row fulfilling the filter predicates operation 1 passes the data to its parent (0).
4.
Operation 0 sends the data to the caller.
Another execution plan that can be generated by the query optimizer for the very same query is the following one.
However, because it uses a feature (
NULL
-aware anti-join) that's only available as of version 11.1, don't expect to see this
type of execution plan in version 10.2. (In my opinion, this execution plan is far more readable than the previous one).
--------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows |
--------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 |
|* 1 | HASH JOIN ANTI NA | | 1 | 1 |
| 2 | INDEX FULL SCAN | DEPT_PK | 1 | 4 |
| 3 | TABLE ACCESS FULL| EMP | 1 | 14 |
--------------------------------------------------------
1 - access("DEPTNO"="DEPTNO")
Subquery in the WHERE Clause #2
This example is an extension of the previous one. It also involves a subquery in the
WHERE
clause. I show it, because I
want to call attention to the fact that the query optimizer is able to generate execution plans like the one discussed in
the previous section, even when the code implementing the subquery is more complex than a plain lookup. The query
and its execution plan are as follows:
SELECT *
FROM t1
WHERE n1 = 8 AND n2 IN (SELECT t2.n1
FROM t2, t3
WHERE t2.id = t3.id AND t3.n1 = 4);
--------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows |
--------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 7 |
|* 2 | INDEX RANGE SCAN | I1 | 1 | 7 |
|* 3 | HASH JOIN | | 13 | 1 |
|* 4 | TABLE ACCESS FULL | T3 | 13 | 1183 |
