Re: how to display partial results from a query (effectively)

Billy Verreynne <vslabs_at_onwe.co.za> wrote in message news:<aplg1j$bbb$1_at_ctb-nnrp2.saix.net>...
> Sharkie wrote:
>
> > Since the query has to finish, it will sometimes take a long
> > time to display the 10 results. There has to be a better way
> > of doing this. Isn't there a way in oracle to quickly only return
> > rows between certain rowcounts?
>
> No. Think about it. I tell you to give me a list of surnames starting
> between D and F.
>
> Then I tell you I only want rows 100 to 200 from the list of matching
> surnames.
>
> You still have to find _all_ the surnames between D and F. Only then you can
> give the the matches starting from 100 to 200.
>
> The query must thus be run in full each time. There is no way to make a
> single SQL statement only run "partially" and return a partial set of
> matches to you.
>
> The only way around this is not to execute the query each time. Back to the
> above example. You have all the surnames between D and F for me. I tell you
> to keep the results lists in your hands. Do not throw it away.
>
> I ask you for all matches between 50 and 60. Between 100 and 150. As you
> have the list in your hand, you can tell me. You do not have to go through
> the search process again.
>
> In Oracle this is called cursors. You open a cursor to contain the result of
> your query. Now you can fetch rows from the cursor in batches.
>
> However.. this is not possible via a normal web interface. A cursor requires
> a session to be kept alive and open. In other words, it needs state.
>
> A web server by default is stateless. So to use cursors via a web server,
> you need to make those connection into sessions. Which is another rusty,
> half-empty, slightly warm, beer can.

That still does not prevent you from 'caching' your results in a table though. The application I work on has the next/previous feature and we run some fairly complicated queries that are somewhat expensive. We have started saving those results in a table so we do not have to re-execute the same complex query over and over. We take the hit on insert, but more than make up for it when the user hits the next button. Also, it freezes the results so they do not change when the user hits next the previous.

Another option we toyed with was saving the results as an XML doc then using XSLT (on the server with Oracle's xml toolkit) to display rows x through y. Received on Tue Oct 29 2002 - 16:03:57 CST