Re: Fetch cursor vs. TEMPORARY TABLE scrolling

<f:chukcha> wrote:
> Hi all,
> I need to get over 2000 records from a table and then page through them
> and display 20 records at a time in my application. What is better: to
> CREATE GLOBAL TEMPORARY TABLE AS SELECT run the query once and then
> somehow scroll through the temp table OR declare a cursor and FETCH
> through the records 20 at a time?
>
> Is there a simpler "pure" SQL approach to this problem without using a
> FETCH?
>
> I try not to be tied to the Oracle way of doing things. My company will
> be moving to service oriented architecture soon and the architecture
> team is pushing me not to use oracle stored procedures. There is some
> talk about migrating from oracle to some other db...
>
> What are my options?
>
> Thank you in advance

Pure SQL approaches that don't know "what" database you are actually working with don't tend to perform well. I would look at an oracle procedure with a thought to eventually replacing it if needed eventually.

Tom Kyte at http://asktom.oracle.com has this subject covered pretty well already in questions he as answered ( plus suggestions from other people ). Received on Fri Dec 15 2006 - 17:20:06 CST