DB2 For i でLimitとOffsetを指定したSQLページング
LIMIT と OFFSET の 2 つは、大規模な結果セットを伴うと思われるユーザー・インターフェース (UI) を構築する開発者には嬉しい照会オプションです。大量の行を返す UI 照会は、処理に時間がかかり過ぎる、ネットワーク・トラフィックが重くなる、結果セットをキャッシュに入れるのに Web またはデスクトップが必要になるなど、面倒なことがあります。さらに、ユーザーがすべての行をレビューすることはあり得ません。しかし、DB2 が、結果セット全体の断片をオンデマンドで返すことにより、必要なものだけ返すとしたらどうでしょうか。
ここで新しい LIMIT と OFFSET 機能が登場します。LIMIT と OFFSET を使うと、 DB2 開発者は大規模な結果セットの一部分だけ抽出できます。IBM i 7.1 TR11 または IBM i 7.2 TR3 が必要です。IBM i が旧バージョンの場合、あまり魅力的ではありませんが、次のような過去のヒントで示した、代わりの SQL ページング方法を実装することもできます。Enable Row Set Paging in a Client/Server Environment Using SQLと Paging Cursors And Position To です。
LIMIT
LIMIT n は偏在し、時間と労力がかかる FETCH FIRST n ROWS ONLY (別名 fetch-first 節) の代替構文です。次で示すように、照会が返す行の数を制限するだけです。
-- 小計が一番多い順から 10 行までだけ返す
SELECT *
FROM SalesOrderHeader SOH
ORDER BY SubTotal DESC
LIMIT 10 -- FETCH FIRST 10 ROWS ONLY と同じ
上記の照会が実行されると、結果セットのサイズが先頭の 10 行に制限されます。記録のため、今後のバージョンの DB2 では、UPDATE と DELETE の両ステートメントに LIMIT が利用できるようになればと思います。
OFFSET
OFFSET 節は、照会結果内のどこから行を返し始めるか、 DB2 に指示します。例えば、照会が 1000 行を返す場合、OFFSET 100 と指定すると、 DB2 は照会結果の先頭 100 行をスキップするよう指示されます。
SELECT ROW_NUMBER() OVER(ORDER BY SUBTOTAL DESC) AS ROW_ID,
SOH.*
FROM SalesOrderHeader SOH
ORDER BY SUBTOTAL DESC
OFFSET 100 ROWS -- 結果の先頭 100 行を無視する
そのため上記の照会では、DB2 は内部で完全な結果セットを構築しますが、結果セットで、行 101 で始まる行のみ返します (DB2 で使用される内部行の番号付けは、主に ORDER BY 節により判断されます)。
ちなみに、 OFFSET は、準備済みステートメントまたは事前コンパイル型ステートメントとの関連以外では使用できません。「Run SQL Scripts」などの一部の対話式ツールは、OFFSET を含む照会を処理し、一方、 STRSQL や STRQMQRY などのグリーン・スクリーン・ツールは、それに対応していません。また、一貫性のある結果を得るには、OFFSET は、 ORDER BY ステートメントのみとともに使用してください。そうしないと、同じステートメントが複数回実行されると DB2 で結果が競合する場合があります。OFFSET が結果セットで可能な数の行を超えて要求されている場合、DB2 は空の結果セットを返します。最後に、結果セットの先頭で行を返すには、 OFFSET 0 を指定します。
LIMITとOFFSETをまとめて
照会で OFFSET と LIMIT を組み合わせることで、 DB2 に大規模結果セットから行の特定のサブセットを返すよう指示できます。あなたが、顧客が注文履歴について問い合わせできるような Web ベースのユーザー・インターフェースを設計しているとしましょう。1 ページに表示される注文の最大数は 10 件です。ユーザーは、ボタンをクリックして次の 10 件 (または前の 10 件) の注文を取得できます。
下図 1 は、標準 Google Web 検索ナビゲーションで実装されているような、この共通の UI 設計を示しています。
150,000 件の注文をした顧客が問い合わせをするとしましょう。Web UI はこれらすべての注文を一度にキャッシュに入れるべきでしょうか、必要に応じて 10 件の注文だけ取得すべきでしょうか。UI の設計上、後者の「ページングされた」アプローチを選択する場合、次のような照会を使用して 150,000 行の特定のサブセットを取得できます。
SELECT SalesOrderId,OrderDate,DueDate,ShipDate,
Status,CustomerId,SubTotal,TaxAmt
FROM SalesOrderHeader SOH
WHERE CustomerId=@CustomerId
ORDER BY SalesOrderId DESC
LIMIT @ROWS_PER_PAGE -- 変数 = 10
OFFSET @PAGE_START_ROW -- 変数 = 10 * ページ番号
変数 @ROWS_PER_PAGE が 10 の場合、かつ変数 @PAGE_START_ROW が 30 の場合、DB2 はその顧客のすべての注文のうち 10 行(行 31-40) を返します。言い換えれば、ユーザーに「ページ 4」の UI 結果を返します。
再使用可能ストアード・プロシージャーに照らして照会をすると、次のような結果になります。
CREATE OR REPLACE PROCEDURE SALES_BY_CUSTOMER
(@CustomerId INT,
@Rows_Per_Page INT,
@Page_Start_Row INT)
RESULT SETS 1
BEGIN
DECLARE PAGING_CUSTOMER CURSOR
WITH RETURN TO CLIENT FOR
SELECT SalesOrderId,OrderDate,DueDate,ShipDate,
Status,CustomerId,SubTotal,TaxAmt
FROM SalesOrderHeader SOH
WHERE CustomerId=@CustomerId
ORDER BY SalesOrderId DESC
LIMIT @ROWS_PER_PAGE
OFFSET @PAGE_START_ROW
;
OPEN PAGING_CUSTOMER;
SET RESULT SETS CURSOR PAGING_CUSTOMER;
END;
プロシージャーは次のように呼び出すことができます。
CALL SALES_BY_CUSTOMER (
@CustomerId=>29734,
@Rows_Per_Page=>10,
@Page_Start_Row=>10);
この場合、結果セットには、結果セットの行 11 から始まる顧客 ID 29734 の注文が 10 行まで含まれます。このプロシージャー設計を使って、ページ・サイズ情報を提供し、目的のページの開始行を計算するのはクライアント次第です。
最後に、結果セットの行の総数を取得する方法を考えます。これは Google が図 1 で示しているように、ユーザーに何ページ利用可能か表示したい場合に便利な情報です。ページごとに 10 行に 50 件の結果がある場合、UI では 5 ページ分の結果があるとユーザーに表示されるため、ユーザーは 5 ページのどのページにも直接ナビゲートすることができます。
何ページあるか判断するには、LIMIT と OFFSET で課せられたサブセットに関係なく、照会が何行返すのかを知る必要があります。この情報は、行の総数を含む結果セットに列を追加することで、クライアントに提供できます。ちなみに GET DIAGNOSTICS 経由で OFFSET を使って、照会の行の総数が返されるなら素晴らしいのですが、実際はそうはいきません。
この改定された SALES_BY_CUSTOMER プロシージャーは TOTAL_ROWS という新しい列を実装します。
CREATE OR REPLACE PROCEDURE SALES_BY_CUSTOMER
(@CustomerId INT,
@Rows_Per_Page INT,
@Page_Start_Row INT)
RESULT SETS 1
BEGIN
DECLARE PAGING_CUSTOMER CURSOR
WITH RETURN TO CLIENT FOR
WITH CTE_Customer_Order AS (
SELECT SalesOrderId,OrderDate,DueDate,ShipDate,
Status,CustomerId,SubTotal,TaxAmt,
ROW_NUMBER() OVER(ORDER BY SalesOrderId) AS Row_Id
FROM SalesOrderHeader SOH
WHERE CustomerId=@CustomerId
)
SELECT CO.*,
(SELECT MAX(Row_Id)
FROM CTE_Customer_Order) AS Total_Rows
FROM CTE_Customer_Order CO
ORDER BY SalesOrderId DESC
LIMIT @ROWS_PER_PAGE
OFFSET @PAGE_START_ROW
;
OPEN PAGING_CUSTOMER;
SET RESULT SETS CURSOR PAGING_CUSTOMER;
END;
この拡張版の例は、 ROW_NUMBER 関数を使って、共通表式 (CTE) 結果セットの各行に増分行 ID を割り当てます。(CTE に基づく) 最後の照会は、列式を使って最上位の (別名 max) 行 ID を取得し、Total_Rows という別名が割り当てられています。この Total_Rows 列を使って、クライアントは、結果セットにある総ページ数を計算し、それに従ってページ・カウントをユーザーに表示できます。
ところで、 IBM の Scott Forstie が、 DB2_NUMBER_ROWS を指定した GET DIAGNOSTICS を使って、OFFSET/LIMIT 制限が課せられていても、照会から (制限された行数と違う) 行の総数を誤って返すという DB2 のバグを指摘しました。しかし、期待される正しい動作は、 DB2_NUMBER_ROWS を指定した GET DIAGNOSTICS は、LIMIT で制限に従って返される行数を返します。
言い換えれば、照会が通常 100 行を返すが LIMIT で 10 行のみ返すよう指定されている場合、GET DIAGNOSTICS は 100 行ではなく 10 行返すはずです。したがって、GET DIAGNOSTICS を使って照会から行の総数を取得する場合、その動作を当てにして先に進まないようにしてください。
