Comments
Description
Transcript
第３章 索引設計 内容 １．索引の目的
DB2 UDB (PC&Unix) デザイン・ガイド 索引設計 第３章 索引設計 お断り：当資料は、DB2 UDB V7.1（AIX,NT,OS2) をベースに作成されています。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 内容 １．索引の目的 ２．索引の構造 ３．索引の性質 ４．索引の設計手順 ５．その他の索引に関するトピック (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 第３章( 1-2 ) ＜第１．００版＞２００１年１月 DB2 UDB (PC&Unix) デザイン・ガイド 索引設計 １.索引の目的 索引の目的 照会処理の処理効率を高める アクセス・パスでの索引の使用による効率のよいデータへのアクセス 行のユニーク性を維持する ユニーク索引 データの並び順を索引順に維持することにより、データ・アクセスの効率を向上させる クラスター索引 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： １.索引の目的 表に作成する索引は、本の索引と同様の機能を果たします。 索引の第一の目的は、データをアクセスする際の処理効率を向上させることです。余計な入出力をすることなく、最短の方法で目 的のデータにたどりつくには、索引は非常に有効です。 ユニーク索引を作成した際には、索引のキー列のユニーク性を保証する機能を使用可能です。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 第３章( 3-4 ) DB2 UDB (PC&Unix) デザイン・ガイド 索引設計 ２.索引の構造 索引の構造はＢツリー構造 ページをたどっていくことにより、表のデータページのデータを見つける ルート・ページ：索引ページの最初のページ ページ番号とそのページに入っている索引列の最大値 ノン・リーフ・ページ：ページ番号とそのページに入っている索引列の最大値 リーフ・ページ：索引列の値とＲＩＤ（ページ番号+行が格納されている場所のページ中のアドレス（オフセット）） ”SELECT col1 from 表 where 索引列＝５５”を実行した時の索引スキャン 索引 ルート・ページ ノンリーフ・ページ ページ３ 列の値 列の値 ３０ ７０ １００ ページ４ 列の値 ４５ ５８ ７０ ページ番号 ３０ リーフ・ページ 列の値 ページ番号 ページ３ ページ４ ページ５ 表 データ・ページ ページ５ ページ番号 列の値 ページ番号 ５５ ページ１０ １００ ページ１０ ページ番号 列の値 ５２ ５５ ５８ ページ番号 ＲＩＤ ＲＩＤ ＲＩＤ 列の値 ページ番号 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： ２.索引の構造 索引の構造は、Ｂツリー構造と呼ばれる階層形式です。 ルート・ページ（索引階層の最初のページ）には、下の階層の索引ページ番号と、そのページに含まれる最大のキーの値が入っ ています。下の階層には、ノン・リーフ・ページと呼ばれるページがあり、さらにその下の階層の索引ページ番号とそのページに含 まれる最大のキーの値が入っています。最後の階層には、リーフ・ページと呼ばれるページがあり、索引列の値とＲＩＤが入ってい ます。ＲＩＤとは、索引列の値を持つ行が入っている表のデータページの番号と、その行がどの場所に格納されているかを示すポ インターです。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 第３章( 5-6 ) DB2 UDB (PC&Unix) デザイン・ガイド 索引設計 索引ページのフリースペース 行のランダムな追加を予想して、事前に確保された空きスペース 目的： ・索引ページのスプリットの頻度を下げる ・索引の再作成の頻度を下げる 設定方法 索引作成時に各リーフ・ページのフリースペースの割合（％）をPCTFREEで指定 CREATE INDEX 索引名 ... PCTFREE 数値（％） （省略時値 １０％） フリースペースが確保されるタイミング： ＬＯＡＤおよびＲＥＯＲＧの時 LOADおよびREORGユーティリティーでの、索引のBuild/Ｒebuildフェーズでフリースペースが確保される 各索引ページの最初の行については、この値の制約を受けないが、後続の行については、フリースペースを保持し ながら、索引ページに追加される ＰＣＴＦＲＥＥの値よりも、LOAD時に指定されたINDEXFREESPACEパラメーターは優先される ルート・ページ ノンリーフ・ページ リーフ・ページ 列の値 列の値 列の値 ページ番号 ７０ ページ４ ページ番号 ページ番号 列の値 ページ番号 ５８ ページ１０ 列の値 ５２ ５５ ５８ ページ番号 ＲＩＤ ＲＩＤ ＲＩＤ フリースペースは１０％ フリースペースはPCTFREEの設定 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： 索引ページのフリースペース割合の設定 PCTFREEパラメーターは3/98 FixPak(FixPak3)で使用可能です。これは、CREATE INDEX ステートメントのオプションです。 フリースペースは、索引ページのスプリットを起こすことなく、索引のエントリーをINSERTするために有効です。 PCTFREEは、索引を作成する時に、フリースペースを各索引ページの何%確保するかを指定します。最初のエントリーだけは、こ の値の制限を受けません。 数値の範囲は０から９９までで、省略時値は１０です。もし、１０以上の値が指定されていても、ノン・リーフ・ページには、１０％のフ リースペースが残されます。 LOADコマンドのINDEXFREESPACE=xパラメーターは、LOAD時に、索引ページに何％のフリースペースを残すかを指定します。 数値の範囲は０から９９までです。最初のエントリーだけは、この値の制限を受けません。省略時値はCREATE INDEXステートメ ントで指定された値です。INDEXFREESPACEの値は、CREATE INDEXステートメントで指定されたPCTFREE値よりも優先されま す。INDEXFREESPACEの値は、索引のリーフ・ページのみに有効な値です。 以下の場合には、フリースペースは必要ありません。 INSERT,DELETEがない 索引キーの更新がなく、固定長列のみなので更新による行のネスティングが発生しない 照会のみである (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 第３章( 7-8 ) DB2 UDB (PC&Unix) デザイン・ガイド 索引設計 LOAD時に索引のフリースペースを指定可能 索引の各リーフページ内のフリースペースの割合（％）を指定 LOAD ...... MODIFIED BY INDEXFREESPACE = 数値（％） CREATE INDEXステートメントのPCTFREEで指定された値を上書き INDEXFREESPACE = 40でLOADした場合 リーフ・ページ 列の値 ページ番号 列の値 ５２ ５５ ５８ ページ番号 ＲＩＤ ＲＩＤ ＲＩＤ 各リーフページの20%が フリースペース (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： LOAD時に索引のフリースペースを指定可能 ＩＮＤＥＸＦＲＥＥＳＰＡＣＥオプションは、Ｖ５．２から使用可能です。 索引のフリースペースの割合をコントロールするためには、MODIFIED BY INDEXFREESPACE=xを指定します。ｘは0から99まで の数値です。ページの最初のエントリーは、この値による制限に関係無く、ページに追加されます。次のエントリーは、 INDEXFREESPACEで指定したフリースペースのしきい値を越えない場合に、ページに追加されます。省略時値は、ＣＲＥＡＴＥ ＩＮ ＤＥＸステートメントで使用された値です。ＬＯＡＤで指定した値は、ＣＲＥＡＴＥ ＩＮＤＥＸステートメントで指定されたＰＣＴＦＲＥＥの値 よりも優先して使用され、索引のリーフページのみに影響を与えます。ＬＯＡＤコマンドのINDEXFREESPACEを使用することによ り、ＰＣＴＦＲＥＥで指定した値を上書きすることができます。ＰＣＴＦＲＥＥの値が大抵のケースには適しているが、次に行うＬＯＡＤに ついてだけは、適当でない場合などに使用します。 ＰＣＴＦＲＥＥは、表にランダムにデータをＩＮＳＥＲＴするようなアプリケーションにより、索引のリーフページが頻繁にスプリットされ てしまうのを防ぐために有効です。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 第３章( 9-10 ) DB2 UDB (PC&Unix) デザイン・ガイド 索引設計 索引ページのスプリット 索引ページが一杯になると、そのページは２つのページに分割される スプリットとは、索引ページが分割されることを指す スプリット時に分割されるデータの割合 索引ページの位置によって、分割されるデータの割合が決まる 索引のリーフ・ページが下図の様な状態であった時、 先頭ページまたは最終ページが一杯になった場合： 端になる方の新リーフ・ページが１０％のデータを引き継ぎ、もう一方のリーフ・ページが９０％のデータを引き継ぐ 中間ページが一杯になった場合： ５０％づつの割合でデータを引き継ぐ リーフページ１ リーフページ２ リーフページ３ 先頭ページ 中間ページ 最終ページ (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： 索引ページのスプリット 索引のリーフ・ページが満杯になった時点で、さらにそのページにデータが格納される必要があったとき、索引ページは分割さ れ、２つのリーフ・ページになります。分割されるデータの割合は、満杯になった索引ページが索引構造内でどの場所にあったか により異なります。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 第３章( 11-12 ) DB2 UDB (PC&Unix) デザイン・ガイド 索引設計 スプリット時のデータ分割 先頭ページのリーフページ１が一杯になった時のデータ分割 先頭ページになったリーフ･ページは１０％ 中間ページになったリーフ・ページは９０％ ページ１.ａ ページ１から １０％のデータ ページ１.ｂ ページ１から ９０％のデータ リーフ･ページ ２ リーフ･ページ３ 最終ページのリーフページ３が一杯になった時のデータ分割 最終ページになったリーフ･ページは１０％ 中間ページになったリーフ・ページは９０％ ページ３.a リーフ･ページ１ リーフ･ページ２ (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 ブランク・ページです 第３章( 13-14 ) ページ３.ｂ ページ３から ページ３から ９０％のデータ １０％のデータ DB2 UDB (PC&Unix) デザイン・ガイド 索引設計 スプリット時のデータ分割 中間ページのリーフページ２が一杯になった時のデータ分割 ２つのリーフページは、５０％づつのデータを引き継ぐ リーフ･ページ１ ページ２.ａ ページ２から ５０％のデータ (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 ブランク・ページです 第３章( 15-16 ) ページ２.ｂ ページ２から ５０％のデータ リーフ･ページ３ DB2 UDB (PC&Unix) デザイン・ガイド 索引設計 ３.索引の性質 索引の性質 ３−１．ユニーク索引／非ユニーク索引 ３−２．Include列つきのユニーク索引 ３−３．クラスター索引／非クラスター索引 ３−４．単一列索引／複合列索引 ３−５．双方向索引 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： ３.索引の性質 索引作成の構文（ＣＲＥＡＴＥ ＴＡＢＬＥ） >>-CREATE----+---------+--INDEX----索引名---------------------> '-UNIQUE--' .-,--------------------. (1) V .-ASC--. | >----ON----+-表名---------+--(-----列名--+------+----+---)-----> | (2) | '-DESC-' '-ニックネーム--' >-----+---------------------+-----------------------------------> '-SPECIFICATION ONLY--' >----*--+--------------------------------------------+----------> | .-,----------. | | (3) V | | '-INCLUDE------------------(-----列名---+---)----' .-PCTFREE 10--------. >----*--+---------+---*---+-------------------+---*-------------> '-CLUSTER-' '-PCTFREE--数値----' >-----+----------------------+--*-------------------------------> '-MINPCTUSED--数値--' .-DISALLOW REVERSE SCANS--. >----+----------------------------+---*--------------------------->< '-ALLOW REVERSE SCANS-----' (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 第３章( 17-18 ) DB2 UDB (PC&Unix) デザイン・ガイド 索引設計 ３−１．ユニーク索引／非ユニーク索引 ユニーク索引 索引キーとなる列の値のユニーク性を維持する データの変更時には索引キーの値についてユニーク性の検査が行われ、重複データである 場合には、アプリケーションにエラーが戻される 非ユニーク索引 索引キーとなる列の値の重複値を許す 構文 CREATE UNIQUE INDEX 索引名 ON 表名 （列名 順序） ......... 複合列索引も可能（最大１６列まで） 関連するシステム・カタログ ＳＹＳＣＡＴ．ＩＮＤＥＸＥＳのＵＮＩＱＵＥＲＵＬＥ列 Ｕ（ユニーク索引） Ｄ（重複キーを許す索引） Ｐ（基本キーに対応して自動生成された索引） (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： ３−１．ユニーク索引／非ユニーク索引 基本キーを作成すると、内部的に、基本キーに対応するユニーク索引が作成されます。 ユニーク索引による列にユニーク性の検査は、以下の場合に行われます。 INSERT IMPORT UPDATE LOADの場合、ユニーク性の検査はLOAD中には行われません。LOAD後、表がCHECK PENDING(検査保留）になりますので、 SET INTEGRITYコマンドにより、ユニーク性の検査を行う必要があります。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 第３章( 19-20 ) DB2 UDB (PC&Unix) デザイン・ガイド 索引設計 ３−２．Include列つきのユニーク索引 ユニーク索引の列として、ユニークではない列を含むことが可能 目的： ・索引のみのアクセスによるパフォーマンス向上 ・冗長な索引を作成しない 索引キーの一部の列については、ユニーク性を保持する ユニークではない列については、ユニーク性の検査が発生しない 作成方法： CREATE UNIQUE INDEX 索引名 ON 表名 （列名） INCLUDE (列名） 複数列の指定が可能（索引列数：１６列 最大キー長：１０２４バイト） ユニークではない列については、索引順（ＡＳＣ，ＤＥＳＣ）の指定は無効 考慮点 必要となるディスク容量の増加 更新頻度が高いと、索引のメンテナンス負荷も増加する 関連するシステム・カタログ ＳＹＳＣＡＴ．ＩＮＤＥＸＥＳのＣＯＬＣＯＵＮＴ列： 索引の列数 ＳＹＳＣＡＴ．ＩＮＤＥＸＥＳのＵＮＩＱＵＥ＿ＣＯＬＣＯＵＮＴ列： 索引のユニークな列数 ＳＹＳＣＡＴ．ＩＮＤＥＸＥＣＯＬＵＳＥのＣＯＬＯＲＤＥＲ列： 索引順の指定 Ａ （Ａｓｃｅｎｄｉｎｇ：昇順） Ｄ （Ｄｅｓｃｅｎｄｉｎｇ：降順） Ｉ （Ｉｎｃｌｕｄｅ列なので、順番はない） (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： ３−２．Include列つきのユニーク索引 CREATE INDEX ステートメントの INCLUDE パラメーターは 3/98 FixPak (FixPak3)で使用可能です。 要求されたデータ全てを、表にアクセスすることなく索引から取り出すことができるアクセス・パスは、 index-only access です。 INCLUDE列を指定してユニーク索引を作成することにより、データページのアクセス頻度が軽減されます。 ＬＯＡＤは、ＩＮＣＬＵＤＥ列のある索引をサポートします。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 第３章( 21-22 ) DB2 UDB (PC&Unix) デザイン・ガイド 索引設計 INCLUDE列つきのユニーク索引：使用例 処理するＳＱＬステートメントの例：（employee_idに基本キーがある） SELECT employee_id, mgr_id FROM my_employee WHERE employee_id = 78379 ; INCLUDE列を使用しない例：２つの索引を作成・維持する必要あり １．表の作成 CREATE TABLE my_employee ( employee_id integer not null, mgr_id integer, phone_no integer, hire_date date, PRIMARY KEY (employee_id) ); - ｄｂ２は基本キーには自動的にユニーク索引を作成する ２．索引のみのアクセスのために、索引を作成する CREATE INDEX col_index ON my_employee (employee_id, mgr_id) ; INCLUDE列を使用する例：１つの索引だけで INDEX ONLY ACCESS １．表の作成 CREATE TABLE my_employee ( employee_id integer not null, mgr_id integer, phone_no integer, hire_date date) ; ２．INCLUDE列つき索引の作成 CREATE UNIQUE INDEX my_index on my_employee (employee_id) INCLUDE (mgr_id) ; ３．基本キーの作成 ALTER TABLE my_employee add PRIMARY KEY (employee_id); - 既存の索引が基本キーになる (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： INCLUDE列つきのユニーク索引：使用例 索引col_indexは、表にアクセスすることなく、索引のみで上記の照会処理要求を満たすことができます。これをindex-only access といいます。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 第３章( 23-24 ) DB2 UDB (PC&Unix) デザイン・ガイド 索引設計 ３−３．クラスター索引／非クラスター索引 クラスター索引 INSERT時に、索引順とデータの並び順を同じにするようにデータを格納しようと試みる 用途：高いクラスター率で索引順にデータを格納することにより、順次スキャンや順次先読み の際のＩ／Ｏ回数が軽減され、処理効率が向上 スキャンするページ数の削減 ORDER BY や GROUP BY 節が索引列に対して指定された時に、内部ソート負荷を軽減 非クラスター索引 索引順に関係なく、追加データは最初に見つかった空きスペースに格納する 用途：１件ヒットのランダムな検索処理の場合には、非クラスター索引でも有効 関連するシステム・カタログ INSERT TO 表 VALUES ( 56, ...) ＳＹＳＣＡＴ．ＩＮＤＥＸＥＳのＩＮＤＥＸＴＹＰＥ列 ＣＬＵ ：クラスター索引 （ＣＬＵＳＴＥＲ） ＲＥＧ ：非クラスター索引 （ＲＥＧＵＬＡＲ） クラスター索引 ノンリーフ・ページ 列の値 ５８ リーフ・ページ 列の値 ページ番号 ページ番号 データ・ページ ５５ ページ１０ 列の値 ５２ ５５ ５８ ページ番号 ＲＩＤ ＲＩＤ ＲＩＤ (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： ３−３．クラスター索引／非クラスター索引 クラスター索引は3/98 FixPak (FixPak3)で可能になっています。 クラスター索引は、データが追加された場合に、索引キー順にデータをデータページに格納しようと制御するので、クラスター索引 のキー順による順次スキャンや、順次先読みの際に有効な索引です。逆に、ユニーク索引による１件ヒットの照会処理などについ ては、クラスター索引を作成する必要はありません。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 第３章( 25-26 ) DB2 UDB (PC&Unix) デザイン・ガイド 索引設計 クラスター索引の使用 作成方法 CREATE INDEX 索引名 ON 表名 （列名） ...... CLUSTER 考慮点 表の空きスペースを準備しておく必要がある（ALTER TABLE 表名 ... PCTFREE 数値(%) ) ALTER TABLEステートメントのPCTFREEオプションで、表の１ページあたりのフリースペースの割合を設定すること により、データ行を索引と同じ順番に挿入することができる PCTFREEの指定がない場合には、表ページにフリースペースはとられない ＬＯＡＤのMODIFIED BY PAGEFREESPACE=数値(%) オプションで、PCTFREEの値を上書きしてLOADが可能 APPENDモードで作成された表にクラスター索引を作成することはできない 索引列がUPDATEされた場合、索引順に行を格納しなおすことはない クラスター率を高く維持するためには、ＵＰＤＡＴＥの代わりにDELETE/INSERTを使用する データページのフリースペース探索順 １．ターゲットページを探す ２．同一エクステントのページを探す ３．表の最後のページに追加する (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： クラスター索引の使用 パフォーマンスのガイドライン INSERT行の索引列の値がランダムである場合が多い表ほど、クラスター率を維持するためにより大きいPCTFREEの値が必 要です 一方、新規行の索引キーの値が、常に新しいHigh Keyの値であった場合、表のクラスター属性により、表の最後に追加され ます。他のページにフリースペースがあっても、クラスタリングには役に立ちません。この場合には、表をAPPENDモードにす ることの方が、クラスター索引や大きなPCTFREEの値よりも良い選択でしょう。 上記の話は、UPDATEにより行長が増えてしまったための、新しいオーバーフロー行についても当てはまります。 索引のPCTFREEを大きい値に設定することにより、高いクラスター性を長期間維持することができ、再編成の必要性を軽減 しますが、より多くのディスクスペースが必要となります。 ＡＰＰＥＮＤモードの表とは、ALTER TABLEモードのAPPENDオプションをＯＮに設定することにより、フリースペースの管理をせず に、追加されたデータ行は常に最終ページに追加される属性を持つ表のことを指します。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 第３章( 27-28 ) DB2 UDB (PC&Unix) デザイン・ガイド 索引設計 クラスター索引の作成 クラスター索引の作成例 クラスター索引を作成後、PCTFREEを設定する CREATE TABLE mytab ( mycol1 integer, mycol2 char (8), mycol3 char (40) ); ALTER TABLE mytab PCTFREE 50; 高クラスター率の維持 大量の変更処理後、クラスター率を確認 表と索引についてRＵＮＳＴＡＴＳ SYSCAT.INDEXESの CLUSTERRATIO あるいは CLUSTERFACTOR をチェック あるいはREORGCHKを実行 クラスター率が低い場合には、REORG ＲＥＯＲＧの作業域としてデータの２−３倍のディスク領域が必要 ＲＥＯＲＧ ＴＡＢＬＥ 表名 [ ＩＮＤＥＸ 索引名 ] [ ＵＳＥ 一時表スペース名 ] ＲＥＯＲＧ中は表に対して排他制御がとられるため、データにアクセスすることはできない 索引名を指定せずにＲＥＯＲＧした場合、クラスター索引が存在すれば、そのクラスター索引順にＲＥＯＲＧされる UPDATEについては、クラスター順は維持されない 可能であれば、DELETE/INSERTを使用する (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： クラスター索引の作成 SQLステートメントがソート処理を必要としている場合（例えば、ORDER BY,GROUPE BY,DISTINCT）で、そのソートに対応した索 引がある場合でSも、データベース・マネージャーが索引を選択しない場合もあります。これは、以下の場合に起こり得ます。 索引クラスター率が低い（SYSCAT.INDEXESのCLUSTERRATIOとCLUSTERFACTOR列を参照） 表が小さいので、表をスキャンするコストが低く、アンサーセットをメモリー内でソートできる表にアクセスするための索引があ る クラスター索引を作成後、再編成をするか、ソートしたデータをロードすることをお勧めします。一般的に、一つの表は１つの索引 についてのみクラスター化が可能です。クラスター索引により、表の物理的な格納順がある特定の順に、可能な限り維持され、 RUNSTATSにより収集されるCLUSTERRATIOあるいはCLUSTERFACTORの値も高く維持されます。 表へのLOADあるいはREORGを実行する前に、PCTFREEを使用してＡＬＴＥＲ ＴＡＢＬＥを行うべきです。クラスター率を維持する ためには、追加行用に、各データページには使用可能なスペースが必要です。スペースが使用可能であれば、追加されたを、既 存のデータに対してクラスター順に格納することができます。使用可能なスペースを残すには、まず最初に表を作成し、その次に PCTFREEパラメーターを指定したALTERステートメントを発行します。 UPDATEによる更新については、クラスタリングされません。もしクラスター索引のキーの値が変わるような更新処理が行われて も、その行は、クラスター順を維持するために他のページに移されることはありません。クラスター順を維持するためには、 UPDATEよりもDELETEとINSERTを使用します。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 第３章( 29-30 ) DB2 UDB (PC&Unix) デザイン・ガイド 索引設計 ３−４．単一列索引／複合列索引 単一列索引 索引キーの列が１つのみの索引 複合列索引 索引キーの列が、複数列からなる索引 複合列索引作成の考慮点 CREATE INDEXで指定する索引列の最初の列は、最もユニーク性が高く、検索条件として使 用頻度が最も高い列を指定する 複合列索引に含まれる列は、検索条件で頻繁に一緒に使用される列の組合わせであること 複合列索引（列１、列２、列３）が、アクセス・パスの候補になりうる例 WHERE 列１＝値 and 列２＝値 and 列３＝値 WHERE 列１＝値 and 列２＝値 WHERE 列１＝値 複合列索引（列１、列２、列３）が、アクセス・パスの候補にならない例 WHERE 列２＝値 and 列３＝値 WHERE 列２＝値 WHERE 列３＝値 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 ブランク・ページです 第３章( 31-32 ) DB2 UDB (PC&Unix) デザイン・ガイド 索引設計 ３−５．双方向索引 双方向索引（Reversal索引） CREATE INDEX時に指定した索引順(ASC,DESC)の逆順のスキャンにも使用可能 目的 逆順の索引を作成する必要がない（ディスク容量の削減、メンテナンス負荷の削減） 逆順読みをするために、内部的に一時表を作成する必要がない ＭＩＮおよびＭＡＸ関数の実行が容易に 作成方法 CREATE INDEX 索引名 ..... ALLOW REVERSE SCANS 省略時値は、DISALLOW REVERSE SCANSで、一方向のスキャンにのみ使用可能 SYSCAT.TABLESのREVERSE_SCANS列 ： Y＝Reversal索引 N＝Non-Reversal索引 双方向索引 考慮事項 列の値 ５８ Ｖ６以前の索引はサポートされない リーフ・ページ 索引メンテナンスのオーバーヘッド 列の値 ページ番号 デッドロック発生の可能性 オプチマイザーは前方向スキャンを選択する傾向がある ページ番号 ページ１０ 列の値 ５２ ５５ ５８ ページ番号 ＲＩＤ ＲＩＤ ＲＩＤ (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： ３−５．双方向索引 既存の、一方向のみに使用する索引は引き続きサポートされ、必ずしも移行する必要はありません。しかし、ユーザーが双方向 索引に変更したい場合には、索引を再作成する必要があります。 省略時値（DISALLOW REVERSE SCANS）では、索引の作成時にその索引は前方向のスキャン、すなわち定義された順でのス キャンのみをサポートするように設定されます。 索引作成時にＡＬＬＯＷ ＲＥＶＥＲＳＥ ＳＣＡＮＳを指定することにより、索引は、前方向と後方向のどちらの向きのスキャンも使用 可能になります。ＤＢ２ ＵＤＢは索引の最後のキーを取り出すことができます。 ユーザーは昇順と降順の索引を作成するために、同じ列に対して２つの索引を作成せずすみます。また、索引の最大値、最小値 を容易に検索することができます。 昇順では、索引のリーフページを左から右へたどります。双方向索引は双方向にポイントする、リーフ・ポインターを持っていま す。これにより、双方向索引を前から後、後から前へスキャンすることが可能です。 後ろ向き、あるいは前向きのポインターは、１つの索引リーフ・ページにあるポインターです。もし、リーフ・ページが左に無い場合 （つまり最初のリーフ・ページの場合）には、後ろ向きのポインターには、ＮＵＬＬ値が入っています。後ろ向きのスキャンには、後ろ 向きにつけられたリーフ・ポインターをたどって、索引のリーフ・ページを右から左へスキャンします。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 第３章( 33-34 ) DB2 UDB (PC&Unix) デザイン・ガイド 索引設計 解説： ３−５．双方向索引 ＜テスト1：双方向索引を使用した場合＞ テスト内容 ”CREATE INDEX emp3ix on employee (empno ASC) ALLOW REVERSE SCANS"で索引を作成後、 降順を指定した照会を実行時のアクセス・パスの確認（dynexplnを使用） dynexplnの出力結果 SQL Statement: select empno from employee3 order by empno desc Estimated Cost = 124 Estimated Cardinality = 2240 Access Table Name = UDBV61.EMPLOYEE3 ID = 2,15 | #Columns = 1 | Scan Direction = Reverse <= REVERSE SCANを行っている | Index Scan: Name = UDBV61.EMP3IX ID = 1 | | Index Columns: | | | 1: EMPNO (Ascending) | | #Key Columns = 0 | | | Start Key: Beginning of Index | | | Stop Key: End of Index | | Index-Only Access | | Index Prefetch: None | | | Return Data to Application | | | | #Columns = 1 | Lock Intents | | Table: Intent Share | | Row : Next Key Share Return Data Completion End of section (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： ３−５．双方向索引 ＜テスト2：双方向索引を使用しない場合＞ テスト内容 ALLOWS REVERSE SCANSオプションなしで昇順の索引を作成後、降順を指定した照会を実行時のアクセス・パスの確認（dynexplnを使用） dynexplnの出力結果 SQL Statement: select empno from employee3 order by empno desc Estimated Cost = 152 Estimated Cardinality = 2240 Data Stream 1: Evaluate at Open | Not Piped | Access Table Name = UDBV61.EMPLOYEE3 ID = 2,15 | | #Columns = 1 | | Index Scan: Name = UDBV61.EMP3IX ID = 1 | | | Index Columns: | | | | 1: EMPNO (Ascending) | | | #Key Columns = 0 | | | | Start Key: Beginning of Index | | | | Stop Key: End of Index | | | Index-Only Access | | | Index Prefetch: None | | | | Insert Into Temp Table ID = t1 ＜＝ 一時表にデータをＩＮＳＥＲＴしている | | | | | #Columns = 1 | | Lock Intents | | | Table: Intent Share | | | Row : Next Key Share | Temp Table Completion ID = t1 End of Data Stream 1 Access Temp Table ID = t1 | #Columns = 1 | Scan Direction = Reverse ＜＝ 一時表をReverseスキャンしている | Relation Scan | | Prefetch: Eligible | Return Data to Application | | #Columns = 1 Return Data Completion (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 第３章( 35-36 ) DB2 UDB (PC&Unix) デザイン・ガイド 索引設計 ４．索引の設計手順 手順 ４−１．基本的な索引候補の検討 ４−２．追加の索引候補の検討 ４−３．索引数の検討 ４−４．索引候補の取捨選択 ４−５．その他の検討事項 ４−６．索引の物理設計 ４−７．索引の検証 ４−８．索引が有効利用されるために 手順４−１については、データの意味から決定可能であり、外部設計後に可能な作業 手順４−２以降については、具体的なＳＱＬ文を元に作業 パフォーマンス改善を目的とし、内部設計から等号テストの局面まで、繰り返し行う必要がある (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 ブランク・ページです 第３章( 37-38 ) DB2 UDB (PC&Unix) デザイン・ガイド 索引設計 ４−１.基本的な索引候補の検討 １．ユニーク索引が必要か ユニーク性の維持が必要な場合：ユニーク索引 参照の整合性が必要な場合：基本キー ＣＲＥＡＴＥ ＴＡＢＬＥ実行時に、自動的に基本キーに対する昇順のユニーク索引が作成される 索引名 ： ＳＱＬ+タイムスタンプ+番号 索引スキーマ： ＳＹＳＩＢＭ ２．外部キーに索引をつける 結合列になる可能性が高い列に索引があると、処理効率は良い (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： ４−１.基本的な索引候補の検討 まず、ユニーク索引が必要かどうかを検討します。ユニーク性を維持しなければならない列が存在するのであれば、ユニーク索引 が必要です。 基本キーの必要性を検討します。他の表の列と整合性を保たなければならない、マスターとなる列が存在するのであれば、表に 基本キーを設定します。基本キーの設定は、表の作成（ＣＲＥＡＴＥ ＴＡＢＬＥ）時に指定するか、または、表の変更（ＡＬＴＥＲ ＴＡ ＢＬＥ）で指定します。 外部キーがある場合、検索条件の結合列となる可能性が高いため、索引の候補になります。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 第３章( 39-40 ) DB2 UDB (PC&Unix) デザイン・ガイド 索引設計 ４−２.追加の索引候補の検討 条件句(WHERE句に現れる述語)の中で頻繁に使用される列について 検討する 結合列 探索条件の列 ＡＮＤで結ばれた等号述語 範囲指定の述語（ＢＥＴＷＥＥＮ，不等号述語） ソート列（ＤＩＳＴＩＮＣＴ、ＯＲＤＥＲ ＢＹ、ＧＲＯＵＰ ＢＹで指定された列） 索引のみのアクセスを目的とした索引 ＩＮＣＬＵＤＥ列つきの索引 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： ４−２.追加の索引候補の検討 さらに、その他の索引候補を検討します。候補になる列は、条件節での登場回数が多い列です。また、ソートの対象となる列も候 補になります。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 第３章( 41-42 ) DB2 UDB (PC&Unix) デザイン・ガイド 索引設計 ４−３.索引数の検討 索引数の目安：表あたり５個以下が望ましい むやみに索引を作成することは、ディスクを無駄に消費し、負荷を増やすことになる オンラインでの更新処理環境： １−２個 照会のみの環境： ５個以上作成してもよい オンラインの更新処理と照会処理の混在した環境： ２−５個 更新処理時には索引のメンテナンスが必要となり、負荷が発生する ＩＮＳＥＲＴ処理では、索引列の追加処理が発生 ＤＥＬＥＴＥ処理では、索引列の削除処理が発生 索引列に対するＵＰＤＡＴＥ処理では、索引列の変更処理 索引のスプリット処理 その他の負荷 索引のある表へのＤＥＬＥＴＥ/ＩＮＳＥＲＴでは、Ｎｅｘｔ Ｋｅｙ Ｌｏｃｋが取られる クラスター索引がある表へのＩＮＳＥＲＴは、索引順を極力保持するようにデータを格納する ディスク・スペース使用量の増加 ＬＯＡＤ、ＲＥＯＲＧ時の索引の再作成の負荷 索引の追加によりプログラムのＰＲＥＰＡＲＥ時間が増加 静的ＳＱＬではＢＩＮＤ時、動的ＳＱＬでは実行時の時間が増加する 検討すべきアクセスパスの組み合わせが増加する (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： ４−３.索引数の検討 索引数が増えると、索引のメンテナンス負荷が高くなり、処理効率が低下します。従って、トランザクションの内容により、索引数を 制限して作成する必要があります。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 第３章( 43-44 ) DB2 UDB (PC&Unix) デザイン・ガイド 索引設計 ４−４.索引候補の取捨選択 索引の作成を避けた方がよい列 可変長列 索引のメンテナンスの負荷が高い ＣＯＬＣＡＲＤ列の値が小さい（重複値の多い）列 ＳＹＳＣＡＴ．ＣＯＬＵＭＮＳのＣＯＬＣＡＲＤ列： ユニークな値の数 ごく小さい表の列 アクセス・パスの決定時に、索引が有効とみなされず、表スキャンになる可能性が高い 複合列索引の考慮点 複合列索引の全ての列が等号で使用されるものは有効 ＦＵＬＬＫＥＹＣＡＲＤが大きいものは有効 ＳＹＳＣＡＴ．ＩＮＤＥＸＥＳ（ＦＵＬＬＫＥＹＣＡＲＤ）：列全体でユニークな値の数 FIRSTKEYCARD,FIRST2KEYCARD,FIRST3KEYCARD,FIRST4KEYCARDが大きいものは有効 ＳＹＳＣＡＴ．ＩＮＤＥＸＥＳ（ＦＩＲＳＴＫＥＹＣＡＲＤ）：１列目のユニークな値の数 索引列は、最も頻繁に等号で指定される列か、最もユニーク性の高い列を順に指定する 最初の索引列で結果行を大幅に絞り込める索引は、利用されやすい 完全にマッチングする索引を優先する （例）索引１（col1,col2,col3) と 索引２（col1,col2）がある場合で、条件がcol1=x and col2=x であれば索引２を優先 ・索引２はFULLKEYCARDが有効であり、かつ、キー長が短いのでバッファーヒット率が高い (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： ４−４.索引候補の取捨選択 FIRSTKEYCARD,FIRST2KEYCARD,FIRST3KEYCARD,FIRST4KEYCARDが大きいものは有効 ＳＹＳＣＡＴ．ＩＮＤＥＸＥＳ（ＦＩＲＳＴＫＥＹＣＡＲＤ）：１列目のユニークな値の数 ＳＹＳＣＡＴ．ＩＮＤＥＸＥＳ（ＦＩＲＳＴ２ＫＥＹＣＡＲＤ）：１、２列目の組み合わせでユニークな値の数 ＳＹＳＣＡＴ．ＩＮＤＥＸＥＳ（ＦＩＲＳＴ３ＫＥＹＣＡＲＤ）：１、２、３列目の組み合わせでユニークな値の数 ＳＹＳＣＡＴ．ＩＮＤＥＸＥＳ（ＦＩＲＳＴ４ＫＥＹＣＡＲＤ）：１、２、３，４列目の組み合わせでユニークな値の数 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 第３章( 45-46 ) DB2 UDB (PC&Unix) デザイン・ガイド 索引設計 ４−５.その他の検討事項 索引用の表スペース 索引用の表スペースを作成する 表データとは別の物理ディスクに配置することによる並列Ｉ／Ｏが期待できる 索引だけ早いディスクに格納することができる 索引用の表スペースとして指定可能なのは、ＤＭＳのＲＥＧＵＬＡＲ表スペースのみ 索引用の表スペースに対するバッファープールを作成する 索引をメモリー上に保持し、バッファーヒットさせたい場合 索引用の表スペースはＤＭＳ表スペース（ＪＦＳ）にする ＡＩＸファイルシステムのキャッシュが使用可能 索引順を維持する クラスター索引により索引順を維持する ＲＥＯＲＧにより定期的に索引順を維持する ユニーク索引の列による１件検索の場合には、クラスター率が低くても問題はない ＲＵＮＳＴＡＴＳの実行 現時点の統計情報に更新することにより、現状で最適なアクセス・パスが選択される（動的ＳＱＬの場合） ＲＵＮＳＴＡＴＳをＡＮＤ ＩＮＤＥＸＥＳオプションつきで実行する ＲＵＮＳＴＡＴＳをＷＩＴＨ ＤＩＳＴＲＩＢＵＴＩＯＮオプションつきで実行し、非一様分布統計情報を収集してみる (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： ４−５.その他の検討事項 策引データを策引用の表スペースに格納するためには、表の作成（ＣＲＥＡＴＥ ＴＡＢＬＥ）時に、その表に対して作成された索引 のデータをどの表スペースに格納するかを明示的に指定します。 CREATE TABLE 表名 (COL1 INTEGER, .......) IN 表の表スペース名 ＩＮＤＥＸ ＩＮ 索引の表スペース名 索引が作成されている索引用の表スペースをＤＲＯＰすることはできません。ＤＲＯＰ時に、関連するデータベース・オブジェクトが 存在するためにＤＲＯＰが不可能である旨のエラー･メッセージが戻されます。この場合、索引を全てＤＲＯＰ後に、表スペースをＤ ＲＯＰするか、または、ＤＲＯＰ ＴＡＢＬＥＳＰＡＣＥ文で、関連する表スペースを全て指定することにより、表スペースをＤＲＯＰする ことが可能です。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 第３章( 47-48 ) DB2 UDB (PC&Unix) デザイン・ガイド 索引設計 ４−６.索引の物理設計 CREATE INDEXの構文 >>-CREATE----+---------+--INDEX----索引名---------------------> '-UNIQUE--' .-,--------------------. (1) V .-ASC--. | >----ON----+-表名---------+--(-----列名--+------+----+---)-----> | (2) | '-DESC-' '-ニックネーム--' >-----+---------------------+-----------------------------------> '-SPECIFICATION ONLY--' >----*--+--------------------------------------------+----------> | .-,----------. | | (3) V | | '-INCLUDE------------------(-----列名---+---)----' .-PCTFREE 10--------. >----*--+---------+---*---+-------------------+---*-------------> '-CLUSTER-' '-PCTFREE--数値----' >-----+----------------------+--*-------------------------------> '-MINPCTUSED--数値--' .-DISALLOW REVERSE SCANS--. >----+----------------------------+---*--------------------------->< '-ALLOW REVERSE SCANS-----' (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： ４−６.索引の物理設計 UNIQUE ユニーク索引を作成する 列名 索引列を指定する。最大１６列まで指定可能。指定された列の長さ属性の合計が１０２４バイト（タイプ付き表の場合には１０２ ０バイト）を越えてはならない。 ＬＯＮＧ ＶＡＲＣＨＡＲ，ＬＯＮＧ ＶＡＲＧＲＡＰＨＩＣ、ＬＯＢ、ＤＡＴＡＬＩＮＫデータ・タイプは、索引列にはできない。 ＡＳＣ、ＤＥＳＣ 索引の順序で、ＡＳＣは昇順、ＤＥＳＣは降順。 ＩＮＣＬＵＤＥ ＩＮＣＬＵＤＥ列つきの索引を作成する際の、列を指定する ＣＬＵＳＴＥＲ クラスター索引を作成する。１表につき１つのクラスター索引を作成可能。また、ＡＰＰＥＮＤモードで作成された表にクラスター 索引を作成することはできない。 ＰＣＴＦＲＥＥ（指定可能範囲０−９９。省略時値１０％） 索引作成時に、索引のリーフ・ページに何パーセントのフリースペースを残すかを数値（％）で指定する。ノン・リーフ・ページに ついては、１０％のフリースペースがとられる。 ＭＩＮＰＣＴＵＳＥＤ（指定可能範囲０−９９。省略時値は０。設定する場合には、パフォーマンスの観点から５０以下の設定を推奨） 索引のオンラインＲＥＯＲＧを行う場合に、索引のリーフ･ページの使用率を指定する。この値が使用率の最小値となり、使用 率がこの値以下になった場合に、索引のオンラインＲＥＯＲＧ（再編成）が自動的に行われ、隣のページをマージされた後、空 のページは解放される。 ＤＩＳＡＬＬＯＷ ＲＥＶＥＲＳＥ ＳＣＡＮＳ／ＡＬＬＯＷ ＲＥＶＥＲＳＥ ＳＣＡＮＳ ＤＩＳＡＬＬＯＷ ＲＥＶＥＲＳＥ ＳＣＡＮＳ（省略時値）：索引作成時に指定した順のスキャンのみ可能 ＡＬＬＯＷ ＲＥＶＥＲＳＥ ＳＣＡＮＳ：リバース・スキャンも可能 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 第３章( 49-50 ) DB2 UDB (PC&Unix) デザイン・ガイド 索引設計 ４−７.索引の検証 EXPLAINツールを使用して、索引が利用されているかを確認 dynexpln db2expln CC(Control Center)のVisual Explain EXPLAIＮツールの使用方法 静的SQL（パッケージ）の場合： db2explnコマンド 構文： db2expln -d データベース名 -c 作成者ID -p パッケージ名 -o 出力ファイル名 ヘルプの画面出力： db2expln -h db2explnのみを打鍵して実行した場合には、プロンプトが画面表示され、対話式に実行が可能 動的SQLの場合： dynexplnコマンド 構文： dynexpln -d データベース名 -q SQL文 -f 入力ファイル名 -g EXPLAINグラフの表示 -o 出力ファイル名 ヘルプの画面出力： dynexpln -h dynexplnのみを打鍵して実行した場合には、プロンプトが画面表示され、対話式に実行が可能 DYNEXPLN_OPTIONS環境変数で、PREPオプションを設定することが可能 dynexplnはDYNEXPLN_OPTIONS環境変数で指定されたPREPオプションを使用して、アクセス・パスを決定する （例）export DYNEXPLN_OPTIONS="QUERYOPT 5 ISOLATION RS" (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： ４−７.索引の検証 db2explnコマンドのヘルプ・テキスト出力：db2expln -h db2expln [-c <creator>] [-d <dbname>] [-e <escape>] [-g] [-i] [-l] [-t|-o <outfile>] [-p <pname>] [-s <sectnbr>] [-u <user> <pw>] [-h|-?] Input Fields: -c <creator> = package qualifier -d <dbname> = database name containing packages -e <escape> = escape character for LIKE predicate -g = show optimizer plan graphs -h = help -i = show operator ID numbers -l = respect package name case -o <outfile> = name of output file -p <pname> = package name -s <sectnbr> = section number of package (use zero for all sections) -t = terminal output desired -u <user> <pw> = user ID and password for connecting to database -? = help dynexplnコマンドのヘルプ・テキスト出力：dynexpln -h dynexpln [[-d <database>] [-f <input file>] [-g] [-i] [-t|-o <output file>] [-q <SQL statement>] [-u <userid> <password>] [-z <delimiter>]] Input fields: -d <database> = database name to connect to -f <input file> = name of file containing SQL statements to describe -g = show optimizer plan graphs -h = help -i = show operator ID numbers -o <output file> = name of output file -q <SQL statement> = SQL statement(s) to describe -t = terminal output desired -u <userid> <password> = userid and password for connecting to database -z <delimiter> = character used to delimit SQL statements (default is no delimiter) -? = help (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 第３章( 51-52 ) DB2 UDB (PC&Unix) デザイン・ガイド 索引設計 アクセス・パスの確認 -例db2explnコマンドによるパッケージのEXPLAIN出力 Package Name = UDBV61.STATICES Prep Date = 2000/01/07 Prep Time = 15:58:15 Bind Timestamp = 2000-01-07-16.25.43.231864 Isolation Level = Read Stability Blocking = Block Unambiguous Cursors Query Optimization Class = 5 Partition Parallel = No Intra-Partition Parallel = No Function Path = "SYSIBM", "SYSFUN", "UDBV61" ------------------- SECTION --------------------------------------Section = 1 SQL Statement: SELECT MGRNO INTO :H00001 FROM department WHERE deptno = 'A00' Estimated Cost = 25 Estimated Cardinality = 1 Access Table Name = UDBV61.DEPARTMENT ID = 3,7 | #Columns = 2 | Single Record | Index Scan: Name = UDBV61.DEPTIX ID = 1 | | Index Columns: | | | 1: DEPTNO (Ascending) | | #Key Columns = 1 | | | Start Key: Inclusive Value | | | | 1: 'A00' | | | Stop Key: Inclusive Value | | | | 1: 'A00' | | Data Prefetch: Eligible | | Index Prefetch: None | Lock Intents | | Table: Intent Share | | Row : Next Key Share | Return Data to Application | | #Columns = 1 Return Data Completion End of section 索引スキャンの索引名 索引スキャンの索引列名 索引列への検索条件 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： アクセス・パスの確認 -例Ｖｉｓｕａｌ Ｅｘｐｌａｉｎの例 Control Centerの「パッケージ」の一覧表示から起動 アクセス・パスを図で表示 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 第３章( 53-54 ) DB2 UDB (PC&Unix) デザイン・ガイド 索引設計 ４−８．索引が有効利用されるために CREATE INDEXのタイミング ＬＯＡＤの前 ユニーク索引がある場合、ユニーク性の検査がＬＯＡＤ時に行われる 索引作成後にＬＯＡＤする方が、ＬＯＡＤ後に索引を作成するよりも速い 大量のIMPORT後 より有効な索引列が見つかった時 DROP INDEXのタイミング 索引が使用されていないことが判明した時 ＥＸＰＬＡＩＮによる確認 ＳＹＳＣＡＴ．ＰＡＣＫＡＧＥＤＥＰ表 ＢＴＹＰＥ列：各パッケージが使用するデータベース・オブジェクトの種類（Ｔ＝表、Ｉ＝索引、Ａ＝別名、Ｖ＝視点、Ｆ＝関数） ＢＮＡＭＥ列：各パッケージが使用するデータベース・オブジェクトの名前 ＰＫＧＮＡＭＥ ＢＩＮＤＥＲ ＢＴＹＰＥ ＰＧＭ０１ ＤＢ２Ｖ６１ Ｔ ＰＧＭ０２ ＤＢ２Ｖ６１ Ｉ ＢＮＡＭＥ ＥＭＰＬＯＹＥＥ ＩＸＥＭＰＮＯ RUNSTATSのタイミング 表のデータがＬＯＡＤされ、適切な索引が作成されたとき 表のデータがＲＥＯＲＧされた時 表および索引のデータの１０−２０％がUPDATE/DELETE/INSERTされた時 アプリケーションをＢＩＮＤする前 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： ４−８．索引が有効利用されるために ＬＯＡＤの前に索引作成を行っておいたほうが、ＬＯＡＤ後に索引を作成した場合と比較すると、合計時間が短くてすみます。また、 事前にユニーク索引を作成しておいた場合には、ＬＯＡＤ時にユニーク性の検査が行われます。 ＬＯＡＤの前に索引を作成しておく場合には、索引を作成するための一時領域を確保しておく必要があります。メモリー領域として は、ＳＯＲＴＨＥＡＰ ＤＢ ＣＦＧに設定された容量のソート領域が使用されます。また、ディスク領域としては、一時表スペースが使 用されます。 索引が有効に活用されているかを確認し、使用されていない場合には、ＤＲＯＰする必要があります。索引が存在することによる メンテナンス負荷を軽減するためです。静的ＳＱＬプログラムについては、ＳＹＳＣＡＴ．ＰＡＣＫＡＧＥＳで確認します。また、動的ＳＱ Ｌプログラムについては、ＥＸＰＬＡＩＮツールで確認します。 ＲＵＮＳＴＡＴＳは、表のデータが大きく変化した場合に、統計情報を最新の状態に更新するために実行します。動的ＳＱＬは、動的 にＢＩＮＤを実行するため、ＲＵＮＳＴＡＴＳで統計情報を変更することによりアクセス・パスが変わる可能性があります。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 第３章( 55-56 ) DB2 UDB (PC&Unix) デザイン・ガイド 索引設計 ５．その他の索引に関するトピック ５−１．オンライン索引再編成 - MINPCTUSED ５−２．索引作成のＳＭＰ対応 ５−３．ダイナミック・ビットマップ・インデックス ５−４．インデックス・アドバイザー (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 ブランク・ページです 第３章( 57-58 ) DB2 UDB (PC&Unix) デザイン・ガイド 索引設計 ５−１．オンライン索引再編成 - MINPCTUSED 格納されたデータ量をしきい値にし、リーフ・ページを自動的に再編成 利点 索引の再作成の頻度と管理作業時間を軽減 ディスク・スペースの有効利用 指定方法 CREATE INDEX........MINPCTUSED 数値（％） MINPCTUSED： オンライン索引再編成を行う、しきい値（単位は％）を設定 リーフ・ページの使用率がしきい値以下になると、オンライン索引再作成が自動実行される ５０（％）以下の値を推奨（省略時値：ゼロ、オンライン索引再編成を行わない） リーフ・ページ 列の値 関連するシステム・カタログ ５２ ５５ ページ番号 ＲＩＤ ＲＩＤ 列の値 ５８ ６０ ページ番号 ＲＩＤ ＲＩＤ SYSCAT.INDEXESのMINPCTUSED列 CREATE INDEX時に指定したしきい値が入る MINPCTUSED = ５０ 考慮点 リーフ・ページ 列の値 V6以降の索引のみサポート 解放された索引ページは、同一表の索引で使用可能 ５２ ５５ ５８ ６０ ページ番号 ＲＩＤ ＲＩＤ ＲＩＤ ＲＩＤ 列の値 ページ番号 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： ５−１．オンライン索引再編成 - MINPCTUSED オンライン索引再作成はＶ６．１で使用可能になりました。これにより、索引キーの削除後に、索引ページの空きスペースを解放す ることができるようになりました。このアルゴリズムは、ユーザーがＣＲＥＡＴＥ ＩＮＤＥＸをＭＩＮＰＣＴＵＳＥＤオプションで定義したし きい値に基づきます。２つの隣り合う索引ページをチェックし、もししきい値が満たされていれば、データベースを止めることなし に、２つのページを再編成します。 既存の索引について、この機能によりオンラインで再編成を行いたい場合には、一度削除した後、ＭＩＮＰＣＴＵＳＥＤを指定して再 編成する必要があります。 ＭＩＮＰＣＴＵＳＥＤの値は０から９９の間の値に設定します。この値が再編成のしきい値、つまり、隣の索引リーフ・ページとマージし ない、索引ページあたりの使用スペースの最小の割合です。この機能の目的は、２つの隣り合う索引リーフ・ページをマージする ことなので、ＭＩＮＰＣＴＵＳＥＤの推奨値は、５０より小さい値です。ＭＩＮＰＣＴＵＳＥＤの値が０である場合、これは省略時の値です が、オンライン再編成を行わないことを意味します。 オンライン索引再編成後に解放された索引リーフ・ページは、再利用可能です。けれども、解放されたページは同じ表の他の索引 についてのみ利用することが可能です。表全体の再編成の場合、ＤＭＳ表スペースについては、他のオブジェクトに関して、ペー ジが解放されます。ＳＭＳ表スペースについては、ディスク・スペースが解放されます。 オンライン索引再編成後でも、索引ノン・リーフ・ページは解放されません。けれども、表全体の再編成を実行することにより、索 引は可能な限り小さく作成されます。リーフおよびノン・リーフ・ページ数の数は、索引の階層と同様に削減されます。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 第３章( 59-60 ) DB2 UDB (PC&Unix) デザイン・ガイド 索引設計 ５−２．索引作成のＳＭＰ対応 索引作成時には、ＳＭＰマシンにおいてＣＰＵ並列処理が可能 索引作成（CREATE INDEX）時 基本キー、ユニーク制約を表へ追加した時 内部的に索引が生成される 表の再編成（REORG TABLE）実行時 考慮点 intra_parallel DBM CFG = YESの場合には、索引作成時のデータのスキャンおよびソートに関 して、複数ＣＰＵによる並行処理が行われる ＳＭＰマシンの場合には、省略時はＹＥＳ indexsort DB CFG = NOの場合には、複数ＣＰＵの利点はない indexsort DB CFG： 索引作成時に索引キーのソートを行うかどうかをコントロールする構成パラメーター 省略時はＹＥＳ (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： ５−２．索引作成のＳＭＰ対応 ＳＭＰマシンにおける索引作成では、表データを読み取り、ソートする処理について、複数ＣＰＵを使用します。 ＳＭＰマシンにＤＢ２を導入した場合には、自動的にintra_parallel ＤＢＭ ＣＦＧがＹＥＳに設定されます。 索引作成についてのみですが、ＩＮＴＲＡ＿ＰＡＲＡＬＬＥＬ ＝ ＹＥＳになっていた場合、ＭＡＸ＿ＱＵＥＲＹＤＥＧＲＥＥの値に関係な く、（ＳＭＰで使用可能なＣＰＵの数+１）のＣＰＵが使用されます。”ＳＭＰで使用可能なＣＰＵの数”の値の最大値は６です。 索引作成時に、複数ＣＰＵが使用されるかについては、動的に決定されます。 ＩＮＴＲＡ＿ＰＡＲＡＬＬＥＬ ＝ ＮＯであれば、索引作成に複数ＣＰＵは使用されません。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 第３章( 61-62 ) DB2 UDB (PC&Unix) デザイン・ガイド 索引設計 ５−３．ダイナミック・ビットマップ・インデックス DB2は現在、ビットマップ・インデックスはサポートしていない ビットマップ・インデックスとは 各キーをビット表現で作成し、索引にビット形式で保存する 索引のディスク容量ははるかに小さくなる スター・ジョインの際に動的にビットマップ・インデックスを生成 AND 述部でのみ働く ＡＮＤで結ばれた列には索引が作成されていること ダイナミック・ビットマップ・インデックス・アンディング（DBIA）のテクノロジーを使用 一時的に自動作成されるもので、恒久的にディスクに保存されるものではない スター・ジョイン ジョインのアクセス・パスの１つ 大きな表と複数の小さな表の結合条件で、結合列に索引がある場合に選択されうるパス 小さい 表 小さい 表 大きな表 小さい 表 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 ブランク・ページです 第３章( 63-64 ) DB2 UDB (PC&Unix) デザイン・ガイド 索引設計 ダイナミック・ビットマップ・インデックスのしくみ DBIA(Dynamic Bitmap Index Anding)の例 SELECT COL1,.... from fact, dem where fact.color=dem1.color and fact.size=dem2.size and fact.price=dem3.pride ... fact.color = dem1.color 10,RED 33,BLUE 34,RED 222,RED dem1表 col1 100 200 300 999,RED color RED RED RED dem2表 col1 1 2 3 size Small Small Small RIDをハッシュ 1 11 fact.size = dem2.size 1 1 1 col1 A B C prise 10000 10000 10000 fact表 1 1 dem3表 10,Large 33,Small 222Small col1 10 33 34 222 999 color RED BLUE RED RED RED size Large Small Medium Small Large price 9900 5000 11000 8000 17700 fact.price < dem3.price 10,9900 33,5000 222,3800 最後に表データを取りだし、全ての検索条件について検査 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： ダイナミック・ビットマップ・インデックスのしくみ ＡＮＤ検索条件の列について、ビットマップ・インデックスが動的に作成されます。ビットマップ・インデックスはまずゼロに初期化さ れます。次に、列のデータが順にビットマップにハッシュされます。その際に、条件に合致していれば、ビットを立てます。これで最 初のビットマップ・インデックスの完成です。 同じハッシュ値に複数の列データがハッシュされる場合もあります。 「10,RED」 と 「33,BLUE」 は両方とも同じオフセットにハッシュされたと仮定します。「10､RED」は”fact.color＝dem1.color”の検索 条件に合致しているので、ビットが１にセットされます。一度セットされた値はリセットされることはありません。従って、これは、「33 BLUE」のキーにとっては間違いのセットとなります。(REDではないのにビットが立ってしまいます。)ですが、最終的に作成された ビットマップを使用して他の条件も含めたデータの検査が行われるので、問題ありません。 次のＡＮＤ条件に関してビットマップ・インデックスが同様に作成され、最初のビットマップ・インデックスと比較します。両方のイン デックスでビットが立っているもののみビットを立てます。スタージョインの対象となる列分繰り返します。その後、最終的に、ビット マップ・インデックスを使用して表データが取り出され、全ての検索条件と合致しているかどうかが検査されます。 結合条件の列には、索引が作成されている必要があります。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 第３章( 65-66 ) DB2 UDB (PC&Unix) デザイン・ガイド 索引設計 ５−４．インデックス・アドバイザー SQL 索引の容量 最適な索引を見つける手助けをする ワークロードをベースに探索 仮想の索引による見積もり、コスト計算が可能 パフォーマンス分析およびチューニングの複雑さを軽減 制約 ファ イル 名 DBA 順位 先 優 Ｌの Ｑ Ｓ 度 頻 行 実 Ｌの Ｑ Ｓ 検索条件 CREATE INDEX ...... ADVISE_WORKLOAD表 ADVISE_INDEX表 Index Advisor CREATE INDEX ...... CREATE INDEX ...... C INDREAT EX E ..... . (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： ５−４．インデックス・アドバイザー 適切な索引の存在は、アクセス・パス選択およびＳＱＬのパフォーマンスに多大な影響を与えます。 インデックス・アドバイザーはＶ６．１から使用可能です。これは、適切な索引の設計や定義に関するマネージメント・ツールです。 ワークロードは、与えられた時間内処理しなければならないＳＱＬステートメントにより設定します。ＳＱＬステートメントはＳＥＬＥＣ Ｔ，ＩＮＳＥＲＴ，ＤＥＬＥＴＥステートメントが使用可能です。 例えば、１，０００件のＩＮＳＥＲＴ、１０，０００件のＵＰＤＡＴＥ、１０，０００件のＳＥＬＥＣＴ、１，０００件のＤＥＬＥＴＥを、一ヶ月間を通し て行います。ワークロードの情報としては、与えられた時間内でのＳＱＬステートメントの頻度とタイプが考慮されます。アドバイス・ エンジンは、このワークロード情報を、データベース情報と共に使用して、推奨する索引を探索します。アドバイス・エンジンの目 標は、ワークロードの合計コストを最小化することです。この情報は、ＡＤＶＩＳＥ＿ＷＯＲＫＬＯＡＤ表に書き込まれます。十分な情 報と制約によって、アドバイス機能は適切な索引を提示することが可能です。 仮想索引は実際にはデータベースに存在しない索引です。これらの索引は、アドバイス機能から推奨される索引であるか、また は、アドバイス機能を使用してユーザーが評価したい索引です。アドバイス機能は、これらの索引も考慮に入れて処理を行い、推 奨しない場合には、削除されます。 アドバイス機能はワークロード情報とデータベースの統計情報を使用して、推奨する索引を生成します。 ＡＤＶＩＳＥ＿ＷＯＲＫＬＯＡＤ表とＡＤＶＩＳＥ＿ＩＮＤＥＸ表を作成するには、ｓｑｌｌｉｂサブ・ディレクトリーの下のｍｉｓｃの下にある、ＥＸＰＬ ＡＩＮ．ＤＤＬスクリプトを実行します。作成されていない場合には、Ｉｎｄｅｘ Ｓｍａｒｔｇｕｉｄｅによっても、表を作成することができます。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 第３章( 67-68 ) DB2 UDB (PC&Unix) デザイン・ガイド 索引設計 インデックス・アドバイザーの利点と使用法 インデックス・アドバイザーの利点 問題となる照会文に対して最適な索引を見つける 資源の制約を受ける、セットになった照会文に対して最適な索引を見つける 索引を作成せずに、ワークロードに関して索引のテストを行う 索引はメモリー内でシミュレートされる インデックス・アドバイザーを使用する３ステップ １．情報の入力・収集を行う 事前にＥＸＰＬＡＩＮ．ＤＤＬを実行し、インデックス・アドバイザーが使用する表を作成しておく ２．インデックス・アドバイザー起動する ３．インデックス・アドバイザーの出力のレビューを行う (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 ブランク・ページです 第３章( 69-70 ) DB2 UDB (PC&Unix) デザイン・ガイド 索引設計 ステップ１：情報の入力と収集 ワークロード情報を収集する (SQL ステートメントおよび頻度) イベント・モニターの利用 SYSSTMTシステム・カタログ SQL ステートメントを ADVISE_WORKLOAD表にＩＮＳＥＲＴする ワークロード情報に変更を加える ＳＱＬステートメントの頻度 ＳＱＬステートメントの優先順位 制約を決める 索引のディスク容量 インデックス・アドバイザーの最長実行時間 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： ステップ１：情報の入力と収集 ワークロードの収集方法としては、 イベント・モニターを使用して動的ＳＱＬを収集する ＳＹＳＳＴＭＴカタログ・ビューを使用して静的ＳＱＬを収集する ステートメントや頻度をＡＤＶＩＳＥ＿ＩＮＤＥＸ表に追加する ＡＤＶＩＳＥ＿ＷＯＲＫＬＯＡＤ表のＳＱＬステートメントの頻度や優先順位は、変更することが可能です。 索引のディスク容量の上限や、アドバイザーの実行する最長時間も考慮事項として入力します。 動的ＳＱＬについては、以下のようにして、ステートメントの実行頻度をモニターから収集します。 モニターのＲＥＳＥＴを実行する。モニタリングを行う環境で、十分な数の動的ＳＱＬが実行されるまで待つ ＧＥＴ ＳＮＡＰＳＨＯＴ ＦＯＲ ＤＹＮＡＭＩＣ ＳＱＬコマンドにより動的ＳＱＬについての情報を収集する "insert into advise_workload(select cmyworkloadc,0,stmt_text,cast(generate_unique() as char(254)),num_executions,1,num_executions,0,0 from SYSFUN.SQLCACHE_SNAPSHOT)"を実行する ワークロード情報において、各ＳＱＬステートメントの省略時の頻度は１であり、省略時の重要度もまた１です。generate_unique()関 数は、ステートメントにユニークなＩＤをアサインするために使用していますが、そのＳＱＬステートメントについての、より意味のあ る説明内容に置き換えてもかまいません。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 第３章( 71-72 ) DB2 UDB (PC&Unix) デザイン・ガイド 索引設計 ステップ２：起動 起動方法は４通り 方法１：コントロールセンター（ＣＣ）を使用（推奨） Index SmartGuide（ＧＵＩ）からの起動 方法２：コマンド・ラインを使用 db2advisコマンドを実行 方法３：SET CURRENT EXPLAIN MODEを使用 SET CURRENT EXPLAIN MODE RECOMMEND INDEXES ：ADVISE_INDEX表に推奨する索引とＥＸＰＬＡＩＮ情報を格納する。 SET CURRENT EXPLAIN MODE EVALUATE INDEXES ：ADVISE_INDEX表にINSERTされた索引情報を考慮して、ＥＸＰＬＡＩＮ情報を格納する。 方法４：ＣＬＩを使用 アプリケーションの中で、動的にレビュー、評価、索引の作成を行う (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： ステップ２：起動 インデックス・アドバイザーはコントロール・センターを使用して起動することができ、この方法が推奨されている方法です。コント ロール・センターから、インデックス・フォルダーが見つかるまで、オブジェクト・ツリーを拡張していきます。インデックス・フォルダー で、マウス・ボタン２をクリックし、ポップアップ・メニューからＳｍａｒｔＧｕｉｄｅを使用してCreate->Indexを選択します。Index Ｓ martGuideが開きます。Index SmartGudeには、ヘルプ機能があり、簡単に使用することができます。SmartGuideは、最近実行され たSQLを探したり、最近使用されたパッケージを探したり、あるいは、SQLステートメントを追加するなどして、ワークロード情報を構 成する機能も持っています。 ＧＵＩの方が使い勝手はよく、操作性に優れています。 入力項目がわかりやすい 出力内容が見やすい 索引の見積もり結果がＫＢ単位で表示される（コマンドで実行すると、ＭＢ単位での出力となる） 索引作成のスクリプトを作成可能 スクリプトを、スクリプト・センターから実行可能 条件付のＵＰＤＡＴＥ／ＤＥＬＥＴＥを指定することも可能です。 Index Advisorは、ＤＢ２コマンド・ウインドウから起動することもできます。コマンド・ラインで、db2advis と打鍵して実行します。 db2advisは以下の３つの場所のうちの１つからワークロード情報を読み込んで開始されます。 コマンド・ライン テキスト・ファイルにあるステートメント ワークロード情報（SQLステートメントと頻度）をＩＮＳＥＲＴした後のＡＤＶＩＳＥ＿ＷＯＲＫＬＯＡＤ表 ツールは、ＣＵＲＲＴＥＮＴ ＥＸＰＬＡＩＮ ＭＯＤＥレジスターを使用し、最適な索引を見つけるための内部的なアルゴリズムを使用し ながら、推奨する索引を探します。 Index SmartGuideおよびdb2advisツールは、ＡＤＶＩＳＥ＿ＷＯＲＫＬＯＡＤ表とＡＤＶＩＳＥ＿ＩＮＤＥＸ表を使用して情報の取り出しと格 納を行います。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 第３章( 73-74 ) DB2 UDB (PC&Unix) デザイン・ガイド 索引設計 ステップ３：レビュー - ＣＣの例 インデックス・アドバイザー GUI：ワークロード入力のフォルダー 必要な情報を入力 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： ステップ３：レビュー - ＣＣの例 インデックス・アドバイザー: 推奨する索引表示のフォルダー (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 第３章( 75-76 ) DB2 UDB (PC&Unix) デザイン・ガイド 索引設計 ステップ３：レビュー - db2advisコマンドの例１ ＳＱＬステートメントを指定した使用方法 db2advis -d sample -s "select count(*) from sales where region ='QUEBEC' " -t 1 Calculating initial cost (without recommended indexes) [31.198040] timerons Initial set of proposed indexes is ready. Found maximum set of [1] recommended indexes Cost of workload with all indexes included [2.177133] timerons cost without index [0] is [31.198040] timerons. Derived benefit is [29.020907] disk space needed for initial set [1] MB total disk space constrained to [-1] MB 1 indexes in current solution [31.198040] timerons (without indexes) [2.177133] timerons (with current solution) |[%93.02] improvement Trying variations of the solution set. Time elapsed. LIST OF RECOMMENDED INDEXES =========================== index[1], 1MB CREATE INDEX WIZ689 ON VALENTIN.SALES (REGION DESC) =========================== Index Advisor tool is finished. (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： ステップ３：レビュー - db2advisコマンドの例１ ｄｂ２ａｄｖｉｓコマンドのオプション -l ： ディスク容量の制限を指定します。既存のスキーマにおける全ての索引について、使用可能であるディスクの最大量をメガバ イト単位で指定します。省略時値は、６４ＧＢであり、これはパーティションあたりで作成可能な、１索引の最大サイズです。. -t ： アドバイスのために所要する最長時間を、分単位で指定します。省略時値は１０です。時間制限をしない場合には、ゼロを指 定します。 -s ： ＳＱＬステートメントを指定します。 -f ： １つ以上のＳＱＬステートメントを含む入力ファイルを指定します。省略時は、標準入力が使用されます。 ２つのハイフンは、コメントであることを意味します。 -- <comment> ワークロード情報の中の各ステートメントの実行頻度は、以下の行を入力ファイルに追加することにより変更可能です。 --#SET FREQUENCY <x> 実行頻度の回数に制限はありません。実行回数が少なくても、高パフォーマンスが特に要求される重要なＳＱＬについて は、値を大きくします。 -w ： 索引のアドバイスを行うワークロード名を指定します。この名前は、ＡＤＶＩＳＥ＿ＷＯＲＫＬＯＡＤ表で使用されている名前で す。 -d ： 接続を確立するデータベース名を指定します。. (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 第３章( 77-78 ) DB2 UDB (PC&Unix) デザイン・ガイド 索引設計 ステップ３：レビュー - db2advisコマンドの例２ 入力となるワークロードファイル”sample.sql”を使用した実行方法 db2advis -d sample -f sample.sql -t 0 ＜sample.sqlの内容＞ --#SET FREQUENCY 100 select count(*) from sales where region = ?; --#SET FREQUENCY 3 select projno, sum(comm) tot_comm from employee, emp_act where employee.empno = emp_act.empno and employee.job='DESIGNER'group by projno order by tot_comm desc; --#SET FREQUENCY 50 select * from sales where sales_date = ?; found [3] SQL statements from the input file Calculating initial cost (without recommended indexes) [62.331280] timerons Initial set of proposed indexes is ready. Found maximum set of [2] recommended indexes Cost of workload with all indexes included [29.795755] timerons cost without index [0] is [58.816662] timerons. Derived benefit is [29.020907] cost without index [1] is [33.310373] timerons. Derived benefit is [3.514618] total disk space needed for initial set [2] MB total disk space constrained to [-1] MB 2 indexes in current solution [62.331280] timerons (without indexes) [29.795755] timerons (with current solution) [%52.20] improvement Trying variations of the solution set. Time elapsed. LIST OF RECOMMENDED INDEXES =========================== index[1], 1MB CREATE INDEX WIZ119 ON VALENTIN.SALES (SALES_DATE DESC,SALES_PERSON DESC) index[2], 1MB CREATE INDEX WIZ63 ON VALENTIN.SALES (REGION DESC) =========================== (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 ブランク・ページです 第３章( 79-80 )