Nested Loops Joinsとは
Nested Loops Joinは、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行のデータが結合条件を満たすかどうかをチェックします。条件を満たす場合は、結合後のデータ行を出力します。
Nested Loops Joinの実行計画は以下のようになります。
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 Joinsを選択する場合
データベースが小さなデータセットと大きなデータセットを結合し、かつ大きなデータセットにおいて結合条件を満たすデータ量が非常に少ない、あるいは条件を満たす最初の1行のみを出力する必要がある場合、ネストされたループ結合は有効です。
通常、ネストされたループ結合は、結合条件にインデックスが存在し、かつドライブテーブルが小さなテーブルの場合に最も効果的です。データソースに1行しかない場合、例えば主キー値の等価フィルタリング検索（例：WHERE id=101）では、結合は単純な検索になります。最適化エンジンは常に、最小のデータソースを左側に配置し、それをドライブテーブルとするよう試みます。
最適化エンジンがネストされたループ結合を使用するかどうかを決定する要因は多数あります。例えば、ヒント、結合条件に等価結合条件が存在するかどうか、結合条件にインデックスが存在するかどうか、およびドライブテーブルが最大でも1行のデータを出力するかどうかなどです。クエリにNested Loops Join制御に関連するヒントが存在する場合、最適化エンジンはその指示に従ってNested Loops Joinを使用するかどうかを完全に決定します。ヒントが指定されていない場合、結合条件に等価結合条件が存在し、かつ結合条件にマッチするインデックスがなく、同時にドライブテーブルが複数行のデータを出力する場合、最適化エンジンは絶対にNested Loops Joinを選択しません。それ以外の場合、最適化エンジンはNested Loops Joinの実行計画を生成し、その計画のコストを計算します。最終的にはコストを基準にNested Loops Joinの使用を決定します。
さらに、結合条件に等価結合が存在しない場合、または結合条件がない場合、最適化エンジンは必ずNested Loops Joinsを使用することになります。これは、最適化エンジンに他の選択肢のアルゴリズムがないためです。
最適化エンジンのNested Loops Joins使用を制御する方法
最も直接的な制御手段は、ヒントを使用して結合アルゴリズムを指定することです。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アルゴリズムを使用するように制御するには、hintを使用できます。
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アルゴリズムを使用できません。