机上SQLチューニング、クイズ！ 駆動表（外部表）はどれだ！！！！ （予想解答編） Tweet

JPOUG Advent Calendar 2014 17日目のエントリーで公開したクイズの予想解答編です。
机上ですし、限られた情報しか提示していないので、実際にやってみたら違う...ということも十分ありえます。 (^^;;;;

外部表はどれがいいか予想できると、いろいろな制限のあるチューニング現場では役立つこともあるんです。LEADINGヒント等で結合順を考える場合にも役立ちますyo!
机上の予想なので間違うこともあります。オプティマイザも統計情報から予想しているわけで(Adaptiveな機能を除く）ハズすことも多いですから... (^^

Oracle® Database SQLチューニング・ガイド 12cリリース1(12.1) B71277-02 - 7 結合

問題１
次に示すSQL文の駆動表（外部表）はどれでしょうか？　

表と索引
create table a1 (
    id number primary key
    , data varchar2(1000)
) nologging
/
create table a2 (
    id number primary key
    , data varchar2(1000)
) nologging
/

統計情報
TABLE_NAME     INDEX_NAME       NUM_ROWS DISTINCT_KEYS CLUSTERING_FACTOR
-------------- -------------- ---------- ------------- -----------------
A1             SYS_C0010377        10000         10000               295
A2             SYS_C0010378         2000          2000                59

SELECT
	/* SQL01 */
	/*+
		MONITOR
		USE_NL(a1 a2)
	*/
	*
FROM
	a1	
	INNER JOIN a2
	ON
		a1.id = a2.id
WHERE
	a1.id BETWEEN 1 AND 100
/

私の答え(予想）
Nested Loop結合かつ、INNER JOINですから駆動表（外部表）はデータセットの小さい方。
(Nested Loop結合でOUTER JOINだと結合順は固定されるので駆動表は見つけやすいですが...INNER JOINの場合はそうはいかないですよね）

ただ、結合条件キーはどちらも主キーですし、WHERE a1.id BETWEEN 1 AND 100は、a2表にも適用されることになるでしょうから、a1、a2どちらも最大で100行ヒットする可能性はあります。
どちらでも正解になる可能性はありますが...w （最初から引っ掛けかよw

細かい条件は提示していないので、提示した情報だけで机上で駆動表（外部表）を決めるとすれば、全体のサイズが小さい、a2でいいんじゃないでしょうか？（私ならそうします。机上ですから）
（当初、もう少し詳細な情報を提示しようとしていたのですが忘れてました...なのでa1だと思った方も可能性は五分五分ですね。前提条件不足でした。...ごめんなさい。ごめんなさい。）

問題２
次に示すSQL文の駆動表（外部表）はどれでしょうか？　
(表と索引、および、統計情報は問題１と同じです。)

SELECT
	/* SQL02 */
	/*+
		MONITOR
		USE_HASH(a1 a2)
	*/
	*
FROM
	a1					
	INNER JOIN a2			
	ON
		a1.id = a2.id
/

私の答え(予想）
Hash結合でINNER JOINかつ、WHERE句はないのでこの問題は簡単ですよね!　（これは迷わないはず！！！）

Hash結合も外部表はデータセットの小さい方ですから...この場合だと、a2が外部表になるはず。

問題３
次に示すSQL文の駆動表（外部表）はどれでしょうか？　
なお、D1とD2の多重度は、D1:D2 = 1:100
(個人的にUMLの多重度表記のほうが好きなので、UML表記の多重度で記述します。）

表と索引
create table d1 (
    id number
    , data varchar2(1000)
) nologging
/
alter table d1 add constraint pk_d1 primary key (id) using index nologging
/
create table d2 (
    id number not null
    , seq# number not null
    , data varchar2(1000)
) nologging
/
alter table d2 add constraint pk_d2 primary key (id, seq#) using index nologging
/

統計情報
TABLE_NAME     INDEX_NAME       NUM_ROWS DISTINCT_KEYS CLUSTERING_FACTOR
-------------- -------------- ---------- ------------- -----------------
D1             PK_D1                 200           200                 9
D2             PK_D2               20000         20000               870

SELECT
    /* SQL03 */
    /*+
        MONITOR
    */
    *
FROM
	d2
WHERE
    EXISTS (
        SELECT 
            1
        FROM
            d1
        WHERE
            d1.id = d2.id
            AND d1.id IN (1,5)
    )
/

私の答え（予想）
これはちょっと意地悪な問題ですが、わかる人ならわかるはず。だと思って（信じて）作った問題です。 :)

最近のオプティマイザは、相関副問合せを可能であれば結合に書き換える（unnest）傾向が強いのをご存知でしょうか？　
となれば、方向はだいだい見えてきます。

d1.id IN (1,5)から最大で2件ヒットすると予想できますよね。
さらに、Nested Loop結合に書き換え、d2を内部表として結合できれば無駄がない。
つまり、駆動表は d1が理想的なはず。

d2を外部表にしてしまうと、WHERE条件がないので結合に置き換えてしまうとHash結合または、d2を全表走査して相関副問合せを都度実行のいづれかになってしまうでしょうね。(unnestの傾向が強いのでHash結合になる可能性が高そう）
ヒットするデータ件数から考えると、どちらも無駄なデータアクセスが多くなる可能性は高いのではないでしょうか。

問題４
次に示すSQL文の駆動表（外部表）はどれでしょうか？　
(表と索引、および、統計情報は問題３と同じです。)

SELECT
    /* SQL04 */
    /*+
        MONITOR
    */
    *
FROM
    d1
    INNER JOIN d2
    ON
        d1.id > d2.id
        AND d2.id BETWEEN 1 AND 5
        AND d2.seq# BETWEEN 2 AND 4
/

私の答え（予想）
結合条件が ">"　であることに気付きましたか？ ;)

これは、Nested Loop結合にも、Hash結合にもなりません。 Merge(sort/merge)結合が選択されます。

内部表は索引が利用できないのでソートが発生します。（ソートは避けられない）
外部表では索引を利用してソート処理が回避可能であれば、回避する。

つまりソート処理の影響を最小にしようとするはず、なので...

d1:d2=1:100という比率からd2のソート処理を重いと判断し回避するために外部表にするだろうなぁ〜〜。
私は、d2が外部表になるだろうと予想しています。
（直積じゃないMerge結合なんて最近お目にかかったことないのですが...)

余談）
Hash結合のない時代のOracleで、Merge結合の実行計画を見せられて、なんでソートしてるんですかね〜と、某ベンダーの方に質問されて一瞬固まったことを思い出したw

問題５
次に示すSQL文の駆動表（外部表）はどれでしょうか？　

表と索引定義
create table b1 (
    id number
    , data varchar2(1000)
) nologging
/
alter table b1 add constraint pk_b1 primary key(id) using index nologging
/
create table b3 (
    id number
    , seq# number
    , data varchar2(1000)
) nologging
/
alter table b3 add constraint pk_b3 primary key (id, seq#) using index nologging
/
create table b2 (
    id number
    , seq# number
    , subseq# number
    , data varchar2(1000)
) nologging
/
alter table b2 add constraint pk_b2 primary key (id ,seq#, subseq#) using index nologging
/

統計情報
TABLE_NAME     INDEX_NAME       NUM_ROWS DISTINCT_KEYS CLUSTERING_FACTOR
-------------- -------------- ---------- ------------- -----------------
B1             PK_B1               20000         20000               870
B2             PK_B2                5000          5000               228
B3             PK_B3                 500           500                23

ERDと多重度
b1 : b3 = 1 : 0..2
b3 : b2 = 1 : 0..10
(UML表記の多重度で記述しています。）


SELECT
    /* SQL05 */
    /*+
        MONITOR
        USE_HASH(b1 b3 b2)
    */
    *
FROM
    b1
    INNER JOIN b3
    ON
        b1.id = b3.id
    INNER JOIN b2
    ON
        b3.id = b2.id
        AND b3.seq# = b2.seq#
/

私の答え（予想）
INNER JOINでHash結合かつ、WHERE句もないので簡単な問題ですよね。

外部表は、b3

ついでに、続けると、 b3とb2を結合すると最大で5000件、b3とb1を結合した場合、最大で500件なので、 結合順として理想的なのは、 b3, b1, b2 ですよね。

問題６
次に示すSQL文の駆動表（外部表）はどれでしょうか？　
(表と索引、および、統計情報は問題５と同じです。)

SELECT
    /* SQL06 */
    /*+
        MONITOR
        USE_HASH(b1 b3 b2)
    */
    *
FROM
    b1
    LEFT OUTER JOIN b3
    ON
        b1.id = b3.id
    LEFT OUTER JOIN b2
    ON
        b3.id = b2.id
        AND b3.seq# = b2.seq#
/

私の答え（予想）
OUTER JOINなのですが、Hash結合なので、多分、予想通りにはならないだろうな〜〜と。

最近のオプティマイザは外部結合かつHash結合の場合、外部表と内部表をデータセットサイズに応じて入れ替える傾向が強いんですよね。PGAの使用サイズが小さくなるように....

とは言っても、机上の話なので、入れ替えないとしたらどうなるだろう..と、外部結合なので、内部表は、そのまま内部表として結合したほうがわかりやすいといえばわかりやすい。
（統計情報に乖離がある場合には入れ替えないほうが良い結果だったりすることもあります。）

注）()内を先に結合するものとします。
入れ替えなかった場合は、記述したままなので、(外部表、内部表)=外部表、内部表ということで、 (b1, b3), b2
データセットの小さい順に従えば、外部表と、内部表をいれかえて、b3, b1。
この結果セットの最大件数は元の外部表がb1ですから20,250件、b2が5,000件なので、また外部表と内部表を入れ替えて、結果として、b2, (b3, b1)ってことに...
(内部的に結合順が入れ替えられ、Right Joinに置き換えられることが多いんですよね〜いつ頃からだろう...10gあたりか。）

で、机上ならどちらを取るかということですが、机上では、ひとまず、SQL文の通りとしておくケースが多いです。あくまで私の場合ですよ。（難しいんですよ、これ。外れる可能性大w）
オプティマイザが内部的に外部表と内部表を入れ替えて問題がなければよし、問題があれば、内部的な入れ替えをヒントで止めるというのが私の基本方針なので、この辺りは、チューナーさんのさじ加減次第じゃないかなぁ、と思っています。

あはは、むずかし過ぎたか... 机上だと orz.

結合順がほぼ想定できる場合、状況次第で変わりそうな結合順、いろいろありますよね。
オプティマイザの気持ちになって考えてみると、いろいろ気づくことも多いんじゃないかなぁ。と思います。

オプティマイザってソートが嫌いなんだ〜、とか、PGA少ない方が好きなんだ〜とか....

役に立ったか、どうなのか不明なオチになりましたが.....

本年もよろしくお願いいたします。 m(_ _)m

あ、そうそう、一つ忘れてました。

次回は、Oracle Database 12c 12.1.0.2.0を使って試したオプティマイザが選択した駆動表（外部表）がどれだったかのか公開する予定です。

---

・机上SQLチューニング、クイズ！　駆動表（外部表）はどれだ！！！！