1.等号述部のフィルター・ファクター

再現用SQL(1)

上下のSQLではWHERE条件に指定された列が異なっています。どちらも単一列索引です。この条件にヒットした列は上が21列、下が84列です。つまり上の方がヒット件数が少なくなっています。このことをC2列の述部の方がフィルター・ファクターが小さいと呼びます。フィルター・ファクターが小さいとは、その条件の方がよく絞り込まれるという意味です。このページは実行結果としての件数が表示されていますが、オプティマイザーは件数も見積もります。順に見て行きましょう。

アクセス・プラン・グラフの着眼点(2)

上下のアクセス・プランは共に索引スキャンですが、条件の列が異なり、使われた索引名も異なっています。図の中央RETURN部分の凡例にある通り、IXSCANオペレーターの上の数：このオペレーターの見積もり出力行数を上下で比べてみると、上は30.5行、下は99.9行と見積もり上も上の方が条件に合致する件数が少なくなっています。元の行数はいつものように１００万行です。また図の凡例の通り、IXSCANオペレーターの下はそこまでの累計コストと累計IOコストです。IOコストは共に2つまり、共に２ページと差がありません。そこまでの累計コストは上25.7384、下25.7751とわずかに上が小さくなっています。この違いは扱う行数の差によってIXSCAN操作の回数が変わることによるCPUコストの差です。今回のケースのようにWHERE条件に指定された列に対して対象の索引選択がほぼ自明であってもコスト評価は行なわれます。

IXSCAN( 4)の上のFETCH( 3)については後で取り上げますが、FETCHでの累積IOコストは上が21.3ページ、下が65.1ページとやはり上が小さくなっており、累計コストも上275.0、下837.2と上が小さくなっています。

アクセス・プラン詳細部分の着眼点(3)

上下共にアクセス・プラン・グラフでのIXSCANオペレーターは4)です。共にWHERE条件の述部はStart Key PredicateとStop Key Predicateとして扱われ、索引をスキャンする範囲を限定しています。今回の述部は等号なのでStart Key PredicateとStop Key Predicateは同じものです。範囲としてたまたま単一の値を指しています。そして上のフィルター・ファクター(Filter Factor)は3.05176e-005 下は9.999e-5となっています。このフルター・ファクターが元の行数１００万行に対して掛けられて、アクセス・プラン・グラフに見られたIXSCANオペレーターの見積もり出力行数になります。これらのフィルター・ファクターは上下それぞれ、1/32768と1/10001に一致します。この分母は次で見ることができます。

db2exfmt　統計情報の着眼点(4)

db2exfmtの最後の統計情報の上下でIndex full key cardinalityを比べてみましょう。上は32768、下が10001となっていてこの逆数が(3)のフィルター・ファクターです。Index full key cardinalityはこの索引列に実在するキー値の重複を除いた異なる値の数をあらわします。C2列索引は0以上32767以下のすべての整数32768通り、C3列索引は0以上10000以下のすべての整数10001通りです。値の種類がこれだけあるので、ひとつの列値に該当する行数の存在する確率は1/Index full key cardinality となります。ここでは分布統計は取られていなくて均一な分布が仮定されています。

db2batch スナップショット・モニター出力の着眼点(5)

このような索引スキャンとしては同様な２つのアクセス・プランを実行させ、上下でくらべてみます。どちらも同じように索引階層を読んで行きBuffer pool index logical read回数が共に３で同じです。一方、表のデータ・ページを読んだ回数:Buffer pool data logical readは上が21回、下が83回と上が少なくなっています。これは、索引のWHERE条件にヒットした行数が上は21、下は83であることに対応しています。これは実行時の実績です。(2)のアクセス・プラン・グラフの見積もり行数30.5と99.9は統計情報による見積もりでした。また、見積もりとしてはFETCHまでの累積IOコストがBuffer pool index logical read + Buffer pool data logical readに近いものです。

上に戻る

2.FETCHの述部とフィルター・ファクター

等号による述部のフィルター・ファクターのもうひとつの例としてWHERE条件に２つの述部を持つ例を挙げます。一方の述部は主キーでこの索引が使われます。ここからの図は上下の対でなく１ページずつの図が続きます。

再現用SQL(6)

SELECT文のWHERE 条件はC1=4864 AND C3 =4864の2つの条件があります。

アクセス・プラン・グラフ(7)

このアクセス・プランは主キーの索引を使っています。凡例にあるように結果行数を見るとIXSCAN後に1行、FETCH後に9.999E-005行となっています。累計IOコストはIXSCANの段階では2ページ、FETCHの段階では3ページの見積もりです。対応して累計コストはIXSCANまでで25.7、FETCHまでで38.5です。これらは後で前のケースと比べます。

アクセス・プラン詳細部分IXSCAN (8)

IXSCANオペレーターの詳細部分に、この索引スキャン中に適用される述部C1=4864がStart Key PredicateおよびStop Key Predicateとして使われていて、Filter Factorは1e-006 つまり1/100万です。これは主キーの索引がユニーク索引であることから100万行の表に対しても1件を選び出す、つまり１００万分の1に絞り込む効果があります。今回は載せていませんが、主キーの索引の統計情報にIndex full key cardinality が1000000となっていてフィルター・ファクターが1/1000000となります。

アクセス・プラン詳細部分FETCH (9)

IXSCAN(オペレーター番号4)の上位のFETCH(オペレーター番号3)にもこのSQLの述部が現れます。FETCHはレコードIDをもとに表からRIDの指す行を取り出す操作です。行はページに格納されるためページ単位の論理および物理IOを伴います。FETCH操作の中の術部は、取り出している行が合致するかどうかを判定し、絞り込みます。FETCHオペレーターの述部：C3=4864にもFilter Factorがあり、ここでは1/10001である9.999e-005です。C3列のカーディナリティが10001通りなので条件に合う確率が1/10001だという意味であり、FETCHした行のうちの1/10001が上位へ渡される見積もりです。

db2batch スナップショット・モニター出力(10)

上のようにWHERE条件に２つの述部があるSQLでひとつは索引スキャンの述部として、もうひとつはFETCHの述部として構成されたアクセス・プランが得られました。これを実行したスナップショット・モニターを見ると予定通り、索引階層をリーフページまで3ページ読み、１行取り出しのための１ページを表から読む動きが観測されます。主キーの列を条件として使った索引スキャンが効果的に使われています。

上に戻る

3.複数述部からの索引選択

上のSQLのFETCHの述部：C3=4863は索引列でもあります。C3列索引を使うアクセスも組み合わせとしてはありえます。しかし、結果として選ばれませんでした。これは、主キーがあれば使うというルールがあるわけではなく、コスト評価の結果です。この点を見てみましょう。

スナップショット・モニター(11)

選ばれなかったC3列索引を使うIXSCANが実行されたとしたら、今回の(5)の下から取った左側の図のように83ページものデータ・ページにアクセスしてしまいます。右側の主キー索引列C1を使えば表のデータ・ページは１ページしか読みません。右側が処理量が少ない結果はスナップショット・モニターから見えます。

コスト評価(12)

このSQLは述部にC1列とC3列がありますから、C1列による主キーの索引もC3列による索引もアクセス・プランの候補となります。このうち主キー索引を使ったアクセス・プランが選択されたのはコスト評価の結果です。もし使われたらどうなるか比較してみましょう。

右側は(7)で見たばかりのアクセス・プラン・グラフで、左側はもしC3列の索引が使われたとしたらをあらわす(2)の下のアクセス・プラン・グラフです。厳密には(2)にはC1=4864という条件含ませませんがそれを除き同等で参考になります。

(2)で上下を比べたのと同様にIXSCAN( 4)の累計IOコストは共に2ページです。累計コストは扱う行数の多い左がわずかに多いだけで大差ありません。しかし、IXSCAN( 4)の後は、左側の大きなフィルター・ファクターがずっと多い出力行数（ここでは索引レコードID数）99.99を生み、それに基づく行のFETCHでもRID数の増加に対応して累計IOコストが大きくなり、その結果、そこまでの累計コストが837.2対38.5と左が大きくなっています。ここで大差がつき右のコストが有利との結論に至ります。始めに少なく絞り込む方が後続の処理量が減って累計コストが少なくなり有利だと言えます。

関連する統計情報

上の例では等号のフィルター・ファクターをIndex full key cardinalityの逆数と説明しましたが、別の言い方も出来ます。

単一列索引においてはIndex full key cardinality = Index first key cardinality　です。単一列索引は２列目以降がないので(索引全列で異なるキー値の数)＝(索引第一列で異なるキー値の数)となります。

Index full key cardinality は SYSSTAT.INDEXESのFULLKEYCARD列のことです。
Index first key cardinality は SYSSTAT.INDEXESのFIRSTKEYCARD列のことです。
Index first key cardinality = SYSSTAT.COLUMNSのCOLCARDです。

これらから、等号で値と比較する述部のフィルター・ファクターは1/COLCARDと言うことができます。

COLCARDは索引第一列でなければ列の先頭から33バイトをハッシュして算出されます。異なる列値の数を得るのにソートせずに算出するので統計情報取得は高速です。
COLCARDが索引第一列ならばIndex first key cardinalityの値が使われます。Index first key cardinalityは索引がキー順に並んでいるので正確に算出されます。
もしも、33バイトを超える列で先頭から33バイトより後の部分にユニーク性が高い値が入っているような列を術部に等号指定で使う場合は、その列を索引先頭列とする索引を作ればCOLCARDが正確になりフィルター・ファクターが正確になります。

参考文献

• developerWorks Japan: Information Management : Information Managementの日本語技術情報サイトです
  
  
• developerWorks: Information Management(US) : Information Managementの英語の技術情報サイトです
  
  

著者について

プロフェッサー奏太郎
DB2を知り尽くすスーパーフクロウ！

生息地： 日本／趣味： アクセス・プランを眺めること／好物： 納豆入りみそ汁

この記事を評価する

コメント

上に戻る

目次

• 1.等号述部のフィルター・ファクター
• 2.FETCHの述部とフィルター・ファクター
• 3.複数述部からの索引選択
• 参考文献
• 著者について
• コメント