Oracle Database-Table connection (hash, merge, nest loop)

Oracle Database-Table connection method (hash, merge, nest loop) nested loop is a good choice for a small subset of connected data. The nested loop is used to scan a table. Every time a record is read, it is searched in another table based on the index. Without an index, it is generally not nested loops. Generally, in the nested loop, the result set of the driver table meeting the condition is not large, and the connected fields of the driver table must be indexed, so that nstedloop is used. If the driver table returns too many records, it is not suitable for nested loops. If the join field does not have an index, hash join is applicable because no index is required. You can use the ordered prompt to change the default drive table of CBO. You can use the USE_NL (table_name1 table_name2) prompt to force the nested loop. Hash join hash join is a common method for CBO to connect large datasets. The optimizer scans small tables (or data sources), creates a hash table in memory using the connection key (that is, calculates the hash value based on the connection field), and then scans large tables, each time a record is read, it is used to detect the hash table and find the row matching the hash table. When a small table can be fully stored in the memory, the cost is close to the sum of the costs of scanning two tables in the full table. If the table is large and cannot be fully stored in the memory, the optimizer splits the table into several different partitions. If the table cannot be placed in the memory, the optimizer writes the partition to the temporary segment of the disk, in this case, a large temporary segment is required to improve the I/O performance as much as possible. Partitions in the temporary segment must be swapped into the memory for hash join. At this time, the cost is close to the cost and the total cost of scanning a large table with a small table + number of partitions. As for the partition of both tables, the advantage is that parallel query can be used, that is, multiple processes can join different partitions at the same time and then merge them. But complicated. When hash join is used, the HASH_AREA_SIZE initialization parameter must be large enough. If it is 9i, we recommend that you use SQL workspace automatic management, set WORKAREA_SIZE_POLICY to AUTO, and then adjust PGA_AGGREGATE_TARGET. Hash join may have advantages in the following conditions: the connection between two huge tables. The connection between a large table and a small table. You can use the ordered prompt to change the default drive table of CBO. You can use the USE_HASH (table_name1 table_name2) prompt to force hash join. Sort merge join sort merge join operations are generally divided into three steps: Perform table access full for each joined table; and SORT the results of table access full; perform merge join to merge the sorting results. The performance overhead of sort merge join is almost in the first two steps. In general, 9i has rarely appeared since there is no index. Because of its high sorting cost, most of them are replaced by hash join. Generally, hash join works better than sort merge join. However, if the row source has been sorted out, you do not need to sort it when executing sort merge join, in this case, the performance of sort merge join is better than that of hash join. Sort merge join performs better than nested loops when full table scan is more desirable than index range scan and table access through rowid. You can use USE_MERGE (table_name1 table_name2) to force the use of sort merge join. Nested loop join is generally suitable for large and small tables. In addition, large tables have indexes and scan small tables in the whole table. Then, matching records in large tables are extracted based on indexes, and the first record is returned quickly. Merger join sorts the two tables and then matches them. Only after all the records are processed can the results be returned. Hash join calculates the hash value for one table, and then calculates the hash value for the Self-segment join of the other table to find the matching record.