Pagination in Oracle

Pagination is common in Web-based applications. The user inputs a set of criteria, which runs a query and then lets the user click the Previous and Next buttons to page through the result set. To achieve this paging functionality, the application needs to be able to get a certain set of rows from the Database query.

Let’s take a look at the different methods in Oracle to achieve Pagination in the Oracle query

Pre 12c

(1) Using ROWNUM Clause
For pagination in oracle, if you want the 5 -10 records of the Dept order by sales desc then go for this.

SELECT a.*
FROM (SELECT ROWNUM rn, b.*
FROM ( SELECT *
FROM dept
ORDER BY sales dsc) b  where rn <=10) a
WHERE a.rn >= 5

The general syntax would be

select *
from
( select rownum rnum, a.*
from (your_query) a
where rownum <= M )
where rnum >= N;

(2) Using ROW_NUMBER() oracle analytic function: It behaves similarly to the ROWNUM pseudo column but is more flexible and has more capabilities

Here is the query for Pagination

SELECT * FROM (
SELECT
d.*,
row_number() over (ORDER BY d.sales DSC) rn
FROM dept d) WHERE rn BETWEEN 5 AND 10 ORDER BY rn;

The above Top N queries will give return different records when two things are tied for the spot when using the top n queries

(3) Using RANK() and DENSE_RANK(): These are analytic functions which can be used to remove the problem stated above
Here is the query for Pagination using RANK

SELECT *
FROM (SELECT d.*,rank() over (ORDER BY d.sales DSC) rn
FROM dept d
)
WHERE rn BETWEEN 5 AND 10 ORDER BY rn;

Here is the query for Pagination using DENSE_RANK

SELECT *
FROM (SELECT d.*,dense_rank() over (ORDER BY d.sales DSC) rn
FROM dept d
)
WHERE rn BETWEEN 5 AND 10 ORDER BY rn;;

With 12c

(4) Top-N feature:

Oracle Database 12c includes support for the ANSI-standard FETCH FIRST/NEXT and OFFSET clauses—together called the row limiting clause. This clause enables you to easily retrieve the first N records from a result set or, alternatively, the first N records after skipping over a set of records, so you can easily paginate through a result set

Here Pagination can be done with the use of offset syntax

– offset 10 rows fetch first 10 rows only

select * from my_test order by id
offset 10 rows fetch next 10 rows only;

– offset 10 rows fetch first 0.1 per cent rows only

select * from my_test order by id offset 10 rows first 0.1 percent rows only;

– offset 10 rows fetch first 3 rows with ties. It means all the top rows with ties will also be included in the result

select * from my_test order by name fetch first 3 rows with ties;

If you check the optimizer plan of the above query, you will find the optimizer is using the rank() function as demonstrated above in the Pre 12c case

Restriction
(1)If you have a SELECT statement with FOR UPDATE, you can’t use it.
(2)The SELECT statement can’t CURRVAL or NEXTVAL of sequences
(3) If the query of the Materialized Views has this clause, then you can’t do an incremental refresh of that Materialized View

Hope you like the article on Pagination in oracle query. Please do provide feedback

Also Reads
Lead Function in Oracle
RANK function in Oracle
Rownum in Oracle
Top-N Queries in Oracle
https://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqljoffsetfetch.html