ネステッド・ループ結合とは
ネステッド・ループ結合とは、2つのデータソースを関連付ける操作を2層のループで実行するものです。例えば、T1 Nested Loop Join T2 の疑似コードがあります。
for row_1 IN (select * from T1 where xxx)
loop
for row_2 IN (select * from T2 where xxx)
loop
if match join condition(row_1, row_2)
then
output (row_1, row_2)
end if
end loop
end loop
最初のループは左側のテーブルの各行をイテレーションし、次に右側のテーブルの各行をイテレーションして、2行のデータが結合条件の制約を満たすかどうかをチェックします。条件を満たす場合は、結合後のデータ行を出力します。
ネステッド・ループ結合の計画は以下のようになります。
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)
オプティマイザーはいつネステッド・ループ結合を選択するのか
データベースが小さなデータセットと大きなデータセットを結合し、かつ大きなデータセットにおいて結合条件を満たすデータ量が非常に少ない、あるいは条件を満たす最初の1行のみを出力する必要がある場合、ネステッド・ループ結合は非常に有用です。
通常、結合条件にインデックスがあり、かつドライバー表が小さなテーブルの場合、ネステッド・ループ結合は最も効果的です。データソースに1行しかない場合、例えば主キー値に対する等価フィルタリング検索（例えば、WHERE id=101）では、結合は単純な検索となります。オプティマイザーは常に最小のデータソースを左側に配置し、それをドライバー表とするように試みます。
オプティマイザーがネステッド・ループ結合を使用するかどうかを決定するには、多くの要因が影響します。例えば、ヒント、結合条件に等価結合条件があるかどうか、結合条件にインデックスが存在するかどうか、およびドライバー表が最大でも1行のデータを出力するかどうかなどです。クエリにネステッド・ループ結合に関するヒントが存在する場合、オプティマイザーはそのヒントの指示に従ってネステッド・ループ結合を使用するかどうかを完全に決定します。ヒントが指定されていない場合、結合条件に等価結合条件が存在し、結合条件に一致するインデックスがなく、かつドライバー表が複数行のデータを出力する場合、オプティマイザーは決してネステッド・ループ結合を選択しません。それ以外の場合、オプティマイザーはネステッド・ループ結合の計画を生成し、計画のコストを計算します。最終的にはコストに基づいてネステッド・ループ結合を使用するかどうかを決定します。
さらに、結合条件に等価結合が存在しない場合、または結合条件がない場合、オプティマイザーは他の選択肢のアルゴリズムがないため、必ずネステッド・ループ結合を選択します。
オプティマイザーによるNested Loops Joinの使用をどのように制御するか
最も直接的な制御手段は、ヒントを使用して結合アルゴリズムを指定することです。USE_NL を使用してオプティマイザーにNested Loops Joinアルゴリズムを使用させることができます。通常は LEADINGhint も併用する必要があります。これは、Nested Loops Joinアルゴリズムを指定した後でも、計画のコストが必ずしも最も低いわけではなく、他のコストの低い計画（結合順序が異なる場合）によって上書きされる可能性があるためです。USE_NL のパラメータは結合の右側のテーブルです。
使用例。デフォルトでは、オプティマイザーはHash Joinアルゴリズムを自動的に選択します。
explain select 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)
オプティマイザーによるNested Loops Joinアルゴリズムの使用を制御するために、ヒントを使用して制御することができます。
explain select /*+leading(t1 t2) use_nl(t2)*/ 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)
Nested Loops Joinアルゴリズムをより効果的に利用するために、オプティマイザーを制御する際には以下の3点に注意する必要があります：
- ドライバーテーブルとして、できるだけデータ量が少ないデータソースを使用します。
- 結合条件がインデックスにマッチしない場合は、新しいインデックスを作成してNested Loops Joinアルゴリズムと組み合わせて使用する必要があります。
- right join、full join、right semi join、right anti joinにはNested Loops Joinアルゴリズムを使用できません。