Database Reference
In-Depth Information
DEPTNO DNAME COUNT(*)
------ ---------- --------
10 ACCOUNTING 3
20 RESEARCH 5
30 SALES 6
5.
According to the rules for stand-alone operations, operation 4, which is executed before
operation 3, scans the
dept_pk
index by applying the
"DEPT"."DEPTNO"=:B1
access
predicate. The bind variable (
B1
) is used to pass the value that's 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 (3).
6.
Operation 3 accesses the
dept
table through the rowids passed from its child (4) and
applies the
"DEPT"."DNAME"='SALES'
filter predicate. Because this operation is used only
to apply a restriction, it returns no data to its parent (1). It just informs its parent whether
the condition is fulfilled. In any case, it's important to note that only one row satisfying the
filter predicate was found. Since a
NOT EXISTS
is used, this matching row is discarded.
7.
Operation 5 scans the
bonus
table and applies the
"BONUS"."ENAME"=:B1
filter predicate.
The bind variable (
B1
) is used to pass the value to be checked by the subquery. Because
this operation is used only to apply a restriction, it returns no data to its parent (1). It's
important, however, to notice that no row satisfying the filter predicate was found. Since a
NOT EXISTS
is used, no rows are discarded.
8.
Operation 1, after applying the filter predicate implemented with operations 3 and 5,
passes the resulting rows to its parent (0).
9.
Operation 0 sends the data to the caller.
The UPDATE Operation
This operation is used when an
UPDATE
statement is executed. Its particular characteristic is that it supports a varying
number of children. Most of the time, it has a single child and therefore is considered a stand-alone operation. Two
or more children are available only when subqueries are used in the
SET
clause. If it has more than one child, the first
child drives the execution of the other children.
Here's a sample SQL statement and its execution plan (see Figure
10-7
for a graphical representation of its
parent-child relationships):
UPDATE emp e1
SET sal = (SELECT avg(sal) FROM emp e2 WHERE e2.deptno = e1.deptno),
comm = (SELECT avg(comm) FROM emp e3)
---------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows |
---------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | | 0 |
| 1 | UPDATE | EMP | 1 | | 0 |
| 2 | TABLE ACCESS FULL | EMP | 1 | 14 | 14 |
| 3 | SORT AGGREGATE | | 3 | 1 | 3 |
|* 4 | TABLE ACCESS FULL| EMP | 3 | 5 | 14 |
| 5 | SORT AGGREGATE | | 1 | 1 | 1 |
