Pagination

This topic describes how to perform pagination in OceanBase Database in Oracle mode.

Prerequisites

• You have connected to an Oracle mode tenant of OceanBase Database. For more information, see Overview of connection methods.
• You have the SELECT privilege. For more information about how to view the privileges of the current user, see View user privileges. If you do not have the privilege, contact the administrator to grant you the privilege. For more information, see Directly grant privileges.

Overview

In Oracle mode of OceanBase Database, you can use the Row_Limiting_Clause clause to implement pagination. The Row_Limiting_Clause clause is used to limit the number of rows returned by a SELECT query. It is commonly used for pagination.

The syntax of the Row_Limiting_Clause clause is as follows:

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

Parameters:

Create a test table and add test data

1. Create a table named 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. Insert test data into the employee_info table.

   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');
   

Use ORDER BY for pagination

Pagination queries must use the ORDER BY clause. If you do not use the ORDER BY clause, the database cannot guarantee the order of the query results, which may lead to the following issues:

• Different query results returned by the same query at different times
• Data duplication or omission in pagination queries
• Unstable pagination results, affecting the user experience

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

Use a unique field for sorting

We recommend that you use a unique field, such as a primary key, for sorting to ensure the uniqueness and stability of the pagination results.

In a parallel execution scenario, if the field used in the ORDER BY clause is not unique, the order of rows with the same sort value is not guaranteed, which may lead to data duplication or omission in pagination queries. For example, if you sort by department_id, the relative order of employees in the same department is arbitrary.

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;

Use FETCH to limit the number of rows returned

Query the first three employee records.

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

Use OFFSET to skip a specified number of rows

Query all employee records starting from the fourth row.

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

Use OFFSET and FETCH for pagination

You can use OFFSET and FETCH to implement pagination queries.

Query the second page of data (3 rows per page):

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

Use percentages for pagination

In addition to using a fixed number of rows for pagination, you can also use the PERCENT keyword to return data based on a percentage.

Query the first 30% of the data

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

Use WITH TIES to include rows with ties

The WITH TIES option returns the specified number of rows and includes all rows with ties if the last row has ties.

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

Use ROWNUM for pagination

In Oracle mode, you can use the ROWNUM pseudo-column for pagination queries, but this method is relatively complex and requires the use of subqueries. We recommend using the Row_Limiting_Clause for pagination queries, as it has a simpler and clearer syntax.

Query the first N rows using ROWNUM

Query the information of the first 3 employees.

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

Use ROWNUM for pagination

To use ROWNUM for pagination, you need to use subqueries. Query the data on page 2 (3 records per page).

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;

References

• For more information about the SELECT syntax, see SIMPLE SELECT.

• For more information about single-table queries, see Single-table query.

• For more information about query optimization, see Overview of query rewriting.