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

2006年1月20日（金）

索引SARGable述部と索引Start key/Stop key述部

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

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

これまで索引スキャンではindex start key predicateとindex stop key predicateの例を見てきました。以下、これを索引Start key/Stop key述部と略します。
今回はもうひとつの索引述部タイプであるindex sargable predicate（索引SARGable述部） について見て行きましょう。最初にページの上下で索引SARGable述部と索引Start key/Stop key述部を対比させます。後半ではいろいろな索引SARGable述部の例を見ます。

再現用SQL(1)

上下のSQLは前回の等号で値と比較する述部のフィルターファクターで見たものと同等です。テスト表T3はこれまで使ったT1とデータが同じで索引構成列が異なります。 上のSQLはWHERE条件がc2列の等号による値との比較となっています。下のSQLではWHERE条件はc3列の等号による値との比較となっています。これらの列を含む索引IX2T3は3列からなる複数列索引でc3,c2,c1の並び順で定義されています。上はc2列が条件ですがこれは索引の第二列、下はc3列が条件ですがこれは索引の第一列です。上の列が索引SARGableな述部で、下の列が索引Start/Stop述部となっていることが後のdb2exfmt出力でわかります。 前回の例は上下で応答時間に差がありませんでした。今回は応答時間にはっきり差が出るようにファイル・システム・キャッシュをバイパスしてバッファープールにヒットしなければディスクへ物理IOが発行されるように表スペース定義を変えてみました。その結果、今回は下の例の方が該当した行数が多いにもかかわらず、応答時間は速くなっています。

アクセス・プラン・グラフの着眼点(2)
上下のアクセス・プラン・グラフは索引も操作もそっくり同じで、見積もり行数・累積コスト・累積IOコストの違いだけです。ここではIXSCAN( 4)の累積IOコストに大きな違いがあることに注目しましょう。上は5849ページ、下は2ページという見積もりです。上位の操作の累計IOコスト・累計コストの違いはここに起因しています。

アクセス・プラン詳細部分の着眼点(3)
IXSCAN 4)オペレーターの上下の詳細部分ではアクセス・プラン・グラフに表示されたように累積IOコストに違いがあります。そして述部が上ではSARGable predicate、下ではStart key predicateとStop key predicateと異なっています。
ここでの索引SARGable 述部は000から999まで順に並んだキーから十の位が(例えば)3のものを選びだすようなものです。十の位が3のものは百の位は0から9まで何でもよいので始めのほうにも途中にも終わりのほうにもいずれにも出てきますから、全部読んで調べる以外に探し方がありません。一方、索引Start key/Stop key述部は百の位が3のものを選ぶようなものです。百の位が2以下は対象外として百の位が3のものから探索を始めることが索引階層構造からできます、また百の位が4になったら探索をやめればよいです。
このように索引SARGable 述部は索引の全キーを読んで判定する述部です。索引Start key/Stop key述部は索引キーのある範囲に限って読むことを可能にする述部です。このため索引リーフ・ページを読む量が減ります。索引SARGableでは全索引キーを読むために索引の全リーフ・ページを読む必要があります。これがIOコストに差がある理由です。なお、フィルター・ファクターは前回出てきたとおりの値で変わっていません。

db2exfmt　統計情報の着眼点(4)
上下は同じ索引が使われているので統計情報は同じです。索引統計の最後に索引を構成する列名がのっています。述部の列が第何列かはここでも確認できます。索引の全リーフ・ページを読むのが実際何ページなのかはここで確認できます。また索引階層数も載っています。

db2batch スナップショット・モニター出力の着眼点(5)
上は索引を5852ページ読み、表を21ページ読んでいます。下は索引を4ページ読み、表を83ページ読んでいます。上の索引SARGable述部による索引アクセスは表スキャンよりは有利かもしれないものの、索引Start key/Stop key述部よりは索引読み取りページ数が多いアクセス・プランであることがわかります。
これはアプリケーションの性能要件によって好ましいこともそうでないこともあるでしょう。索引Start key/Stop key述部が使われているかどうかはIXSCANの詳細の述部の表示でわかりましたが、もう少し手軽な確認方法を次で見ます。

db2explnの着眼点
db2explnは生成されたアクセス・プランを表示するツールです。db2explnではdb2exfmtのように途中までの累計コストや累計IOコストやフィルター・ファクターは見えません。 しかしながら、db2expln固有の#Key Columnsという項目で索引Start/Stop 述部が使われていることを確認できます。上の索引スキャンでは#Key Columns = 0 は索引Start/Stop述部が遣われていないことを意味します。下の索引スキャンでは#Key Columns = 1 で先頭1列が索引Start/Stop述部として使われることを意味します。見やすさ・手軽さではこちらが便利です。

上に戻る

同義語
範囲区切り述部（Range Delimiting）と索引Start key/Stop Key 述部：
DB2 UDB管理ガイドの述部の用語のところに、範囲区切り述部(Range Delimiting Predicate)という用語があり、索引スキャンの範囲を限定し索引探索の開始および停止キー値を提供すると書いてあります。これはdb2exfmtで出てくるIndex Start Key PredicateとIndex Stop Key Predicateと同じです。

索引SARGable述部についてはdb2exfmtもDB2 UDB管理ガイドも同じです。管理ガイドではSARGableと大文字を使っています。これは”サーチ・アーギュメントとして使える”と言う意味のIBMによる造語であることを強調したためでしょう。

索引SARGable述部の例
今回ここまでの下段の例は複数列索引の第一列が索引Start Key/Stop key述部になっているものでした。索引第一列がいつも索引Start/Stop述部になるわけではありません。次に索引第一列が索引 SARGable述部となる例を見ていきましょう。

• 例1.　索引Start/ Stop述部と索引SARGable述部の組み合わせ
  最初の例はWHERE C3 <> 100 and C3 between 50 and 150 です。これも索引スキャンとなっていますが、述部はC3 between 50 and 150がStart/Stop述部となり、C3 <> 100は索引SARGable述部となっています。<>では索引の読み取り範囲を限定できないためSARGableとなります。between 50 and 150のフィルター・ファクターは前々回の式(f1 + f2 -1)により、(0.0150985 + 0.995 -1) =0.0100985 となります。索引SARGable述部はこれに対してさらに絞り込みます。 <> 100による絞り込みも含めると 0.0100985 * 0.9999 = 0.01009749 となり、元の100万行に対して 10098行を絞り込み見積もりとなります。これがIXSCAN ( 3)の上の見積もり行数です。
  
• SARGable述部の例2.　where c3 + 1 =101
  次の例は左辺が演算で書いてあります。WHERE C3 = 100 と書くのと WHERE C3 +1 =101 と書くのでは意味は同じになります。アクセス・プラン・グラフも索引スキャンとなっています。ところが、このコストをひとつ前：例1)のコストと比べると２桁上がっています。今回の始めの下段の例と比べてもコストは２桁上がっています。その要因は索引スキャンのIOコスト（ページ数）が２桁大きいことに起因しています。それはこの述部の判定には索引リーフ・ページを全部読むことによります、それは述部がIndex SARGableだからです。
  アクセス・プラン・グラフを見て索引スキャンだからといって安心せず、述部がindex SARGableなものだけになっていないか確認するようにしましょう。このケースはWHERE C3 = 100と書き直せば索引Start key/Stop key述部に代わりずっとコストが下がります。応答時間の向上も見込めます。
  
• 例3.　where c3*c3 + 1 =10001
  三番目の例はどこまでindex SARGableとして述部が使われるのか試すテストです。これも索引SARGable述部となります。オプティマイザーは2)や3)のような一次方程式、二次方程式を解いてくれるわけではなく、全てのC3の値を索引から得て代入してみて等号を満たしたものを取り出します。2)と3)のコストをじっと見比べるとIXSCANのIOコスト（ページ数）は同じでコストがわずかに3)が多くなっています。これは比較のための演算のCPUコストが3)がわずかに多いことによります。
  
• 例4.　where char(c3) = ’100’
  列C3に関数をかぶせてみても索引SARGable述部になっていることがわかります。
  
• 例5.　where upper(char(c3)) = ’100’
  列C3に関数を二重にかぶせても索引SARGable述部になっていることがわかります。
  
• 例6)　where c1 like ‘%5571%’
  後方一致のLIKEも索引SARGable述部になりうることがわかります。
  
• 例7) Start/Stop述部　where c1 like ‘5571%’
  最後は前方一致のLIKEは索引Start key/Stop key述部となる例です。IXSCANのIOコストが小さくなりました。

まとめ

クイズ

1. where c1 = ‘x’
2. where c2 = ‘y’
3. where c3 = ‘z’
4. where c1 between ‘x’ and ‘y’
5. where c2 between ‘x’ and ‘y’
6. where c3 between ‘x’ and ‘y’
7. where c1 <> ‘x’
8. where c2 <> ‘y’
9. where c3 <> ‘z’
10. where c1 like ‘xxxx%’
11. where c2 like ‘xxxx%’
12. where c3 like ‘xxxx%’
13. where c1 like ‘%yyyy’
14. where c2 like ‘%yyyy’
15. where c3 like ‘%zzzz’

上に戻る

上に戻る