Database Reference
In-Depth Information
Figure 10-7.
Parent-child relationships of the
UPDATE
operation
4 - filter("E2"."DEPTNO"=:B1)
In this execution plan, all three children of the
UPDATE
related-combine operation are stand-alone operations.
The rules described earlier indicate that the execution plan carries out the operations as follows:
1.
Operation 0 has one child (1). It can't be the first operation being executed.
2.
Operation 1 has three children (2, 3, and 5), and operation 2 is the first of the three 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.
The second and third child (3 and 5) might be executed several times (at most, as many
times as the number of rows returned by operation 2). Since both these operations are
stand-alone, and each has a child, their execution starts with the children (4 and 6).
5.
For each distinct value in the
deptno
column returned by operation 2, operation 4 scans
the
emp
table and applies the
"E2"."DEPTNO"=:B1
filter predicate. In doing so over the
three executions, it extracts 14 rows and passes them to its parent (3).
6.
Operation 3 computes the average salary of the rows passed to it from operation 4 and
returns the result to its parent (1).
7.
Operation 6 scans the
emp
table, extracts 14 rows, and passes them to its parent (5). Note
that this subquery is executed only once because it's not correlated to the main query.
8.
Operation 5 computes the average commission of the rows passed to it from operation 6
and returns the result to its parent (1).
9.
Operation 1 updates each row passed by operation 2 with the value returned by its other
children (3 and 5) and passes to its parent (0) the number of rows that were updated.
Note that even if the
UPDATE
statement modifies the 14 rows,
A-Rows
column shows 0 for
this operation.
10.
Operation 0 sends the number of rows that were updated to the caller.
The CONNECT BY WITH FILTERING Operation
This operation is used to process hierarchical queries. It's characterized by two child operations. The first one is used
to get the root(s) of the hierarchy, and the second one is executed once for each level in the hierarchy.
