次ページの取得

最初のページを効率的に取り出すために、パイプライン化された最初のN件のみを選択するクエリを 実装できたら、次ページ以降を取りだすクエリも必要になるでしょう。それを実現するためのチャレンジは、前のページの行をどのようにスキップするかです。 これには2つの方法が考えられます。まず1つ目は、先頭から行に番号を付けて、必要なページよりも前の行番号のデータをフィルタする、 オフセット法です。2つ目は、私がシーク法と 呼んでいる、全ページの最後のエントリを検索し、それ以降の必要な行を読み出す方法です。

以下は、最も広く使われているオフセット法の例を表したものです。 この方法の利点は簡単に使える所にあるでしょう。専用のキーワード(offset)が用意されているデータベースでは特にそうです。このキーワードは、fetch first拡張の一部として SQL標準にも採用されています。

シンプルなのに加えて必要なページを取りだすのに行のオフセットだけしか指定しなくて良いのも、この方法の利点でしょう。しかし、 必要なページにたどり着くまでに、データベースは最初からそこまでの全ての行を数えなくてはなりません。図7.2で、取り出すページ数を大きくして行った時に インデックスのスキャンの範囲が増える様子を表しています。

この方法には、2つの欠点があります。(1) 順序付けはクエリが呼び出される 度にやり直されるので、新しい売上が挿入されるとページがずれてしまいます。(2) 先のページを見るほど、応答時間が長くなります。

シーク法では、ページの区切りに前のページの値を使うので、 これらの問題を排除できます。つまり、全ページの最後のエントリの 1つ後に来るべき値を検索するのです。これは、シンプルにwhere句で表現することができます。 逆に言うと、シーク法では既に表示した値を選択しなくて済むのです。

次の例は、シーク法を表したものです。例示のために、1日に売上は 1回しかないと仮定してみましょう。そうすると、SALE_DATEは 一意なキーと言うことになります。ある日の後の売上を選択したい時には、 降順のソートになるのでより小さい(<)を条件にします。 昇順の場合、より大きい(>)を条件ににします。それから、fetch firstによって結果を10行に絞れば良いのです。

SELECT *
  FROM sales
 WHERE sale_date < ?
 ORDER BY sale_date DESC
 FETCH FIRST 10 ROWS ONLY

行番号の代わりに、下限を指定するのに前のページの最後の値を使えば良い事が分かります。データベースがSALE_DATE < ?をインデックスアクセスに使えるようになるので、これはパフォーマンスの観点からすると非常に大きな利点です。 これにより、純粋な意味で前のページの行をスキップすることができるようになります。 さらに、新しい行が挿入されても常に同じ結果が得られるようになります。

しかし、この方法は1日に2つ以上の売上がある場合には、図7.2にあるようにうまく動かなくなります。 最初のページにあった最後の日付(つまり昨日)を指定すると、 最初のページに既に表示されたものだけでなく、昨日の全ての結果が スキップされてしまうためです。これは、都度日付を指定するので最初のエントリからの順番を、order by句が確定的に作れないためです。 しかし、改ページにシンプルな範囲条件を使うには、これは必須条件でもあります。

確定的な結果を返すorder by句なしでは、定義上、 データベースは行の順番を1つに決められません。大抵は 同じ順番で行が得られるのは、データベースが大抵は同じ方法で クエリを実行するからです。しかし、実際には同じSALE_DATEを持つ売上の順序を変えても、order by句の条件は 満たされます。最近のデータベースでは、クエリを実行する度に順序が違うのもよくある事になりました。それは、データベースが意図的に行を 入れ替えているのではなく、データベースがクエリの並列実行を活用するようになったためです。スレッドの実行完了の順序が確定的でないため、 同じ実行計画でも異なる行の順序で結果が得られるということです。

機能的には「最新の日付が最初」で並べ替えるというだけの仕様でも、我々開発者としては、order byが行の順序を確定的に するのを確実にしておかねばなりません。そのために、行の順序を確定的にする任意の列をorder by句に追加する必要があるでしょう。パイプライン化されたorder byで使っているインデックスに他の列が既にあるなら、order by句にその列を追加し、そのままパイプライン化されたorder byとして実行できるように しましょう。それでも行の順序が確定的にならない場合は、適宜、一意な列をインデックスに追加していきましょう。

次の例では、行の順序を確定的にするために、プライマリキーであるSALE_IDをorder by句とインデックスに追加しています。さらに、望む結果を得るために、どちらの列も 一緒に「その次の値」が得られるよう指定しています。

CREATE INDEX sl_dtid ON sales (sale_date, sale_id)

SELECT *
  FROM sales
 WHERE (sale_date, sale_id) < (?, ?)
 ORDER BY sale_date DESC, sale_id DESC
 FETCH FIRST 10 ROWS ONLY

where句は、あまり知られていないであろう 「行値」式を使って書かれています(「SQLの行値式」のボックスを参照)。 複数の値を1つの論理単位にまとめ、通常の比較演算子を適用できるようにしたものです。スカラ値では、降順では未満を表す条件は「その次の値」を 表します。つまり、このクエリは与えられたSALE_DATEと SALE_IDのペアの次に来る売上のみを返します。

行値式はSQL標準の一部ではありますが、サポートされているデータベースは少数に留まります。SQL Server 2017 は行値式をサポートしていません。Oracleでは基本的にはサポートされていますが、行値式に対する範囲条件の適用は できません(ORA-01796)。MySQLは行値式を正しく評価しますが、インデックスアクセス時のアクセス述語としては使えません。 Db2(LUWのみ、10.1以降)とPostgreSQL(8.4以降)は行値式の述語をサポートしており、 かつ対応するインデックスがある場合は、インデックスへのアクセスにも使用可能です。

行値式を正しくサポートしていないデータベースでも、 シーク法に近い方法を使う事はできます。ただし、PostgreSQLのようにエレガントでも効率的でもなくなってはしまいます。この近似法では、 必要なロジックを記述するために「普通の」比較のやり方を使います。次は、Oracleでの例です。

SELECT *
  FROM ( SELECT *
           FROM sales
          WHERE sale_date <= ?
            AND NOT (sale_date = ? AND sale_id >= ?)
          ORDER BY sale_date DESC, sale_id DESC
       )
 WHERE rownum <= 10

where句は2つの部分からなっています。 最初はSALE_DATEに以下(<=)の条件を適用して いるだけです。ただし、これだけでは必要以上の行が選択されてしまいます。where句のここは、データベースがインデックスに アクセスできるようにしているだけのシンプルな部分です。where句の2番目の部分では、全ページで既に表示済みの行を削除しています。「近似法のインデックス」という ボックスで、なぜwhere句がこのようになっているかを 説明しています。

WHERE (
         (sale_date < ?)
       OR
         (sale_date = ? AND sale_id < ?)
      )

WHERE sale_date <= ?
  AND (
         (sale_date < ?)
       OR
         (sale_date = ? AND sale_id < ?)
      )

実行計画から、where句の最初の部分が アクセス述語として使われている事が分かります。

---------------------------------------------------------------
|Id | Operation                      | Name    |  Rows | Cost |
---------------------------------------------------------------
| 0 | SELECT STATEMENT               |         |    10 |    4 |
|*1 |  COUNT STOPKEY                 |         |       |      |
| 2 |   VIEW                         |         |    10 |    4 |
| 3 |    TABLE ACCESS BY INDEX ROWID | SALES   | 50218 |    4 |
|*4 |     INDEX RANGE SCAN DESCENDING| SL_DTIT |     2 |    3 |
---------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=10)
   4 - access("SALE_DATE"<=:SALE_DATE)
       filter("SALE_DATE"<>:SALE_DATE
           OR "SALE_ID"<TO_NUMBER(:SALE_ID))

SALE_DATEのアクセス述語によって、データベースは 前のページで表示済みの日をスキップすることができます。where句の2番目の部分は、フィルタ述語のみになっています。 つまり、データベースは全ページのエントリの内のいくつかをもう一度調査し、すぐに捨てていると言う事が分かります。図7.3で、そのアクセスパスを説明しています。

図7.4では、 オフセット法とシーク法のパフォーマンスの特徴を比較しています。グラフの左寄りでは、測定結果にばらつきがあってはっきり分かりませんが、 ページ数が20を超えてからはその違いは歴然としています。

この説明が気に入れば、きっとこの本も 気に入るはず。