Pagination Queries in Oracle: Analytics, Row Limiting Clause

Pagination is the process of dividing query results (typically on a user interface) into discrete pages, where each page contains a smaller/more manageable number of rows. In classic offset pagination, each page shows a fixed count of rows (say N): if N is 20, then the first page shows 1-20 rows, a “Next” navigation moves control to the second page with 21-40 rows, and so on.

This article demonstrates how to write queries to achieve this style of pagination in Oracle.

Two solutions are presented below with examples. The first uses analytic functions and is compatible with pre-12c versions of Oracle. The second, more compact solution uses Oracle’s row limiting clause.

Pagination query — setting the context

Pagination queries need some way to identify the start and end markers for rows to be returned for a page. Let’s say this is done using these variables:

• PAGE_SIZE: Count of rows to be returned by the query per fetch / number of rows to be displayed on a single page.
• PAGE_NUM: Page number for which the result set is required. This determines the starting position from which the ordered rows are to be returned.

  Starting position is calculated as:
  ((PAGE_NUM — 1) * PAGE_SIZE) + 1

  e.g. if PAGE_SIZE = 20:
  For PAGE_NUM = 1, starting position = 1.
  For PAGE_NUM = 2, starting position = 21.
  For PAGE_NUM = 3, starting position = 41.

For the demo, we will define PAGE_SIZE and PAGE_NUM as bind variables…

SQL> -- Count of rows to be returned by the SQL per fetch
SQL> var PAGE_SIZE number

SQL> -- Page number for which the result set is required
SQL> var PAGE_NUM number

…and assign them values:

SQL> exec :PAGE_SIZE := 5

PL/SQL procedure successfully completed.

SQL> exec :PAGE_NUM  := 2

PL/SQL procedure successfully completed.

With PAGE_SIZE = 5 and PAGE_NUM = 2, the query should fetch row numbers 6-10 from the result set.

Let’s see the pagination queries in action.

Pre-12c: Pagination with Analytic Function

The solution performs the following steps to get a specific subset of rows from the query results.

1. Order the result set and apply analytic function ROW_NUMBER to assign a running counter 1..N (say “row_index”) to each row.
2. Use the above result set as an inline view, and pick from it only those rows where row_index lies in the window defined by
   [((PAGE_NUM — 1) * PAGE_SIZE) + 1] and
   [PAGE_NUM * PAGE_SIZE].

The SQL:

-- Pre-12c Pagination SQL with analytic function
-- Bind variables:
-- PAGE_SIZE: Count of rows to be returned = 5
-- PAGE_NUM: Page number = 2
select  object_id, object_name  
from (select  a.object_id, a.object_name,
      row_number() over (order by a.object_id) row_index
      from dba_objects a
      )
where row_index between 
              ((:PAGE_NUM — 1) * :PAGE_SIZE) + 1 
          and (:PAGE_NUM * :PAGE_SIZE);

When run:

SQL> -- Pre-12c Pagination SQL with analytic function
SQL> -- Bind variables:
SQL> -- PAGE_SIZE: Count of rows to be returned = 5
SQL> -- PAGE_NUM: Page number = 2
SQL> select  object_id, object_name
  2  from (select  a.object_id, a.object_name,
  3        row_number() over (order by a.object_id) row_index
  4        from dba_objects a
  5        )
  6  where row_index between
  7                ((:PAGE_NUM - 1) * :PAGE_SIZE) + 1
  8            and (:PAGE_NUM * :PAGE_SIZE);

 OBJECT_ID OBJECT_NAME
---------- ---------------------
        13 UET$
        14 SEG$
        15 UNDO$
        16 TS$
        17 FILE$

Oracle 12c+ provides a more compact way of implementing the same functionality, using what’s called the row limiting clause.

12c: Row Limiting Clause with OFFSET/FETCH

The row limiting clause, with its support for ANSI-standard FETCH FIRST/NEXT and OFFSET clauses, eliminates the need for an inline view.

Syntax:

select [columns]
from [table]
order by [key]
offset [starting point for the FETCH — 1]
fetch next [count of rows to be returned i.e. PAGE_SIZE] rows only;       

Fetch can be specified in terms of percent as well, with percent only in place of rows only.

The SQL:

-- 12c Pagination SQL with OFFSET/FETCH
-- Bind variables:
-- PAGE_SIZE: Count of rows to be returned = 5
-- PAGE_NUM: Page number = 2
select object_id, object_name
from dba_objects 
order by object_id
offset (:PAGE_NUM - 1) * :PAGE_SIZE rows 
fetch next :PAGE_SIZE rows only;	

When run:

SQL> -- 12c Pagination SQL with OFFSET/FETCH
SQL> -- Bind variables:
SQL> -- PAGE_SIZE: Count of rows to be returned = 5
SQL> -- PAGE_NUM: Page number = 2
SQL> select object_id, object_name
  2  from dba_objects
  3  order by object_id
  4  offset (:PAGE_NUM - 1) * :PAGE_SIZE rows
  5  fetch next :PAGE_SIZE rows only;

 OBJECT_ID OBJECT_NAME
---------- ---------------------
        13 UET$
        14 SEG$
        15 UNDO$
        16 TS$
        17 FILE$

Note: With row limiting clause, 12c+ essentially provides a simplified way to represent the same query as you might use in a pre-12c database. Under the surface, Oracle applies query transformation to rewrite the row limiting clause to use analytics.

Summary

This article illustrates two ways of implementing pagination (i.e. rows N through M of a result set) in Oracle:

1. SQL query with filter on analytic function row_number()
2. SQL query with row limiting clause [available Oracle 12c onwards]

For Further Reading

• ORACLE-BASE: Paging Through Data
• 12c First N
• Pre-12c Nth Highest Salary
• Oracle Magazine (Oct 2013): Oracle Database 12c Part 1 Features