Re: Temporary table

jtdennis_at_atl.mindspring.com (John Dennis) wrote:

>grant_at_towersoft.com.au (Fuzzy) wrote:
 

>>People,
 

>>In SQL Server and Sybase, there is the concept of a temporary table,
>>that can be used as a holding space for results that will need further
>>work.
 

>>Is there such a beast in Oracle? I can't find any references in the
>>doco, and I've thought about creating views on the fly, but this could
>>get messy.
 

>Nope. This is one my list of top-ten things I wish Oracle had that
>Sybase does. The opposite list is quite long so don't thing I'm a
>Sybaser. Anyway, no temp tables.
 

>Options:
> Use dynamic SQL to create temp tables on the fly. It could also be
>improved to remove them later with DBMS_BATCH.
 

> Create semi-permenant table. On that is cleaned out on a regular
>basis. This can even include the session-id for use by multiple
>users.

Third option: Create a temporary table using the NONRECOVERABLE option (available, I think, since Version 7.2). Exampe:

create table TEMP_TABLE
tablespace ABC
UNREVOCERABLE
as
select * from dept
where dept_number < 5000

This way, Oracle will not generate Redo-Log information.

But be careful: the UNRECOVERABLE will only count for the actual statement, not for the table as a whole; all subsequent actions (delet, insert, update) against the table WILL generate redo-logs.

>Neither is pretty. You prob. already solved this problem. If it was
>a different solution let me know what it was.
 

>John Dennis

>John Dennis
>Tactics, Inc
>Atlanta, GA

Willy Klotz

Willys Mail     FidoNet   2:2474/117  2:2474/118  
                Mailbox: analog 06297 910104
                         ISDN   06297 910105
                Internet: 0629791010_at_t-online.de                
                ->   No Request from 06.00 to 08.00 <-
======================================================================