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

2006年3月22日（水）

INDEX ORING と INDEX ANDING

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

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

今回は2つ以上の索引を使う索引アクセスである INDEX ORING と INDEX ANDING を見ます。INDEX ORING と INDEX ANDING とリスト・プリフェッチ(第3回で紹介)の3つは deferred data page access(据え置きデータページ・アクセス)と呼ばれています。今回は4つのパートからなります。

1. INDEX ORINGとINDEX ANDINGの特徴 2. 索引アクセスの体系 3. 据え置きデータ・ページ・アクセスの行ロック取得タイミング 4. 据え置きデータ・ページ・アクセスの非同期IOサーバーの使用

再現用SQL(1)
今回も表T1を使います。上のSQLはC2列とC3列の等号の述部を OR で結び、下のSQLはANDで結合します。C2列、C3列にはそれぞれ単1列索引が作成されています。ORでは120行がヒットしANDでは1行がヒットしています。

アクセス・プラン・グラフ(2)
上のINDEX ORINGでは2つの索引がIXSCANでアクセスされその結果がSORTされRIDSCAN(レコードIDスキャン)で取り出され行がFETCHされています。OR条件を満たすために2つの索引からの結果が合併されています。IXSCANの結果行数はC2列の索引では30.5176、C3列の索引については99.99行と見積もられています。FETCH後の最終的な見積行数はこれらの和の130.505行です。IXSCAN, SORT, RIDSCAN, FETCHの流れは第3回で見たリスト・プリフェッチと同等です。リスト・プリフェッチは INDEX ORINGで索引が1個の場合ともいえます。

下の INDEX ANDINGでは同様に2つの索引が IXSCANされています。各索引の出力は IXANDオペレーターに渡されています。IXANDでは各索引から得たレコードIDをハッシュした値をつき合わせて共通のものを選択します。IXANDの結果は0.00305145行です。これは1,000,000 ^* (1/32768) ^* (1/10001)に一致します。IXANDの後は上と同様にリスト・プリフェッチと同じSORT, RIDSCAN, FETCHが続いています。

下のケースのSQL はINDEX ANDINGに頼らなくても1索引だけで取り出せます。例えば C3列の索引で30.5176行絞って表から対象の行を取り出し、それらをFETCHの段階でC2列の条件で絞ればよいです。第5回の(6)(7)(8)(9)(10)では単一列索引(C1列)の IXSCANの後に FETCHでもう1列(C3列)の条件で絞っていました。下のケースで1索引スキャンだけですませるアクセス・プランの候補があるにもかかわらず、索引を2個使うIXANDが選ばれたのはコスト評価の結果です。

IXANDまでの累積IOコストは4（3階層の索引をルートページ以外に2ページ下がる索引アクセスが索引2個分）です。C3索引だけを使ったアクセス・プランがあったとしたらそれは30.5行のランダムな表アクセスが必要となりそのIOコスト(見積ページ数)は2よりずっと大きく見込まれ、総コストを押し上げてIXANDを使うケースに負けます
。 IXANDの上位のFETCHのIOコストは4.00305です。これは下位のIOコスト4に0.00305行取り出しのIOコスト0.00305を加えたものです。あらかじめ索引レコードでANDINGを行うことで少ないIOコストのFETCHおよび少ない累計コスト・総コストが見込まれました。このようにIXANDが選ばれるかどうかもコストによって判定されます。

今回はアクセス・プラン詳細部分の着眼点は後で見ます。

db2exfmt　統計情報の着眼点(3)
INDEX ORINGもINDEX ANDINGも同じ索引を使っています。C2列索引には32768通りの値があり、C3列索引には10001通りの値があります。第5回と同じくこれが等号の述部のフィルター・ファクター：1/32768, 1/10001を決め、見積行数：100万 ^* ( 1/32768)行と100万 ^* ( 1/10001) 行の算出の元となりました。

索引のリーフ・ページ数はC2列索引が1710ページ、C3列索引が1664ページとC2列の方が少なくなっています。索引列長は同じです。この違いもC2列索引のカーディナリティーが高い(索引キー値の種類が多い)ためです。大きさの比は3.2対1ほど離れていません。索引には索引キー長＊索引キーのカーディナリティーに加えて100万個のRIDがどちらも入っているため差は縮まります。

db2batch スナップショット・モニター出力の着眼点(4)
上の INDEX ORINGで、1.2つの索引を読みRIDを得て、2.RID をページ番号順にSORTし、3.該当行のあるページを取り出す状況がモニターされています。アクセス・プラン・グラフのSORTは2個ですが実際のSORTは1回です。ORの両方の条件にヒットしたRIDがあるかもしれません。結果として返す行は1行なので対象 RID 全体の重複排除が必要です。さらに、同じページにある2つ以上のRIDの取り出しのためのページ取り出しを1回ですませます。今回は120行の取り出しのために表の117ページが取り出されています。わずかですが3ページの取り出しの重複を削減しています。このケースはバッファープールに100%ヒットしていて物理IOは発行されていません。

下のINDEX ANDINGでも同様に、1.2つの索引を読みRIDを得て、ANDINGし、2.RIDをページ番号順にSORTし、3.該当行のあるページを取り出す状況がモニターされています。

このようにINDEX ANDINGは複数の索引からANDINGで対象RIDを絞り込んで得た後に表をアクセスします。INDEX ORINGは複数の索引からORINGで対象RIDを得た後に表をアクセスする形式の索引アクセスです。どちらも重複ページ取り出しの削減のためRIDのページ単位のSORTが含まれます。

上に戻る

今回で索引アクセスの種類がすべて出そろいました。ここで整理しておきましょう。 第2回で索引スキャンと表スキャンを取り上げました。索引スキャンには実際には2つのグループと合計5つのタイプがあります。標準の索引スキャンには索引だけをアクセスするINDEX SCANと表のFETCHを伴うINDEX SCAN + FETCHの2つのタイプがあります。後者は第5回で等号の述部のフィルター・ファクターの例に出てきました。

もうひとつのグループが据え置きデータ・ページ・アクセス（deferred data page access）です。これには第3回で取り上げたリスト・プリフェッチと今回のINDEX ORINGとINDEX ANDINGがあります。据え置きデータ・ページ・アクセス（deferred data page access）の名前の由来は、索引からRIDをひとつ得て直ちに行を取り出すのではなく複数の索引のORINGやANDINGや、リスト・プリフェッチのように多数のRIDをまとめて効果的にプリフェッチする特徴からきています。標準のINDEX + FETCHは少量の索引レコードがヒットするケースを高速に処理します。据え置きデータ・ページ・アクセスは索引のヒット件数が多いケースを救う効率的なアクセス方式です。

標準の索引アクセスと据え置きデータ・ページ・アクセスは処理効率だけでなく、行ロックの獲得タイミングが異なります。次にこの点を見ましょう。

上に戻る

INDEX ORING アクセス・プラン・グラフ詳細部分の着眼点(5-1)
INDEX ORINGの例では2つの索引のそれぞれに対してIXSCANがありました。どちらのIXSCANにも行ロックの意図(ROWLOCK)がありますが、これがどちらもNONE、行ロックなしとなっています。つまりINDEX ORINGではIXSCANの際に該当のレコードIDに行ロックは取られません。このEXPLAINは分離レベルCS ( READ_COMMITTED)で取得しています。
標準の索引スキャンでどうだったか？　第5回の資料の同じ部分を見るとIXSCANで行のNEXT KEY SHAREロックを取っていることがわかります。NEXT KEY SHAREは単純な読み取りロックです。

分離レベルCS (READ_COMMITTED)でも索引スキャンの際にRIDに行ロックを取らないのが据え置きデータ・ページ・アクセスの特徴です。INDEX ANDINGやリスト・プリフェッチでも同じです。

INDEX ORING アクセス・プラン・グラフ詳細部分の着眼点(5-2)
紙面の都合で別ページに分かれましたが、IXSCANの述部としては、それぞれの索引のIXSCANについてC2列、C3列の述部がIndex Start key/Stop key Predicateとして載っています。

INDEX ORING アクセス・プラン・グラフ詳細部分の着眼点(5-3)
このアクセス・プランは、読み取れた行はすべてCOMMIT済みのものだ、という分離レベルCS (READ_COMMITTED)の定義に適合しているのでしょうか？
答えはFETCHの部分にあります。FETCHの処理ではRIDSCANで取ってこられたページの行の行ロック NEXT KEY SHAREを取ります。FETCH で読めた段階では読み取りロックが取得できているので行は COMMITされたものだといえます。

据え置きデータ・ページ・アクセス(リスト・プリフェッチ、INDEX ORING、INDEX ANDING)ではなぜ、このように行ロック取得をFETCH の段階まで遅らせるのか？

これらのアクセス・プランではある程度多くのRIDを扱います。読み取りロックとはいえ、多量の行ロックを長期に保持することを控えて必要な行だけにできるだけ遅めにロックを保持することがその狙いです。

2つのIXSCANで述部を既に絞り込みに適用しましたが、FETCHでも同じ術部をORで結合して再度適用していることが、FETCHのSargable Predicateの部分に載っています。なぜ一度適用した述部を二重に適用するのでしょうか？
IXSCANの際に行ロックを取っていないので、選ばれた行はその後のSELECT処理途中にDELETEされたり、列値がUPDATEされたりする可能性があります。FETCHで索引の適用した述部全体を適用して消えたり変わったりした行があれば除外するためです。

述部の再適用にあたって、下位のRIDSCANから来る130.505行はFETCHの出力でも130.505行のまま変わっていません。念のための適用であって、さらなる絞り込みは起こらない仮定です。

このように据え置きデータ・ページ・アクセスは複数の索引を活用したり,多くのRIDを効率よく扱いながら、分離レベルCSの定義を遵守して動きます。分離レベルRS(ISO REPEATABLE_READ)も同様です。

INDEX ANDING アクセス・プラン・グラフ詳細部分の着眼点(5-4)
INDEX ANDINGの上位のFETCHでも同様に行の NEXT KEY SHAREロックが取られ、IXSCANに使われた述部全体が再適用されます。

上に戻る

第3回のリスト・プリフェッチはbetweenによる述部を使用しました。上のような等号による述部の代わりに今回のINDEX ORINGやINDEX ANDINGをbetweenによる述部を使ってより多くの行がヒットするケースがバッファープールにあまりヒットしない状態を見てみましょう。

アクセス・プラン・グラフ(6)
上側のbetweenの述部のorによるINDEX ORINGの再現 SQLはwhere 条件の対象行が増えるため、最初のケースより各箇所のIOコスト、累積コスト、行数が増えています。複数のIXSCANと後続の SORT, RIDSCAN, FETCHがそろっていて INDEX ORINGを形成しています。今回は FETCH 後に20,882行ヒットする見積です。

下側のbetweenの述部のandによるINDEX ANDINGの再現SQLもIOコスト、累積コスト、行数が最初のケースより増えています。複数のIXSCAN に続くIXAND, SORT, RIDSCAN, FETCHの流れが表示されていてINDEX ANDINGを形成しています。今回は FETCH後に36.4行ヒットする見積です。

db2batch スナップショット・モニター出力の着眼点(7)
今回の最初の例と比べ、物理IOが発生しています。また、各種READ回数も増えています。流れは、1.2つの索引を読み RID を得て、2.RID をページ番号順にSORTし、3.データ・ページの物理IOの大多数が非同期IOサーバーによって並列先読みされ、4.該当行のあるページを取り出す、状況が表示されています。
ヒットした行数が20984行であるのに対してデータ・ページの論理読み取りは8493回であり、重複の排除により6割が削減されています。また、物理読み取り8060ページ中の7362ページ(9割)が非同期並列読取りによって取り出し効率アップされています。

下のINDEX ANDINGも1.2つの索引を読みRIDを得て、ANDINGし、2.RIDをページ番号順にSORTし、3.データ・ページの物理IOの大多数が非同期IOサーバーによって並列先読みされ、4.該当行のあるページを取り出す状況がモニターされています。

上に戻る

INDEX ORINGとINDEX ANDINGについての要点は以下です。
複数の索引からRIDを選択して表をアクセスします。
物理読み取りには非同期IOサーバーが使われます。
リスト・プリフェッチも加えた3つを据え置きデータ・ページ・アクセス(deferred data page access)と呼びます。
据え置きデータ・ページ・アクセスでは省略時の分離レベルCSと分離レベルRSでは索引スキャンの時点では行ロックは取られずFETCHの時点で行ロックが取ることによって行ロックの対象と期間を限定しています。

クイズ

1.	次のうち正しいものはどれでしょう
A)	INDEX ANDINGに使われるオペレーターはIXANDである
B)	INDEX ORINGに使われるオペレーターはIXORである

2.	第5回の(6)(7)(8)(9)(10)下側のSELECT文は今回(1)(2)(3)下側のSELECT文と似ていますが、今回のようなINDEX ANDINGになっていませんでした。第5回の(6)(7)(8)(9)(10)下側でINDEX ANDINGが選ばれなかったのはコスト評価でどんな点が効いたのでしょう？
第5回： select sum(d3) as sum, count(d3) as count from t1 where c1=4864 and c3=4864 第8回： select sum(d3) as sum, count(d3) as count from t1 where c2=6311 and c3=6311

付録QA

Q1.	今回の(4)下側のような INDEX ANDINGで表のたった1行を取り出すために非同期IOサーバーを経由したならば、かえって遅くなるのでは？
A1)	このケースをバッファーヒットしない状況で実行すると非同期IOサーバーは使われずにDB2エージェントが自身で物理読み取りを行う状況がモニターされます。少ない行の取り出しには非同期IOサーバーが使われないこともあるようです。

上に戻る

上に戻る