Database Reference
In-Depth Information
Outer Joins
The three basic join methods described in the previous sections support outer joins. When an outer join is executed,
the only difference visible in the execution plan is the
OUTER
keyword that is appended to the join operation. To illustrate,
the following SQL statement is executed, because of hints, with an outer hash join. Notice that even if the SQL statement
is written with the new join syntax, the predicate uses the Oracle proprietary syntax based on the
(+)
operator:
SELECT /*+ leading(t1) use_hash(t2) */ *
FROM t1 LEFT JOIN t2 ON t1.id = t2.t1_id
-----------------------------------
| Id | Operation | Name |
-----------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | HASH JOIN
OUTER
| |
| 2 | TABLE ACCESS FULL| T1 |
| 3 | TABLE ACCESS FULL| T2 |
-----------------------------------
1 - access("T1"."ID"="T2"."T1_ID"
(+)
)
Except for hash joins that are right outer joins, the preserved table (for example, the
t1
table in the previous
SQL statement) must be the left input of the join operation. The following execution plan, based on the same SQL
statement as the previous one, illustrates this. In practice, the query optimizer chooses to build the hash table on the
smallest result set. Of course, this is useful to limit the size of the work area. In this case, because table
t1
is smaller
than table
t2
, for illustration purposes it's necessary to force the query optimizer to swap the two join inputs with the
swap_join_inputs
hint:
SELECT /*+ leading(t1) use_hash(t2) swap_join_inputs(t2) */ *
FROM t1 LEFT JOIN t2 ON t1.id = t2.t1_id
--------------------------------------
| Id | Operation | Name |
--------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | HASH JOIN
RIGHT OUTER
| |
| 2 | TABLE ACCESS FULL | T2 |
| 3 | TABLE ACCESS FULL | T1 |
--------------------------------------
1 - access("T1"."ID"="T2"."T1_ID"(+))
Consequently, whenever the query optimizer has to generate an execution plan for a SQL statement containing
an outer join, except with hash joins, its options are limited.
