Oracle Database - Join Operation / Method / Mechanisms

Joins are statements that retrieve data from more than one table. To perform a join and retrieve data, Oracle has several possible operation / method / algorithm.

There are only three join mechanisms used by Oracle:

• Sort Merge Joins
• hash_join
• Nested Loop Joins

All three join methods are nested loop joins with different startup cost.

What about :

• sort_join
• cartesian_join

The query optimizer considers the following when choosing an execution plan :

• The optimizer first determines whether joining two or more tables definitely results in a row source containing at most one row. The optimizer recognizes such situations based on UNIQUE and PRIMARY KEY constraints on the tables. If such a situation exists, then the optimizer places these tables first in the join order. The optimizer then optimizes the join of the remaining set of tables.
• For join statements with outer join conditions, the table with the outer join operator must come after the other table in the condition in the join order. The optimizer does not consider join orders that violate this rule. Similarly, when a subquery has been converted into an SQL - Anti-join or SQL - Semijoins, the tables from the subquery must come after those tables in the outer query block to which they were connected or correlated. However, hash antijoins and semijoins are able to override this ordering condition in certain circumstances.

With the query optimizer, the optimizer's choice of join orders can be overridden with the ORDERED hint. If the ORDERED hint specifies a join order that violates the rule for an outer join, then the optimizer ignores the hint and chooses the order. Also, you can override the optimizer's choice of join method with hints.

• Joins