窓関数は、SQLでページネーションを実装するもう1つの方法です。柔軟で、ここまでに書いたものと同じく、標準に則っています。 しかし、パイプライン化された最初のN件のみを選択するクエリで使えるのはSQL Server、Oracle、PostgreSQL 15以上のみです。 MySQL、MariaDB0、 DB2 (LUW)では、必要な行を取得した後にインデックススキャンを中止できないので、 クエリの実行が非効率になってしまいます。
次の例は、窓関数の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 DESCROW_NUMBERは、over句で定義されているソート順に対応した行を列挙する関数です。外側のwhere句では
2ページ目で表示する結果(11行目から20行目)に限定するために、この列挙された値を使っています。
協力してください
この記事が気に入ったら、私の書いた本「SQLパフォーマンス詳解」や私によるトレーニングもきっと気にいるはず。
Oracleでは、パイプライン化された最初のN件だけを選択するクエリを
実行するために実行中断の条件を認識でき、SALE_DATEとSALE_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)- PostgreSQL
バージョン15の実行計画から、後続の実行が中断されうることを示すRun Conditionが表示されます。
QUERY PLAN ------------------------------------------------------- Subquery Scan on tmp (cost=0.42..141724.98 rows=334751 width=249) (actual time=0.040..0.052 rows=10 loops=1) Filter: (tmp.rn >= 11) Rows Removed by Filter: 10 Buffers: shared hit=5 -> WindowAgg (cost=0.42..129171.80 rows=1004254 width=249) (actual time=0.028..0.049 rows=20 loops=1) Run Condition: (row_number() OVER (?) <= 20) Buffers: shared hit=5 -> Index Scan Backward using sl_dtid on sales (cost=0.42..111597.36 rows=1004254 width=241) (actual time=0.018..0.025 rows=22 loops=1) Buffers: shared hit=5
WINDOW NOSORT STOPKEY処理の表示は、
ソート処理が行われず(NOSORT)、上限に達したらデータベースはクエリの実行を中断する事(STOPKEY)を表しています。中断された処理はパイプライン化されたものである事を考えれば、このクエリの実行は前の節で説明した
オフセット法と同じぐらい効率的である事が分かるでしょう。
この最適化のサポートは、SQL製品の間では決して一般的なことではありません。
row_number()ありの場合partition by句なしの場合(下記参照)
この最適化は概念上はどんなモノリシックな関数でも動作するはずですが、分析の実装の中では明らかにROW_NUMBER関数を意識したものになっています。
where句ありの場合 :OVER(ORDER BY…)+WHERE x=?
partition byを使用する際の注意点: where句が1つのパーティション内の行だけを対象にしてくれますが、
partition byコマンドがあることで、いくつかの製品ではこの最適化が無効化されてしまいます。
これは、パーティションされた窓関数がビューの一部である一方、外側のクエリが1つのパーティションにビューを絞る時に発生する可能性があります。
where句があればどの関数でも動作するわけではありません
窓関数の強みはページネーションではなく、数値解析にあります。もしまだ窓関数を使った事がなければ、関係するドキュメントを数時間読んで 勉強する価値は、間違いなくあると言えます。
この説明が気に入れば、きっとこの本も 気に入るはず。
