Re: temporary tables in oracle

From: Gerard M. Averill <e-mail.address_at_my.sig>
Date: Thu, 11 Feb 99 16:26:33 GMT
Message-ID: <79v055$km6$1@news.doit.wisc.edu>

In article <36ba8520.10759300_at_news.demon.co.uk>, eric_at_deptj.demon.co.uk (Eric Junkermann) wrote:
>On Fri, 29 Jan 1999 16:06:26 -0600, Kent Eilers
><kent.eilers_at_pca.state.mn.us> wrote:
>
>>I come from sybase/sql-server where we used temp tables instead of
>>cursors (big reason: giant performance increase). Do you Oracle gurus
>>use temp tables in procedures? I have found little documentation on
>>this in two Oracle books I have.
>>
>>Thanks.
>
>Oracle doesn't work like that.
>
><begin soapbox>
>

<< snip >>
>
><end soapbox>
>

In fact it can and does -- I've gotten tremendous performance gains by using dynamic SQL to generate such "temporary tables" using "CREATE TABLE ... UNRECOVERABLE AS SELECT ..." Using this approach, I can generate complex multistep calculations into temp tables before inserting the final results into permanent tables. IMHO, there's no other way I could have accomplished this so efficiently in Oracle (at least in 7.3.whatever -- I don't know about Oracle 8's capabilities at this point).

Don't let theories about theory and practice blind you in the practice of programming ;)

Gerard

---

Gerard M. Averill, Researcher
CHSRA, University of Wisconsin - Madison GAverill<at>chsra<dot>wisc<dot>edu Received on Thu Feb 11 1999 - 10:26:33 CST