Three types of table connectivity technologies for Oracle

Oracle has three kinds of table connectivity technologies, namely, nested joins, merge joins, and Hash joins.

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 dataset, and then merge, which is done by sorting the dataset of Table A, sorting the results in workspace A, and sorting the dataset of Table B. The sorting results are saved in Workspace B, and the data in the workspace A and B is finally merged.

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) Hash connection processing of the two data sets known as construction input (build input) and probe input (probe input), the construction of each row of input to construct a hash table, probe input of each row of the hash table to explore, To find records that meet the connection criteria.

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.

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/