Using ROWNUM to paginate the data in Oracle

In one of my previous posts, I showed you how to use ROWNUM with ORDER BY clause. Building on that in this post I will show you how you can paginate your results using ROWNUM in Oracle DB and how you should not paginate your data.

Suppose I have a table with the following structure:

 CREATE TABLE app_user(
  username varchar2(255) PRIMARY KEY,
  first_name VARCHAR2(255),
  last_name VARCHAR2(255),
  password VARCHAR2(255),
  created_on DATE DEFAULT SYSDATE NOT NULL 
);

You can find the INSERT statements to populate some data to this table in the gist here.

Pagination

We will use ROWNUM to help us in paginating the data. ROWNUM is used to indicate the position number of the row among all the rows. So when we say we want the first 10 records it would mean we want the records with ROWNUM <=10 and ROWNUM >= 1. (Remember that ROWNUM starts with 1). The next 10 records would be ROWNUM <= 20 AND ROWNUM >= 11 and so on. Translating this to a SQL query is as shown below:

SELECT b.* FROM (
    SELECT a.*, ROWNUM row_no FROM app_user a
    WHERE ROWNUM <= 20
) b WHERE b.row_no >= 11;

The above code retrieves the 2nd 10 user records. The query can be parameterized to retrieve any page of data.

Generally, we take page no as the input and page size is fixed to some value say 10. In such a case the upper and lower limits for SQL query can be calculated as:

int lowerLimit = (pageNo - 1 ) * pageSize + 1;
int higherLimit = pageNo * pageSize;

Pagination – Wrong Way

There is another way I can achieve the same paginated results by using the below query:

SELECT b.* FROM (
    SELECT a.*, ROWNUM row_no FROM app_user a
) b WHERE b.row_no >= 11 AND b.row_no <= 20;

The results will still be the same and you will not find the difference until you have multiple joins and with large data you will end up with a very slowwww query. The reason for this is the execution plan generated in both the scenarios is entirely different.

Execution plan – correct pagination

In the above execution plan, the DB engine has identified that we want to paginate and hence accordingly it has taken some action and the cardinality is shown as 20.

Execution plan – wrong pagination

In the above execution plan of the wrong pagination we see that even though we are filtering the records by ROWNUM but in reality it hasn’t identified that we want to paginate through the records and hence I didn’t take any action and the cardinality is shown as 99 which is the count of all the records in the table.

This will be a significant cost when we have multiple JOINS and each JOIN bringing in lots of data.