Database Reference
In-Depth Information
AND t3.id = t4.t3_id
AND t1.n = 19
----------------------------------------
| Id | Operation | Name |
----------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | MERGE JOIN | |
| 2 | SORT JOIN | |
| 3 | MERGE JOIN | |
| 4 | SORT JOIN | |
| 5 | MERGE JOIN | |
| 6 | SORT JOIN | |
|* 7 | TABLE ACCESS FULL| T1 |
|* 8 | SORT JOIN | |
| 9 | TABLE ACCESS FULL| T2 |
|* 10 | SORT JOIN | |
| 11 | TABLE ACCESS FULL | T3 |
|* 12 | SORT JOIN | |
| 13 | TABLE ACCESS FULL | T4 |
----------------------------------------
7 - filter("T1"."N"=19)
8 - access("T1"."ID"="T2"."T1_ID")
filter("T1"."ID"="T2"."T1_ID")
10 - access("T2"."ID"="T3"."T2_ID")
filter("T2"."ID"="T3"."T2_ID")
12 - access("T3"."ID"="T4"."T3_ID")
filter("T3"."ID"="T4"."T3_ID")
The processing isn't really different from the two-table join discussed in the previous section. However, it's
important to emphasize that data is sorted several times because each join condition is based on different columns.
For example, the data resulting from the join between table
t1
and table
t2
, which is sorted according to the
id
column of table
t1
, is sorted again by operation 4 according to the
id
column of table
t2
. The same happens with the
data returned by operation 3. In fact, it has to be sorted according to the
id
column of table
t3
. In summary, to process
this type of execution plan, six sorts have to be performed, and all of them have to be performed before being able to
return a single row.
Work Areas
To process a merge join, up to two work areas in memory are used to sort data. If a sort is completely processed in
memory, it's called an
in-memory sort
. If a sort needs to spill temporary data to the disk, it's called an
on-disk sort
.
From a performance point of view, it should be obvious that in-memory sorts should be faster than on-disk sorts. The
next sections discuss how these two types of sorts work. I also discuss how to recognize which one is used to process a
SQL statement, based on the output of the
dbms_xplan
package.
