Oracle SQLチューニングの基本は実行計画を理解することです。実行計画はツリー構造で、様々なオペレーションから構成されます。
この記事では、NESTED LOOPS オペレーションについて説明します。
NESTED LOOPS オペレーションとはどのようなオペレーションか？
比較的少量のデータを結合する結合処理である、ネステッドループ結合の実行時に使用されるオペレーションです。駆動表と内部表のデータを1件ずつ突き合わせる形で、データを結合します。
ネステッドループ結合時は、Oracle Databaseのバージョンアップに伴って改善されています。このため、ネステッドループ結合の実行計画は、バージョンに応じていくつかのパターンがあります。
実行計画の例と処理イメージ図
以下のSQL実行時のネステッドループ結合の実行計画を示します。
SELECT cid, cname, pa.pid, pname
FROM ch, pa
WHERE ch.pid = pa.pid AND pa.pid = 1ネステッドループ結合の実行計画には、バージョンに応じていくつかのパターンがあります。
Classic Nested Loop Join
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 20 (100)| |
| 1 | NESTED LOOPS | | 10 | 40120 | 20 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL | PA | 1 | 2004 | 9 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| CH | 10 | 20080 | 11 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IDX_CHPA | 10 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
2 - filter("PA"."PID"=1)
4 - access("CH"."PID"=1)Multi Join Key Pre-fetching (9i-)
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 40120 | 20 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| CH | 10 | 20080 | 11 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 10 | 40120 | 20 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | PA | 1 | 2004 | 9 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IDX_CHPA | 10 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
3 - filter("PA"."PID"=1)
4 - access("CH"."PID"=1)Nested Loops Join IO batching (11.1-)
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.01 | 45 |
| 1 | NESTED LOOPS | | 1 | | 10 |00:00:00.01 | 45 |
| 2 | NESTED LOOPS | | 1 | 10 | 10 |00:00:00.01 | 35 |
|* 3 | TABLE ACCESS FULL | PA | 1 | 1 | 1 |00:00:00.01 | 32 |
|* 4 | INDEX RANGE SCAN | IDX_CHPA | 1 | 10 | 10 |00:00:00.01 | 3 |
| 5 | TABLE ACCESS BY INDEX ROWID| CH | 10 | 10 | 10 |00:00:00.01 | 10 |
---------------------------------------------------------------------------------------------------
3 - filter("PA"."PID"=1)
4 - access("CH"."PID"=1)NESTED LOOPS オペレーションに関するFAQ
NESTED LOOPS オペレーションが使われる条件には何がありますか？
CBOがネステッドループ結合が最適な結合アルゴリズムであると判断した場合に使われます。
Oracle Databaseの結合アルゴリズムには、ネステッドループ結合、ハッシュ結合、ソートマージ結合の3種類があります。CBOは、アクセス対象のデータ量などを勘案して最適な結合アルゴリズムを選択しますが、比較的アクセス対象のデータ量が少ない場合、ネステッドループ結合が選択されやすい傾向にあります。
NESTED LOOPS オペレーションを使う実行計画は望ましくないですか？
アクセス対象のデータ量が少ない場合、CBOはネステッドループ結合を選択するようになります。
アクセス対象のデータ量が多い場合は、CBOの判断が不適切な場合があります。CBOの判断ミスが疑われる場合は、以下の対処を試みてください。
- オプティマイザ統計情報を収集し、CBOが適切な判断を行えるようにする
- ヒントなどを用いて、ハッシュ結合、ソートマージ結合に誘導する
NESTED LOOPS オペレーションを使う実行計画に誘導するヒントは何ですか？
USE_NLヒントを指定します。ただし、USE_NLヒントを使う場合は、原則的に結合順序を指定する LEADINGヒントや ORDEREDヒントと共に使うことに注意してください。
/*+ USE_NL(<内部表>) LEADING(<駆動表>) */なぜならば、USE_NLヒントは内部表として指定した表が、実際に内部表として選択されない限り、ネステッドループ結合に誘導出来ないためです。このため、LEADINGヒントやORDEREDヒントで結合順序を指定して、USE_NLヒントで内部表として指定した表が、実際に内部表として選択されるようにします。
参考情報
- HASH JOINオペレーション
- MERGE JOINオペレーション
- SORT JOINオペレーション
- ネステッド・ループ結合 – Oracle Database SQLチューニング・ガイド
- 津島博士のパフォーマンス講座 第34回 索引スキャンとSQL自動変換について
- NL History – Oracle Scratchpad
キーワード
索引アクセス INDEX UNIQUE SCAN NESTED LOOPS 索引レンジ・スキャン