« 私のチューニング、アダプティブなオプティマイザにも、今のところ勝てそうな気がするぞ〜〜〜っ。と思った師走のある日。 | トップページ | 机上SQLチューニング、クイズ! 駆動表(外部表)はどれだ!!!! (予想解答編) »

2014年12月17日 (水)

机上SQLチューニング、クイズ! 駆動表(外部表)はどれだ!!!!

予備プラン発動中 :)

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

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


突然ですが、

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


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

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

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

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

次に示されるSQL文の駆動表(外部表)はどれでしょうか? 

前提

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


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


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

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

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

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


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

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
/


問題5
次に示す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表記の多重度で記述しています。
20141221_120733

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#
/


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

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さんです。

|

トラックバック

この記事のトラックバックURL:
http://app.cocolog-nifty.com/t/trackback/106341/60843119

この記事へのトラックバック一覧です: 机上SQLチューニング、クイズ! 駆動表(外部表)はどれだ!!!!:

コメント

コメントを書く