Getting Oracle SQL result set pages

When you want to display the result of a query that returns a large number of rows, it's common to divide the complete result in pages, and display them one page at a time. To offer the user a way of navigating through the pages, there are clickable page numbers or "next"/"previous" buttons at the bottom of the page. This technique is called result set paging.

To implement this efficiently, the paging should also be built into the query. Because it's not efficient when we use a query to return the complete result from the database, and discard rows we don't want to display afterwards.

Let's say that you want to page the following query:

SELECT col1, col2
  FROM example_table
  WHERE col2 = 'some_condition'
  ORDER BY col1 DESC

Before we can divide the result set, we need to number the rows of the result set. The pseudo column "rownum", provided by Oracle databases, numbers the rows of the result. The first row of the result is always 1.

Using this knowledge, we can retrieve the "page" we want using the WHERE clause with the corresponding start row and end row numbers. In the example below, we want to fetch rows from number 201 to 300.

SELECT *
FROM (SELECT r.*, ROWNUM AS row_number
      FROM (SELECT col1, col2
            FROM example_table
            WHERE col2 = 'some_condition'
            ORDER BY col1 DESC) r)
WHERE row_number >= 201 AND row_number <= 300;

The reason why we wrap the original query in another SELECT query, is that the database assigns ROWNUMs before the ordering, which makes our ordering ineffective for paging. To make sure the ROWNUMs are ordered, we retrieve the ROWNUM column in the outer SELECT query. The database will then assign the ROWNUM in the already correctly ordered result set.

This query can be optimized for newer Oracle databases by removing the top outer SELECT clause, while leaving the WHERE clause intact.