📊

Oracleページング完全ガイド：1000万件実測で判明したOFFSET劣化とキーセット方式

2025/02/13に公開

はじめに

Oracle Databaseで大規模データをページングする際、OFFSET ... FETCH ...構文は本当に高速なのでしょうか？

本記事では、Oracle 19cに1000万件のデータを投入して実測した結果をもとに、以下を明らかにします。

OFFSET FETCH方式は100万件を超えると速度劣化する（500万件で7.17秒）
ROW_NUMBER方式も同様に劣化する（500万件で5.76秒）
キーセットページネーション(Seek Method)なら一貫して高速（500万件でも0.38秒、95%高速化）
Oracle Result Cacheで頻繁にアクセスされるページを劇的に高速化できる

すべての検証コードはGitHubで公開しており、誰でも再現可能です。

検証環境

データベース: Oracle Database 19c Enterprise Edition
データ件数: 10,000,000件
テーブル: ソフトウェア商材を扱う商社向けデータ（TRADING_SOFTWARE）
インデックス: PRICE DESC, SOFTWARE_ID DESCの複合インデックス
計測ツール: PL/SQL + DBMS_UTILITY.GET_TIME

テーブル定義例

CREATE TABLE 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 TRADING_SOFTWARE VALUES (1, 'ERP Package',       'VendorA',  500000, 10, SYSDATE);
INSERT INTO TRADING_SOFTWARE VALUES (2, 'CRM Suite',         'VendorB',  300000,  5, SYSDATE);
INSERT INTO TRADING_SOFTWARE VALUES (3, 'Security App',      'VendorC',  800000,  2, SYSDATE);
INSERT INTO TRADING_SOFTWARE VALUES (4, 'Accounting Tool',   'VendorA',  200000,  8, SYSDATE);
INSERT INTO TRADING_SOFTWARE VALUES (5, 'Analytics Platform','VendorB',  900000,  1, SYSDATE);
INSERT INTO TRADING_SOFTWARE VALUES (6, 'Mobile Dev Kit',    'VendorC',  250000, 15, SYSDATE);
INSERT INTO TRADING_SOFTWARE VALUES (7, 'Test Automation',   'VendorA',  150000, 20, SYSDATE);
INSERT INTO TRADING_SOFTWARE VALUES (8, 'DevOps Suite',      'VendorB',  400000, 12, SYSDATE);
INSERT INTO TRADING_SOFTWARE VALUES (9, 'Legacy Migration',  'VendorA',  650000,  3, SYSDATE);
INSERT INTO TRADING_SOFTWARE VALUES (10,'E-commerce Platform','VendorC',1000000,  4, SYSDATE);

COMMIT;

OFFSET FETCH 方式

基本構文

SELECT
    t.*
FROM TRADING_SOFTWARE t
ORDER BY t.PRICE DESC
OFFSET :offset ROWS FETCH NEXT :limit ROWS ONLY;

注意点と性能特性
- 小規模OFFSET(10万件以下): 高速に動作し実用的(0.4秒～0.7秒)
- 大規模OFFSET(100万件以上): 速度劣化が発生(100万件で1.66秒、500万件で7.17秒)
- Oracleの最適化によりSTOPKEYが使われるが、完全に読み飛ばしを防ぐわけではない
- 必ず実行計画を確認し、大規模ページングではキーセット方式を検討すること

ROW_NUMBER 方式

基本構文

SELECT *
FROM (
    SELECT
        t.*,
        ROW_NUMBER() OVER (ORDER BY t.PRICE DESC) AS rn
    FROM 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を利用して早期打ち切りを行う
- OFFSET FETCHより若干高速だが、大規模ページングでは同様に速度劣化が発生
- 標準SQLであり、MySQLなど他DBでも記述は可能 (最適化されるかはDBエンジン次第)

インデックス設計と最適化

ページングクエリのパフォーマンスは、適切なインデックスの有無に大きく依存します。

ソートキーに対する複合インデックス

-- PRICE でソートする場合
CREATE INDEX IDX_TRADING_SOFTWARE_PRICE
  ON TRADING_SOFTWARE (PRICE DESC, SOFTWARE_ID DESC);

-- REGISTERED_AT でソートする場合
CREATE INDEX IDX_TRADING_SOFTWARE_DATE_ID
  ON TRADING_SOFTWARE (REGISTERED_AT DESC, SOFTWARE_ID DESC);

インデックス設計のポイント
- ソートキーを第1カラムに配置
- 一意性を保証するため主キーを第2カラムに追加
- Oracleは物理的な降順インデックスを持たないが、DESC指定でオプティマイザが最適化しやすくなる
- インデックスによりSTOPKEY(pipelined top-N)が働けば、大量データからでも高速に必要件数だけ取得可能

実行計画の確認

各方式の実行計画を確認し、最適化が効いているかをチェックします。

OFFSET FETCH方式の実行計画

EXPLAIN PLAN FOR
SELECT t.*
FROM TRADING_SOFTWARE t
ORDER BY t.PRICE DESC
OFFSET 100 ROWS FETCH NEXT 20 ROWS ONLY;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

ROW_NUMBER方式の実行計画

EXPLAIN PLAN FOR
SELECT *
FROM (
    SELECT
        t.*,
        ROW_NUMBER() OVER (ORDER BY t.PRICE DESC) AS rn
    FROM TRADING_SOFTWARE t
) tmp
WHERE tmp.rn BETWEEN 101 AND 120;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

キーセットページネーション方式の実行計画

EXPLAIN PLAN FOR
SELECT t.*
FROM TRADING_SOFTWARE t
WHERE (t.PRICE < :last_price) 
   OR (t.PRICE = :last_price AND t.SOFTWARE_ID < :last_id)
ORDER BY t.PRICE DESC, t.SOFTWARE_ID DESC
FETCH FIRST 20 ROWS ONLY;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

確認ポイント
- WINDOW NOSORT STOPKEY: ウィンドウ関数での早期打ち切り
- INDEX FULL SCAN DESCENDING または INDEX RANGE SCAN: インデックスを利用したアクセス
- キーセット方式ではINDEX RANGE SCANが使用され、WHERE句による絞り込みが効率的に行われる

キーセットページネーション (Seek Method)

大規模ページング(100万件以上)で速度劣化を避けるための手法です。

基本構文

-- 初回ページ取得
SELECT
    t.*
FROM TRADING_SOFTWARE t
ORDER BY t.PRICE DESC, t.SOFTWARE_ID DESC
FETCH FIRST 20 ROWS ONLY;

-- 2ページ目以降 (前ページの最後の値を使用)
SELECT
    t.*
FROM TRADING_SOFTWARE t
WHERE (t.PRICE < :last_price) 
   OR (t.PRICE = :last_price AND t.SOFTWARE_ID < :last_id)
ORDER BY t.PRICE DESC, t.SOFTWARE_ID DESC
FETCH FIRST 20 ROWS ONLY;

注意点と性能特性
- WHERE句で範囲を絞り込むため、インデックスを効率的に利用
- 大規模OFFSETでも一貫して高速 (0.06秒～0.38秒)
- 不要な読み飛ばしが発生せず、データ量に依存しないパフォーマンス
- リアルタイムデータ更新に強い(ページング中の挿入/削除の影響が少ない)
制約事項
- 任意ページへのジャンプができない(順次ページングのみ)
- 前ページの最後の値(カーソル)を保持する必要がある
- ソートキーが一意でない場合は主キーを組み合わせる必要がある
- アプリケーション側でカーソル管理のロジックが必要
実装のポイント
- 複合ソートキーの場合は、OR条件で正しく連結する
- インデックスはソートキーと主キーの複合インデックスを作成
- クライアントに最後の値(price, id)を返し、次回リクエストで受け取る

方式別の性能比較（実測結果）

Oracle 19cで1000万件のデータを使用して計測した結果です。

位置	OFFSET FETCH	ROW_NUMBER	Keyset	改善率
0～20件目	0.86秒	0.87秒	0.00秒	即座
100件目～	0.43秒	0.45秒	0.00秒	即座
1万件目～	0.49秒	0.48秒	0.00秒	即座
10万件目～	0.66秒	0.64秒	0.00秒	即座
100万件目～	1.66秒	1.58秒	0.06秒	96%高速化
500万件目～	7.17秒	5.76秒	0.38秒	95%高速化

結論

小規模ページング(10万件以下): すべての方式で実用的（0.4～0.9秒）
大規模ページング(100万件以上): キーセット方式のみ実用的（OFFSET方式は速度劣化が顕著）
実行計画: すべての方式でWINDOW NOSORT STOPKEYが使用されるが、キーセット方式のみWHERE句による範囲絞り込みが効く

Oracle Result Cacheによる高速化

頻繁にアクセスされるページングクエリに対しては、Oracle Database 11g以降で提供されるResult Cache機能を活用できます。

Result Cacheの基本

Result Cacheは、SELECT文の結果をSGA(System Global Area)内にキャッシュし、同じクエリが実行された際に物理I/Oをスキップして高速に結果を返します。

使用方法

SELECT /*+ RESULT_CACHE */ 
    t.*
FROM TRADING_SOFTWARE t
ORDER BY t.PRICE DESC
OFFSET :offset ROWS FETCH NEXT 20 ROWS ONLY;

Result Cacheの特徴
- データベースレベルでのキャッシュのため、アプリケーション側の実装不要
- 元表のデータが更新されると自動的にキャッシュが無効化される
- 複数セッション間でキャッシュを共有できる
- 外部キャッシュ(Redis等)と異なり、データ整合性が自動的に保証される
適用シーン
- 初期ページ(OFFSET 0)など頻繁にアクセスされるクエリ
- 更新頻度が低く、参照が多いマスターデータのページング
- 複雑な集計を伴うページングクエリ
設定確認と調整

-- Result Cache有効化確認
SHOW PARAMETER result_cache_mode;

-- Result Cacheサイズ確認
SHOW PARAMETER result_cache_max_size;

-- Result Cache統計確認
SELECT * FROM V$RESULT_CACHE_STATISTICS;

Result Cache使用時の注意点

キャッシュの有効期限
- データ更新が発生すると即座に無効化されるため、更新頻度の高いテーブルには不向き
- 参照専用またはバッチ更新のテーブルに最適
メモリ使用量
- SGAのサイズを圧迫する可能性があるため、result_cache_max_sizeを適切に設定
- 大量のキャッシュが必要な場合はインメモリオプションの検討も
バインド変数との相性
- OFFSET値が異なると別クエリとして扱われるため、キャッシュヒット率が下がる
- 初期ページなど特定のOFFSET値に限定して使用するのが効果的

ベストプラクティスまとめ

1. ページング方式の選択フローチャート

データ件数とアクセスパターンを確認
    ↓
10万件以下、または初期ページのみアクセス
    → OFFSET FETCH + Result Cache
    
頻繁にアクセス、更新頻度低い
    → ROW_NUMBER + Result Cache
    
100万件以上、深いページまでアクセス
    → キーセットページネーション（必須）
    
任意ページジャンプが必須
    → ページ範囲を制限 + OFFSET FETCH

2. セキュリティ（動的ソート）

ORDER BYを動的に組み立てる際は必ずホワイトリスト検証
OracleではORDER BY :bindが使えないため、カラム名の文字列埋め込みが必要
SQLインジェクション対策として、許可されたカラム名のみを受け入れる

-- NG: SQLインジェクションリスク
ORDER BY ${user_input}

-- OK: ホワイトリスト検証
IF sort_column IN ('PRICE', 'REGISTERED_AT', 'SOFTWARE_ID') THEN
    ORDER BY sort_column DESC
END IF

3. 本番環境での検証

実際のデータ量で必ずEXPLAIN PLANを実行し、STOPKEYが効いているか確認
統計情報を最新化（DBMS_STATS.GATHER_TABLE_STATS）
大規模データならパーティショニング、マテリアライズドビュー、インメモリオプションも検討

4. Oracleバージョン対応

バージョン	推奨方式
Oracle 11g以前	ROW_NUMBER方式（OFFSET FETCH未対応）
Oracle 12c以降	OFFSET FETCH、ROW_NUMBER、Keyset すべて利用可能
Oracle 19c	本記事の検証環境（Result Cache含めすべての機能が利用可能）

検証の再現性

本記事のすべての性能データは、実際の環境で計測した結果です。再現性を担保するため、検証に使用したすべてのコードを公開しています。

GitHubリポジトリ

OraclePaging

以下が含まれます

DDL: テーブル・インデックス作成スクリプト
データ投入: 1000万件のランダムデータ生成スクリプト
パフォーマンステスト: 3方式（OFFSET FETCH、ROW_NUMBER、Keyset）の実測スクリプト
Result Cacheテスト: キャッシュ効果の実測スクリプト
実行計画確認: EXPLAIN PLANによる最適化確認スクリプト
結果サマリー: パフォーマンス比較表とレポート

実行方法

git clone https://github.com/okamyuji/OraclePaging.git
cd OraclePaging
cp env.example .env
# .envファイルにOracle接続情報を設定
./run_all_tests.sh

すべてのテストが自動実行され、結果が表示されます。