Database Reference
In-Depth Information
As described in Chapter 10, the
MERGE JOIN
operation is of type unrelated combine. This means the two children
are processed at most once and independently of each other. Provided that both inputs return data, the processing of
the previous execution plan can be summarized as follows:
t1
are read through a full scan, the
n = 19
restriction is applied, and the
resulting rows are sorted according to the columns used as the join condition (
id
).
•
All rows in table
t2
are read through a full scan and sorted according to the columns used as
the join condition (
t1_id
).
The two sets of data are joined together, and the resulting rows are returned. Note that the join
•
All rows in table
•
itself is straightforward because the two sets of data are sorted according to the same value
(the columns used in the join condition).
It's interesting to notice in the previous execution plan that the join condition is applied by the
SORT JOIN
operation of the right input, not by the
MERGE JOIN
operation as you might expect. This is because for each row
returned by the left input, the
MERGE JOIN
operation accesses the memory structure associated to the right input to
check whether rows fulfilling the join condition exist.
■
The key difference between the
SORT JOIN
operation and the
SORT ORDER BY
operation is that the former
always performs a binary sort, but the latter, depending on the nLS configuration, can perform either a binary or a
linguistic sort.
Note
The most important limitation of the
MERGE JOIN
operation (like for the other unrelated-combine operations)
is its inability to take advantage of indexes to apply join conditions. In other words, indexes can be used only as an
access path to evaluate restrictions (if specified) before sorting the inputs. Therefore, in order to choose the access
path, you have to apply the methods discussed in Chapter 13 to both tables. For instance, if the
n = 19
restriction
provides strong selectivity, it could be useful to create an index to apply it:
CREATE INDEX t1_n ON t1 (n)
In fact, with this index in place, the following execution plan might be used. You should notice that table
t1
is no
longer accessed through a full table scan:
----------------------------------------------
| Id | Operation | Name |
----------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | MERGE JOIN | |
| 2 | SORT JOIN | |
| 3 | TABLE ACCESS BY INDEX ROWID| T1 |
|* 4 | INDEX RANGE SCAN | T1_N |
|* 5 | SORT JOIN | |
| 6 | TABLE ACCESS FULL | T2 |
----------------------------------------------
4 - access("T1"."N"=19)
5 - access("T1"."ID"="T2"."T1_ID")
filter("T1"."ID"="T2"."T1_ID")