Database Reference
In-Depth Information
-----------------------------------------------
| Id | Operation | Name |
-----------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | MERGE JOIN | |
| 2 | SORT JOIN | |
| 3 | TABLE ACCESS FULL | T2 |
|* 4 | SORT JOIN | |
|* 5 | TABLE ACCESS BY INDEX ROWID| T1 |
| 6 | INDEX FULL SCAN | T1_PK |
-----------------------------------------------
4 - access("T1"."ID"="T2"."T1_ID")
filter("T1"."ID"="T2"."T1_ID")
5 - filter("T1"."N"=19)
The
SORT JOIN
operation is required because the
MERGE JOIN
operation needs to access the memory structure
associated to the right input to check whether rows fulfilling the join condition exist. And that access must be
performed in a memory structure that not only contains the data in a specific order, but also allows performing
efficient lookups based on the join condition.
Cartesian products based on a merge join are executed differently. Compared to all other cases described in
this section, the main optimization applied to them is that data doesn't need to be sorted. The reason is simple:
no join condition exists, and therefore it's not possible to sort the data according to the columns referenced in that
nonexistent condition. As a result, idependently of the access paths used to get data, no
SORT JOIN
operations are
required. For the right input, it's nevertheless necessary to store the data in a memory structure. For that purpose,
a
BUFFER SORT
operation (which, despite its name, doesn't sort the data) is used. The following example illustrates
such a case:
SELECT /*+ ordered use_merge(t2) */ *
FROM t1, t2
-------------------------------------
| Id | Operation | Name |
-------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | MERGE JOIN CARTESIAN| |
| 2 | TABLE ACCESS FULL | T1 |
| 3 | BUFFER SORT | |
| 4 | TABLE ACCESS FULL | T2 |
-------------------------------------
Four-Table Join
The following execution plan is an example of a typical left-deep tree implemented with merge joins (refer to
Figure
14-2
for a graphical representation). The example also shows how to force a merge join by means of the
leading
and
use_merge
hints. Note that the
leading
hint supports several tables:
SELECT /*+ leading(t1 t2 t3 t4) use_merge(t2 t3 t4) */ t1.*, t2.*, t3.*, t4.*
FROM t1, t2, t3, t4
