Re: Help: Tuning "temporary" tables

Gerard,

I don't know how you've got this implemented, but I've had the same problem. One solution, if you're using PL/SQL and Oracle 7.3, is to use PL/SQL tables, which are dynamic memory structures and can take on the same fields as a table in the database. Inserts, updates, deletes, etc. occur in memory and generate no redo or rollback overhead.

The only problem is that you can't use SQL on this tables. But you could write a PL/SQL package to simulate a stack without much trouble.

On another front, I've read that Oracle 8 has an INSERT option which bypasses redo and rollback, something like the DIRECT PATH option on SQL*Loader. I haven't confirmed this myself, however.

Regards,
Ken

Gerard M. Averill wrote:
>
> I've got a table which serves as a sort of stack in that it is used by a
> generic calculation process to store "temporary" intermediate calculations of
> complex "derived fields" (whose values are eventually stored elsewhere).
> Subsequently it experiences a lot of insert and delete activity against it;
> however, because of the nature of the data being stored in it (i.e. incomplete
> calculations), there's a fair amount of overhead (transactions, redo logging)
> which serves no real purpose (in the event of a server error this data is of
> no use; rather the calculation process itself would be restarted).
>
> If there are others out there who have encountered this situation and have
> advice on the best way to "tune" such a table, I'd love to hear it. (Ideally,
> there would be a CREATE TABLE option for turning off logging or something
> similar; kind of like the UNRECOVERABLE option.)
Received on Thu Dec 04 1997 - 00:00:00 CST