Navigating a Very Large Result Set

From: Stuart Christopherson <heracles_at_nospam.com.au>
Date: Mon, 15 Jul 2002 21:42:56 +1000
Message-ID: <1AyY8.2916$Cq.123048@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 - 06:42:56 CDT