SQLパフォーマンス向上？ネステッドループの詳細解説と実践的なヒント

概要

本記事では、Oracleの表結合方法におけるネステッドループに焦点を当て、その詳細な検証を行います。ネステッドループは、分かりにくい側面があるかもしれませんが、理解しやすいように解説します。記事がお役に立てれば幸いです。

ネステッドループとは

ネステッドループは、ループ処理をネストにして結合処理が行われるアルゴリズムです。外側のループが内側のループを制御し、条件に合致する行を探索する仕組みを持っています。ネステッドループ結合は、結合対象のデータサブセットが比較的小さい場合に特に適しています。

ネステッドループの基本的な動作

• 外側のテーブル（ドライブテーブル）から1行ずつ取り出します。
• 外側のテーブルの各行に対して、内側のテーブル（プローブテーブル）が順次走査されます。
• 条件に合致する行が見つかると、結合された行が結果に追加されます。
• 外側のテーブルの次の行に進み、同じ手順を繰り返します。

使用シナリオ

ネステッドループは強力なクエリの選択肢であり、制約が高く、結果セットがわずかなレコードのみを返す場合に適しています。通常、ドライブテーブルのレコードは少なく、被駆動テーブルの接続列にはユニークなインデックスまたは選択性の高い非ユニークなインデックスがある場合、ネステッドループの効率が高まります。

ネステッドループは最初の数行のレコードを非常に迅速に返すため、エンドユーザーは速やかに最初の一連のレコードを取得でき、同時にデータベースは他のレコードを処理して返すことができます。ただし、クエリのドライブテーブルのレコードが非常に多い場合や、被駆動テーブルの接続列にインデックスが存在しないか、またはインデックスが高い選択性を持たない場合、ネステッドループの効率は低下します。

使用例

検証用データ作成

-- 表作成
CREATE TABLE t1 (
    id     INT,
    name   VARCHAR2(20)
);

CREATE TABLE t2 (
    id1    INT,
    name   VARCHAR2(20)
);

 -- データ導入
BEGIN
    FOR i IN 1..5 LOOP
        INSERT INTO t1 VALUES (
            i,
            'A' || i
        );

    END LOOP;

    COMMIT;
END;

BEGIN
    FOR i IN 1..100000 LOOP
        INSERT INTO t2 VALUES (
            i,
            'B' || i
        );

    END LOOP;

    COMMIT;
END;

-- インデックス作成

CREATE INDEX idx_t2_id1 ON
    t2 (
        id1
    );

実行計画確認

検証用SQL

 select a . * , b . *
from t1 a , t2 b
where a . id=b . id1 and a . name like 'A1%';

実行計画確認

実行計画が以下の通りです。

Plan hash value: 3013014930
 
-------------------------------------------------------------------------------------------
| Id  | Operation                    | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |            |     1 |    50 |     5   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |            |     1 |    50 |     5   (0)| 00:00:01 |
|   2 |   NESTED LOOPS               |            |     1 |    50 |     5   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL         | T1         |     1 |    25 |     3   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN          | IDX_T2_ID1 |     1 |       |     1   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| T2         |     1 |    25 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - filter("A"."NAME" LIKE 'A1%')
   4 - access("A"."ID"="B"."ID1")
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

実行計画説明

• 1つ目のループに関しては、以下のSQLで取得したデータに対して、ループ制御を行います。

 select * from t1 a where a.name like 'A1%';

• 2つ目のループに関しては、以下の結合条件を基づいて、テーブルt2に対して、ループ制御を行います。

 select * from t2 b where b.id1 = a.id;

パフォーマンス向上のためのポイント

• 外部テーブルのカーディナリティを低く保つ
  カーディナリティ（Cardinality）は、データモデリングやデータベース設計の文脈で使用される用語で、ある関係における1つの集合から別の集合へのマッピングの度合いを示します。
• 内部テーブルの結合列はインデックスに含める