索引をいつ使用するかを自分で決定することはない、という点は重要です。 データベース・マネージャーが、 使用可能な表および索引の情報に基づいて決定を行います。 しかし、パフォーマンスを向上できるものとして必要な索引を作成するなら、 そのプロセスにおいて重要な役割を果たすことになります。 また、索引を作成した後、索引についての統計を収集する (RUNSTATS ユーティリティーを使用) か、 事前取り出しサイズ (上記で言及) を変更し、 状況に応じてその統計を最新のものに保つことも重要です。 つまり、管理者は、作成可能な索引の種類、 およびそれらを作成する方法を理解していなければなりません。
データベース照会に関係するどの列にも索引が存在していない場合、 その照会において参照される各表ごとに、表走査を実行しなければなりません。 表が大きいほど、表走査にかかる時間は長くなります。 表走査 が行われるのは、 データベース・マネージャーが表の各行に順次アクセスするときです。 これは、データベース・マネージャーが索引を使用してデータにアクセスするときに行われる索引走査 と比較することができます。 (索引走査の概念を参照。)
最適化プログラムが、表走査よりも索引走査を使用したほうが速くなると見積もった場合は、索引を使うよう選択します。 特に表が大きくなるにつれ、索引ファイルは一般にファイル全体に比べて小さくなり、 読み取りのための時間が少なくなります。 さらに、索引全体を走査することが必要になることはまずありません。 索引に述部を適用すると、データ・ページから読み取られる行数が減ります。
各索引項目は、検索キー値と、その値が入った行を指すポインターで構成されています。 値を逆方向に検索できるのは、 CREATE INDEX ステートメントで ALLOW REVERSE SCANS パラメーターが指定されている場合だけです。 したがって、適切な述部を使用して、検索を一まとめにすることができます。 索引を使ってオーダー順序で行を入手することにより、 データベース・マネージャーが行を表から読み取ってからその行を分類する必要がなくなります。 ALLOW REVERSE SCANS を指定すると、 順方向および逆方向に行を順次に直接読み取るよう索引を使用できます。 詳細については、SQL 解説書 を参照してください。
固有索引には、組み込み列と検索キー値および行ポインターが含まれます。
注: | ユーザーは、データベース・マネージャーが索引を使用するかどうかについては制御することができません。 たとえば、照会中の表に索引があるというだけで、 照会結果がオーダー順序で作成されるという保証はありません。 データベース・マネージャーは照会時にこの索引を使用することはできますが、 索引の使用が必須ではありません。 ORDER BY 文節を使用することによってのみ、 結果セットを順序づけることが "できます"。 |
索引により、アクセス時間を大幅に短縮することができます。 しかし、索引は、パフォーマンスに悪い影響を与えることもあります。 索引を作成する前に、複数の索引を作成することが、 ディスク・スペースや処理時間に対してどんな影響を与えるかを考慮してください。
索引は、アプリケーション・プログラムの要件に合わせて慎重に選択する必要があります。
特定のパッケージで索引を使用するかどうかを決めるには、 SQL Explain 機能を使用できます (第 26 章, SQL Explain 機能を参照)。
DB2 インデックス・アドバイザーは、表データにとって最適の索引のセットを選択するのに役立ちます。 このツールは、次のような複数の方法で呼び出すことができます。
DB2 インデックス・アドバイザーの詳細については、SQL アドバイス機能で説明されています。
どんな索引を作成するかは、データおよびその用途によって異なります。 どの索引がもっとも有用かを判断する上で、以下に示す指針が役立つでしょう。
INCLUDE 列の詳細については、 管理の手引き: 計画 の『索引、索引の拡張、または索引の指定の作成』という章を参照してください。
注: | クラスター化索引が定義されると、データ・ページで挿入が行われるようにするために、 空きスペースが予約された状態で表が各データ・ページにロードされる必要があります。 (空きスペースは、ALTER TABLE ステートメントで PCTFREE キーワードを使用して、 または LOAD コマンドの pagefreespace MODIFIED BY 文節を使用して予約します。) |
注: | 宣言済み一時表では索引はサポートされません。 |
索引を作成するとパフォーマンスが向上するのは、一般に次のような環境の場合です。
次のような WHERE 文節の場合、
WHERE WORKDEPT='A01' OR WORKDEPT='E21'
一般に WORKDEPT に対する索引があれば便利ですが、 それらの値が頻繁に発生する場合はそうでもありません。
以下の例では、DISTINCT 文節を使用しています。
SELECT DISTINCT WORKDEPT FROM EMPLOYEE
データベース・マネージャーでは、 WORKDEPT に対して昇順または降順に定義された索引を使用することによって、 重複値を削除することができます。 以下の GROUP BY 文節の例のようにして、 この同じ索引を使用して値をグループ化することもできます。
SELECT WORKDEPT, AVERAGE(SALARY) FROM EMPLOYEE GROUP BY WORKDEPT
たとえば、以下の SQL ステートメントが発行されたとします。
SELECT LASTNAME FROM EMPLOYEE WHERE WORKDEPT IN ('A00','D11','D21')
EMPLOYEE 表の WORKDEPT 列と LASTNAME 列に索引が定義されている場合、 表全体を走査するよりも索引を走査する方が、 ステートメントをより効率的に処理することができます。 述部は WORKDEPT についてであるため、 この列は索引の最初の列でなければなりません。
CREATE UNIQUE INDEX x ON employee (workdept) INCLUDE (lastname)
lastname を索引キーの一部としてではなく、 組み込み列として指定することは、 lastname が索引の葉ページでのみ保管されるということを意味します。
以下は、索引を正しく使用、 管理することがパフォーマンスにどのように影響するかを理解する上で役に立つ情報です。
大きな表に索引を作成する場合、SMP マシンを使用しているならば、 intra_parallel を YES (1) か SYSTEM (-1) に設定して、 並列パフォーマンスの機能を利用することを考慮してください。
複数のプロセッサーを使用して、データの走査や分類を行うことができます。 索引の作成時に複数のプロセッサーを有していても特に利点がないのは、 indexsort データベース構成パラメーターが NO の場合だけです (このパラメーターの省略時値は YES です)。 このパラメーターは、索引の作成時に索引キーの分類を行うかどうかを制御します。
索引は、他の表データを保管するのに使用するのとは別の表スペースに保管することができます。 これにより、読み書きヘッドの移動が少なくなり、 ディスク装置をさらに効率的に使用できるようになります。 索引表スペースを、高速な物理装置に保管されるように作成することもできます。
また、表スペースに別のバッファー・プールを割り当てて、 大量のデータ・ページによりバッファーが満杯になって索引ページが押し出されることのないようにすることもできます。
索引が別々の表スペースに置かれていないと、 データと索引ページの両方が同じエクステント・サイズと事前取り出し数量を使用します。 索引用に別の表スペースを使用する場合は、 表スペースのすべての特性に別の値を選択するオプションがあります。 索引は一般に表より小さく、いくつかのコンテナーに分散しているため、 8 や 16 のような小さいエクステント・サイズを選ぶのが普通です。 詳細については、索引ページの事前取り出しを参照してください。 SQL 最適化プログラムでは、表スペースに高速の装置を使用することが考慮されます (照会最適化に対する表スペースの影響を参照)。 表スペースの詳細については、管理の手引き: 計画 を参照してください。
SQL ステートメントで並べ替え (たとえば、ORDER BY、GROUP BY、DISTINCT) が必要であり、 さらにその並べ替えを行うのに適した索引がある場合、 データベース・マネージャーが索引を選択しない 可能性があります。 こういうことが起きるのは、次のような場合です。
クラスター化索引を作成した後で、ユーザーが REORG、つまり分類、 および LOAD を実行することが勧められています。 ただし、一般に 1 つの索引では 1 つの表しかクラスター化できないことに注意してください。 表と索引は、その表に対するクラスター化索引の順序で構築する必要があります。 RUNSTATS ユーティリティーによって集められた CLUSTERRATIO または CLUSTERFACTOR 統計を向上させて、 クラスター化索引はデータの特定順序を維持しようと試みます。
表のロードまたは再編成を行う前にその表を変更するときは、 PCTFREE を使用することも考慮するべきです。 クラスター化を維持するために、各ページは、 追加の挿入に備えて使用可能なスペースをそれぞれのデータ・ページに設ける必要があります。 このスペースが使用できると、既存のデータで追加の挿入をクラスター化できます。 そうすると、追加データのクラスター化のために各ページに、 あるパーセントの空きスペースを残して、 データをその表にロードすることを考慮したいことでしょう。 これは、最初に表を作成してから、 PCTFREE パラメーターを使用してその表を変更することによって行うことができます。 同じように、データを再編成する前に、 PCTFREE パラメーターを使用して表を変更することを考慮する必要があります。 そうしないと、PCTFREE が設定されていない場合は、 再編成によってすべての余分のスペースが除去されてしまいます。
クラスター化は、現状では更新の間維持されません。 つまり、クラスター化索引のキー値が変更されるような方法でレコードを更新する場合、 レコードはクラスター化順序を維持するために改ページに必ずしも移動されるとは限りません。 クラスター化を維持するために、UPDATE を使用する代わりに、 DELETE とそれから INSERT を使用してください。
新規索引を作成した後は、RUNSTATS ユーティリティーを使用して索引統計を収集するようにしてください。 それらの統計を使うことによって、 索引の使用によってアクセス・パフォーマンスが向上するかどうかを最適化プログラムが判断することが可能になります。 このトピックの詳細については、RUNSTATS ユーティリティーを使用しての統計収集を参照してください。
索引から最高のパフォーマンスを得るには、 索引を周期的に再編成することを考慮してください。 表を更新すると、索引ページの事前取り出しの効果が減少します。 索引ページの事前取り出しの効果を保持するには、 索引を再編成する必要があります。
索引の再編成には、索引を消去してから再作成するか、 または REORG ユーティリティーを使用します。 詳細については、カタログとユーザー表の再編成を参照してください。
頻繁に再編成しなくても済むよう、索引の作成時に PCTFREE を指定することができます。 索引の作成中に PCTFREE パラメーターを指定すると、 索引葉ページが作成される時に空きスペースが各索引葉ページに残されます。 その結果、索引に関連した将来の活動で、 索引ページ分割をほとんど生じさせることなくレコードを索引に挿入できます。 索引ページ分割が生じると、索引ページは隣接したものとならず、 連続したものともなりません。 これは、索引ページの事前取り出しを実行する機能が低下する原因になります。 索引についてふさわしい PCTFREE を選択することで、 索引を再編成する必要をなくしたりその頻度を下げることができます。
注: | 索引の作成時に指定される PCTFREE は、 再編成中に索引が再作成されるときに使用されます。 |
索引を消去してから再作成すると、 ほぼ隣接し連続した一連の新しいページが作成されます。 これにより、索引ページの事前取り出しが行われるときに、効果性が改善されます。
REORG ユーティリティーの実行にはコストがかかりますが、 データ・ページを確実にクラスター化します。 このようにしてクラスター化を行うと、 大量のデータ・ページにアクセスする索引走査を行う場合に大きな利点があります。
対称マルチプロセッサー (SMP) システム環境で処理を行っている場合には、 REORG ユーティリティーは、 intra_parallel が YES または ANY のときに複数のプロセッサーを使用します。
定期的に、使用頻度が最も高い照会に対して EXPLAIN を実行して、 それぞれの索引が最低でも 1 回は使用されているかどうかを調べるようにしてください。 どの照会でも使用されていない索引がある場合には、その索引の除去を検討する必要があります。
また、EXPLAIN を使用すると、 大きな表の表走査がネスト・ループ結合の内部結合として処理されているかどうかを調べることもできます。 それはすなわち、結合述部列の索引が欠落しているか、 またはその索引が結合述部を適用するのに効果的でないと考えられることを示しています。 あるいは、結合述部がないことも考えられます。
揮発性 表は、 実行時にその内容が空であるものから内容が非常に大きなものに至るまで、 さまざまな内容を持つ表として定義されます。 揮発性表を使用するアクセス・プランを生成すると、 最適化プログラムは、揮発性表へのアクセスに索引走査ではなく、 表走査を優先的に使用します。
ALTER TABLE...VOLATILE ステートメントを使用して表を「揮発性」として宣言すると、 最適化プログラムは揮発性表に対して索引走査を使用できるようになります。 このトピックの詳細については、 管理の手引き: 計画 または SQL 解説書 を参照してください。