Oracle Database - Join (Operation|Method|Mechanism|Algorithm)
About
Joins are data operation / method / algorithm in order to match data from more than one table.
They all require sorting and then matching aspects of a dataset.
There are only three join mechanisms used by Oracle:
What about :
Choosing the good join method depends of the data statistics. Then only the database system can make this decision and not the developers.
NESTED LOOPS is a row operation, returning the first records to the next operation quickly.
MERGE JOIN is a set operation; it does not return records to the next operation until all of the rows have been processed.
A join operation can input only two row sources (row set).
Articles Related
How the Query Optimizer Chooses Execution Plans for Joins
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.
Documentation / Reference