Database Reference
In-Depth Information
■
there are specific cases where the children of the
MERGE JOIN
operation aren't exactly executed according to
the two rules just mentioned. the “Merge Joins” section in Chapter 14 provides information about such specifc cases.
Note
Here's a sample query and its execution plan based on the output generated by the
unrelated-combine.sql
script (see Figure
10-4
for a graphical representation of its parent-child relationships):
SELECT ename FROM emp
UNION ALL
SELECT dname FROM dept
UNION ALL
SELECT '%' FROM dual
-----------------------------------------------------
| Id | Operation | Name | Starts | A-Rows |
-----------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 19 |
| 1 | UNION-ALL | | 1 | 19 |
| 2 | TABLE ACCESS FULL| EMP | 1 | 14 |
| 3 | TABLE ACCESS FULL| DEPT | 1 | 4 |
| 4 | FAST DUAL | | 1 | 1 |
-----------------------------------------------------
Figure 10-4.
Parent-child relationships of the
UNION-ALL
unrelated-combine operation
In this execution plan, the unrelated-combine operation is the
UNION-ALL
. The other three are stand-alone
operations. By applying the rules given earlier, you see that the execution plan carries out the operations as follows:
1.
Operation 0 has one child (1). It can't be the first one being executed.
2.
Operation 1 has three children, and, among them, operation 2 is the first in ascending
order. Therefore, the execution starts with operation 2.
Operation 2 scans the
emp
table and returns 14 rows to its parent (1).
3.
4.
When operation 2 is completely executed, operation 3 is started.
Operation 3 scans the
dept
table and returns four rows to its parent (1).
5.
