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")
 
Search WWH ::




Custom Search