ページネーションクエリ

本記事では、OceanBaseデータベースのOracleモードでページネーションクエリを実行する方法について説明します。

前提条件

• OceanBaseデータベースのOracleモードテナントに接続しています。データベースへの接続操作の詳細については、接続方法の概要を参照してください。
• SELECT権限を持っています。現在のユーザー権限を確認する操作の詳細については、ユーザー権限の確認を参照してください。この権限がない場合は、管理者に連絡し権限を付与してもらってください。ユーザー権限に関する操作の詳細については、直接権限付与を参照してください。

概要

OceanBaseデータベースのOracleモードでは、Row_Limiting_Clauseを使用してページネーションクエリを実現できます。Row_Limiting_ClauseはSELECTクエリが返す行数を制限するために使用され、ページネーション操作で一般的です。

Row_Limiting_Clauseの構文は以下のとおりです：

[ OFFSET offset { ROW | ROWS } ]
[ FETCH { FIRST | NEXT } [ { rowcount | percent PERCENT } ]
    { ROW | ROWS } { ONLY | WITH TIES } ]

パラメータ説明：

テストテーブルの作成とテストデータの追加

1. テーブル employee_info を作成します。

   CREATE TABLE employee_info(
       employee_id NUMBER(10,0),
       employee_name VARCHAR2(50),
       department_id NUMBER(10,0),
       salary NUMBER(10,2),
       hire_date DATE,
       CONSTRAINT pk_employee PRIMARY KEY(employee_id)
   );
   

2. テーブル employee_info にテストデータを挿入します。

   INSERT INTO employee_info VALUES
   (1001,'Smith',10,8000.00,DATE'2020-01-15'),
   (1002,'Allen',20,9500.00,DATE'2019-03-20'),
   (1003,'Jones',10,12000.00,DATE'2018-06-10'),
   (1004,'Blake',30,7500.00,DATE'2021-02-28'),
   (1005,'Scott',20,11000.00,DATE'2019-11-05'),
   (1006,'Ford',10,9000.00,DATE'2020-08-12'),
   (1007,'King',30,8500.00,DATE'2020-05-18'),
   (1008,'Turner',20,10000.00,DATE'2019-09-25'),
   (1009,'Adams',10,10500.00,DATE'2020-12-01'),
   (1010,'Miller',30,9200.00,DATE'2021-01-10');
   

ORDER BY を使用したページネーション

ページネーションクエリでは、ORDER BY 句の使用が必須です。ORDER BY を使用しない場合、データベースはクエリ結果の順序を保証せず、以下の問題が発生する可能性があります：

• 同一のクエリを異なる時間に実行しても、結果の順序が異なる
• ページネーションクエリでデータの重複や欠落が発生する
• ページネーション結果が不安定となり、ユーザーエクスペリエンスに影響を与える

SELECT employee_id, employee_name, department_id, salary 
FROM employee_info 
ORDER BY employee_id 
OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY;

一意のフィールドを使用したソート

ページネーション結果の一意性と安定性を確保するため、一意のフィールド（主キーなど）を使用したソートを推奨します。

並列実行シナリオ では、ORDER BY で使用されるフィールドが一意でない場合、ソート値が同じ行の順序は不確定となり、ページネーションクエリでデータの重複や欠落が発生する可能性があります。例えば、department_id でソートする場合、複数の従業員が同一の部門に所属するとき、これらの従業員の相対的な順序は任意となります。

SELECT employee_id, employee_name, department_id, salary 
FROM employee_info 
ORDER BY department_id, employee_id 
OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY;

FETCH を使用して返される行数を制限する

最初の3件の従業員情報を照会します。

SELECT employee_id, employee_name, department_id, salary 
FROM employee_info 
ORDER BY employee_id 
FETCH FIRST 3 ROWS ONLY;

OFFSET を使用して指定された行数をスキップする

4行目からのすべての従業員情報を照会します。

SELECT employee_id, employee_name, department_id, salary 
FROM employee_info 
ORDER BY employee_id 
OFFSET 3 ROWS;

OFFSET と FETCH を使用したページネーションの実装

OFFSET と FETCH を使用してページネーションクエリを実装できます。

2ページ目のデータ（1ページ3件）を照会します：

SELECT employee_id, employee_name, department_id, salary 
FROM employee_info 
ORDER BY employee_id 
OFFSET 3 ROWS FETCH NEXT 3 ROWS ONLY;

パーセンテージでのページネーションの使用

固定行数によるページネーションのほかに、PERCENT キーワードを使用してデータをパーセンテージで返すこともできます。

最初の30%のデータを照会する

SELECT employee_id, employee_name, department_id, salary 
FROM employee_info 
ORDER BY salary 
FETCH FIRST 30 PERCENT ROWS ONLY;

WITH TIESを使用して並列行を含める

WITH TIES オプションは、指定された行数を返すと同時に、最終行に並列値がある場合は、すべての並列行を含みます。

SELECT employee_id, employee_name, department_id, salary 
FROM employee_info 
ORDER BY salary 
FETCH FIRST 3 ROWS WITH TIES;

ROWNUMを使用したページネーション

Oracleモードでは、ROWNUM 仮想列を使用してページネーションクエリを実行することもできますが、この方法は比較的複雑で、サブクエリと組み合わせて使用する必要があります。ページネーションクエリには、構文がより簡潔で明確な Row_Limiting_Clause の使用を推奨します。

ROWNUMを使用して最初のN件のデータを照会する

最初の3件の従業員情報を照会します。

SELECT employee_id, employee_name, department_id, salary 
FROM employee_info 
WHERE ROWNUM <= 3 
ORDER BY employee_id;

ROWNUMを使用したページネーションクエリの実装

ROWNUM を使用したページネーションクエリの実装にはサブクエリが必要です。2ページ目（各ページ3件）のデータを照会します。

SELECT * FROM (
    SELECT employee_id, employee_name, department_id, salary, ROWNUM rn 
    FROM (
        SELECT employee_id, employee_name, department_id, salary 
        FROM employee_info 
        ORDER BY employee_id
    ) 
    WHERE ROWNUM <= 6
) 
WHERE rn > 3;

関連ドキュメント

• SELECT 構文の詳細については、SIMPLE SELECTを参照してください。

• 単一テーブルクエリの詳細については、単一テーブルクエリを参照してください。

• クエリの最適化の詳細については、クエリのリライトの概要を参照してください。