ネステッド・ループ結合とは
ネステッド・ループ結合は、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アルゴリズムの使用を指示できます。通常は LEADING ヒントも併用する必要があります。これは、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アルゴリズムを使用できません。