Proper Pagination design in Oracle using Analytic Functions

A common web development problem is how to prevent slogging the database with full table scans when trying to paginate a web page. I defer the solution to Tom Kyte, re-posting the section "Pagination in Getting Rows N Through M" in this article:
http://www.oracle.com/technology/oramag/oracle/07-jan/o17asktom.html


Pagination in Getting Rows N Through M

The last thing I'll take a look at is performing pagination with analytics. 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 query. In the prior column dealing with ROWNUM and in the "Top-n Query" section above, I demonstrated how to achieve that pagination, but it required two levels of inline views and could be considered rather unwieldy. A syntactically easier way to write that pagination query is


select *
from (
select /*+ first_rows(25) */
your_columns,
row_number()
over (order by something unique)rn
from your_tables )
where rn between :n and :m
order by rn;


For example, suppose you wanted to paginate through the ALL_OBJECTS view, wanted to show 25 rows on a page, and needed to show page 5. The query would be


SQL> variable n number
SQL> variable m number
SQL> exec :n := 101; :m := 125
PL/SQL procedure successfully completed.

SQL> select *
2 from (
3 select /*+ first_rows(25) */
4 object_id,object_name,
5 row_number() over
6 (order by object_id) rn
7 from all_objects)
8 where rn between :n and :m
9 order by rn;

OBJECT_ID OBJECT_NAME RN
--------- ----------- ---
102 I_SEQ1 101
103 I_OBJAUTH1 102
.
.
.
124 I_ACCESS1 123
125 I_TRIGGER1 124
126 I_TRIGGER2 125

25 rows selected.

I chose 25 for the FIRST_ROWS hint because that is my page size and pagination-style queries should be optimized to get the first page as fast as possible. I assign ROW_NUMBER to each row after sorting by OBJECT_ID. Then I use a simple BETWEEN to retrieve the specific rows I need (101 through 125 in this case).

You need to order by something unique for these pagination queries, so that ROW_NUMBER is assigned deterministically to the rows each and every time. Otherwise, you end up with a problem similar to what I pointed out with the salary top-n query. For more details on deterministic queries, go to oracle.com/technology/oramag/oracle/06-sep/o56asktom.html. Also, for complete information on using analytic functions, see download.oracle.com/docs/cd/B19306_01/server.102/b14223/analysis.htm#i1007779 (chapter 21 of the Data Warehousing Guide). In my opinion, analytic functions are the coolest thing to happen to SQL since the SELECT keyword was introduced.