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

予備プラン発動中 :)

ということで、諸事情により開け忘れた窓を開けるためJPOUG Advent Calendar 2014 ２回目の登場となりました。

JPOUG Advent Calendar 2014 17日目のエントリーです。

突然ですが、

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

なお、このクイズには次の制限があります。

別途、本ブログで解答エントリーが公開（年明けを予定）されるまで、Oracle Databaseを利用して答え求めるのは禁止。 :-)
Oracle Databaseを利用せず、机上で、どの表を駆動表（外部表）にすれば理想的な実行計画になりそうか考えてみてね。

また、解答はOracle Database 12c Release 1 12.1.0.2.0 のオプティマイザをインストールしたまま（初期化パラメータはデフォルトのまま）の環境を使って行います。
（11gでも違わないと思いますが)

というクリスマスプレゼント :)

次に示されるSQL文の駆動表（外部表）はどれでしょうか？　

前提

統計情報と実データとの間に乖離はありません。
リテラル値で指定した検索条件に該当するデータは必ず存在します。

※引っ掛け問題もあるよ！　:)

問題１
次に示す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
/

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

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

問題３
次に示す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)
    )
/

問題４
次に示す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
/

問題５
次に示す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#
/

問題６
次に示す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#
/

冬休みの宿題〜〜〜〜っ。暇つぶしにトライしてみてくださいね。（ニヤニヤ　

解答エントリーの公開は年明けを予定していま〜す。

We wish your merry christmas and a happy new year!

次の扉は、Tamie Yamamotoさんです。