インデックス・アドバイスの凝縮

DB2 for IBM i には「インデックス・アドバイザー」という機能が標準装備されています。

DB2 for IBM i のオプティマイザーは、与えられた SQL とデータの状態から最適なプランを作成するだけではありません。
その過程で、常々"こんなインデックスがあれば効率のいいアクセスができるのに"とか"こんなインデックスがあったらもっと精度の高いアクセス・プランが作れるのに"といったようなことを考えているわけです。

その情報を適宜書き出すようにしたのが、この「インデックス・アドバイザー」という機能になります。

この「アドバイス」は個々のSQL について成されるようになっています。たいていの場合、同じテーブルに対して実行されるSQL はひとつではないわけで、その結果として発行されたSQL の数だけ「アドバイス」されたインデックスが作成されてしまう、というようなことも起き得ます。

それでかまわないこともありますが、いくつか似たようなインデックスがアドバイスされているような場合には"これをまとめられないのかな?" という感想を持つのはもっともなことでしょう。

DB2 for IBM i のインデックス・アドバイザーには、同一のテーブルについての「アドバイス」をまとめて、できるだけ少ないインデックスにまとめて"凝縮"する機能があります。

今回は、この「アドバイスされたインデックス」をまとめてより少ないインデックスに「凝縮」する機能について、見ていきたいと思います。


GUI

通常は System i ナビゲーターの「データベース」の「表」から↓のようにメニューをたどって見ることができるようになっています。

(↑で指しているテーブルとは異なっていますが)メニューを実行すると、↓のような画面が現れます。

この画面から↓のように右クリックメニューを選択できるようになっています。

「索引を作成」と選択すると、インデックスを作成するためのダイアログ・ボックスが表示されます。

「SQL の表示」を選択すると、インデックスを作成するためのSQL が生成され、「SQL スクリプトの実行」が起動されます。

「ステートメントの表示」を選択すると、「プラン・キャッシュ・ビューアー」が起動されます。このテーブルに関係のある SQL (= このインデックスを作成すると影響を受けるSQL、ですね) が表示されるようになっています。

「索引を作成」を選択すると↓のような画面になります。

プログラミング・インターフェイス

また、ツールなどで使用するために、GUI ではなくプログラミングできるインターフェイスで情報を取得したい、という場合もありますね。

CONDENSEDINDEXADVICE というビューがありますので、これをアクセスすれば同様の情報を得ることができます。
たとえば、↓のようなかんじですね。

SELECT 
 TABLE_SCHEMA ,
 SYSTEM_TABLE_NAME,                       
 SUBSTR(KEY_COLUMNS_ADVISED,1,35), 
 INDEX_TYPE,
 LAST_ADVISED,    
 TIMES_ADVISED, 
 ESTIMATED_CREATION_TIME, 
 LOGICAL_PAGE_SIZE,    
 MOST_EXPENSIVE_QUERY, 
 AVERAGE_QUERY_ESTIMATE, 
 TABLE_SIZE 

FROM CONDENSEDINDEXADVICE 

WHERE table_schema LIKE 'Q%' AND average_query_estimate > 10

この例では、WHERE のところで Q でライブラリー名(スキーマ名)が始まるもの -- つまりシステム・ライブラリー内のテーブルのみを選択していますが、もちろん、いろんな指定ができます。ぜひいろいろ試してみてください。

ビューの元になっている、テーブル関数を使用する、という手もあります。
引数はライブラリー名(スキーマ名)とテーブル名の二つです。

SELECT * 
FROM TABLE(QSYS2/CONDENSE_ADVICE('QUSRSYS', 'QAEZDISK')) AS CIA 

↑と同じテーブルなのですが、微妙に差がありますね。

* を個別に書きたい場合は↓のようになります。

SELECT 
 TABLE_PARTITION,
 KEY_COLUMNS_ADVISED,
 INDEX_TYPE,
 LAST_ADVISED,
 TIMES_ADVISED,
 ESTIMATED_CREATION_TIME,
 LOGICAL_PAGE_SIZE,
 MOST_EXPENSIVE_QUERY,
 AVERAGE_QUERY_ESTIMATE,
 TABLE_SIZE,
 NLSS_TABLE_NAME,
 NLSS_TABLE_SCHEMA


FROM TABLE(QSYS2.CONDENSE_ADVICE('QUSRSYS', 'QAEZDISK')) AS CIA ;

↓が↑の GUI のところで見た例についての実行例になります。

その他

↓のリンクが参考になると思います。

Index Advisor Surfaces Maintained Temporary Indexes Activity on DB2 for i5/OS

インデックス・アドバイザーについていろいろ載っています。
"凝縮"されたビューは、元々のインデックス・アドバイザーのテーブルを見ています。各カラムの意味は同じなので、意味のあいまいなものは参照して確かめてみてください。
たとえば、↓のようなかんじですね。
Logical Page Size Advised (KB) Recommended page size to be used on the PAGESIZE keyword of the CREATE INDEX SQL statement when creating this index.
Most Expensive Query Estimate Execution time in seconds of the longest-running query that generated this index advice.
Average of Query Estimates (seconds) Average execution time in seconds of all queries that generated this index advice.

TechTip: Cut to the Chase with DB2 Index Advisor PTFs

Simplify DB2 for i5/OS index advice

"凝縮"されたビューの定義なども載っています。
QSYS2.CONDENSEDINDEXADVICE (    
   TABLE_NAME              FOR COLUMN TABNAME      VARCHAR(258) CCSID 37 NOT NULL , 
   TABLE_SCHEMA            FOR COLUMN TABSCHEMA    CHAR(10) CCSID 37 NOT NULL , 
   SYSTEM_TABLE_NAME       FOR COLUMN SYS_TNAME    CHAR(10) CCSID 37 NOT NULL , 
   PARTITION_NAME          FOR COLUMN TABPART      VARCHAR(128) CCSID 37 DEFAULT NULL , 
   KEY_COLUMNS_ADVISED     FOR COLUMN KEYSADV      VARCHAR(16000) CCSID 37 DEFAULT NULL , 
   INDEX_TYPE                                      CHAR(14) CCSID 37 DEFAULT NULL , 
   LAST_ADVISED            FOR COLUMN LASTADV      TIMESTAMP DEFAULT NULL , 
   TIMES_ADVISED           FOR COLUMN TIMESADV     BIGINT DEFAULT NULL , 
   ESTIMATED_CREATION_TIME FOR COLUMN ESTTIME      INTEGER DEFAULT NULL , 
   LOGICAL_PAGE_SIZE       FOR COLUMN "PAGESIZE"   INTEGER DEFAULT NULL , 
   MOST_EXPENSIVE_QUERY    FOR COLUMN QUERYCOST    INTEGER DEFAULT NULL , 
   AVERAGE_QUERY_ESTIMATE  FOR COLUMN QUERYEST     INTEGER DEFAULT NULL , 
   TABLE_SIZE                                      BIGINT DEFAULT NULL , 
   NLSS_TABLE_NAME         FOR COLUMN NLSSNAME     CHAR(10) CCSID 37 DEFAULT NULL , 
   NLSS_TABLE_SCHEMA       FOR COLUMN NLSSSCHEMA   CHAR(10) CCSID 37 DEFAULT NULL )
テーブル関数の方も載っています。実体は QSYS/QDBSSUDF2(CONDENSE_ADVICE) なんですね。
CREATE FUNCTION QSYS2.Condense_Advice(TABLE_SCHEMA VARCHAR(128), 
                                      TABLE_NAME VARCHAR(128) ) 
  RETURNS TABLE(TABLE_PARTITION VARCHAR(128), KEY_COLUMNS_ADVISED VARCHAR(16000), 
                INDEX_TYPE CHAR(14), LAST_ADVISED TIMESTAMP, TIMES_ADVISED BIGINT, 
                ESTIMATED_CREATION_TIME INTEGER, LOGICAL_PAGE_SIZE INTEGER,
                MOST_EXPENSIVE_QUERY INTEGER, AVERAGE_QUERY_ESTIMATE INTEGER,
                TABLE_SIZE BIGINT, NLSS_TABLE_NAME CHAR(10),NLSS_TABLE_SCHEMA CHAR(10))
  LANGUAGE C 
  NOT DETERMINISTIC 
  READS SQL DATA 
  CALLED ON NULL INPUT 
  SCRATCHPAD 325064 
  DISALLOW PARALLEL     
  FINAL CALL 
  CARDINALITY 1 
  EXTERNAL NAME 'QSYS/QDBSSUDF2(CONDENSE_ADVICE)' 
  PARAMETER STYLE DB2SQL;   


-- Query the condenser UDTF directly, providing selection criteria and ordering 
-- the results of the 15 most important condensed entries
     SELECT * FROM TABLE(QSYS2.CONDENSE_ADVICE('CRPDTA','B123456')) AS a
       WHERE a.table_size >(1024*1024) AND MONTH(last_advised)= MONTH(CURRENT TIMESTAMP)
     ORDER BY average_query_estimate DESC 
     FETCH FIRST 15 ROWS ONLY ;

V5R4 Enhancements for Index Advisor and Autonomic Indexes Or Make Your SQL Code Fly in V5R4

[Top Pageに戻る]

Ads by TOK2