Oracleラーニングノート(2)-ネストされたループ接続

10765 ワード
データベース#データベース#
せつぞくほうしき
Oracleのテーブル接続方法は、次の3つです.
  • ネストループリンク(Nested Loops Join)
  • ハッシュリンク(Hash Join)
  • ソートマージ(Merge Sort Join)
    • 接続は2つのテーブルAおよびBに関連し、一般的にはネストされたループリンクは、Aの各レコード(Aテーブル条件を満たす)を遍歴した後、一致するレコードが見つかるまでBテーブルを遍歴し、2層のループに等しい.一方,ハッシュリンクとソートのマージは,まずそれぞれ自身のレコード(ソートまたはhash)を処理し,処理が完了してからバッチでマッチングすると見なすことができる.ハッシュ・リンクとソート・リンクは、大量のデータを返す操作を処理するのに適しており、大きなスループットのクエリーはネスト・ループにとってあまり効率的ではありません.
    準備は2つのテーブルtestを作成することです.join_t 1とtest_join_t 2では、2つのテーブルにそれぞれ100個と10000個の記録がある.
    create table test_join_t1 as select rownum as col1, rownum + 1 as col2, rownum + 2 as col3 from dual connect by level <= 100;
create table test_join_t2 as select rownum as col1, rownum + 1 as col2, rownum + 2 as col3 from dual connect by level <= 10000;

    ネストされたループ接続
    ネストされたループ接続は、一般的なアプリケーションではほとんどの割合を占めています.まず、最も基本的な接続クエリー文を実行します.
    select
    *
from   
    test_join_t1 t1, test_join_t2 t2
where
t1.col1 = t2.col1;

    
----------------------------------------------------------
Plan hash value: 1262213046
-----------------------------------------------------------------------------------
| Id  | Operation          | Name         | Rows  | Bytes | Cost (%CPU)| Time  |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |   100 |  7800 |    11  (10)| 00:00:01 |
|*  1 |  HASH JOIN         |              |   100 |  7800 |    11  (10)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| TEST_JOIN_T1 |   100 |  3900 |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| TEST_JOIN_T2 | 10000 |   380K|     8   (0)| 00:00:01 |
-----------------------------------------------------------------------------------
    実行計画から、OracleオプティマイザがHash Joinを選択していることがわかります.ネストされたループ・リンクはありません.ここでは、Oracleが実行計画の選択方法について詳しく説明しません.ループネストリンクを検証するには、SQLにHINT/*+leading(t 1)use_を追加します.nl(t2) */.HINTの意味はtest_join_t 1テーブルは、起動テーブルとしてループネスト方式でテーブル接続される.
    select
/*+ leading(t1) use_nl(t2) */
    *
from   
    test_join_t1 t1, test_join_t2 t2
where
t1.col1 = t2.col1;

Plan hash value: 3805247585
---------------------------------------------------------------------------------------------
| Id  | Operation          | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |      1 |        |    100 |00:00:00.10 |    2717 |
|   1 |  NESTED LOOPS      |              |      1 |    100 |    100 |00:00:00.10 |    2717 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|   2 |   TABLE ACCESS FULL| TEST_JOIN_T1 |      1 |    100 |    100 |00:00:00.01 |      10 |
|*  3 |   TABLE ACCESS FULL| TEST_JOIN_T2 |    100 |      1 |    100 |00:00:00.10 |    2707 |
---------------------------------------------------------------------------------------------
    HINTを変更してtest_join_t 2テーブルを駆動テーブルとする
    select
/*+ leading(t2) use_nl(t1) */
    *
from
    test_join_t1 t1, test_join_t2 t2
where
t1.col1 = t2.col1;

Plan hash value: 2586695766
---------------------------------------------------------------------------------------------
| Id  | Operation          | Name         | Starts | E-Rows | A-Rows |   A-Time  | Buffers |

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |      1 |        |    100 |00:00:00.18 |   30041 |
|   1 |  NESTED LOOPS      |              |      1 |    100 |    100 |00:00:00.18 |   30041 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|   2 |   TABLE ACCESS FULL| TEST_JOIN_T2 |      1 |  10000 |  10000 |00:00:00.01 |      34 |
|*  3 |   TABLE ACCESS FULL| TEST_JOIN_T1 |  10000 |      1 |    100 |00:00:00.17 |   30007 |
---------------------------------------------------------------------------------------------

    まず、実行計画の各フィールドの意味について説明します.
  • Starts:テーブルアクセス数
  • E-Rows:Oracle見積戻り数
  • A-Rows:Oracle実績返却レコード数
  • A-Time:リアル実行時間
    • 以上の2回の実行計画を比較することで、結論を出すことができます.
  • ドライバテーブルは1回のみ
    • にアクセスする.
  • ドライバテーブルがアクセスする回数はドライバテーブルのレコード数と一致する(ドライバテーブルが返すレコード数が一致するはずで、後でテストする)
    • .
    次に、t 1テーブルを駆動テーブルとしてwhere制限条件col 2を加えて[10,50]の範囲内とする
    select
/*+ leading(t1) use_nl(t2) */
    *
from   
    test_join_t1 t1, test_join_t2 t2
where
t1.col1 = t2.col1 and
t1.col2 >= 10 and
t1.col2 < 50;

Plan hash value: 3805247585
---------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| Id  | Operation          | Name         | Starts | E-Rows | A-Rows |   A-Time  | Buffers |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |      1 |        |     40 |00:00:00.04 |    1089 |
|   1 |  NESTED LOOPS      |              |      1 |     40 |     40 |00:00:00.04 |    1089 |
|*  2 |   TABLE ACCESS FULL| TEST_JOIN_T1 |      1 |     40 |     40 |00:00:00.01 |       6 |
|*  3 |   TABLE ACCESS FULL| TEST_JOIN_T2 |     40 |      1 |     40 |00:00:00.04 |    1083 |
---------------------------------------------------------------------------------------------
    実行計画から分かるように、駆動テーブルt 1は1回のみアクセスされ、被駆動テーブルt 2は40回アクセスされ、駆動テーブルが実際に返されたレコード数と一致する.上記のsqlを変更すると、駆動テーブルは依然としてt 1であり、被駆動テーブルt 2に制限条件col 2を追加する範囲は[10,50]である.
    select
/*+ leading(t1) use_nl(t2) */
    *
from
    test_join_t1 t1, test_join_t2 t2
where
t1.col1 = t2.col1 and
t2.col2 >= 10 and
t2.col2 < 50;

Plan hash value: 3805247585
---------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| Id  | Operation          | Name         | Starts | E-Rows | A-Rows |   A-Time  | Buffers |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |      1 |        |     40 |00:00:00.08 |    2709 |
|   1 |  NESTED LOOPS      |              |      1 |     40 |     40 |00:00:00.08 |    2709 |
|   2 |   TABLE ACCESS FULL| TEST_JOIN_T1 |      1 |    100 |    100 |00:00:00.01 |       6 |
|*  3 |   TABLE ACCESS FULL| TEST_JOIN_T2 |    100 |      1 |     40 |00:00:00.08 |    2703 |
---------------------------------------------------------------------------------------------

    実行計画から分かるように、駆動テーブルt 1のアクセス数は依然として1であり、制限条件に限定された40個ではなく、駆動テーブルのアクセス数は100である.これにより、ネストされたループの特徴が得られます.
  • ドライバテーブルは1回のみ
    • にアクセスする.
  • ドライバテーブルがアクセスした回数はドライバテーブルの「実際の戻りレコード数」であり、ドライバテーブルにクエリー条件を追加してもアクセス回数に影響しません.

    • 次にt 2テーブルのcol 2にインデックスを作成し、上記のsqlを再実行して実行計画を見てみましょう.
    create  index index_t2_col2 on test_join_t2(col2);

select
/*+ leading(t1) use_nl(t2) */
    *
from
    test_join_t1 t1, test_join_t2 t2
where
t1.col1 = t2.col1 and
t2.col2 >= 10 and
t2.col2 < 50;

Plan hash value: 3428653669
-----------------------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| Id  | Operation                    | Name          | Starts | E-Rows | A-Rows|   A-Time   | Buffers | Reads  |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |               |      1 |        |     40|00:00:00.01 |      24 |      2| 
|   1 |  NESTED LOOPS                |               |      1 |        |     40|00:00:00.01 |      24 |      2| 
|   2 |   NESTED LOOPS               |               |      1 |     40 |   4000|00:00:00.01 |      20 |      2| 
|   3 |    TABLE ACCESS FULL         | TEST_JOIN_T1  |      1 |    100 |    100|00:00:00.01 |       6 |      0|
|*  4 |    INDEX RANGE SCAN          | INDEX_T2_COL2 |    100 |     45 |   4000|00:00:00.01 |      14 |      2|
|*  5 |   TABLE ACCESS BY INDEX ROWID| TEST_JOIN_T2  |   4000 |      1 |     40|00:00:00.01 |       4 |      0|
----------------------------------------------------------------------------------------------------------------
    から分かるように、t 2テーブルはインデックスを外してINDEX RANGE SCANを用いて範囲スキャンを行う.駆動テーブルt 1は依然として1回のみアクセスされ、駆動テーブル範囲スキャンは100回実行され、実行計画からインデックスは100回アクセスされ、インデックスがスキャンされるたびにrowidに基づいてテーブル読み取りが行われ、t 2テーブルは4000回アクセスされることがわかる.インデックスが作成されていない場合の100回を大きく上回っています.もちろん、HINTを使用しているため、Oracleにt 1をドライバテーブルとして要求しています.次にt 2テーブルをドライバテーブルとして実行計画を見てみましょう
    select
/*+ leading(t2) use_nl(t1) */
    *
from
    test_join_t1 t1, test_join_t2 t2
where
t1.col1 = t2.col1 and
t2.col2 >= 10 and
t2.col2 < 50;

Plan hash value: 2941912635
--------------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| Id  | Operation                    | Name          | Starts | E-Rows | A-Rows|   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |               |      1 |        |     40|00:00:00.01 |     132 |
|   1 |  NESTED LOOPS                |               |      1 |     40 |     40|00:00:00.01 |     132 |
|   2 |   TABLE ACCESS BY INDEX ROWID| TEST_JOIN_T2  |      1 |     40 |     40|00:00:00.01 |       9 |
|*  3 |    INDEX RANGE SCAN          | INDEX_T2_COL2 |      1 |     45 |     40|00:00:00.01 |       5 |
|*  4 |   TABLE ACCESS FULL          | TEST_JOIN_T1  |     40 |      1 |     40|00:00:00.01 |     123 |
--------------------------------------------------------------------------------------------------------

    まとめ
    駆動テーブルt 2については、インデックスを用いて最終的なレコードを取得してから、循環アクセス被駆動テーブルt 1に進む.上記のテストにより、ネストされたループは、2つのforループ操作で構成されていることがわかります.
  • クエリー駆動テーブルレコードクエリー条件を満たすすべてのレコード
    • を取得する.
  • は、第1ステップで得る駆動テーブルレコードを巡回し、リンク条件値を持ち込んでリンク条件を満たす全ての被駆動テーブルレコード
    • を取得する.
  • は、第2のステップで得る被駆動テーブルの記録を巡り、クエリ条件が満たされているか否かを判定し、そうであれば結果を返し、そうでなければ記録
    • をスキップする.
    ネストされたループの主な特徴と適用シーンをまとめます.
  • ネストループには駆動テーブルと被駆動テーブルの概念があり、駆動順序の異なる実行計画の違いは非常に大きい
    • である.
  • ドライバテーブルは1回のみアクセスされ、ドライバテーブルは複数回アクセスされる.ネストされたループ・アクセス・テーブルの回数は、ドライバ・テーブルの戻りレコード数に直接影響されます.そのため、実際に返されるレコード数(A-Rows)の小さいテーブルを駆動テーブルとし、記録数の大きいテーブルを被駆動テーブルとして返さなければならない.
  • 駆動テーブルのクエリー条件にインデックスを設定することで、クエリー効率を向上させることができる
    • .
  • 接続条件にインデックスを作成することで、クエリ効率を向上させることもできる
    • .
  • 被駆動テーブルのクエリー条件におけるインデックスの作成がクエリー効率に与える影響は状況によって異なり、必ずしもメリットをもたらすとは限らない.
    • すべてのjVMパラメータとそのデフォルト値を印刷
    ES 2021の新機能