Oracle Databaseにはテーブルの結合方法として、以下の3つがあります。
- ネステッドループ(Nested Loop)結合
- ハッシュ(Hash)結合
- ソート/マージ(Sort Merge)結合
Oracle Databaseをご存じの方は、結合結果の1件目を速く返却したい様な逐次処理ではネステッドループ結合が、結合結果の全件を速く返却したい様なバッチ処理ではハッシュ結合が優位といった様なことを聞いたことがあるかと思います。
今回は、それぞれの結合を実行した際のSQLトレースを取得して、テーブル結合時のテーブルアクセスの状況から、上記の優位性があると言われる様な挙動を示すのか、実機検証をして確認してみました。
1. 検証方法
今回の検証で実行したSQLや使用したテーブル、検証手順について紹介します。
1.1 検証で実行するSQLについて
2つのテーブルを結合して1件だけ取得する様な下記のSQLを実行し、各テーブルのアクセス状況を確認します。結合方法の制御はヒント句を用いて行います。
SELECT /*+ LEADING(t1 t2) <結合方法を指定するヒント句> */ t1.id, t1.ym, t2.val FROM tab01 t1, tab02 t2 WHERE t1.id = t2.id AND rownum <= 1; <結合方法を指定するヒント句> Nested Loop: USE_NL(t2) Hash : USE_HASH(t2) Sort/Merge : USE_MERGE(t2)
1.2 検証で使用するテーブルについて
検証で使用する2つのテーブルtab01とtab02のカラム構成は同一で、下記の構成となります。なお、ネステッドループ結合を行うために、tab02の結合列(id列)にのみ、インデックスを付与しておきます。参考までにDDLを最後に掲載しておきます。
| id | NUMBER(10) |
| ym | CHAR(6) |
| flg | CHAR(1) |
| val | NUMBER(10) |
| memo | CHAR(50) |
各テーブルのレコード件数は、tab01が50万件、tab02が100万件用意します。tab01のid列の値と同じ値が、tab02のid列には1件存在する様なレコードをテーブルに格納しておきます。
1.3 検証手順
下記の手順で検証対象のSQLを実行し、SQLトレースを取得します。今回はLinux x86_64環境のOracle Database19c (19.20.0)で検証を実施しました。
-- SQLトレースの取得開始 SQL> ALTER SESSION SET EVENTS '10046 trace name context forever, level 12'; -- 検証対象SQLの実行 SQL> SELECT /*+ LEADING(t1 t2) <結合方法を指定するヒント句> */ t1.id, t1.ym, t2.val 2 FROM tab01 t1, tab02 t2 3 WHERE t1.id = t2.id 4 AND rownum <= 1; -- SQLトレースの取得終了 SQL> ALTER SESSION SET EVENTS '10046 trace name context off'; -- SQL*Plusの終了 SQL> exit -- トレースファイルの確認 ※占有環境なので最新のトレースファイルにSQLトレースが出力されている想定 $ ls -altr <初期化パラメータdiagnostic_dest>/diag/rdbms/<DB名>/<SID名>/trace/*_ora_*.trc |tail -n 20 -- tkprofによるSQLトレースの成型 $ tkprof <上記で確認したトレースファイル名> <出力ファイル名>
2. ネステッドループ結合
2.1 結合時の挙動について
ネステッドループ結合は、片方のテーブルを駆動表(外部表)、もう片方のテーブルを内部表として、以下のステップで実行されます。
- 駆動表から1件レコードを取り出す
- 取り出したレコードの結合列の値に対して、結合条件を満たした値を結合列に持ったレコードを内部表から取り出し、結合結果として返却する
- 駆動表に次のレコードがあれば1.に戻る
この結合方式では、結合条件に合致するレコードを探すために、必ず両テーブルの全レコードにアクセスするということはありません。1～3のステップで示した様に、順次テーブルのレコードにアクセスしていくイメージで、2のステップで結合条件に合うレコードが見つかる度に結果として返却していきます。ネステッドループ結合が結果の1件目を速く返却してくれるのは、この様な結合の動きをしているからです。
なお、2のステップで結合条件を満たしたレコードを探すために、内部表をフルスキャンしてしまうと非常に効率が悪いため、ネステッドループ結合を使用する場合は、内部表の結合列にインデックスが付与されていることが非常に重要となります。
また、内部表のスキャンが駆動表の件数に比例して発生するため、レコード件数が少ないテーブル(テーブル件数自体は多くても絞り込み条件で少なくなるでも可)を駆動表にすることも、効率的なネステッドループ結合を行う際の重要なポイントとなります。
2.1 検証結果
以下の取得したSQLトレースから、ネステッドループ結合が前述した様なテーブルアクセスをしているか確認してみます。
SELECT /*+ LEADING(t1 t2) USE_NL(t2) */ t1.id, t1.ym, t2.val FROM tab01 t1, tab02 t2 WHERE t1.id = t2.id AND rownum <= 1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.00 0.00 0 7 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.00 0.00 0 7 0 1 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 110 (TEST) Number of plan statistics captured: 1 Rows (1st) Rows (avg) Rows (max) Row Source Operation ---------- ---------- ---------- --------------------------------------------------- 1 1 1 COUNT STOPKEY (cr=7 pr=0 pw=0 time=57 us starts=1) 1 1 1 NESTED LOOPS (cr=7 pr=0 pw=0 time=55 us starts=1 cost=5 size=22 card=1) 1 1 1 NESTED LOOPS (cr=6 pr=0 pw=0 time=50 us starts=1 cost=5 size=22 card=1) 1 1 1 TABLE ACCESS FULL TAB01 (cr=3 pr=0 pw=0 time=35 us starts=1 cost=2 size=12 card=1) 1 1 1 INDEX RANGE SCAN IDX01TAB02 (cr=3 pr=0 pw=0 time=12 us starts=1 cost=2 size=0 card=1)(object id 74809) 1 1 1 TABLE ACCESS BY INDEX ROWID TAB02 (cr=1 pr=0 pw=0 time=4 us starts=1 cost=3 size=10 card=1)
上記で着目したい部分が、SQLの実行計画で赤字となっている3行で、2行目より駆動表をtab01、3行目より内部表をtab02(の結合列を要素として持ったインデックスidx01tab02)として、ネステッドループ結合を行っていることが1行目よりわかります。
左側にある3つの数字は、各ステップで処理したレコード件数について、初回実行時(1st)、平均(avg)、最大値(max)の件数を出力していますが、今回1回しかこのSQLを実行していないため、全て同じ値になります。3行とも数値が1であることから、各ステップでは1レコードだけ処理していたことがわかります。このことから
- 駆動表のtab01から1件レコードを取り出す
- 取り出したレコードの結合列の値に対して、結合条件を満たした値を結合列に持ったレコードを(idx01tab02インデックス経由で)内部表のtab02から1件取り出して、結果を返却して終了
という動きをしていることがわかりました。
ちなみに、SQLトレースの掲載は割愛しますが、駆動表1件に対して結合条件を満たすレコードが内部表に2件以上あるケースとして、1000件(全て異なるブロックに)存在した場合の検証も実施しました。実行計画の「INDEX RANGE SCAN IDX01TAB02」のcr値(論理読込ブロック数)が、結合条件を満たすレコードが1件しか存在しない時と変わらなかったことから、結合条件を満たす1件を見つけた時点で内部表のスキャンが終了していると考えられます。
※補足：実行計画中にNESTED LOOPSが2つ登場している件について
前述の赤字の3行の上にもNESTED LOOPSがありますが、これはidx01tab02インデックス経由でtab02テーブルへアクセスする際に、I/O効率を向上させたNested Loops join Batching方式が使用されたことを示しています。ここではこの方式の詳細については割愛しますので、詳細を知りたい方はこちらをご参照ください。
3. ハッシュ結合
3.1 結合時の挙動について
ハッシュ結合は、片方のテーブルをビルド表(駆動表)、もう片方のテーブルをプローブ表(内部表)として、以下のステップで実行されます。
- 駆動表の全レコードをスキャンして、結合列のハッシュ値がキーで、キーに対応するレコードを格納したハッシュテーブルをメモリ内に作成する
- 内部表をスキャンして、結合列のハッシュ値が1.で作成したハッシュテーブルにマッチするレコードを内部表から取り出し、結合結果として返却する
この結合方式では、ハッシュテーブルを作成するために、必ず駆動表の全レコードへのアクセスが発生します。最初の1件を速く返却する観点で見ると、ネステッドループ結合と異なり、駆動表の全件アクセスが発生するため、不利な結合方式と言えます。
その代わり、全ての結果を速く返却する観点で見ると、内部表に結合条件を満たすレコードがあるかの確認が、メモリ上のハッシュテーブルとのマッチングで高速に行えるため、有利な結合方式といえます。特に内部表に結合条件を満たすレコードが多い場合は、ネステッドループ結合よりも優位になる傾向があります。
なお、駆動表をメモリ上にハッシュテーブル化する方式なので、駆動表が大きいとメモリに格納しきれず、ディスクも併用する動きになり結合効率が低下するため、小さいテーブルを駆動表にすることが、ハッシュ結合を使用する際の重要なポイントとなります。
3.2 検証結果
以下の取得したSQLトレースから、ハッシュ結合が前述した様なテーブルアクセスをしているか確認してみます。
SELECT /*+ LEADING(t1 t2) USE_HASH(t2) */ t1.id, t1.ym, t2.val FROM tab01 t1, tab02 t2 WHERE t1.id = t2.id AND rownum <= 1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.06 0.06 0 5205 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.06 0.06 0 5205 0 1 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 110 (TEST) Number of plan statistics captured: 1 Rows (1st) Rows (avg) Rows (max) Row Source Operation ---------- ---------- ---------- --------------------------------------------------- 1 1 1 COUNT STOPKEY (cr=5205 pr=0 pw=0 time=61385 us starts=1) 1 1 1 HASH JOIN (cr=5205 pr=0 pw=0 time=61385 us starts=1 cost=5921 size=22 card=1) 500000 500000 500000 TABLE ACCESS FULL TAB01 (cr=5202 pr=0 pw=0 time=15207 us starts=1 cost=1437 size=6000000 card=500000) 1 1 1 TABLE ACCESS FULL TAB02 (cr=3 pr=0 pw=0 time=25 us starts=1 cost=2865 size=10000000 card=1000000)
上記で着目したい部分が、SQLの実行計画で赤字となっている3行で、2行目より駆動表をtab01、3行目より内部表をtab02として、ハッシュ結合を行っていることが1行目よりわかります。
2行目の駆動表(tab01)にアクセスしている部分のRows情報を見ると、tab01のレコード件数である500000となっていることから、1件の検索結果を取得したいだけにも関わらず、ハッシュテーブルを作成するために、tab01をフルスキャンして全アクセスしていることがわかりました。
3行目の内部表(tab02)にアクセスしている部分のRows情報を見ると、1件となっていることから、内部表については結合条件を満たすレコードが発見されたら、スキャンを終了していることがわかりました。
4. ソート/マージ結合
4.1 結合時の挙動について
ソート/マージ結合は、他2つの結合方法の様な駆動表・内部表といった明確な違いはあなく、以下のステップで実行されます。ここでは説明の便宜上、片方のテーブルを駆動表、もう片方のテーブルを内部表と表記します。
- 駆動表を結合列でソートする
- 内部表を結合列でソートする
- ソート後の駆動表と内部表のレコードを先頭から結合列で比較(マージする様なイメージ)し、結合条件を満たしたレコードを、結合結果として順次返却する
この結合方式では、結合するための準備として、両テーブルをそれぞれ結合列でソートする処理が発生します(※)。そのため、結合結果を1件だけ取得したい場合でも、両テーブルの全レコードにアクセスが発生するため、基本的には他の結合方式より非効率な結合方式と言えます。
ただし、ソード/マージ結合の際に結合列でソートされている事で、結合後の処理で発生するソートを回避できる(例: 結合列でのORDER BY句がある)ことで、結合以外も含めたSQL処理全体でのトータル処理コストが抑えられる場合は、他の結合方式よりも優位になることもあります。また、ハッシュ結合で駆動表が大きいことでハッシュテーブルがメモリに格納しきれない場合に、ソート/マージ結合の方が優位となることもあります。
なお、結合条件が等価条件(＝)ではなく、非等価条件(＞や≦など)の場合はハッシュ結合が使用できないため、ソート/マージ結合が選択されます。
(※)駆動表側の結合列にインデックスが構成されている場合は、駆動表側のソート処理をスキップさせることが可能です。ただし、内部表側はインデックスがあってもソート処理を回避することはできません。
4.2 検証結果
以下の取得したSQLトレースから、ソート/マージ結合が前述した様なテーブルアクセスをしているか確認してみます。
SELECT /*+ LEADING(t1 t2) USE_MERGE(t2) */ t1.id, t1.ym, t2.val FROM tab01 t1, tab02 t2 WHERE t1.id = t2.id AND rownum <= 1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.14 0.14 0 15604 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.14 0.14 0 15604 0 1 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 110 (TEST) Number of plan statistics captured: 1 Rows (1st) Rows (avg) Rows (max) Row Source Operation ---------- ---------- ---------- --------------------------------------------------- 1 1 1 COUNT STOPKEY (cr=15604 pr=0 pw=0 time=144505 us starts=1) 1 1 1 MERGE JOIN (cr=15604 pr=0 pw=0 time=144504 us starts=1 cost=10592 size=34 card=1) 1 1 1 SORT JOIN (cr=5202 pr=0 pw=0 time=53488 us starts=1 cost=3723 size=6000000 card=500000) 500000 500000 500000 TABLE ACCESS FULL TAB01 (cr=5202 pr=0 pw=0 time=26652 us starts=1 cost=1437 size=6000000 card=500000) 1 1 1 SORT JOIN (cr=10402 pr=0 pw=0 time=91011 us starts=1 cost=6869 size=10000000 card=1000000) 1000000 1000000 1000000 TABLE ACCESS FULL TAB02 (cr=10402 pr=0 pw=0 time=57880 us starts=1 cost=2865 size=10000000 card=1000000)
上記で着目したい部分が、SQLの実行計画で赤字となっている5行で、3行目より駆動表をtab01、5行目より内部表をtab02として、ソート/マージ結合を行っていることが1行目よりわかります。
3行目の駆動表(tab01)にアクセスしている部分のRows情報を見ると、tab01のレコード件数である500000となっていて、2行目にSORT JOINとあることから、tab01をソートするためにtab01をフルスキャンして全件アクセスしていることがわかります。同様に4,5行目から内部表(tab02)についても、tab02に対して全件アクセスしていることがわかります。これらから、1件の検索結果を取得したいだけにも関わらず、駆動表と内部表の全レコードにアクセスが発生していることがわかりました。
5. さいごに
今回の検証で、各結合方式がどのようにテーブルへアクセスして結合しているかを確認できました。今回は結合方式毎のブロックアクセスに着目したため、絞り込み条件が入った場合にどうなるか、説明のみで実機確認は割愛したNested Loops join Batchingはどうなるかなど、機会があれば実機で確認したいと思います。
最後に、今回の検証で使用したテーブルtab01, tab02のDDLを記載しておきます。なお、統計情報の取得は、テーブルに検証用データを格納後に実施しました。
-- tab01 create table tab01( id number(10) not null, ym char(6) not null, flg char(1), val number(10), memo char(50) ); exec dbms_stats.gather_table_stats(ownname=>'TEST',tabname=>'TAB01',CASCADE => TRUE); -- tab02 create table tab02( id number(10) not null, ym char(6) not null, flg char(1), val number(10), memo char(50) ); create index idx01tab02 on tab02(id, ym) tablespace users; exec dbms_stats.gather_table_stats(ownname=>'TEST',tabname=>'TAB02',CASCADE => TRUE);
次の記事：SQLを書き換えないでSQLの実行計画を変更する方法について調べてみた
