[JPOUG Tech Talk Night #16]1歩踏み込むSQLの結合方法

Transcript

1. 1歩踏み込むSQLの結合⽅法 JPOUG Tech Talk Night #16 辻 研⼀郎 ⽇本オラクル株式会社 2026/7/23
2. アジェンダ SQL結合⽅法の • 基礎確認 • ⼀歩踏み込む • まとめ 2 Copyright © 2026, Oracle and/or its affiliates
3. SQLの結合⽅法 はじめに 本⽇は、ネステッド・ループ結合、ハッシュ結合、ソート/マージ結合の3つ。特に前の2つを扱います これらの特性をお伝えして、どの結合⽅法が最適かを識別する⼿助けとなる情報をお届けすることが⽬標です 3つの結合、重要度は ネステッド・ループ結合 ≒ ハッシュ結合 ＞＞ソート/マージ結合 ひとことで特性は ネステッド・ループ結合 OLTP ハッシュ結合 DWH/バッチ処理 ソート/マージ結合 ⾮等価結合で上記結合が選べないとき 3 Copyright © 2026, Oracle and/or its affiliates
4. ネステッド・ループ結合の基本説明 employees表とdepartments表の結合 SELECT e.emp_last_name,e.dept_name FROM employees e, departments d WHERE e.dept_id = d.dept_id; 2つの表を結合するとき、最初に読む表を外部表、次に読む表を内部表といいます。結合⽅法によって別の呼び⽅がある場合もあります ①外部表から 外部表（駆動表） 1レコード取り出す dept_id dept_na me 10 営業 11 開発 12 ⼈事 13 経理 … … departments表 4 Copyright © 2026, Oracle and/or its affiliates ②内部表の⼀致する レコードを検索 内部表 10 ③ ①、②を外部表の レコード件数ぶん 繰り返す emp_id emp_last _name dept_id 7001 ⽥中 10 7005 鈴⽊ 11 7003 佐藤 12 7002 ⽥島 10 7010 ⽥中 13 … … … employees表
5. ネステッド・ループ結合のポイント employees表とdepartments表の結合 SELECT e.emp_last_name,e.dept_name 外部表のレコード数は⼩ FROM employees e, departments d さいほどいい WHERE e.dept_id = d.dept_id; （レコード数が⼩さい表が 外部表となる） 2つの表を結合するとき、最初に読む表を外部表、次に読む表を内部表といいます。結合⽅法によって別の呼び⽅がある場合もあります 内部表へのアクセスはイン デックスアクセスであること ①外部表から ②内部表の⼀致する が⾮常に望ましい 外部表（駆動表） 1レコード取り出す レコードを検索 内部表 dept_id dept_na me 10 営業 11 開発 12 ⼈事 13 経理 … … departments表 5 Copyright © 2026, Oracle and/or its affiliates 10 ③ ①、②を外部表の レコード件数ぶん 繰り返す emp_id emp_last _name dept_id 7001 ⽥中 10 7005 鈴⽊ 11 7003 佐藤 12 7002 ⽥島 10 7010 ⽥中 13 … … … employees表
6. ハッシュ結合の基本説明 orders表とorderitems表の結合 SELECT o.customer_name, l.unit_price * l.quantity FROM orders o, order_items l WHERE l.order_id = o.order_id; ①ハッシュテーブル作成 （メモリ上） 外部表（ビルド表） ORDER_ ID ORDER_STA TUS ②結合 ITEM_ID ORDER_I D QUAN TITY UNIT_PR ICE 5001 1001 キーボード 2 4500 5002 1001 マウス 1 2500 PRODUCT_NAME CUSTOME R_NAME ORDER_D ATE 1001 佐藤商事 2026/7/1 SHIPPED 11500 5003 1002 モニター 1 32000 1002 鈴⽊物産 2026/7/2 PROCESSING 32000 5004 1003 USBケーブル 3 1200 1003 ⽥中⼯業 2026/7/3 SHIPPED 15600 1004 ⾼橋電機 2026/7/4 CANCELLED 0 5005 1003 ドッキングステーション 1 12000 1005 伊藤産業 2026/7/5 PROCESSING 28000 5006 1005 オフィスチェア 1 28000 5007 1007 SSD 2 9500 5008 1007 メモリー 2 6800 5009 1009 Webカメラ 1 8500 5010 1009 ヘッドセット 1 7200 ・・・ orders表 TOTAL_ AMOUNT 内部表（プローブ表） ・・・ orderitems表 6 Copyright © 2026, Oracle and/or its affiliates
7. ハッシュ結合のポイント orders表とorderitems表の結合 SELECT o.customer_name, l.unit_price * l.quantity FROM orders o, order_items l WHERE l.order_id = o.order_id; 等価結合 ①ハッシュテーブル作成 （メモリ上） 外部表（ビルド表） ORDER_ ID ORDER_STA TUS ②結合 ITEM_ID ORDER_I D QUAN TITY UNIT_PR ICE 5001 1001 キーボード 2 4500 5002 1001 マウス 1 2500 PRODUCT_NAME CUSTOME R_NAME ORDER_D ATE 1001 佐藤商事 2026/7/1 SHIPPED 11500 5003 1002 モニター 1 32000 1002 鈴⽊物産 2026/7/2 PROCESSING 32000 5004 1003 USBケーブル 3 1200 1003 ⽥中⼯業 2026/7/3 SHIPPED 15600 1004 ⾼橋電機 2026/7/4 CANCELLED 0 5005 1003 ドッキングステーション 1 12000 1005 伊藤産業 2026/7/5 PROCESSING 28000 5006 1005 オフィスチェア 1 28000 5007 1007 SSD 2 9500 5008 1007 メモリー 2 6800 5009 1009 Webカメラ 1 8500 5010 1009 ヘッドセット 1 7200 ・・・ orders表 TOTAL_ AMOUNT 内部表（プローブ表） 外部表はメモリに載る くらいのサイズ つまり、⼩さいサイズ ⼀度だけ全体をスキャン 7 Copyright © 2026, Oracle and/or its affiliates ・・・ orderitems表
8. ソート/マージ結合 • 両⽅の表が結合キーでソートされ、ソートされたリストをマージ する (⾮等価結合に有効) SELECT … FROM tab1,tab2 WHERE tab1.c1 > tab2.c1 GROUP BY … ; ① 外部表と内部表を結合キーでソート 外部表は索引がある場合はソートが回避されますが、内部 表側は必ずソートが発⽣ ② 外部表から先頭1⾏を取り出して、内部表側で⼀致しない ⾏が⾒つかるまで、⾏が読み取られます。 ⼀致しない⾏が⾒つかったら、そこで内部表の⾛査をストップ し、外部表の次の⾏に対して、また同じ処理を実⾏していき ます。これを外部表の全⾏に対して実施します ! ! ! tab1(外部表) tab2(内部表) !"# !"$ $ $ % & ' & ( ( ) * + + # ) !"# !"$ # % $ & $ # ' ( ' ' ) $ * ( + + , ' , % 結合キー 8 Copyright © 2026, Oracle and/or its affiliates "
9. アジェンダ SQL結合⽅法の • 基礎確認 • ⼀歩踏み込む • まとめ 9 Copyright © 2026, Oracle and/or its affiliates
10. ネステッド・ループ結合 望ましい型を確認することで、ネステッド・ループ結合が最適であるか⾒抜く ネステッド・ループ結合の望ましい型 • 外部表（駆動表）は⼩さいこと • 内部表へはインデックス検索であること SELECT e.emp_last_name,e.dept_name FROM employees e, departments d WHERE e.dept_id = d.dept_id and d.dept_name = '開発'; 参照整合性制約がついてそうな結合キーで⼦表で 外部表（駆動表）の⾏ も更新が多い場合、索引がついている可能性あり 数を⼩さくする条件がある departments d.dept_name = '開発' 10 参照整合性制約を使⽤すると、主キーには⼀意性を保証するために⼀意索引が作成されますが、外部キーには索 引が作成されません。ただし、⼦表の外部キー列に索引が存在しないと、以下の図のように親表の主キーを更新す ることで⼦表を共有ロック（⼦表を処理する間）してしまいますので、他のトランザクションから更新できなくなります。 そのため、⼤量に更新を⾏うようなシステムでは、⼦表の外部キーの索引を作成するか、または参照整合性制約を 使⽤しないことを検討して下さい。 これは、親表をDELETEなどすると、それに対応する⼦表のデータを処理する必要がありますが、索引が存在しないと 全表スキャンになるため、その間はアクセスさせないように共有ロックする必要があるからです。 employees Copyright © 2026, Oracle and/or its affiliates 津島博⼠#21
11. ネステッド・ループ結合 実⾏計画とヒントの書き⽅の注意 SQL> SELECT … FROM tab1,tab2 WHERE tab1.c1 = tab2.c1 GROUP BY … ; 実行計画 --------------------------------------------------------| Id | Operation | Name | Rows | --------------------------------------------------------| 0 | SELECT STATEMENT | | | | 1 | HASH GROUP BY | | | | 2 | NESTED LOOPS | | xxx | | 3 | TABLE ACCESS FULL | TAB2 | 100 | <- 外部表（駆動表） | 4 | TABLE ACCESS BY INDEX ROWID| TAB1 | xxx | <- 内部表 |* 5 | INDEX RANGE SCAN | IX_TAB1 | xxx | ヒント句︓USE_NL(内部表)の使い⽅ USE_NL(TAB1)のようにカッコの中には内部表を単⼀表指定。 USE_NL(TAB2 TAB1) という記述もよく⾒かけますが、内部的にUSE_NL(TAB2) USE_NL(TAB1)とみなされ、結合順序を指定す る効果はなく、TAB2もしくはTAB1をネステッド・ループ結合の内部表として使うという意味になる 結合順序まで指定する時は、LEADINGもしくはORDEREDと同時に使⽤し、LEADING(TAB2 TAB1) USE_NL(TAB1)といった形 で記載する ヒント句の内部表単⼀表指定は、ハッシュ結合やソート/マージ結合のときも同様 11 Copyright © 2026, Oracle and/or its affiliates
12. ネステッド・ループ結合 NLJ Batching (11g以降) 津島博⼠#34 SQL> SELECT … FROM tab1,tab2 WHERE tab1.c1 = tab2.c1 GROUP BY … ; --------------------------------------------------------| Id | Operation | Name | Rows | ROWIDを --------------------------------------------------------並べ替えて | 0 | SELECT STATEMENT | | | | 1 | HASH GROUP BY | | | | 2 | NESTED LOOPS | | xxx | Nested Loops Join(2) | 3 | NESTED LOOPS | | xxx | Nested Loops Join(1) => 結果を駆動表(2) | 4 | TABLE ACCESS FULL | TAB2 | 100 | 外部表（駆動表）(1) |* 5 | INDEX RANGE SCAN | IX_TAB1 | xxx | 内部表(1)（索引のみにアクセス） | 6 | TABLE ACCESS BY INDEX ROWID| TAB1 | xxx | 内部表(2)（ここのI/Oを最適化） (2) (1) 外部表 内部表索引 key 1 rowid 1 key 1 ROWIDでソート key 1 rowid 1 key 3 rowid 2 key 3 key 3 rowid 2 key 2 rowid 4 key 4 key 4 rowid 6 Copyright © 2026, Oracle and/or its affiliates blk1 blk2 key 2 rowid 4 key 2 12 内部表 ※ROWIDフォーマット key 4 rowid 6 ⾮同期IO でスキャン blk3 blk4 blk5 blk6 オブジェクト データファイル ブロック ⾏ ROWIDを並べかえれば必要なブロックがわかる ROWIDベースかつ⾮同期IOなのでデータ 順は保証されない
13. ネステッド・ループ結合 実⾏計画ループ回数はStartsで確認しますが、E-RowsとA-Rowsの出⼒に注意 駆動表の⾏数だけ内部表のアクセスが実⾏されるので、内部表のStartsにアクセスした回数（駆動表のARows）、E-Rowsに1回の⾒積り⾏数が出⼒される（パラレル実⾏も同じ）津島博⼠#68 SQL> SELECT /*+ gather_plan_statistics */ * FROM tab01 t1,tab02 t2 WHERE t1.c1=t2.c1 AND t2.c2 < 1; --DBMS_XPLAN.DISPLAY_CURSORの結果 -----------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | | A-Rows | -----------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | | 100 | E-Rows×Startsと | 1 | NESTED LOOPS | | 1 | 101 | | 100 | A-Rowsを比較する | 2 | NESTED LOOPS | | 1 | 101 | | 100 | |* 3 | TABLE ACCESS FULL | TAB02 | 1 | 101 | | 100 | |* 4 | INDEX UNIQUE SCAN | PK_TAB01 | 100 | 1 | | 100 | | 5 | TABLE ACCESS BY INDEX ROWID| TAB01 | 100 | 1 | | 100 | -----------------------------------------------------------------------------Predicate Information (identified by operation id): --------------------------------------------------3 - storage("T2"."C2"<1) filter("T2"."C2"<1) 4 - access("T1"."C1"="T2"."C1") 13 Copyright © 2026, Oracle and/or its affiliates
14. ネステッド・ループ結合とハッシュ結合 ネステッド・ループ結合とハッシュ結合の処理特性の違い ネステッド・ループ結合 外部表 内部表 （駆動表） （概算）コスト︓外部表READ ＋ 外部表件数 × 内部表インデックス検索 WHERE句 条件で絞る ・・・ インデックス作成 OLTPの特性を感じる 複数回 ハッシュ結合 内部表 (Probe) 外部表 （Build） 1回 14 Copyright © 2026, Oracle and/or its affiliates （概算）コスト︓外部表READ ＋ ビルド表作成 ＋ 内部表フルスキャン パラレル実⾏、圧縮、 パーティション DWH/バッチ処理の特性を感じる
15. ハッシュ結合 PGAの中にハッシュテーブルが収まっているかの確認 DBMS_XPLAN.DISPLAY_CURSOR(ALLSTATS LAST)の例 OMem optimal実⾏に必要と⾒積もられたメモリー 1Mem one-pass実⾏に必要と⾒積もられたメモリー Used-Mem 実際に使⽤された作業領域メモリー Used-Tmp 実際に使⽤されたTEMP領域 ------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | A-Rows | OMem | 1Mem | Used-Mem | Used-Tmp | ------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 1 | | | | | | 1 | SORT AGGREGATE | | 1 | 1 | | | | | |* 2 | HASH JOIN | | 1 | 10M | 64M | 8M | 8192K (1)| 512M | | 3 | TABLE ACCESS FULL| DIM_CUSTOMER | 1 | 2M | | | | | | 4 | TABLE ACCESS FULL| SALES | 1 | 50M | | | | | ------------------------------------------------------------------------------------------------- 15 Copyright © 2026, Oracle and/or its affiliates 表⽰ 判定 Used-Mem ... (0)かつUsed-Tmpなし optimal。PGA内で完結 Used-Mem ... (1)かつUsed-Tmpあり one-pass。TEMPへ退避して1回処理 括弧内が2以上でUsed-Tmpあり multipass。TEMP上のデータを複数回処理
16. ハッシュ結合 Bloom Filterによる内部表（probe表）の結合前フィルタリング Bloom Filterとは、外部表（Build）側の結合キーから作るビット配列。 ⾮常に⼩さいサイズで、「絶対に⼀致しない」⾏、もしくは「⼀致する可能性がある」⾏を判別するフィルタを作成することができる。 この性質を使い、外部表（Probe）側の⾏をハッシュ結合前に事前フィルタリングが可能 厳密な⼀致条件については後続の結合処理で判定する Bloom Filterありのハッシュ結合 内部表(Probe) 外部表(Build) 作 成 ハッシュ 表 BF 結 合 B F Bloom Filterは多段適⽤可能 作 成 ハッシュ 表1 BF1 外部表2(Build) 内部表の⾏が減らせそうな場合、 Bloom Filterが作成される 作 成 ハッシュ 表2 BF2 16 Copyright © 2026, Oracle and/or its affiliates 内部表(Probe) 外部表1(Build) ハッシュ 表1 ハッシュ 表2 結 合 結 合 B B F F 2 1
17. ハッシュ結合 スター・スキーマと Right-deep Join スター・スキーマは、売上や数量などを格納する中央のファクト表を、複数のディメンション表で囲むデータ ウェアハウス向けの構造です。 ディメンション表には、商品・顧客・店舗・⽇付など、分析の切り⼝となる情報を格納します。 表の関係図が星形に⾒えるため、この名前で呼ばれます 表のサイズは、中央のファクト表が圧倒的に⼤きく、周辺のディメンション表は⼩さいという特徴があります Supplier 168MB Customer 2.25GB Lineorder 338GB Part 144MB ディメンション表 Date_DiM 0.2MB ファクト表 TAB1 TAB2 TAB3 ディメンション表 ファクト表 次ページからの説明では 上記のように単純化します 17 Copyright © 2026, Oracle and/or its affiliates
18. ハッシュ結合 スター・スキーマと3表以上のハッシュ結合 Right-deep Join SQL> SELECT … FROM tab1,tab2,tab3 2 WHERE tab1.c1=tab2.c1 AND tab1.c2=tab3.c2 AND tab2.c3=xxx AND tab3.c2=xxx GROUP BY … ; 実行計画（Left-deep Join） ------------------------------------| Id | Operation | Name | ------------------------------------| 0 | SELECT STATEMENT | | | 1 | HASH GROUP BY | | |* 2 | HASH JOIN | | |* 3 | HASH JOIN | | |* 4 | TABLE ACCESS FULL| TAB2 | | 5 | TABLE ACCESS FULL| TAB1 | |* 6 | TABLE ACCESS FULL | TAB3 | Right-deep Join Left-deep Join サイズが⼤きくて Build側として不適 （ハッシュテーブルが PGAに乗らない） 18 実行計画（Right-deep Join） ------------------------------------| Id | Operation | Name | ------------------------------------| 0 | SELECT STATEMENT | | | 1 | HASH GROUP BY | | |* 2 | HASH JOIN | | |* 3 | TABLE ACCESS FULL | TAB3 | |* 4 | HASH JOIN | | |* 5 | TABLE ACCESS FULL| TAB2 | | 6 | TABLE ACCESS FULL| TAB1 | TAB3をBuild表側に移動 ヒント句でも指定できます SWAP_JOIN_INPUTS(TAB3) Copyright © 2026, Oracle and/or its affiliates Probe TAB1を含む⽅(TAB1とTAB1,2の結合結 果)が常にProbe表側にいる また、TAB2,TAB3のハッシュテーブル作成時 にBloom Filterが⽣成された場合TAB1の Probe処理前に多段適⽤できる
19. ハッシュ結合 Getting started with Oracle Database In-Memory Part IV – Joins In The IM Column Store https://blogs.oracle.com/in-memory/getting-started-with-oracle-database-in-memory-partiv-joins-in-the-im-column-store スター・スキーマとRight-deep Join + Bloom Filterの例 SELECT /*+ PARALLEL(2) */ p.p_name, SUM(l.lo_revenue) FROM lineorder l,date_dim d,part p WHERE l.lo_orderdate = d.d_datekey AND l.lo_partkey = p.p_partkey AND p.p_name = ʻhot lavenderʼ AND d.d_year = 1996 AND d.d_month = 'December' GROUP BY p.p_name; このようにRight-deep Join + Bloom Filter でスタースキーマの分析クエリを⾼速化可能 Bloom Filterはさらにパーティション、パラレル実 ⾏、Smart Scanを⾼速する効果あり 19 Copyright © 2026, Oracle and/or its affiliates
20. まとめ 3つの結合、重要度は ネステッド・ループ結合 ≒ ハッシュ結合 ＞＞ソート/マージ結合 特性は ネステッド・ループ結合 OLTP 外部表（駆動表）は⼩さいこと、内部表には索引アクセスであること NLJ Batchingで11g以降⾼速化されている ハッシュ結合 DWH/バッチ処理 外部表（Build表）はPGAにのること、内部表はフルスキャン スタースキーマへのRight-deep Join + Bloom Filter対応 ソート/マージ結合 ⾮等価結合で上記結合が選べないとき 20 Copyright © 2026, Oracle and/or its affiliates