Re: A classic search issue.

If your only concern is to provide the data in a forward direction, why not just open the cursor and fetch 20 rows each time you need them, closing the cursor when the user is finished ... I think the solutions you propose should only be necessary if you need to scroll forward and backward ... or if your application has some very unusual requirements I'm not comprehending.

Assuming you need scrollable cursor functionality, you covered the bases pretty well ... but I'll add a couple of comments.

If you know how you need to order the output, you can use an index to cause the results to be returned in order by default (i.e. without an ORDER BY) ... this is a bit of a cheat and theoretically could break in the future if the implementation changed, but my experience is that the behavior has been consistent for a long time ... so it probably wouldn't change any time soon.

Another possiblity is to cache in the middle tier, but add some smarts to your caching algorithm to page data out to disk when the in-memory size reaches some threshold value, then page back in as needed when the user scrolls backward ... until Oracle supports server-side scrollable cursors, such workarounds will always be necessary ... rumor is that scrollable cursors are coming soon, but defining 'soon' is tricky.

"Morten Primdahl" <morten_at_caput.com> wrote in message news:3AA619B1.CE923160_at_caput.com...
>
> Hi.
>
> I'd like to present a large result set 20 records at a time.
> As I see it, there are two approaches,
> 1) Cache the result set in a middle layer buffer
> 2) Select using where rownum > X and rownum < Y
> The first approach results in other issues as memory costs
> w. multiple concurrent users, one user performing
> queries from multiple clients (but only has one cache)...
> These can ofc. be worked around. The second approach
> seems expensive (I need an ORDER BY clause) - how are
> problems like this typically solved? Caching partial
> resultsets?
>
> Another question: Using SET HEADING OFF, how do I avoid
> getting the blank lines in SQLplus?
>
> Thanks,
>
> Morten
>
> --
> Morten Primdahl Caput A/S Tel +45 70 12 24 42
> morten_at_caput.com Nygade 6 Fax +45 70 11 24 42
> http://www.caput.com/ DK-1164 Kbh K
>
Received on Wed Mar 07 2001 - 19:34:59 CST