Re: get x number of records from the table

From: matt mcConnell <matt_at_m-centric.com>
Date: Fri, 14 Sep 2001 12:31:58 +0200
Message-ID: <3ba1dbb5$1_at_news.airtel.net>


> This limiting of records to display should, I feel, be done by the
> application, not the query. Have the program open the cursor and fetch the
> limit for records on a page, and then display the page, if the user asks
 for
> more then fetch the next set.

This approach has be suggested a couple of times by other messages in the thread. It will work fine, but may not be the optimal way to "page" the results of a query. It is best for an environment where you can keep the cursor open while you wait for the user to decide if he/she wants more records. That's not really feasible in an asynchronous environment like a web server - as soon as the first page request is filled the cursor needs to be closed and the connection reused for another request.

The only way you could control the number of records fetched "by the application, not the query" would be to query for all of them (in some order), then discard all of the ones you don't want.

The best approach in a web environment may be to build an ordered index into the table you're querying. Of course, this will only work if you always want your results to maintain a certain ordering. Otherwise, you will have take the first option. There may be tricks that your development environment allows to skip to random locations in cursors (JDBC ResultSet has some, for example). Whether they are doing anything more than fetching, then discarding rows depends on Oracle's low-level implementation.

matt

---
The real problem is entropy.
Received on Fri Sep 14 2001 - 12:31:58 CEST

Original text of this message