スポンサーサイト

上記の広告は1ヶ月以上更新のないブログに表示されています。
新しい記事を書く事で広告が消せます。

(DB2)SQL実行時に特定のインデックスが使われないようにする方法

むかしこんにゃく工房←スポンサーサイト

特にDB2の静的SQLのような場合だと、SQL文にBETWEENを使ってローバリューとハイバリューを設定することがよくある。
例えば、オンラインの検索画面で、検索条件に「分類コード」とか「受注番号」とかその他項目がいっぱいあって「分類コード」以外が任意入力だと、
SQLのWHERE条件に、
WHERE 分類コード = :分類
AND 受注番号 BETWEEN :受注A AND :受注B
AND ○○○○ BETWEEN :○○A AND :○○B
AND ×××× BETWEEN :××A AND :××B
AND :
みたいな感じで書いてバインドパッケージすれば、バインドパッケージするSQLが1つで済むので静的SQL環境ではよく使われる手法だと思う。
(入力されたパターンごとにSQLを作らなくてよいので)

ただ、この手法を使うとDB2のオプティマイザー様が意図しないインデックスを使用することがある。

例えば、インデックス1に分類コード、インデックス 2に受注番号の2つの索引が貼ってあった場合、必須入力の「分類コード」の索引を使いたいのに、「受注番号」の絞込み率が高すぎてバインドパッケージのときに受注番号の索引が使われてしまうのだ。

DB2にはオラクルのヒント機能のようなものはないし、プランテーブルを手で作っておいて使ってバインドパッケージするような裏技的な方法もできるかもしれないが、ここではSQLを直す方法をメモしておく。

SQLで「OR」を使うとその項目の索引が使われなくなることを利用して、上記のSQLの場合、
WHERE 分類コード = :分類
AND (受注番号 BETWEEN :受注A AND :受注B OR 0=1)
AND ○○○○ BETWEEN :○○A AND :○○B
AND ×××× BETWEEN :××A AND :××B
AND :

とすれば、「受注番号」の索引は使用されなくなり、その結果、「分類コード」の索引が使われるかもしれない。
(オプティマイザー様の判断で表スペーススキャン等になるかも知れないが、どのインデックスが使われたかは、プランテーブル作るなり、SYSPACKDEPカタログ表にSELECT文投げるなりして確認。)
スポンサーサイト

(DB2)静的SQL環境でインデックス使用状況を簡単に調査する

むかしこんにゃく工房←スポンサーサイト

性能評価において使用インデックスを調べる有名な方法にPLAN_TABLE表を使用する方法があります。
ただし、これは事前にPLAN_TABLE表を作成しておく必要があるなどちょっと面倒くさかったりします。

静的SQLの環境ではSQLのバインド時に、SQLごと使用するインデックスをカタログ表のSYSPACKDEPに記録するため、この表にアクセスすれば、SQLが想定しているインデックスを使ってくれているのか調べることが出来ます。

もちろん、PLAN_TABLEのような事前に何かしておくといったことは不要です。

SQL例)
SELECT DCOLLID,DNAME,BNAME
FROM SYSIBM.SYSPACKDEP
WHERE DCOLLID = 'コレクションID'
AND DNAME = 'パッケージ名(SQL名)'
;
注)DBADM以上の特権(権限)のあるユーザで実行する必要あり。

抽出結果例
DCOLLID      DNAME      BNAME
COL1             SQL1         テーブル名
COL1             SQL1         インデックス名1
COL1             SQL1         インデックス名2
  :
この例の場合は、SQL1がインデックス名1とインデックス名2の複合索引でアクセスすることが分かります。

上記SQLを投げると、列BNAMEに該当するパッケージ(SQL)の使用オブジェクト(テーブルやインデックスなど)一覧が取得できます。
従って、BNAME列にインデックスがあれば、そのインデックスを使用していることが分かり、なければインデックスを使用しない表スキャンアクセスであることが分かります。

(ホストDB2)テーブル一覧の取得

むかしこんにゃく工房←スポンサーサイト

オラクルの場合、TABLESディクショナリ表にアクセスすれば可能だが、DB2ではSYSTABLES表というカタログ表があるのでこれにSQL文をなげます。

例)
SELECT * FROM SYSIBM.SYSTABLES;

その辺のサイトとか見ているとスキーマはSYSIBMではなくSYSCATを使用しているサンプルが多いが、仕事ではSYSIBMしか使ったことがないので、こちらのサンプルを載せておきます。

自分のスキーマだけ抽出したいなら、CREATOR列がスキーマになるので、下記のように条件をつけるといいでしょう。

SELECT * FROM SYSIBM.SYSTABLES WHERE CREATOR = 'USER1';

なお、この表はDBADM以上の特権(権限)のあるユーザでないと実行時SQLエラーになってしまいます。
プロフィール

syao88

Author:syao88
FC2ブログへようこそ!

最新記事
最新コメント
最新トラックバック
月別アーカイブ
カテゴリ
たるいカウンタ
検索フォーム
RSSリンクの表示
リンク
ブロとも申請フォーム

この人とブロともになる

QRコード
QR
上記広告は1ヶ月以上更新のないブログに表示されています。新しい記事を書くことで広告を消せます。