SQLチューニング（第47回）

「 画面PAGINATION処理する時これは守ろう 」（1／7回）

今回のOracle SQLチューニングからは新シリーズがスタートします。
全7回シリーズで「画面PAGINATION処理する時これは守ろう」と題してお送りします。
まず第1回目となる今回は 『各論に入る前の概論について』説明していきたいと思います。

それで早速はじめましょう。

画面分割処理（PAGINATION処理）とは？

オンラインプログラムの中から抽出されたデータの件数が多すぎる場合、すべてのデータを一画面で表示させようと
するとDBに負荷が生じてしまうことがあります。

このようなプログラムでは、DBサーバから抽出されたデータは一度、WASサーバに取り込んだ後、クライアントPCへ
出力させています。このような処理は、DBサーバとWASサーバ間のネットワーク帯域が高負荷となるだけではなく
WASサーバの物理メモリも過剰に消費するため、その結果としてDBの性能問題が発生することに繋がってしまいます。

本来、オンラインプログラムは頻繁な照会と少ないデータ件数を抽出するため、WEB画面で構成されるのが一般的です。
照会頻度の高いプログラムが、多くのデータを１画面で出力されるように作成している場合、DBサーバとWASサーバの
両方に悪影響を及ぼす可能性があります。

従って、多くのデータを抽出するプログラムの場合、１画面にすべてのデータを表示させずにページを分割させて
最小データのみを表示させるように作成しなければなりません。これを 画面分割（Pagination）処理といいます。

　画面分割処理において知るべき重要な特性として、全データをページ単位で分割（以降ページ処理）させる際には、
　最初のページに表示させるデータは、業務処理時に最初に処理するデータでなければなりません。

　例えば、特定顧客の購入履歴を表示させる画面であれば、以前の購入履歴を表示するのではなく、最近購入した
　コンテンツを表示させたいと考えるはずです。
　そのためには、SQL作成時に購入日付で降順にソートした上で、ROWNUMを利用してデータ全体の必要な部分だけを
　切り出して画面に出力できるように作成するのが一般的です。

　このようなページ処理では、以下のように大きく２つの方法が多く使用されています

　まず最初に考えられる方法は、ページ処理時に1ページへ50行ずつ出力する場合、最初のページにはソート基準を
　満たすデータの中からROWNUM順番が抽出した1〜50までのデータを表示させ、2ページ目はROWNUM順番が
　抽出した1～100までのデータから51～100までのデータを画面に出力するようにSQLを作成します。
　しかし、このような方法で作成されたページ処理は、ページ番号が増えるほどSQLのパフォーマンスが低下します。
　その理由は、実際の１画面に表示させるデータは50件であるものの、ページ番号が増えるとROWNUMは全ページ分の
　データをすべて処理した後に再度50件分を表示させることになるからです。

　第2に考えられる方法は、最初の方法での非効率な部分を除去するためのSQLを作成する方法です。
　これは画面上にNEXTボタンを配置するのと同じ概念です。
　このページ処理では、ROWNUM が最初のページで表示させるデータとして順番に 1 ～ 50 までのデータを抽出した後
　２番目のページでは、1～50までのデータは抽出せずに 51 ～ 100 だけを抽出して表示します。
　この方法を取ることによって、最初の方法での非効率な部分を除去することができます。

　ただし、2番目の方法のように不要なデータを抽出せずに処理するには、最初のページのデータ（1〜50）を除外する
　別の条件をWhere句に追加しておく必要があります。
　そして、Where句に追加された条件によってSQLパフォーマンスに影響を与えてはいけません。
　従って、新しく追加する条件は、Order By句に適合し、既存のWhere句の条件と追加された条件の列が同じインデックス
　に含まれるようにインデックス構成を変更する必要があります。
　この前提条件を満たさなければ、必要なデータだけを抽出して処理することができません。

　つまり、最初の方法でSQLを作成する場合と比較すると、2番目の方法を適用することは困難です。
　ＷＥＢ画面では、最初のページが最も頻繁に照会されることが多く、ページ番号が増加するほど照会される頻度が少なく
　なる傾向にあるからです。それらを考慮して考えた場合、最初の方法を適用することが多いと言えます。

　ここで注意すべきポイントがあります。
　上記のようなＷＥＢ画面で適用するページ処理では、画面照会時間や照会時に発生する負荷が少なくするように
　考えておく必要があります。
　この点を踏まえると、全データを全て読み込んだ後に処理する方法ではなく、全データから特定のページで表示させる
　データのみを処理（部分範囲処理）した後に、画面抽出ができるようにSQLを作成しなければならないのです。

　

　今回は「画面PAGINATION処理する時これは守ろう」の概論について説明してきました。
　いかがでしたか？次回からは、いよいよ本テーマの各論へ入っていきたいと思います。

SQLチューニングブログ（第47回）　終

次回ブログテーマ

「　ROWNUM処理を良くしよう　」

次回は「画面PAGINATION処理する時これは守ろう」の第2回目です。