Zenn
🖥️

Oracleでのページングとソート

2025/02/13に公開
SQL
sort
Oracle
pagination
tech

Oracleでのページングとソート

  • Oracle19c(CEE エディション含む)で膨大なデータを効率的にページングしつつソートする実装例。
  • 一般的にOFFSET ... FETCH ...は読み飛ばしが発生し非効率といわれるが、Oracleは最適化を行い、適切なインデックスが存在すれば実質的に早期打ち切り(STOPKEY)をしてくれるケースがある。
  • 標準SQLのウィンドウ関数ROW_NUMBER()を使う方法も同様に最適化(WINDOW NOSORT STOPKEY)が効く場合がある。
  • 以下では、ソフトウェア商社の商材データを例に、ページングとソートを行う実装をまとめる。

目次

前提と要件

  • 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を使う場合も同様に確認し、最適化されているかをチェック

注意点とベストプラクティス

  1. 動的ソートキー・動的クエリの安全性

    • SQLインジェクションを避けるため、ORDER BYなどを動的に組み立てる際は必ずホワイトリストやパラメータバインドを考慮
    • OracleではORDER BY :bindは使えないので、アプリケーションで安全にカラム名を決定して文字列埋め込みする方法が必要

  2. 大量データに対する検証

    • 実際のデータ量と統計情報が増えた段階で必ず EXPLAIN PLAN / AUTOTRACEを実行
    • 大規模ならパーティショニング、マテリアライズドビュー、インメモリオプションなども検討

  3. バージョン差異への配慮

    • Oracle11g以前ではOFFSET FETCHが使えず、ROW_NUMBER (サブクエリ)方式がメイン
    • 12c以降(19c含む)であればどちらの方式も選択可能

  4. レスポンス速度を保証するなら API レイヤーでキャッシュや別ストアを活用

    • DBを直接たたく場合、どうしてもソートキーや条件に左右される
    • Webアプリやマイクロサービスなら、必要に応じてキャッシュ層を用意し、ページング結果をキャッシュする方法もある

参考情報

Discussion