Re: paginated resultset for web display

Hi folks,

 This is probably not what you want -
 it is certainly inefficient and not scalable.  the cost of getting a row is in a n-square relation to the position of  that row in the resultset.

 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.

 this would'nt give you random access though, only sequential.

 you might consider getting all rows in one shot and generate separate pages on the web-server if you need random access.

Karsten

In article <87670jwzir.fsf_at_HSE-Montreal-ppp19508.qc.sympatico.ca>,   Greg Stark <greg-spare-1_at_mit.edu> wrote:
>
> select * from
> (select rownum n,fname,... from
> emp
> where lname like :name || '%'
> and rownum <= :stop
> order by <<something>>
> ) where n > :start
>
> Always use bind parameters where possible.
>
> Depending on the range you're looking for this may be inefficent; I'm
not sure
> if Oracle needs to build a temporary table for this type of query. If
so and
> it's possible for there to be thousands of records and for the user
to jump to
> arbitrary pages then this may be too memory and temporary tablespace
intensive
> to be feasible.
>
> 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?
> >
> > Ideally I'm looking for a way to do a query something like this:
> >
> > Select fname, lname from emp where lname like 'Smith%'
> > and rownum >50
> > and rownum <101
> >
> >
> > Peter Rexer
> >
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
>
> --
> greg
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Oct 08 1999 - 07:12:09 CDT