Best practice for pagination in Oracle?

This may help:

   SELECT * FROM 
     ( SELECT deptno, ename, sal, ROW_NUMBER() OVER (ORDER BY ename) Row_Num FROM emp)
     WHERE Row_Num BETWEEN 5 and 10;

A clean way to organize your SQL code could be trough WITH statement.

The reduced version implements also total number of results and total pages count.

For example

WITH SELECTION AS (
    SELECT FIELDA, FIELDB, FIELDC FROM TABLE), 
NUMBERED AS (
    SELECT 
    ROW_NUMBER() OVER (ORDER BY FIELDA) RN, 
    SELECTION.*
    FROM SELECTION)
SELECT
    (SELECT COUNT(*) FROM NUMBERED) TOTAL_ROWS,
    NUMBERED.*
FROM NUMBERED
WHERE 
    RN BETWEEN ((:page_size*:page_number)-:page_size+1) AND (:page_size*:page_number)

This code gives you a paged resultset with two more fields:

  • TOTAL_ROWS with the total rows of your full SELECTION
  • RN the row number of the record

It requires 2 parameter: :page_size and :page_number to slice your SELECTION

Reduced Version

Selection implements already ROW_NUMBER() field

WITH SELECTION AS (
    SELECT 
        ROW_NUMBER() OVER (ORDER BY FIELDA) RN,
        FIELDA, 
        FIELDB, 
        FIELDC 
    FROM TABLE) 
SELECT
    :page_number PAGE_NUMBER,
    CEIL((SELECT COUNT(*) FROM SELECTION ) / :page_size) TOTAL_PAGES,
    :page_size PAGE_SIZE,
    (SELECT COUNT(*) FROM SELECTION ) TOTAL_ROWS,
    SELECTION.*
FROM SELECTION 
WHERE 
    RN BETWEEN ((:page_size*:page_number)-:page_size+1) AND (:page_size*:page_number)

If you're already using analytics (ROW_NUMBER() OVER ...) then adding another analytic function on the same partitioning will add a negligible cost to the query.

On the other hand, there are many other ways to do pagination, one of them using rownum:

SELECT * 
  FROM (SELECT A.*, rownum rn
          FROM (SELECT *
                  FROM your_table
                 ORDER BY col) A
         WHERE rownum <= :Y)
 WHERE rn >= :X

This method will be superior if you have an appropriate index on the ordering column. In this case, it might be more efficient to use two queries (one for the total number of rows, one for the result).

Both methods are appropriate but in general if you want both the number of rows and a pagination set then using analytics is more efficient because you only query the rows once.


In Oracle 12C you can use limit LIMIT and OFFSET for the pagination.

Example - Suppose you have Table tab from which data needs to be fetched on the basis of DATE datatype column dt in descending order using pagination.

page_size:=5

select * from tab
order by dt desc
OFFSET nvl(page_no-1,1)*page_size ROWS FETCH NEXT page_size ROWS ONLY;

Explanation:

page_no=1 page_size=5

OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY - Fetch 1st 5 rows only

page_no=2 page_size=5

OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY - Fetch next 5 rows

and so on.

Refrence Pages -

https://dba-presents.com/index.php/databases/oracle/31-new-pagination-method-in-oracle-12c-offset-fetch

https://oracle-base.com/articles/12c/row-limiting-clause-for-top-n-queries-12cr1#paging

Tags:

Oracle

Pagination

Recent Posts