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

Well, you would not

 CREATE GLOBAL TEMPORARY TABLE AS SELECT You would do this once:

 CREATE GLOBAL TEMPORARY TABLE t (columns...) ON COMMIT DELETE ROWS;

Then in the application,

INSERT INTO t (columns) SELECT ...

The thing with platform independence is you can either write efficient code that makes full use of the performance features you paid for, and then revisit it if you move, or write crappy code that just about gets by on multiple platforms with the minimum of rework. I'm not a fan of the second approach personally.

I've been hearing a lot about SOA recently. So doesit mean "writing crappy code"? Received on Sun Dec 17 2006 - 04:47:30 CST