Summarization of three kinds of table join algorithms in Oracle

Oracle has three kinds of table connectivity technologies, namely, nested joins, merge joins, and Hash joins. The following is a detailed analysis of the three kinds of table connection algorithm, the need for friends can refer to the

1. Nested loop joins (NESTED Loop join)
A nested connection divides the data set to be processed into an outer loop (a driving data source) and an inner loop (driven data source), which executes only once (executes first), and the number of times the loop executes is equal to the number of data sets performed outside the loop.

The advantage of this connection is that there is very little memory usage.
This connection is efficient if the drive data source is limited and the driven table has the appropriate index on the connection column.
This type of connection is common to OLTP systems.

2. Sort merge joins (sort merge Join)
As the name suggests, a sort merge is the first to sort the connected datasets, and then merge, which is done roughly by sorting the dataset of Table A, sorting the results in workspace A, sorting the dataset in table B, and sorting the results in Workspace b , and finally merge the data in workspace A and B.

For this connection method, the cost of sorting is very large, and the memory parameters associated with the sort workspace are: Sort_area_size and sort_area_retained_size, all in the PGA.

3. Hash joins (hash join)
the two datasets processed by the hash join are called construction input (build input) and probe input (probe input), each row of the constructed input is used to construct the hash table, and each row of the probe input detects the hash table to find the records that meet the join conditions.
The smaller table as the construction input, the larger table as the probe input, so that the hash connection efficiency is relatively high, in the execution plan, the smaller input table in front, the larger probe table in the back.
A hash connection can only be performed under the condition of an equal connection.
the memory parameters associated with the hash table workspace are: Hash_area_size, also in the PGA.