Re: Navigating a Very Large Result Set

If you are using something like a web page - so it is stateless - then you do use rownum, but in a a tricky fashion. select * from
(select rownum rnum, * from mytable where ... order by a, b) where

    rnum>10 and rnum<50

also see asktom.oracle.com

Jim
"Stuart Christopherson" <heracles_at_nospam.com.au> wrote in message news:1AyY8.2916$Cq.123048_at_ozemail.com.au...
> G'day,
>
> Quick question:
>
> I am writing an application that allows the user to navigate through a set
> of search results. It's possible the list of results could be very large
> (1000's) so I want to allow the user to display 50 results at any one
time,
> rather than attempt to bring them all back at once. Basically I want to
> query the results table with the following SQL:
>
> SELECT * FROM my_results_table
> WHERE id = X
>
> and use a start and end value to handle the starting row and ending row of
> the fetch cursor.
>
> I though the pseudocolumn ROWNUM might be the solution, but it seems that
> you can't use a greater than (>) with ROWNUM.
>
> Currently I am looping through the cursor and fetching only those records
> that fall within the start and end row values. Not very efficient at all!!
>
> Does anybody have any suggestions how this might be achieved without
> implementing some unmanageable order_no column in the results table?
>
> Thanks.
>
>
Received on Mon Jul 15 2002 - 09:36:24 CDT