効率的な ページネーションのための窓関数の使用


Applies to
DB2No
MySQLNo
OracleYes
PostgreSQLNo
SQL ServerYes

窓関数は、SQLでページネーションを実装するもう1つの方法です。 柔軟で、ここまでに書いたものと同じく、標準に則っています。 しかし、パイプライン化された最初のN件のみを選択するクエリで使えるのは SQL ServerとOracleのみです。DB2とPostgreSQLでは、必要な行を取得した後に インデックススキャンを中止できないので、クエリの実行が非効率になって しまいます。MySQLは、窓関数をサポートしていません。

次の例は、窓関数のROW_NUMBERをページネーションのクエリに 使用する例です。

SELECT *
  FROM ( SELECT sales.*
              , ROW_NUMBER() OVER (ORDER BY sale_date DESC
                                          , sale_id   DESC) rn
           FROM sales
       ) tmp
 WHERE rn between 11 and 20
 ORDER BY sale_date DESC, sale_id DESC

ROW_NUMBERは、over句で定義されている ソート順に対応した行を列挙する関数です。外側のwhere句では 2ページ目で表示する結果(11行目から20行目)に限定するために、この列挙された 値を使っています。

初心者からエキスパートまで役に立つ内容です。
特に駆け出しのエンジニアは持っておくといい

Oracleでは、パイプライン化された最初のN件だけを選択するクエリを 実行するために実行中断の条件を認識でき、SALE_DATESALE_IDの インデックスを使えます。

DB2
Explain Plan
-------------------------------------------------------------
ID | Operation                   |               Rows |  Cost
 1 | RETURN                      |                    | 65658
 2 |  FILTER                     |  100933 of 1009326 | 65658
 3 |   FETCH SALES               | 1009326 of 1009326 | 65295
 4 |    IXSCAN (REVERSE) SL_DTID | 1009326 of 1009326 |  5679

Predicate Information
 2 - RESID (11 <= Q3.$C8)
     RESID (Q3.$C8 <= 20)

DB2 LUW 10.5では、これを最初のN件のみを選択するクエリとしては 実行しない事に注意しましょう。ソートの処理はしませんが、 インデックスは全体を読んでしまいます。また、20行を読み込んだ後も 実行を中断しません。

20行の読み込み後に正しく実行を中断するには、 実行中断のための条件で二重にクエリをラップし、 その後10行をフィルタする必要があります。

SELECT *
  FROM (SELECT *
          FROM (SELECT sales.*
                     , ROW_NUMBER() OVER (ORDER BY sale_date DESC
                                                 , sale_id   DESC) rn
                 FROM sales
               ) tmp
         WHERE rn <= 20
       ) tmp2
 WHERE rn > 10
 ORDER BY sale_date DESC, sale_id DESC;
Explain Plan
-----------------------------------------------------------------
ID | Operation                       |               Rows |  Cost
 1 | RETURN                          |                    |    21
 2 |  FILTER                         |            7 of 20 |    21
 3 |   FETCH SALES                   |      20 of 1009326 | 65352
 4 |    IXSCAN (REVERSE) SALES_DT_ID | 1009326 of 1009326 |  5736

Predicate Information
 2 - RESID (10 < Q3.$C8)

rn <= 20と言うフィルタはPredicate Informationに 表示されていませんが、行数の見積には反映されている点に注意しましょう。

Oracle
---------------------------------------------------------------
|Id | Operation                      | Name    | Rows |  Cost |
---------------------------------------------------------------
| 0 | SELECT STATEMENT               |         | 1004K| 36877 |
|*1 |  VIEW                          |         | 1004K| 36877 |
|*2 |   WINDOW NOSORT STOPKEY        |         | 1004K| 36877 |
| 3 |    TABLE ACCESS BY INDEX ROWID | SALES   | 1004K| 36877 |
| 4 |     INDEX FULL SCAN DESCENDING | SL_DTID | 1004K|  2955 |
---------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">=11 AND "RN"<=20)
2 - filter(ROW_NUMBER() OVER (
           ORDER BY "SALE_DATE" DESC, "SALE_ID" DESC )<=20)

WINDOW NOSORT STOPKEY処理の表示は、 ソート処理が行われず(NOSORT)、上限に達したらデータベースはクエリの実行を 中断する事(STOPKEY)を表しています。中断された処理はパイプライン化されたもので ある事を考えれば、このクエリの実行は前の節で説明した オフセット法と同じぐらい効率的である事が分かるでしょう。

窓関数の強みはページネーションではなく、数値解析にあります。 もしまだ窓関数を使った事がなければ、関係するドキュメントを数時間読んで 勉強する価値は、間違いなくあると言えます。

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

Photo of Markus Winand
Markus Winand氏は、開発者がSQLパフォーマンスを改善するお手伝いをしています。 彼は、SQL Performance Explainedの 著者でもあり、出張トレーニングhttp://winand.at/での リモート講義も 行っています。