Shulou(Shulou.com)05/31 Report--

What are the principles and matters needing attention of oracle hash join? in view of this problem, this article introduces the corresponding analysis and solutions in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible way.

7.3 introduced, used only for CBO and equivalent connections. The implied parameter _ hash_join_enabled defaults to true, which can be overridden by use_hash even if it is false

Oracle determines the number of hash partition based on hash_area_size/db_block_size/_hash_multiblock_io_count. The hash table consists of several hash partition, and each partition contains multiple hash bucket.

Tables small and big, the former will be selected as the driver table, assuming that the result set is s, and the latter is b

Construction driver table

1 iterate through s, and do hash operation on each record according to the connection column; use two hash functions, temporarily called func_1/func_2, whose hash values are value1 and value2

(2) allocate bucket according to value1 and store s and value2 in it; you only need to query column / join columns without the need for entire row records; when constructing hash partition, there is a bitmap corresponding to each partition, indicating whether the bucket to which the partition belongs has a record.

3 if s > hash_area_size, the partition containing the most records is stored in temp and repeated until it is completed

4 sort the hash partition by the number of records. When s > hash_area_size, try to keep the smaller partition in memory.

Construct driven table

Iterate through b 5 times, in the same way as step 1

6 match the bucket of s according to value1, then traverse all its records and verify whether the connection columns are equal; if successful, return records; if bucket is not found in memory, first visit bitmap, if the number of records is > 0, temporarily write the corresponding records in b to temp, if = 0, skip directly, that is, bitmap filtering; repeat until all records in memory are completed

7 deal with si and bj in temp, pair sn/bn with equal partition number, drive table with a small number of records, and construct hash table; repeatedly with value2 until completion

Matters needing attention

1 the selectivity of the join column of the driver table is high. If there are too many records in a bucket, the traversal will consume CPU seriously and the logical reading is not high (located in PGA). Please refer to http://www.dbafan.com/blog/?p=151

2 the driver table should be as small as possible, preferably all can be loaded into memory hash_area_size

10104 event

Track hash join, record hash partition/bucket and how many records per bucket

Hint

Use_hash (table_1 table_2) is equivalent to use_hash (table_1) + use_hash (table_2), that is, it does not determine the connection order.

No_swap_join_inputs (rowsource_alias) and swap_join_inputs (rowsource_alias) can specify a driven table and a driven table, which cannot be swap

Select / * + leading (table_1 table_2) use_hash (table_2) no_swap_join_inputs (table_2) * / *

From t1 table_1, t2 table_2

Where table_1.n1 = table_1.n1

| | Id | Operation | Name | Rows | Bytes | Cost | |

| | 0 | SELECT STATEMENT | | 45000 | 16m | 44 | |

| | * 1 | HASH JOIN | | 45000 | 16m | 44 | |

| | 2 | TABLE ACCESS FULL | T1 | 3000 | 547K | 14 |

| | 3 | TABLE ACCESS FULL | T2 | 3000 | 547K | 14 |

Select / * + leading (table_1 table_2) use_hash (table_2) swap_join_inputs (table_2) * / *

From t1 table_1, t2 table_2

Where table_1.n1 = table_1.n1

| | Id | Operation | Name | Rows | Bytes | Cost | |

| | 0 | SELECT STATEMENT | | 45000 | 16m | 44 | |

| | * 1 | HASH JOIN | | 45000 | 16m | 44 | |

| | 2 | TABLE ACCESS FULL | T2 | 3000 | 547K | 14 |

| | 3 | TABLE ACCESS FULL | T1 | 3000 | 547K | 14 |

The answers to the questions about the principles and precautions of oracle hash join are shared here. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel to learn more about it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.