Oracle 結合方法と結合タイプ

はじめに

Oracleの結合方法/結合タイプについて記載します。

結合アルゴリズム

結合アルゴリズム	日本語	ヒント句
Nested Loops Joins	ネステッド・ループ結合	USE_NL/NO_USE_NL/USE_NL_WITH_INDEX
Hash Joins	ハッシュ結合	USE_HASH/NO_USE_HASH
Sort Merge Joins	ソートマージ結合	USE_MERGE/NO_USE_MERGE
Merge Join Cartesian	デカルト結合(直積)

結合タイプ

結合タイプ	日本語	Nested Loops Joins	Hash Joins	Sort Merge Joins
Cross Join	クロス結合	✔		✔
Semi/anti-join	セミ/アンチ結合	✔	✔	✔
Outer join	外部結合	✔		✔
inner join(theta Join)	内部結合	✔		✔
Equi-join	等価結合	✔	✔	✔
Band join	バンド結合			✔

各結合アルゴリズムの概要

Nested Loops Joins(ネステッド・ループ結合)

▽動作

• ループ処理をネスト(入れ子構造)にして結合処理を実行
• 外部ループで参照される表=外部表。内部ループで参照される表=内部表
• 外部表の返却行数分、内部表に対するループ処理が発生するため、外部表は駆動表とも呼ばれる
• はじめに外部表にアクセスする
• 外部表から戻された行数分、内部表にアクセスし、条件に合致するデータを返却する

▽特徴

• カーディナリティが小さい表を外部表とする
• 外部表のカーディナリティが内部表の参照回数となる
• 実レコード件数ではなくカーディナリティが小さい表を外部表とする
• 内部表のアクセス効率を上げる
• 内部表の結合列に索引があると効率的

▽パフォーマンス

• 処理回数のみを考慮した場合、外部表から返される行数が少なれければ内部表に対するループ回数が減るため、条件指定で返却行数が少ない表を外部表に指定する(カーディナリティが小さい表を外部表に指定する)
• 外部表からの返却行数が少ない場合でも内部表を全件検索せざるを得ない場合はパフォーマンスが悪い
• 内部表に対する絞込み条件と外部表との結合条件で索引が効率的に使用されているか等、内部表に対するアクセス方法を考慮する必要がある

Hash Joins(ハッシュ結合)

▽動作

• 表1から抽出条件に合致する結果セットを返却
• 結果セットの結合キーを基にハッシュ表をPGA内に作成
• 表2から絞込み条件に合致する結果セットを返す
• 結果セットの結合キーを順にハッシュし、ハッシュ表と照合し結合条件に合致する行を特定

▽特徴

•  カーディナリティが小さい方を先に処理する(PGAに作成)
• PGA上に作成される表が小さくなるため、結合処理が効率的
• 一時表領域へのI/Oが発生する可能性がある
• ハッシュ表がメモリ内に収まらない場合には一時表領域を使用するためディスクI/Oの発生により性能が劣化する可能性がある
  ⇒PGA_AGGREGATE_TARGETまたはHASH_AREA_SIZEを考慮する
• 等価条件でのみ使用される
• 結合条件が等価結合でない(範囲指定)場合、ハッシュ結合は使用されない
• アクセス方法はフルスキャン
• 必ず表のフルスキャン、または索引フルスキャン

▽パフォーマンス 

   ・一方の表から結合キーをもとにメモリ内(PGA)にハッシュテーブルを構築した際、ハッシュテーブルがPGA内で収まりきれない場合、一時表領域(TEMPファイル)を使用して他方の表と結合処理が行われる。そのため、他方の結合キーによっては、TEMPファイルに対するディスクI/Oが発生するのでその分がオーバヘッドになる

Sort Merge Joins(ソート・マージ結合)

▽動作

• 表1の結果セットを結合列でPGA内でソート
• 表2の結果セットを結合列でPGA内でソート
• ソート処理はシリアル実行
• ソート結果をPGA内でマージして結果を返却

▽特徴

• 一時表領域(Tempファイル)へのI/Oが発生する可能性がある
• ソート処理がメモリ内に収まらない場合、一時表領域を使うためディスクI/Oの発生し、性能劣化が発生する可能性がある
  ⇒PGA_AGGREGATE_TARGETまたはSORT_AREA_SIZEを考慮する
• 表1は一定条件でソート処理が回避できる(表2のソートは回避できない)
• 表1は、結合列に索引が定義され、かつNOT NULL制約が存在する場合、索引フルスキャンを実行することでソート処理を回避可能
• 表2は、ソートを実行しながら結合を進めるため、ソート処理は回避できない

▽パフォーマンス

• 各表の結合キーがソートされてPGA内に保持されるため、ソートに必要な領域がPGA内で収まりきれない場合、一時表領域のTEMPファイルが使用される。ソートされた結合キー同士をマージするときに、ディスクI/Oが発生し、その分がオーバヘッドとなる

Merge Join Cartesian(デカルト積)

▽動作

• 結果セット1の全行と結果セット2の全行を直積(掛け算)
• 3表以上の結合が行われる場合、結合条件が存在する場合でも直積結合が選択される場合もある。直接の結合関係にない小さな表同士の直積結合を行い、結合条件のある一方の表と結合するような場合も有り得る

▽特徴

• 2つの行ソースに対する結合条件がない場合に直積が選択される
• 結合条件に漏れがないかを確認

▽パフォーマンス

• 一般的に直積はパフォーマンスが悪いが、スタースキーマ構造となっているDWHシステムでは有効な場合がある

各結合タイプの概要

クロス結合(直積/デカルト積)

• 2つの表を結合する際に結合条件を明記しない場合に選択される。
• Oracleは1番目の表の各行と2番目の表の各行を結合する。
• 直積の行数は1番目の表の行数と2番目の表の行数を掛けた数になる。

内部結合(単純結合)

• 2つ以上の表を結合し、その結合条件を満たす行のみを返却する。
• 一般的に結合と言う場合、内部結合を指す。

外部結合

• 結合される両方の表が結合条件を満たす行も、一方の表には条件を満たす行がない行も全て返却する。
• 外部結合は内部結合の結果を拡張した位置づけ。

等価結合/非等価結合

• 等価結合は、結合条件に等価演算子を含む構文のことを言う。
• 等価結合は、ある特定の列の値が等しい行を結び付ける。
• 等価演算子以外の演算子で2つの表が結合されている場合は非等価結合。
• 問合せ文には非等価結合と等価結合も含むことが可能。

自己結合

• 対象表自身を結合する方法。
• 自己結合する表はFROM句で2回(あるいはそれ以上)指定され、各表名の後ろには、結合条件やSELECT句の列名を修飾する別名が続く。

アンチジョイン

• NOT EXISTS句やNOT IN句を用いて記述する。結合条件と一致しない組み合わせを返却する
• 2つの表をアンチ結合した場合、最初に記述された表に存在し、後に記述された表には存在しない行を返す(右側の副問合せとの結合に失敗した行を戻す)

セミジョイン

• EXISTS句やIN句を用いて記述する。一致する行が1つ見つかると値を返す。
• 2つの表を半結合した場合、最初に記述された表に存在する行で、後に記述された表に1行ないしは複数行存在する行を戻す。
• 半結合と従来の結合との違いは、最初に記述される表の行と一致する行が、後ろに記述される表に複数行存在する場合でも1行しか戻されないという点。

その他

バンド結合(12.2～)

• 1つのデータセットのキー値が2番目のデータセットの指定された範囲(「バンド」)内になければならない特別なタイプの非等価関数。
• 同じテーブルは、第1および第2のデータセットの両方として機能することが可能。
• 12.2以降、データベースはバンド結合をより効率的に評価する。
• 最適化は、定義されたバンドの外にある行の不必要なスキャンを回避する。
• オプティマイザはコスト見積もりを使用して、結合方法(ハッシュ/ネストループ/ソートマージ)とパラレルデータ分散方法を選択する。

参考資料

https://docs.oracle.com/cd/E82638_01/TGSQL/toc.htm
Expert Oracle SQL

Expert Oracle SQL: Optimization, Deployment, and Statistics

SQL実践入門──高速でわかりやすいクエリの書き方 

SQL実践入門──高速でわかりやすいクエリの書き方 (WEB+DB PRESS plus)