Oracle SQLチューニング（第４７回）「 画面PAGINATION処理する時これは守ろう 」(1/7)
今回のOracle SQLチューニングからは新シリーズがスタートします。
全7回シリーズで「画面PAGINATION処理する時これは守ろう」と題してお送りします。
まず第1回目となる今回は 『各論に入る前の概論について』説明していきたいと思います。
それで早速はじめましょう。
SQLチューニングのためには、そのSQLがどのように動いていて、データベースにどのように影響しているのか、を把握する必要があります。『MaxGauge』があれば簡単に状況が把握でき、適切なSQLチューニングができるようになります。
『MaxGauge』の資料はこちらから。
目次[非表示]
画面分割処理（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回目です。
SQLチューニングのためには、そのSQLがどのように動いていて、データベースにどのように影響しているのか、を把握する必要があります。『MaxGauge』があれば簡単に状況が把握でき、適切なSQLチューニングができるようになります。
『MaxGauge』の資料はこちらから。
