ネステッドループ結合(nested loop join)とは
複数のテーブルを結合するSQLを実行すると、ORACLEはネステッドループ、ハッシュ、マージの3種類のいずれかの結合方法を選択して実行計画を立てます。本ページではネステッドループの特徴やパフォーマンスについて記載します。
ネステッドループ結合の特徴
ネステッドループ結合は最初にアクセスする表(外部表。駆動表とも呼ばれる)をフェッチし、そのフェッチしたデータと結合可能なデータを内部表から検索する処理をループして結合します。 以下のような特徴があります。・RBO、CBO共に選択可能
統計情報取得やダイナミックサンプリング等をせずとも選択可能です。(統計情報を取得しなくても良いというわけではありません。あくまで選択可能というだけです)
・等価結合だけでなく非等価、範囲条件の結合も可能
以下のいずれもネステッドループ結合が選択可能です。
select * from tab1 a,tab2 b where a.col1=b.col1
select * from tab1 a,tab2 b where a.col1>b.col1
select * from tab1 a,tab2 b where a.col1<>b.col1
・全データの結合が終わっていない段階でクライアント側に結果を戻すことができる
オンライン処理等で、最初の数十件だけでも早くデータを戻したいような用途に向いています。
・外部結合の場合結合順序が固定される
以下のsqlをネステッドループで結合する場合はヒントで変えようとしても無視され必ずtab1が外部表になります。
select * from tab1 a,tab2 b where a.col1=b.col1(+)
・結合条件式から内部表の索引スキャンが可能
以下のSQLの例ではtab2が内部表ですがtab2自体には抽出条件が無いにも関わらずcol1に対する索引ユニークスキャンが選択できています。 これは外部表からフェッチしたデータのcol1を条件として内部表にアクセスできるためです。これはネステッドループの大きな利点であり、 このケースだとハッシュ結合等では索引は利用されず外部表、内部表共にテーブルフルスキャンとなります。 従って、オンライン系SQLによくある外部表が数件～数十件程度に絞られ、内部表にユニークスキャンできるようなSQLだとその他結合よりも優位性があります
・一時領域の利用が少ない
ハッシュ結合やソートマージ結合はある程度の結合処理が終わるまで結果を戻すことができないため結合途中のデータをメモリ(UGA。足りなくなった場合はDISK書き出し)上に留めておく必要があり 結合データが多いと大きな一時領域が必要になります。 これに対してネステッドループは上述の通り全件結合が終わらない段階で結果を戻すことができるため必要な一時領域が少ないという特徴があります
TEST@orcl102 > select * from tab1 a ,tab2 b where a.col1 = b.col1;
実行計画
----------------------------------------------------------
Plan hash value: 2308043903
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 52 | 3 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 52 | 3 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL | TAB1 | 1 | 26 | 2 (0)| 00:00:01 |外部表(駆動表)
| 3 | TABLE ACCESS BY INDEX ROWID| TAB2 | 1 | 26 | 1 (0)| 00:00:01 |内部表
|* 4 | INDEX UNIQUE SCAN | PK_TAB2 | 1 | | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("A"."COL1"="B"."COL1")
11g以降のネステッドループ結合について
11g以降、ネステッドループのロジックはDISK I/O待ち時間の削減を目的として一部見直されたことで10gまでとは異なる実行計画が選択される場合があります。 具体的には以下のように内部表へのアクセスを索引までで止めておき、最後にまとめて表へアクセスするようになっています。 恐らくこの最後の表へのI/Oを非同期等の方法でまとめて実行することでI/O時間の短縮を図るようになったものと考えられます。
TEST@ORCL112 > select * from tab1 a,tab2 b where a.col1=b.col1;
実行計画
----------------------------------------------------------
Plan hash value: 1300327366
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 1 | 12 | 4 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | TAB1 | 1 | 6 | 3 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | PK_TAB2 | 1 | | 0 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| TAB2 | 1 | 6 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("A"."COL1"="B"."COL1")
ネステッドループ結合のパフォーマンス
ネステッドループのcostや処理時間は基本的には以下のように考えられます。外部表のアクセス時間＋(内部表のアクセス時間*外部表の抽出行数)
つまり、ネステッドループが早くなる条件としては以下のような条件を満たすSQLとなります。 ・外部表の抽出データが少ない ・内部表へのアクセス方法がユニークスキャンか、少件数に絞れるレンジスキャン。または、件数が非常に少ない表のフルスキャンマニュアル
・概要・パフォーマンス・チューニングガイド
折りたたみ
展開