Hash Joinとは
Hash Joinの原理は、左テーブルのデータを使ってハッシュテーブルを構築し、右テーブルがそのハッシュテーブルを検索することで結合演算を完了することです。その実行プロセスの擬似コードは以下のとおりです。
step 1 build hash table：
for row_1 in (select * from left_table)
loop
hash_value = HASH(row_1)
insert_hash_table(hash_value, row_1)
end loop
step 2 probe hash table:
for row_2 in (select * from right_table)
loop
hash_value = HASH(row_2)
row = lookup_hash_table(hash_value, row_2)
if match join condition(row, row_2)
then
output (row, row_2)
end if
end loop
Hash Joinの計画は以下のとおりです。
Query Plan:
======================================
|ID|OPERATOR |NAME|EST. ROWS|COST |
--------------------------------------
|0 |HASH JOIN | |99000 |194200|
|1 | TABLE SCAN|t1 |100000 |41911 |
|2 | TABLE SCAN|t2 |100000 |41911 |
======================================
Outputs & filters:
-------------------------------------
0 - output([1]), filter(nil),
equal_conds([t1.c1 = t2.c1]), other_conds(nil)
1 - output([t1.c1]), filter(nil),
access([t1.c1]), partitions(p0)
2 - output([t2.c1]), filter(nil),
access([t2.c1]), partitions(p0)
オプティマイザーはいつHash Joinを選択するか
一般的に、結合するデータ量が大きい場合（または結合する小さなテーブルの割合が高い場合）、オプティマイザーはHash Joinを検討します。また、等価結合である必要があります。
比較的小さいデータセットでは、メモリにマテリアライズされたデータが少なく、Hash Joinのパフォーマンスが最も優れています。この場合、結合コストは2つのデータセットに対して1回の読み取りだけです。ハッシュテーブルはメモリ内にあるため、データベースはロックを介してデータ行にアクセスする必要がありません。この技術は、データベースバッファキャッシュ内のブロックを繰り返しロックして読み取る必要性を回避することで、論理I/Oを削減します。
データセットが大きく、メモリがデータを格納するには不十分な場合、データベースはデータソースをパーティション化し、パーティションごとに結合を行います。これにより、大量のソートエリアメモリと一時テーブル領域のI/Oが使用されます。この方法にも効率的なシナリオがあります。特に、データベースが並列クエリを使用する場合です。
オプティマイザーによるHash Joinの使用を制御する方法
最も直接的な制御方法は、ヒントを使用して結合アルゴリズムを指定することです。USE_HASH ヒントを使用することで、オプティマイザーにHash Joinアルゴリズムの使用を指示できます。通常は LEADING ヒントも併用する必要があります。これは、Hash Joinアルゴリズムを指定しても計画のコストが必ずしも最も低いわけではなく、他のコストが低い計画（結合順序が異なる場合）によって上書きされる可能性があるためです。USE_HASH のパラメータは結合の右テーブルです。
使用例。デフォルトでは、オプティマイザーは自動的にNested Loops Joinsアルゴリズムを選択します。
explain select 1 from t1, t2 where t1.c1 = t2.c1;
Query Plan:
============================================
|ID|OPERATOR |NAME|EST. ROWS|COST |
--------------------------------------------
|0 |NESTED-LOOP JOIN| |99000 |4120845|
|1 | TABLE SCAN |t1 |100000 |41911 |
|2 | TABLE GET |t2 |1 |40 |
============================================
Outputs & filters:
-------------------------------------
0 - output([1]), filter(nil),
conds(nil), nl_params_([t1.c1])
1 - output([t1.c1]), filter(nil),
access([t1.c1]), partitions(p0)
2 - output([1]), filter(nil),
access([t2.c1]), partitions(p0)
オプティマイザーにHash Joinアルゴリズムの使用を制御させるには、ヒントを使用して制御できます。
explain select /*+leading(t1 t2) use_hash(t2)*/ 1 from t1, t2 where t1.c1 = t2.c1;
Query Plan:
======================================
|ID|OPERATOR |NAME|EST. ROWS|COST |
--------------------------------------
|0 |HASH JOIN | |99000 |194200|
|1 | TABLE SCAN|t1 |100000 |41911 |
|2 | TABLE SCAN|t2 |100000 |41911 |
======================================
Outputs & filters:
-------------------------------------
0 - output([1]), filter(nil),
equal_conds([t1.c1 = t2.c1]), other_conds(nil)
1 - output([t1.c1]), filter(nil),
access([t1.c1]), partitions(p0)
2 - output([t2.c1]), filter(nil),
access([t2.c1]), partitions(p0)
Hash Joinアルゴリズムを効果的に利用するために、オプティマイザーを制御する際には以下の2点に注意する必要があります：
- データ量が比較的小さいデータソースを使用してハッシュテーブルを構築するようにしましょう。つまり、ドライバーテーブルとして使用します。
- 非等価結合ではHash Joinアルゴリズムを使用できません。