| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: paginated resultset for web display
karsten_schmidt8891_at_my-deja.com writes:
> the better way to do it is to open a cursor, fetch 50 rows, and whenever
> you need the next page fetch the next 50 rows from that > cursor. that way
> you retrieve each row only once. if you want to get fancy about
> performance, look at array fetching.
Unfortunately this isn't appropriate for a web application. It forces the database to keep around result sets that may or may not ever be used again. Or it may be fetched days later or the same page may be fetched several times or in arbitrary order. HTTP is inherently a stateless protocol, the client may ask for any piece of data at any time regardless of the context. Keeping state on the server like this is only asking for trouble.
That said this is a bad design anyways, it only works right if lname is indexed in which case the following is much better and more reliable anyways:
select * from emp
where lname > :start
and lname like :name || '%'
and rownum < :stop
order by lname,...
which is called with :start bound to the last name seen on the previous page.
If lname is indexed this should be an index scan which means the rownum will actually dtrt. It also means each access is log(n) so paging through the whole table is nlog(n). It also works even if records are deleted or inserted while the user is paging through, they don't get a consistent view but they don't skip or see duplicate records.
> > Peter Rexer <prexer_at_my-deja.com> writes: > > > > > I'm trying to set up a query to bring back rows 1-50, then 51-100 from > > > a web application. Is there an easy way to have Oracle do this for me, > > > or do I need to bring back the resultset and store it in my web > > > server's cache?
 |
 |