I've got a situation where I would like to paginate through several thousand records quickly.
The way I have classically looked for records (1 through 250) in oracle has been to run the query, then query that (to get rownums for an ordered record set), and then query _that_ query to get records n through whatever.
The downside of this is that if the base query takes a while to run, I don't see any speed increase in getting each block of data. (i.e. to get records 1-10000 takes as long as getting records 1-250), since it has to finish the query first before returning.
Do I have any options on the database end here? Are there any tricks to return that data in the same kind of chunks of 250 or so?
Well if its only a couple of thousand rows why not cache the whole result set at the application server end and then paginate throug the cached resultset.
Another alternative is to write the whole resultset to a global temp table and then query that for pagination.
You can also use the first_rows(n) hint to try to get the first n rows but it is a bit hit and miss as to wether it will help you (maybe even hinder you) and it will only possibly give benefit for pages at the start of the resultset.