“Inside the Oracle Optimizer - Removing the black magic”というブログを見つけました。
Oracleオプティマイザ(CBO)に関する情報などを扱っているようです。
今回はその中から外部結合時の内部動作に関する記事を訳してみました。
聴き慣れない単語(”Lateral Views”とか･･･)もあったので、誤訳を見つけた方はコメント欄でご指摘ください！

原文:Outerjoins in Oracle

— beginning of translation —

Oracleにおける外部結合

リリース6以来、制限事項を設けながらも、Oracleは左側外部結合をサポートしてきましたが、Oracle特有の構文が使われていました。9iでは、内部結合とそれぞれの外部結合において、OracleはANSI SQL92/99構文を取り入れました。
Oracle特有の左側外部結合構文とANSI SQL92/99構文とは決して同等ではありません。後者の構文のほうにはより多くの表現方法があります。

ANSI外部結構文とOracle特有の外部結合構文では同等な点もあるため、混乱している人もいることでしょう。
以下の例を見れば、これら2つの構文の同義点と相違点がわかります。

Oracle特有の構文

クエリAをみてください。これはOracle構文で書かれた左側外部結合です。
ここでは、T1は左側に記述されている表であるため非結合行も保持されます。これに対しT2の非結合行にはnullが入ります。

A.
SELECT T1.d, T2.c
FROM T1, T2
WHERE T1.x = T2.x (+);

ANSI構文の左側外部結合

ANSI構文の外部結合では、クエリAはクエリBのように表すことができます。

B.
SELECT T1.d, T2.c
FROM T1 LEFT OUTER JOIN T2
ON (T1.x = T2.x);

同義点

以下のクエリを見てください。
Oracleでは、検索条件の述語に(+)がある場合(例えば、クエリCでは、T2.y (+) > 5)、外部結合が行われる前にこの検索条件が表T2に適用されることを意味します。

C.
SELECT T1.d, T2.c
FROM T1, T2
WHERE T1.x = T2.x (+) and T2.y (+) > 5;

クエリDはクエリCをANSI構文で記述した左側外部結合であり、同等の内容を表しています。左側外部結合の右側の表に検索条件を適用することは検索条件と結合条件を記述するのと同等であることを意味します。

D.
SELECT T1.d, T2.c
FROM T1 LEFT OUTER JOIN T2
ON (T1.x = T2.x and T2.y > 5);

同様に、クエリEでは、T2.y (+) IS NULLのように検索条件の述語に(+)が記述されているため、この検索条件が外部結合が行われる前にT2に適用されることを意味します。

E.
SELECT T1.d, T2.c
FROM T1, T2
WHERE T1.x = T2.x (+) and T2.y (+) IS NULL;

クエリFのANSI左側外部結合構文はクエリEと同等です。

F.
SELECT T1.d, T2.c
FROM T1 LEFT OUTER JOIN T2
ON (T1.x = T2.x and T2.y IS NULL);

クエリGを見てください。OracleはクエリGの検索条件、T2.y IS NULL、を外部結合が行われた後に適用します。クエリGではT2がT1との結合に失敗した行、あるいはT2.yの値がたまたまnullであった行が戻されます。

G.
SELECT T1.d, T2.c
FROM T1, T2
WHERE T1.x = T2.x (+) and T2.y IS NULL;

クエリHのANSI左側外部結合構文はクエリGと同等です。クエリHのWHERE句は、ON句に記述されている結合条件で左側外部結合が行われた後に適用されます。

H.
SELECT T1.d, T2.c
FROM T1 LEFT OUTER JOIN T2
ON (T1.x = T2.x)
WHERE T2.y IS NULL;

クエリIを見てください。左側の表の検索条件は外部結合処理の前、もしくは後に適用されます。

I.
SELECT T1.d, T2.c
FROM T1, T2
WHERE T1.x = T2.x (+) and T1.Z > 4;

クエリJのANSI左側外部結合構文はクエリIと同等です。

J.
SELECT T1.d, T2.c
FROM T1 LEFT OUTER JOIN T2
ON (T1.x = T2.x)
WHERE T1.Z > 4;

導出ビュー

Oracleでは、ANSI構文の左側、および右側外部結合は、内部的には左側外部結合導出ビューの条件として表されます。
多くの場合、左側外部結合導出ビューはマージされるため、ANSI構文の左側(または右側)外部結合は、完全にOracle本来の左側外部結合演算子の条件として表すことができます。(導出ビューはインラインビューであり、FROM句で導出ビューの前に記述されている他の表を相関参照しています。）

クエリKはANSI構文の左側外部結合です。このクエリは、内部的にはまず初めにクエリLのように表されます。

K.
SELECT T1.d, T2.c
FROM T1 LEFT OUTER JOIN T2
ON (T1.x = T2.x and T2.k = 5);L.
SELECT T1.d, LV.c
FROM T1,
LATERAL (SELECT T2.C
FROM T2
WHERE T1.x = T2.x and T2.k = 5)(+) LV;

クエリLの導出ビューはクエリMを作るためにマージされます。

M.
SELECT T1.d, T2.c
FROM T1, T2
WHERE T1.x = T2.x (+) and T2.k (+)= 5;

クエリNを見てください、ANSI構文の左側外部結合です。
現在のところ、クエリNはオラクル元来の左側外部結合演算子では記述できません。

N.
SELECT T1.m, T2.n
FROM T1 LEFT OUTER JOIN T2
ON (T1.h = 11 and T1.y = T2.y)
WHERE T1.q > 3;

クエリNは左側外部結合導出ビューを使ってクエリOに変換されます。
ON句に記述されている検索条件が左側外部結合の一部でなければならないため、クエリOの導出ビューはマージされません。

O.
SELECT T1.m, LV.n
FROM T1,
LATERAL(SELECT T2.n
FROM T2
WHERE T1.h = 11 and T1.y = T2.y)(+) LV
WHERE T1.q > 3;

クエリPを見てください。クエリPの外部結合条件には論理和が記述されています。
現在のところ、クエリNはオラクル特有の左側外部結合演算子では記述できません。

P.
SELECT T1.A, T2.B
FROM T1 LEFT OUTER JOIN T2
ON (T1.x = T2.x OR T1.Z = T2.Z);

クエリPは結合条件に論理和を含む左側外部結合導出ビューを使ってクエリQに変換されます。

Q.
SELECT T1.A, LV.B
FROM T1,
LATERAL (SELECT T2.B
FROM T2
WHERE T1.x = T2.x OR T1.Z = T2.Z) (+) LV;

ANSI構文の完全外部結合

Oracle 11gR1以前は、ANSI構文の完全外部結合はすべて２つの分岐を持ったUNION ALLに変換されていました。一つは左側外部結合の導出ビューを持ち、もう一方はNOT EXISTSサブクエリから構成されていました。
この問題を解決するために、11gR1よりハッシュ完全外部結合がサポートされました。完全外部結合が使えない場合、Oracleは11gR1以前の方法を適用します。

クエリRを見てください。クエリRはANSI構文で記述した完全外部結合です。

R.
SELECT T1.c, T2.d
FROM T1 FULL OUTER JOIN T2
ON T1.x = T2.y;

11gR1以前では、クエリRは内部的にクエリSに変換されていたでしょう。

S.
SELECT T1.c, T2.d
FROM T1, T2
WHERE T1.x = T2.y (+)
UNION ALL
SELECT NULL, T2.d
FROM T2
WHERE NOT EXISTS
(SELECT 1 FROM T1 WHERE T1.x = T2.y);

ハッシュ完全外部結合がサポートされたことにより、クエリRはクエリTのように簡潔に表わされ、ビューVFOJはマージされません。

T.
SELECT VFOJ.c, VFOJ.d
FROM (SELECT T1.c, T2.d
FROM T1, T2
WHERE T1.x F=F T2.y) VFOJ;

外部結合から内部結合への変換

クエリUを見てください。外部表T2の検索条件には外部結合演算子(+)が含まれていません。
そのため、左外部結合が行われた後、この検索条件が適用されます。結果的にT2.xがnullの行は結果から除外されます。こうして、Oracleは外部結合を内部結合へと変換するのです。

U.
SELECT T1.d, T2.c
FROM T1, T2
WHERE T1.x = T2.x (+) and T2.y > 5;

クエリVのANSI左側外部結合構文はクエリUと同等です。クエリVのWHERE句は、ON句の条件に基づいて左側外部結合が処理された後に適用されます。

V.
SELECT T1.d, T2.c
FROM T1 LEFT OUTER JOIN T2
ON (T1.x = T2.x)
WHERE T2.y > 5;

OracleはクエリUとクエリVを内部結合を用いてクエリWに変換します。

W.
SELECT T1.d, T2.c
FROM T1, T2
WHERE T1.x = T2.x and T2.y > 5;

Q&A

Q1:クエリNとクエリOについて理解できません。検索条件がON句に記述される場合とWHERE句に記述される場合とでは何が違うのですか。

A1: 表T11と表T22を見てください。

T11:
A | B
1 | 2
2 | 3
3 | 5

T22:
X | Y
7 | 2
8 | 4
9 | 4

次のクエリN’はANSI構文の左側外部結合で、表T11とT22を結合した結果3行が戻されます。検索条件は結合条件の一部となっているため常に失敗します。
この結合条件は、ON句に２つの述語を含みますが、常にFALSEと判定されます。そして、左側の表であるT11のすべての行が結果として戻されます。

N’.
SELECT *
FROM T11 LEFT OUTER JOIN T22
ON (T11.A > 9 and T11.B = T22.Y);

A   B    X   Y
--- --- --- ---
1   2
2   3
3   5

しかし、仮に検索条件、T11.A > 9がWHERE句に移った場合、検索結果は0行となります。

Q2: 外部結合から内部結合への変換は新しい機能ですか？

A2: いいえ、この機能はリリース7から使えます。

Q3: native完全外部結合は11gR1より前のリリースで使うことはできますか。

A3: はい。10.2.0.3、および10.2.0.4から使用可能ですが、デフォルトの設定では使えません。

— end of translation —

This entry was posted on 水曜日, 1月 23rd, 2008 at 17:59:30 and is filed under Oracle, 翻訳. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.