TechRepublic’s Oracle newsletter covers automating Oracle utilities, generating database alerts, solving directed graph problems, and more. Automatically subscribe today!

A common problem developers face
when building custom search engines is to implement some kind of paging
functionality; that is, to allow a user to issue a query that returns a large
number of rows but only show the first 20 or so matches. These are displayed
until the user clicks on a link to request the next 20, or a previous set of 20
records from a database application.

The problem that arises with database access is that
requests from a Web site are stateless. It’s very inefficient for the database
to keep a cursor available while waiting for the user to request the next set
of hits. It’s possible to code an HTML page where the cursor lives on the
client side, but cursors are a limited resource, so it’s better to close them
once the page is finished loading.

Oracle cursors don’t support moving backward through a rowset; and it’s always possible for a user to back up in
the browser or request a rowset that is out of
sequence. Clearly, it’s the database server’s responsibility to return the
limited set of rows.

The pseudocolumn that contains the
current row number is ROWNUM. Many first attempts at returning a subset of
records from somewhere in the middle of the table don’t
work:

select * from all_objects where rownum between 30 and 49;

This fails because the ROWNUM column is applied to rows only
as they’re being fetched and filtered. When the first row is fetched, it’s
thrown out because its ROWNUM is 1. Then, the next row is fetched; it’s also
thrown out because it’s the new “1” and so on, until all the rows are used.
This query will never return any rows. The solution is that you always have to
fetch rows 1 through 30 before you can even see 30 to 50:

select * from all_objects
where rownum <= 49;

Then, you can use it as a subquery
and filter out all the rows before your start point (notice that I had to provide an alias for “rownum” in order for it to
compile). For an example, view
Listing A.

To ensure that it’s efficient, try to use bind variables for
the limits. This will make all such requests textually the same, which eliminates reparsing the query each time a different range is requested. For an example, view
Listing B.

If you’re working in a language that returns rowsets through stored procedures, the database will
automatically handle the bind variables for you internally. Then, the
application code will receive only the records it requested and not have to decide which rows to retrieve. For an example, view
Listing C.

Note that ROWNUM is calculated after sorting, so using an
ORDER BY clause will still work on the new order of rows. However, ROWNUM is
used by the rule-based optimizer to “short-circuit” queries and return rows to
the next part of the query as soon as the ROWNUM clause is satisfied.