Oracle Database - Join (Operation|Method|Mechanism|Algorithm)

Card Puncher Data Processing


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).

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

Discover More
Bi Server Architecture With Client
OBIEE - Stitch Join (Intern Join Operations)

Stitch Join are join operations performed by OBIEE with its intern engine: the Oracle BI Server’s join engine. The BI Server creates TMP (temporary) files in the /tmp directory The Oracle BI Server’s...
Card Puncher Data Processing
Oracle Database

Documentation about the Oracle database
Card Puncher Data Processing
Oracle Database - Join Order

To execute a statement that joins more than two tables, Oracle joins two of the tables and then joins the resulting row source to the next table. This process is continued until all tables are joined into...
Card Puncher Data Processing
Oracle Database - SORT JOIN (or SORT MERGE JOIN)

SORT JOIN sorts a set of records onthe join column. It is used in combination with a a MERGE JOIN operation in order to perform a . Show all comments by “Rixt” on any blog post by “Nico”...
Card Puncher Data Processing
Oracle Database - The Query Plan Generator

The main function of the plan generator is to try out different possible plans for a given query and pick the one that has the lowest . The plan for a query is established by first generating subplans...
Card Puncher Data Processing
Oracle Partition - Partition-Wise Join (PWJ)

The most fundamental parallel execution optimization is a partition-wise join. If two rather large tables are often joined together in SQL statements, consider the potential benefits of partition-wise...
Data System Architecture
SQL - Joins (Home)

in SQL. A join is a SQL clause statement which define the set operation such as: intersection, union, that the database must perform between two or more relation (table, view, query, ...). The...
Oracle Database Sql Processing
SQL Engine - Row source generator

The row source generator performs the row source generation steps of an SQL processing. It receives the optimal execution plan from the optimizer and produces an iterative plan, called the query plan,...

Share this page:
Follow us:
Task Runner