数行を取り出すコストの最小化

条件を満たす多くの行のうちほんの一部のみを取り出すアプリケーションのパフォーマンスを改善できます。

このタスクについて

プログラム固有のプログラミング・インターフェース情報の開始
アプリケーションが SELECT ステートメントを発行すると、Db2® は、アプリケーションが条件を満たす行すべてを取り出すものと想定します。 この想定は、バッチ環境では最適です。 しかし、SPUFI のような対話式 SQL アプリケーションの場合、可能性としては大きな結果セットになる照会を定義しながら、最初の少数の行しか取り出さない、ということがよくあります。

OPTIMIZE FOR n ROWS 文節は、以下のいずれかのアクションを実行するアプリケーションの意図を宣言します。
  • 結果セットのサブセットだけを取り出す。
  • 最初の少数の行の検索に優先度を与える。

Db2 は、OPTIMIZE FOR n ROWS 文節を使用して、 最初の数行を検索する応答時間を最小にするようなアクセス・パスを 選択します。 分散された照会の場合、n の値は、Db2 が各 DRDA ネットワーク伝送 でクライアントに送る行数を決めます。

OPTIMIZE FOR n ROWS 文節は、条件を満たす行すべてを取り出します。ただし、OPTIMIZE FOR n ROWS を使用すると、条件を満たす行すべてを取り出すための合計経過時間は、Db2 により結果セット全体に対して最適化を行う時間よりも長くなる場合があります。

OPTIMIZE FOR n ROWS が効果的なのは、増補的に処理できる照会のみです。 照会が、最初の行を戻す前に全体の結果セットを Db2 が収集するようなものである場合、Db2 は OPTIMIZE FOR n ROWS 文節を無視します。 例えば、以下の場合です。

  • 照会が SELECT DISTINCT またはセット関数に DISTINCT を指定して (COUNT(DISTINCT C1) など) 使用している。
  • GROUP BY または ORDER BY のいずれかが使用されていて、必要とする順序付けを提供できる索引がない。
  • 集約関数は使用されるが、GROUP BY 文節が使用されていない。
  • 照会が UNION を使用している。

手順

条件を満たす規模の大きな行セットのうち数行を取り出すアプリケーションを最適化するには、以下の方法を使用します。

  • ソート操作を回避するため、OPTIMIZE FOR 1 ROW を指定します。 OPTIMIZE FOR 1 ROW は、 最初の限定行を迅速に戻すアクセス・パスを選択するよう Db2 に指示します。 結果として、Db2 は可能な限りソートを回避します。n に 1 以外の値を指定すると、Db2 はコストに基づきアクセス・パスを選択し、ソート操作の可能性が残ります。

    OPTIMIZE FOR 1 ROW は、ローカルおよびリモートのどちらの照会に対しても使用できます。この値の場合、分散照会のブロック・フェッチは防止も制限もされません。

    OPTIMIZE FOR 1 ROW を指定しても引き続きソート操作が行われる場合には、OPT1ROWBLOCKSORT サブシステム・パラメーターの値を ENABLE に設定できます。

  • ローカル照会の場合、OPTIMIZE FOR n ROWS の指定は、照会結果セットの行全体のうちわずかなパーセンテージの行数を頻繁にフェッチするアプリケーションでのみ行ってください。 例えば、単一の端末画面に収まる行だけを読み取れれば十分なアプリケーションもあります。そのような場合、照会結果セットの残りの部分をアプリケーションが読み取ることはめったにありません。こうしたアプリケーションでは、OPTIMIZE FOR n ROWS を指定すると、結果的にパフォーマンスが向上する可能性があります。この場合、Db2 は最初の n 行をできるだけ迅速に送達できる SQL アクセス・パスを選択しようとするからです。
  • リモート照会の場合、以下のような特定の状態に関して適切な値の n を指定します。
    • 単一の伝送でネットワークを通して流れる行数を制限するには、n に小さな値を指定します。
    • 大きな結果セットを受け取るためのパフォーマンスを向上させるには、n に大きな値を指定します。大きな値を指定すると、Db2 は n 行を複数の伝送で送信しようと試みます。 大きな結果セットを取り出す際のパフォーマンスを向上させるには、対象の照会の結果セット全体が処理されるまでは他の SQL ステートメントを発行しないでください。複数の照会のデータ検索がオーバーラップした場合は、Db2 が結果セットのデータ を DDF アドレス・スペース内のバッファーに入れなければならなくなることがあります。
  • コール・レベル・インターフェース (CLI) アプリケーションの場合、Db2 がすべての照会に対して OPTIMIZE FOR n ROWS を使用するよう指定できます。これを行うには、OPTIMIZEFORNROWS キーワードを初期化ファイルに指定します。

タスクの結果

OPTIMIZE FOR n ROWS 文節を指定した場合、以下のアクセス・パスの変更が起きる可能性があります。
  • 結合方式が変更される可能性があります。最も可能性の高い選択はネスト・ループ結合です。1 行だけを取り出すコストが低いからです。
  • ORDER BY でソートの必要がなくなるので、ORDER BY 文節に一致する索引が選ばれる可能性が高くなります。
  • リスト・プリフェッチが選択される可能性は低くなります。
  • Db2 により、順次プリフェッチが要求される可能性は低くなります。取り出すのが数行であると推定するためです。
  • 結合照会の場合、外部表に作成された索引が ORDER BY 文節に必要な順序付けを提供すると、 ORDER BY 文節の列の表を外部表として選択する可能性が高くなります。
    プログラム固有のプログラミング・インターフェース情報の終了

例

最高の給与を持つ従業員を判別するために、 従業員表を定期的に照会するものとします。 以下の照会を使用できます。

SELECT LASTNAME, FIRSTNAME, EMPNO, SALARY
  FROM EMP
  ORDER BY SALARY DESC;

列 EMPNO に索引が定義されているので、従業員レコードは EMPNO 順になっているとします。降順索引も SALARY 列に存在すると、その索引のクラスター化が不十分である可能性があります。ランダムな同期入出力操作が多くなるのを回避するため、Db2 は表スペース・スキャンを使用して、行を SALARY でソートすることになります。ソート操作により、最初の数行がアプリケーションに返されるのが遅れる可能性があります。ただし、以下のステートメントを使用すると、ソート操作のコストを回避できます。

SELECT LASTNAME,FIRSTNAME,EMPNO,SALARY
  FROM EMP
  ORDER BY SALARY DESC
  OPTIMIZE FOR 20 ROWS;

このステートメントを使用すると、Db2 はおそらく SALARY 索引を直接使用します。 この照会では、給与が高い上位 20 人の従業員のみを取り出す必要があることを指示するようになりました。