[Oracle] Summary of three table join algorithms

[Oracle] Three table connection algorithms summary Oracle has three table connection technologies: Nested connection, merge connection, and hash connection. 1. nested loop Join divides the dataset to be processed into an External LOOP (driving data source) and an internal LOOP (driven data source ), the External Loop is executed only once (first executed), and the number of inner loops is equal to the number of datasets executed in the External 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 Merge joins (Sort Merge Join), as the name suggests, Sort Merge sorts connected datasets first and then Merge them. The execution process is roughly as follows: Sort the data sets of Table, the sorting result is saved in Workspace A. The sorting result is saved in Workspace B. The data in Workspace A and workspace B is merged. 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. the two data sets processed by Hash Join are called build input and probe input respectively. Each row of input records is constructed 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.