Re: Rownum

William Jenkins <wcn_at_caesun.msd.ray.com> writes:

> A Web based application which provides the end user a means to retrieve,
> report and manipulate a database. The application provides reports of
> the database via html screens. As the database grows, we can have many
> thousands of entries.

This is a very common problem. There's really only one solution that I've seen that addresses both the UI problem of limiting the result set as well as the performance issue of limiting the data being manipulated in an OLTP environment (and the web is nearly always OLTP).

The solution is that you absolutely must be doing an index range scan and be both ordering and limiting based on that indexed column. Then you can simply use rownum to limit and pass the key value as the parameter for the next page.

It works like this:

SELECT /*+ INDEX(table,idx_people_lastname) */

       lastname, ...
  FROM people
 WHERE _search clauses_
   AND firstname > ?
   AND rownum < ?
 ORDER BY lastname

in this example it's imperative that lastname have an index on it. You have to babysit Oracle with explain plan and make absolutely sure that Oracle is doing an index range scan and not sorting.

For each page you pass 20 or whatever as the last parameter in this query, and for the "next page" button you pass the lastname from the last record of the first page as the second to last parameter.

This still can run into a problem if the user enters a query that matches few records in a large table. Oracle might do an index range scan and end up having to search the entire table, which is fairly inefficient. However for small tables this works well. For large tables you'll need a much more complicated index strategy with bitmapped indexes and domain indexes anyways.

-- 
greg