DB2 UDB アクセス・プラン速習

2006年4月7日（金）

クラスター率

資料ページと併せてご覧ください。 第9回資料はこちら

執筆者 プロフェッサー 秦太郎

今回はFETCHのIOコストに影響を与えるクラスター率(cluster ratio)を見ます。クラスター率は索引キー値に対して行が集まっている度合いを表します。キー値に対して行がよく集まって格納されている状態をクラスター率が高いといいます。今回は3つのパートから成ります。

1. クラスター率の大小によるORDER BYへの索引の使用 2. VOLATILE表によるコスト比較 3. コスト比較と選ばれたアクセス・プラン

クラスター率が高い索引ではORDER BYを索引順の読み取りだけで処理する場合があります。SORTがなくなります。この選択はルールではなくコスト評価によるものです。順に見ていきましょう。

再現用SQL(1)
上のSQLは第6回で使った表t3に対するSELECTでORDER BY c3,c2が指定されています。表t3はt1と同じデータからなり、c3列、c2列は乱数です。並び順は固有な連番であるc1列順です。ここにc3c2c1列の索引が作成されています。これは後で見るようにクラスター率の低い索引です。

下のSQLは表t8に対するSELECTでORDER BY c3,c2が指定されています。表t8は表t3のデータをc3,c2,c1の順にソートしてロードしたものです。表t8のc3c2c1列索引は後で見るようにクラスター率の高い索引です。

アクセス・プラン・グラフ(2)
上の例は表t3のORDER BYがTBSCAN（5)とSORT（4)で処理されています。索引は使われていません。SORT後、見積行数9990行をGROUP BYしています。

下の例は表t8のORDER BYがIXSCAN（4)とFETCH（3)で処理されています。ソートは行われていません。FETCH後の見積行数9990行をGROUP BYしています。IX2T8索引はc3,c2,c1列の複数列索引なのでこの索引順に取り出せばc3,c2順にソートされたのと同じ結果です。

このようにORDER BYの処理はTBSCAN + SORT(上側)と、その並び順の索引によるIXSCAN(下側)の2つがあります。

EXPLAIN出力詳細部分(3)
上のTBSCAN + SORTでTBSCANのIOコストは表のページ数9265です。SORTでもIOコストは変わっていません。

下のIXSCAN では索引先頭列C3に索引Stop Predicate C3 < 1000 が指定されていてフィルター・ファクター0.09999 です。IXSCANのIOコストは後で見る全リーフページ5848ページ の 0.09999倍である584.742に、索引ツリーレベル3のうち、すでに数えたリーフページ1と特別扱いされているルー・トページ1を除いた残り1ページを加えた585.742となっています。

上のTBSCANの述部もC3 < 1000でフィルター・ファクターも同じ0.09999です。これが上下で同じ見積行数99990を得る元となっています。表の行数は共に100万行でした。

統計情報(4)
上の表スキャンで使われる表t3は9265ページであることがのっています。 下の索引スキャンで使われる表t8はクラスター率：INDEX CLUSTERING STATISTICS が100.0でこの索引の並び順に行が集まっていることがわかります。データはそのように作成ロードしました。他に索引階層：Index tree levels 3（3階層）、リーフページ数:Index leaf pages 5848　が読み取れます。

上の表t3についてc3c2c1列索引のクラスター率：INDEX CLUSTERING STATISTICS の元であるCLUSTER RATIOをDB2カタログ・ビューからSELECTしてみると0になっていることがわかります。

スナップショット・モニター出力(5)
上はORDER BYがTBSCAN + SORTで処理されたdb2batch出力です。索引は使わず、表スキャン【1】で選んだ行をソート【2】している様子がわかります。

下はORDER BYがIXSCANで処理された様子がわかります。また、ソート回数も0です。

これらは異なる表の異なるアクセス・プランなのでそれぞれの特徴は見えるものの、コストの違いがどこにあるか見きわめるための対等な比較はできません。

次に上がIXSCANよりTBSCAN + SORTが有利となったポイントを見ましょう。そのために上のケースを強制的にIXSCANに変えます。それは表にVOLATILE属性を指定すればよいです。VOLATILE属性はコスト上不利であっても索引経由のアクセスがあれば選択するというものです。下のケースにも同じくVOLATILE属性をつけてみます。

上に戻る

再現用SQL・volatile(6)
表のVOTATILE属性はALTER TABLEで変更できます。両方の表にVOLATILE属性をつけます。

アクセス・プラン・グラフ・volatile(7)
上が索引スキャンに変わり、上下とも同じにIXSCAN + FETCHの形式になりました。上下はアクセス・プラン・グラフの形は同じですがTotal Costが違います。違いがどこにあるかをグラフの下から順に比べてみます。するとIXSCAN（4)まではIOコスト、コスト、出力行数までも全く同じです。そしてFETCH（3)の累計IOコストと累計コストが上下で違っていることがわかります。上のFETCHの累計IOコストは9849.55である一方、下の累計IOコストは1512.05で上より小さくなっています。この違いがクラスター率によるものです。

クラスター率が低ければ同じキー値の行や隣接するキー値の行次の行が別のページにある可能性が高くなります。別のページを読むために物理IOは増えます。このためコスト上多くのIOコストが見込まれます。

クラスター率が大きければ同じキー値の行や隣接するキー値の行が集まっていて一度読んだページにまた次の行がみつかる確率が高くなります。この時、バッファプールに余裕があって読み込んだページが残る限り、同じページの読み取りに物理IOはいりません。これが小さいIOコストに対応します。

一方、FETCHの見積行数はともに9990行と同じです。同じ9990行をFETCHするのに多くのページ読み込みを要するのがクラスター率の低い索引です。ここではクラスター率の違いがFETCHまでの累計IOコストの違いを生み、それが累計コストの違いを生んでいます。

FETCH(3)までの累計IOコストは上が9849.55、下は1512.05です。これにはその前のIXSCANのIOコストを含んでいます。FETCH（3)だけのIOコストを上下で比較してみるなら、上が9849.55-585.742 = 8368.80 下が1512.05-585.742=926.30 です。表のページ数の約8割と約1割という違いで大差といってよいでしょう。

同様にGRPBYだけのコストを上下で比べると、(GRPBYまでの累計コスト)-(FETCHまでの累計コスト)で得られて：上が120298-120291 = 7、下が2948.21-2940.54 = 7.67 です。上下は同じだといってよいでしょう。

RETURNだけのコストを上下で比べると、Total Cost-(GRPBYまでの累計コスト)で得られて：上が120312-120298 = 14、下が2961.87-2948.21 = 13.61 です。これも上下同じと言ってよいでしょう。

このようにクラスター率の違いが上のコストを押し上げました。その他は変わりません。

EXPLAN詳細部分・volatile(8)
IXSCAN(4)とFETCH（3)を上下で比較すると、FETCHの累積IOコストは上が大きくなっていることがここでも読み取れます。その結果、累積総コストも上が大きくなっています。どちらも索引にもVOLATILE　CARDINALITY属性が現れています。

統計情報・volatile(9)
どちらもIXSCANなので上も索引統計情報が表示されています。上下の違いはIndex clustering statistic で上が0、下が100だけです。これはロード元データの並び順によるものです。

スナップショット・モニター出力・volatile（10)
今回は物理IOが発生するようにデータベース起動後、最初に実行しました。
上下ともにIXSCAN + FETCHですが、上は索引でヒットした約10万行読むのに10万回のBuffer pool data logical readsが発生しています。Buffer pool data physical read も表の全ページ数に近い回数読んでいて該当ページが表全体にわたって散らばっている様子を表しています。

下は同じ索引でヒットした約10万行読むのに約900回のBuffer pool data logical readsが発生しています。少ないページ数の物理読み込みにより該当する行が集まって格納されているといえます。

上に戻る

コスト比較と選ばれたアクセス・プラン(11)
上側のケースのTBSCAN + SORTとIXSCAN + FETCHの両方のコストが得られました。下側のケースのIXSCAN + FETCHのコストは得られています。また、VOLATILE指定による変化はありません。

残りの下側をTBSCAN + SORTとしたコストは索引をDROPすれば得られます。資料の添付は略しますが、上のTBSCAN + SORTと途中経過も含めて全く同じアクセス・プランで全く同じコストでした。

これら4通りを表にまとめ、左右の比較と上下の比較を行ってみます。
左右で比較すると：
上側t3表でTBSCAN+SORTが選ばれたのはIXSCANよりTotal Costが小さいため
下側t8表で IXSCANが選ばれたのはTBSCAN+SORTよりTotal Costが小さいため
といえます。

上下で比較すると：
TBSCAN+SORTは表t3でも表t8でもTotal Costは同じ。
IXSCAN は上側t3表より下側t8表のTotal Costが小さい。この差を生み出したのが(6)で見たように索引のクラスター率です。
そしてTBSCAN + SORTのTotal Costはこの二つの中間となっていることが、上下でコスト比較結果を逆転させアクセス・プランの選択を変えました。

IOコストが大きいアクセス・プランはコストが大きく左右の比較上不利になります。これは、物理IOが多い処理は実行時間増加につながることに対応します。より実行時間が速いアクセス・プランをコストを使って選択するという考え方は理にかなっていると言えるでしょう。

上に戻る

db2exfmtの統計情報 Index clustering statisticsはDB2カタログ・ビューSYSCAT.INDEXESのCLUSTERRATIOに対応しています。当資料ではこれにならってクラスター率と呼びました。当資料では索引詳細統計は取得していません。索引詳細統計が取得されている場合にはCLUSTERRATIOの代わりにCLUSTERFACTORがdoubleの精度で取得されます。

今回、表t8のデータは並び順をc3,c2,c1にソートしてからロードすることでクラスター率を高くしました。
自動的にクラスター率を高く維持するためのオプションとして索引のCLUSTER属性があります。索引にCLUSTER属性をつけるにはCREATE INDEX 文でCLUSTER属性を指定します。MDCでない標準表には1表に1つだけCLUSETER索引を作成できます。それをクラスタリング索引と呼びます。クラスタリング索引があれば、新しい行はできるだけ既存のキー値の近くにINSERTするように空きスペース探索がされます。クラスタリング索引を持つ表には既存キーの行の近くに行が入る余裕を持つために表のフリースペースを確保しておくべきです。表を再編成した場合もクラスター索引順に行が集まり、クラスター率が高くなります。 もし、主キーをクラスター索引にしたい場合は、UNIQUE索引をCLUSTER属性つきで作ってALTER TABLE ・・・. ADD PRIMARY KEY(ユニーク索引列) と主キーに後で変更すればよいです。

IXSCANに続くFETCHのIOコスト、総コストはIXSCANの出力行数だけでなく索引のクラスター率によって変わります。

クラスター率の高い索引の索引列順のORDER BYにはコスト評価の結果、SORTを使わずその索引順のIXSCANが使われることがあります。

クイズ

1.	次のうち正しいものはどれでしょう？
	1)	クラスター率が高いとIXSCANのIOコストが小さくなる
	2)	クラスター率が高いとFETCHのIOコストが小さくなる

2.	IXSCAN + FETCHのアクセス・プランでクラスター率の大小がコストにあまり影響しないのはどのケースでしょう？
	1)	IXSCANの述部のフィルター・ファクターが大きい
	2)	IXSCANの述部のフィルター・ファクターが小さい
	3)	IXSCANの出力見積行数が少ない
	4)	IXSCANの出力見積行数が多い

上に戻る

上に戻る