🖥️
Oracleでのページングとソート
Oracleでのページングとソート
- Oracle19c(CEE エディション含む)で膨大なデータを効率的にページングしつつソートする実装例。
- 一般的に
OFFSET ... FETCH ...
は読み飛ばしが発生し非効率といわれるが、Oracleは最適化を行い、適切なインデックスが存在すれば実質的に早期打ち切り(STOPKEY
)をしてくれるケースがある。 - 標準SQLのウィンドウ関数
ROW_NUMBER()
を使う方法も同様に最適化(WINDOW NOSORT STOPKEY
)が効く場合がある。 - 以下では、ソフトウェア商社の商材データを例に、ページングとソートを行う実装をまとめる。
目次
- 前提と要件
- テーブル定義例
- サンプルデータ投入
- OFFSET FETCH 方式
- ROW_NUMBER 方式
- インデックス設計と最適化
- 実行計画 (EXPLAIN PLAN) の確認
- 注意点とベストプラクティス
- 参考情報
前提と要件
- Oracle19c(もしくは12c以降)が利用可能である
- ソートおよびページングを行うテーブルを想定(ここでは
TRADING_SOFTWARE
とする) - ソフトウェア商材を扱う商社向けのデータで、件数が膨大に増える可能性がある
- インデックスを正しく設定し、実行計画を確認して早期打ち切り(
STOPKEY
)が行われることを狙う
テーブル定義例
CREATE TABLE trade_user.TRADING_SOFTWARE (
SOFTWARE_ID NUMBER(10) NOT NULL,
SOFTWARE_NAME VARCHAR2(100) NOT NULL,
VENDOR_NAME VARCHAR2(100) NOT NULL,
PRICE NUMBER(10,2) NOT NULL,
STOCK_QUANTITY NUMBER(10) NOT NULL,
REGISTERED_AT DATE NOT NULL,
CONSTRAINT PK_TRADING_SOFTWARE PRIMARY KEY (SOFTWARE_ID)
);
-
SOFTWARE_ID
は主キー - ソートに利用するカラム例として
PRICE
やREGISTERED_AT
を想定
サンプルデータ投入
INSERT INTO trade_user.TRADING_SOFTWARE VALUES (1, 'ERP Package', 'VendorA', 500000, 10, SYSDATE);
INSERT INTO trade_user.TRADING_SOFTWARE VALUES (2, 'CRM Suite', 'VendorB', 300000, 5, SYSDATE);
INSERT INTO trade_user.TRADING_SOFTWARE VALUES (3, 'Security App', 'VendorC', 800000, 2, SYSDATE);
INSERT INTO trade_user.TRADING_SOFTWARE VALUES (4, 'Accounting Tool', 'VendorA', 200000, 8, SYSDATE);
INSERT INTO trade_user.TRADING_SOFTWARE VALUES (5, 'Analytics Platform','VendorB', 900000, 1, SYSDATE);
INSERT INTO trade_user.TRADING_SOFTWARE VALUES (6, 'Mobile Dev Kit', 'VendorC', 250000, 15, SYSDATE);
INSERT INTO trade_user.TRADING_SOFTWARE VALUES (7, 'Test Automation', 'VendorA', 150000, 20, SYSDATE);
INSERT INTO trade_user.TRADING_SOFTWARE VALUES (8, 'DevOps Suite', 'VendorB', 400000, 12, SYSDATE);
INSERT INTO trade_user.TRADING_SOFTWARE VALUES (9, 'Legacy Migration', 'VendorA', 650000, 3, SYSDATE);
INSERT INTO trade_user.TRADING_SOFTWARE VALUES (10,'E-commerce Platform','VendorC',1000000, 4, SYSDATE);
COMMIT;
OFFSET FETCH 方式
- 基本構文
SELECT
t.*
FROM trade_user.TRADING_SOFTWARE t
ORDER BY t.PRICE DESC
OFFSET :offset ROWS FETCH NEXT :limit ROWS ONLY;
-
注意点
- 一般には先に
:offset
分を読み飛ばすイメージなので、大量データだとパフォーマンスリスクがある - Oracleの最適化により
STOPKEY
が使われると、インデックス経由で実際には無駄な読み飛ばしが少なくなる可能性がある - 必ず実行計画を確認すること
- 一般には先に
ROW_NUMBER 方式
- 基本構文
SELECT *
FROM (
SELECT
t.*,
ROW_NUMBER() OVER (ORDER BY t.PRICE DESC) AS rn
FROM trade_user.TRADING_SOFTWARE t
) tmp
WHERE tmp.rn BETWEEN :start AND :end
ORDER BY tmp.PRICE DESC;
-
注意点
- ページングは
tmp.rn BETWEEN 11 AND 20
のように指定 - Oracleは
WINDOW NOSORT STOPKEY
を利用して早期打ち切りする可能性がある - 標準SQLであり、MySQLなど他DBでも記述は可能 (最適化されるかはDBエンジン次第)
- ページングは
インデックス設計と最適化
- 例: 登録日やIDを使う場合
CREATE INDEX IDX_TRADING_SOFTWARE_DATE_ID
ON trade_user.TRADING_SOFTWARE (REGISTERED_AT DESC, SOFTWARE_ID DESC);
- Oracleは物理的な降順インデックスを持つわけではないが、クエリに合わせて
DESC
を指定するとオプティマイザが参照しやすい場合がある - 条件やソート列が複数ある場合、複合インデックスやマテリアライズドビューなども検討
- インデックスにより
STOPKEY
(pipelined top-N)が働けば、大量データからでも高速に必要件数だけ取得可能
実行計画 (EXPLAIN PLAN) の確認
EXPLAIN PLAN FOR
SELECT *
FROM (
SELECT
t.*,
ROW_NUMBER() OVER (
ORDER BY t.REGISTERED_AT DESC, t.SOFTWARE_ID DESC
) AS rn
FROM trade_user.TRADING_SOFTWARE t
) tmp
WHERE tmp.rn BETWEEN 11 AND 20
ORDER BY tmp.REGISTERED_AT DESC, tmp.SOFTWARE_ID DESC;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-
WINDOW NOSORT STOPKEY
やINDEX FULL SCAN DESCENDING
が表示されれば、インデックスを利用した早期終了が行われている可能性が高い -
OFFSET FETCH
を使う場合も同様に確認し、最適化されているかをチェック
注意点とベストプラクティス
-
動的ソートキー・動的クエリの安全性
- SQLインジェクションを避けるため、
ORDER BY
などを動的に組み立てる際は必ずホワイトリストやパラメータバインドを考慮 - Oracleでは
ORDER BY :bind
は使えないので、アプリケーションで安全にカラム名を決定して文字列埋め込みする方法が必要
- SQLインジェクションを避けるため、
-
大量データに対する検証
- 実際のデータ量と統計情報が増えた段階で必ず
EXPLAIN PLAN / AUTOTRACE
を実行 - 大規模ならパーティショニング、マテリアライズドビュー、インメモリオプションなども検討
- 実際のデータ量と統計情報が増えた段階で必ず
-
バージョン差異への配慮
- Oracle11g以前では
OFFSET FETCH
が使えず、ROW_NUMBER (サブクエリ)
方式がメイン - 12c以降(19c含む)であればどちらの方式も選択可能
- Oracle11g以前では
-
レスポンス速度を保証するなら API レイヤーでキャッシュや別ストアを活用
- DBを直接たたく場合、どうしてもソートキーや条件に左右される
- Webアプリやマイクロサービスなら、必要に応じてキャッシュ層を用意し、ページング結果をキャッシュする方法もある
Discussion