Implement search paging queries
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.
Scott Stephens worked for Oracle for more than 13 years in technical support, e-commerce, marketing, and software development.