Re: Temporary table use and the redo log

From: Jonathan Lewis <>
Date: Fri, 11 Sep 2009 18:05:18 +0100
Message-ID: <>

<> wrote in message
> Hi
> I'm using a global (session) temporary table to break up a section
> of a stored procedure that is needed in
> a number of places. Always for use in a select.
> If the database crashes, gets rolled back, anything, I don't care
> about the contents of that
> table. Especially -I don't want redo logs for it-. Is there any way I
> can accomplish this?
> From what I can see so far I should look into:
> - unrecoverable
> - insert /*+APPEND */ into temp_table select...
> - possibly using a table variable
> Nologging is on by default for a tempory table but you still get some
> logging
> Am I on the right track?
> thanks

I've read through the posts, and don't see a version number. As various people have suggested, in principle the optimum strategy you want is

    create temporary table ... on commit preserve rows;     insert /*+ append */

You still generate undo and redo on indexes, and various feature will invalidate the append hint.

But there are various bugs in different versions of Oracle that cause problems. Most versions of 9i, for example, lose the bulk processing optimisation of insert as select; all versions up to invalidate all cursors on the object when you truncate.


Jonathan Lewis

Author: Cost Based Oracle: Fundamentals

The Co-operative Oracle Users' FAQ
Received on Fri Sep 11 2009 - 12:05:18 CDT

Original text of this message