Implementing search paging queries in Oracle

One of the problems developers often encounter when building a custom search engine is to implement some sort of paging functionality, that is, to allow a user to submit a query that returns a lot of row data, but only the first 20. When a user clicks on a link, the next 20 or 20 data is removed from the database application.

One problem with database access is that requests from the Web site are stateless. The efficiency of having a database maintain a cursor is very low while waiting for the next set of data to be requested by the user. Writing code for HTML makes it possible for a cursor to be located on the client, but a cursor is a limited resource, so it is best to close the cursor every time the page is loaded.

An Oracle cursor does not support moving backwards through a rowset (rowset), but the user always backs up in the browser or requests a rowset in a sequential order. Clearly, returning a limited row of data is the responsibility of the database server.

The Pseudo-column (Pseudocolumn) rownum contains the current line number. The first time a lot of people try to return a subset of the records in the middle of a table, they find that the following approach doesn't work:

SELECT * from All_objects where rownum between and 49;

This does not work because the rownum column is applied to the record only when the record is removed or filtered. The first line is thrown when it is taken out, because its rownum is 1. The next line is then fetched, and it is thrown, because it is the new "1", and so on, until all rows are used. This query does not return any records. The workaround is to have a record of 1 to 30 rows before you see records from 30 to 50:

select * from all_objects where rownum <= 49;

You can then use it as a subquery and filter out all the records before the start point (note that I must provide an alias for "rownum" to compile):

select * from (select rownumr,all_objects.* from all_objects where rownum <=
49) t
where t.r >= 30;

To ensure its efficiency, you might want to use binding variables for this restriction. This will use all such requests literally, eliminating the reparse of queries each time a different scope is requested:

select * from (select rownumr,all_objects.* from all_objects where rownum <=
:min) t
where t.r >= :max;

If you use a language that returns a rowset through a stored procedure, the database automatically processes the binding variables internally. The application code then receives only the records it requests, without having to decide which records to receive.

create or replace procedure search(p_mininteger,p_maxinteger,p_rowset out
sys_refcursor)
is
begin
select cursor(*) into p_rowset
from (select rownumr,all_objects.* from all_objects where rownum <=
p_max) t
where t.r >= p_min;
end search;
/
show errors

Note that the rownum is calculated after the sort, so a new record order is obtained by using the orders by clause. However, the rule-based optimizer rownum to "short-circuit" the query and returns records to the next part of the query when the ROWNUM clause is satisfied.