インデックス・アドバイザーは、 データに合わせて索引を設計したり定義したりする必要を減らす管理ツールです。
インデックス・アドバイザーは、以下の点で優れています。
この SQL アドバイス機能に関連した、いくつかの概念を説明します。 まず、作業負荷 が存在します。 作業負荷は、 データベース・マネージャーが所定の期間に処理しなければならない一連の SQL ステートメントです。 この SQL ステートメントには、SELECT、INSERT、UPDATE、 および DELETE ステートメントを含められます。 たとえば、1 カ月の間、データベース・マネージャーは、1 000 の INSERT、 10 000 の UPDATE、10 000 の SELECT、 および 1 000 の DELETE を処理しなければならないことがあります。 作業負荷内の情報は、 所定の期間における SQL ステートメントのタイプと頻度に関係するものです。 アドバイス・エンジンは、この作業負荷情報をデータベース情報と共に使用して、 索引を推奨します。 アドバイス・エンジンの目的は、作業負荷の合計コストを最小化することです。
次に、仮想索引 という概念があります。 仮想索引とは、現在のデータベース・スキーマに存在しない索引です。 これらの索引は、アドバイス機能によって提示された推奨索引であるか、 アドバイス機能に評価させる索引のいずれかになります。 さらにこれらの索引は、アドバイス機能が処理の一部であるとみなし、 推奨されない場合に廃棄される索引でもあります。 仮想索引は、ADVISE_INDEX 表を使用して、アドバイス機能へ受け渡しされます。
アドバイス機能は、作業負荷とデータベースからの統計を使用し、 推奨索引を生成します。
アドバイス機能は、2 つの Explain 表を使用します。
この表では、考慮する作業負荷を記述します。 表の各行は 1 つの SQL ステートメントであり、 関係する頻度によって記述されます。 作業負荷ごとに識別子が存在し、 それが "WORKLOAD_NAME" という表の 1 つのフィールドになります。 同じ作業負荷の一部であるすべての SQL ステートメントには、 同じ WORKLOAD_NAME が付けられていなければなりません。
「索引 (Index)」ウィザードおよび db2advis ツールは、 作業負荷情報を取り出したり保管したりするのにこの表を使用します。
この表には、推奨索引についての情報が格納されています。 情報は、SQL コンパイラー、「索引 (Index)」ウィザード、db2advis ツール、 またはユーザーによってこの表に入れられます。
この表は、次の 2 つの目的で使用します。
注: | この表を作成するには、 sqllib サブディレクトリーの misc サブディレクトリーにある EXPLAIN.DDL を実行します。 まだ作成していなければ、「索引 (Index)」ウィザードで表を作成することもできます。 |
インデックス・アドバイザーを使用するときの処理には、入力、アドバイザーの呼び出し、 出力、および考慮する必要のあるいくつかの事例が関係します。
インデックス・アドバイザーの入力を作成するには、次の 3 つの方法があります。
つまり、次のいずれかの方法を使用して、評価される SQL を作成します。
インデックス・アドバイザーを呼び出すには、次の 4 つの方法があります。
これは、インデックス・アドバイザーを使用するときの、推奨の方法です。 コントロール・センターからオブジェクト・ツリーを展開してゆき、 「索引 (indexes)」フォルダーを見つけます。 マウス・ボタン 2 で「索引 (indexes)」フォルダーをクリックし、 ポップアップ・メニューから「作成 (Create)」->「索引 - ウィザードを使用 (Index using wizard)」を選択します。 「索引 (Index)」ウィザードがオープンします。 「索引 (Index)」ウィザードには広範囲のヘルプが備えられており、 簡単に使うことができます。 ウィザードには、最近実行された SQL を探したり、 最近使用されたパッケージを調べたりすることによって作業負荷を構成する機能、 あるいは手動で SQL ステートメントを追加することによって作業負荷を構成する機能も備えられています。
コマンド行で、db2advis と入力します。 db2advis は、 次の 3 つの位置のいずれかから作業負荷を読み取ることにより始動します。
ツールは、最善の索引を選ぶための内部最適化アルゴリズムと組み合わせて CURRENT EXPLAIN MODE レジスターを使用し、 推奨索引を獲得します。 出力は、必要に応じて、使用している端末の画面、ADVISE_INDEX 表、 そして出力ファイルに出力されます。
たとえば、単一照会 "select count(*) from sales where region = 'Quebec'" について、 ツールを使用して索引を推奨することができます。
$ db2advis -d sample \ -s "select count(*) from sales where region = 'Quebec'" \ -t 1 performing auto-bind Bind is successful. Used bindfile: /home3/valentin/sqllib/bnd/db2advis.bnd 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] total 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.
作業負荷についても、 db2advis ツールを使用して索引を推奨できます。 次のようにして、"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 = ?;
その後、次のコマンドを実行します。
$ db2advis -d sample -i sample.sql -t 0 found [3] SQL statements from the input file Calculating initial cost (without recommmended 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) =========================== Index Advisor tool is finished.
たとえば、CURRENT EXPLAIN MODE 特殊レジスターは RECOMMEND INDEXES に設定されます。 この設定により、SQL コンパイラーは、ADVISE_INDEX 表に置かれる Explain データおよび推奨索引を獲得します。 ただし、SQL ステートメントは実行されません。
または、CURRENT EXPLAIN MODE 特殊レジスターが EVALUATE INDEXES に設定されます。 この設定により、 SQL コンパイラーは ADVISE_INDEX 表に置かれた索引を使用します。 ユーザーは、評価する索引ごとに新しい行を挿入します。 各索引に必要な情報は、索引名、表名、および評価される索引を構成する列名です。 これらを入力したら、 特殊レジスター CURRENT EXPLAIN MODE を EVALUATE INDEXES に設定します。 その後、SQL コンパイラーは ADVISE_INDEX 表を走査して、 USE_INDEX="Y"となっている索引を見つけます。 (これらは、仮想索引と呼ばれます)。 EVALUATE INDEXES モードで実行するすべての動的ステートメントについては、 それらの仮想索引が使用可能であるとして Explain が実行されます。 仮想索引によってステートメントのパフォーマンスが改善される場合、 SQL コンパイラーは次に、その仮想索引を使用することを選択します。 パフォーマンスが改善されないのであれば、その索引は無視されます。 EXPLAIN の結果を参照すれば、 提案された索引が SQL コンパイラーによって使われたかどうかを確認できます。 使用された索引は、アクセスを向上させるために実装されたとみなされます。
このインターフェースを使ってアプリケーションを作成している場合、 アドバイザーも使用できます。
アドバイザーからの結果を使用する方法は、いくつかあります。
アドバイス機能によって推奨されている索引を表示するには、 次の照会を使用することができます。
SELECT CAST(CREATION_TEXT as CHAR(200)) FROM ADVISE_INDEX
特定の照会についてより良い推奨を獲得するために、 その照会そのものに対してアドバイス機能を使用することが提案されています。 「索引 (Index)」ウィザードを使用し、 該当する照会だけを含む作業負荷を作成することにより、 1 つの照会のために索引を推奨することができます。
サンプルの作業負荷は、イベント・モニターの出力から収集することができます。 イベント・モニターでは、動的 SQL の実行を集めることができます。 その後、このステートメントをアドバイス機能に送ります。
「索引 (Index)」ウィザードは、単純明快で使いやすいビジュアル・インターフェースであり、 アドバイス機能にアクセスするための優れた方法です。