ハッシュ結合とは
ハッシュ結合の原理は、左側のテーブルのデータを用いてハッシュテーブルを構築し、右側のテーブルがそのハッシュテーブルを照合することで結合演算を実現するものです。その実行プロセスの擬似コードは以下の通りです。
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
ハッシュ結合の計画は以下のようになります。
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)
オプティマイザーはいつハッシュ結合を選択するか
一般的に、結合するデータ量が大きい場合（または結合する小さなテーブルの割合が非常に高い場合）、オプティマイザーはハッシュ結合を検討します。ただし、結合は等価結合である必要があります。
比較的小規模なデータセットでは、比較的少ないメモリでマテリアライズされたデータを使用し、ハッシュ結合のパフォーマンスが最も優れています。この場合、結合コストは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アルゴリズムを使用できません。