Summary of three Oracle table join algorithms

Source: Internet
Author: User

Oracle has three table connection technologies: Nested connection, merge connection, and hash connection.

1. nested loop Join)

Nested connection divides the dataset to be processed into an External Loop (driving data source) and an internal loop (Driven Data Source). An External Loop is executed only once (first ), the number of inner loop executions equals to the number of datasets executed in the outer loop.

The advantage of this connection is that the memory usage is very small.

If the drive data source is limited and the drive table has an index on the connection column, this connection method is efficient.

This connection mode is common in OLTP systems.

2. Sort and Merge Join (Sort Merge Join)

As the name suggests, sort and merge means to sort connected datasets separately and then merge them. The execution process is roughly as follows: sort the dataset of table A and save the sorting result in Workspace; sort the data sets of Table B and save the sorting results in Workspace B. Finally, merge the data in Workspace A and workspace B.

For this connection method, the sorting overhead is very large. Memory parameters related to the sorting workspace include sort_area_size and sort_area_retained_size, all of which are in PGA.

3. Hash Join)

The two datasets processed by the hash connection are called build input and probe input respectively. Each row of records of the constructed input is used to construct a hash table, test each row of input records to test the hash table to find records that meet the connection conditions.

A small table is used as the construction input, and a large table is used as the test input. In this way, the hash join efficiency is relatively high. In the execution plan, a small input table is located at the beginning, the larger test table is later.

Hash connections can only be performed under equal connections.

The memory parameters related to the hash table workspace are hash_area_size, which is also in PGA.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.