[Oracle] table join

In the OLTP system, nested connections account for about 70%, hash connections account for 20%, and merge sorted connections account for 10%.

Nested connection

Algorithm: Nested join selects a small table as the driver table from two tables, and a large table as the driver table. First, access the driver table (only once ), then, access the driver table multiple times based on the number of rows returned by the driver table (the number of times the driver table accesses is equal to the number of rows returned by the driver table ).

Based on the above algorithm, we can know that to make the nested connection performance better, we must satisfy the following three conditions at the same time:

1) the number of rows returned by the driver table must be small enough (reducing the number of visits to the driver table );

2) The restrictions on the driver table must be indexed (Improving the performance of the driver table );

3) The connection conditions of the driven table must be indexed (Improving the Performance of accessing the driven table ).

As mentioned above, nested connections account for 70% in the OLTP system, because the OLTP system returns less data and is suitable for nested connections. Another reason is that there is almost no restriction on nested connections, all operations can be connected using nested connections, unlike hash connections and merge sort connections.

Hash connection

Algorithm: Hash join selects a small table from two tables as the driving table. The large table is the driving table. A hash table is constructed based on the driving table, then, each row of the driven table performs hash matching in the hash table. The driver table and the driver table are accessed once each time.

Based on the above algorithm, we can know that hash connectionsOnly supports equivalent connections.

To ensure good hash connection performance, make sure that the driver table is small enough so that the memory PGA can put down the hash table.

Merge sort join algorithm: The merge sort join algorithm sorts two tables first, and then compares and merges the tables according to the sorting results. The merging and sorting links do not have the concepts of the driver table and the driver table. There are many restrictions on merging sort connections. Supported values include <, but not like and <>
The maximum performance overhead of this connection method is 2 sorting, so it is rarely used in OLTP systems.