Hash join implemented by ORACLE connection

Hash join implemented by ORACLE connection
Recently, I have read some information about HASH JOIN, which is summarized as follows. Hash join is a table JOIN method introduced by oracle 7.3 to supplement nested loop and sort merge. Hash join has the following features: 1. it can only run in CBO Mode 2. because the hash function is used for calculation, it is only applicable to equivalent operations 3. sensitive to the size of hash_area_size, too large or too small will affect the execution efficiency. ORACLE's automatic memory management mechanism is recommended; 4. hash join is a CPU-intensive operation (used for hash operations, etc.). Especially in parallel mode, the effect on the cpu is more obvious. Therefore, when the cpu resources are very tight, we can try to block hashjoin to improve the overall performance of the database. 5. As the data of small tables, it is best to have a evenly distributed hash join-related parameter in the Link column: 1. HASH_JOIN_ENABLED has become the hidden function _ hash_join_enabled in the 10G version. We can modify this parameter at the system and session levels. 2. the HASH_AREA_SIZE parameter controls the size of the memory zone available for hash join. By default, this parameter is twice the size of SORT_AREA_SIZE. We do not recommend that you manually modify the value of this parameter in ORACLE, to improve the performance of hashjoin, it is best to ensure that the data of the entire small table set can be fully put into the memory, but it is of little significance to continue to add the hash erea after it is completely put into the memory, and may reduce the efficiency (for example, the consumption is in memory management ). In a single session, there may be multiple hash areas at the same time, so an SQL query may have multiple hash joins at the same time. 3. worderea_size_policy4.pga_aggregate_target this parameter specifies whether automatic memory management is used for pga memory management. When automatic memory management is adopted, the memory occupied by a single session cannot exceed 5% of pga_aggregate_target. If parallel mode is used, the total number of parallel processes cannot exceed 30%. To use large memory for hash join, you can set wordarea_size_police to manual mode to manually specify the memory size of each area. 5. The HAHS_MULTIBLOCK_IO_COUNT parameter determines the number of data blocks that are read into the hash area each time. Therefore, it will affect the IO performance. In versions earlier than 8.0, the default value is 1. In 8i and later versions, the default value is 0. It is generally set to 1-(65536/DB_BLOCK_SIZE ). In 9i, this parameter is a hidden parameter: _ HASH_MULTIBLOCK_IO_COUNT, which can be queried and modified in table x $ ksppi. In MTS, this parameter does not work (only 1 is used ). Its maximum value is affected by the OS I/O bandwidth and DB_BLOCK_SIZE. It cannot be greater than MAX_IO_SIZE/DB_BLOCK_SIZE. In 8i and later versions, if this value is set to 0, it means that Oracle automatically calculates this value for each query. This value has a great impact on IO performance. Therefore, it is recommended that you do not modify this parameter and use the default value 0 so that Oracle can calculate this value on its own. To set this value, make sure that the following inequality is true: R/M <Po2 (M/C) Where R indicates the size of a small table; M = HASH_AREA_SIZE * 0.9; po2 (n) is the 2nd power of n; C = HASH_MULTIBLOCK_IO_COUNT * DB_BLOCK_SIZE. Hash join process: a basic idea of the Hash join algorithm is to follow the small row sources (called build input. We can remember a small table as S and a large table as B) create a hash table that can exist in the hash area memory, and then use a large row sources (called probe input) to test the hash table created earlier. (The small and large tables here are not based on the physical size of the table, but on the data size queried under the current query condition ). Step 1: partition small table data. The partition size meets the following conditions: (Number of Partitions) * C <= Favm * M where C is Cluster size, its value is DB_BLOCK_SIZE * HASH_MULTIBLOCK_IO_COUNT; Favm is the percentage of memory available in hash area, usually about 0.8; M is the size of Hash_area_size. Step 2: read some small table S data and calculate the values of hash1 and hash2 for each piece of data. hash1 and hash2 are obtained by different hash algorithms respectively, hash1 is used to map records and partitions, while hash2 maps records to buckets. Each partition contains multiple buckets. Creating a hash table is based on hash2. Step 2: update the bitmap vector based on the hash value and set the ID position of the corresponding bucket to Step 1: Write the record to the corresponding partition. If the memory is insufficient, write the partition with the largest data volume in the memory to the hard disk. If the partition has been written to the hard disk before, the record is directly written to the corresponding partition on the hard disk, the written data includes columns and hash2. Step 2: Read the remaining part of small table S, 2-4 until all small table S is read. Step 1: Sort all S partitions by data size, and then read the partitions into the memory in ascending order so that the memory can contain the most partitions. Step 2: Create a hash table based on hash2. Step 2: Read the record of Table B, calculate hash1 and hash2, and query the bitmap vector through hash2. If the corresponding identification bit is 0, the record is discarded, otherwise, continue (this is called bitmap filtering "). Step 2: Map records to corresponding partitions Based on hash1 for filtered data. If the partitions are in the memory, link hash2 to the hash table, query the corresponding table 1 record and write the connection result to the hard disk or return it to the client. If the corresponding partition is on the hard disk, the record is written to the large table partition corresponding to the small table partition. Step 2: Continue to read the large table until table B has finished reading the table. Step 2: hash the small table partitions stored in the hard disk and the large table partitions. At this time, the small table partition is not necessarily the driving table, instead, select a partition with a small amount of data in the two partitions as the driving partition. This mechanism is called "dynamic role swap ". Step 2: repeat 11 until all partitions are processed. Note: If a driver partition is too large to be loaded into the memory, oracle will read part of the partition data in batches and create a hash table, none of the driver data will match all the data in the test partition, resulting in multiple reads of the test partition, resulting in performance degradation. This is called nested-loop hash join. If nested-loop hash join occurs, we can reduce the value of HAHS_MULTIBLOCK_IO_COUNT and increase the number of partitions to solve this problem. Three modes: optimal: In this case, the hash_area_size can fully load the driver table. Therefore, no partition is written to the hard disk, which is the most efficient. Onepass: At this time, the hash_area_size can accommodate one or more partitions, but cannot load all the driver tables. Therefore, some partitions are written to the hard disk, resulting in reduced efficiency. Multipass: At this time, the hash_arem_size is not enough to load the size of a single partition. Therefore, the nested-loop hash join is generated, which is the most complicated and worst-performing hash join. The performance will decrease sharply.