Re: Creating temporary tables

Hi,

The Sybase mechanism for temporary tables where they are automatically removed by the DBMS has no equivalent in Oracle, i.e., there are no "true" temporary tables in Oracle.

You can have the program create the tables each time and specifically drop them at the end. But what we find in most applications are permanent work tables in which one column contains a session id that uniquely identifies a running process. Each process generates a new id and uses it to identify the records that it creates and works with. Before terminating the process deletes its entries from the work table. You must inform the DBA to perform regular maintenance on the work tables in order to remove records that would have been left by processes that did not terminate normally.

One problem with this: if you create a lot of records, the delete operation at the end may take a while to complete. For these "big jobs" we create a permanent work table for each user of the program so that the program can issue a "truncate" command at the end (N.B.: Users cannot run two copies of the same program at the same with this scheme). The Oracle truncate command is very fast compare to the standard SQL delete command but the truncate is not standard to all RDBMS's (is it?).

Gilles Pétrin Received on Tue Feb 25 1997 - 00:00:00 CST