Re: Temporary table use and the redo log
Date: Mon, 7 Sep 2009 17:57:35 +0000 (UTC)
Message-ID: <h83hif$qp0$1_at_solani.org>
On Mon, 07 Sep 2009 09:23:31 -0700, codefragment_at_googlemail.com wrote:
> 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
The storage for temporary tables is allocated entirely from the temporary tablespace. Blocks in a temporary tablespace are not proteced by redo so nologging is on by default as you say. Even more than that, there is no "current version" and "read consistent" version of the temporary blocks. Temporary blocks are written from PGA to temporary tablespace in a way very similar to what /*+ APPEND */ hint does. In other words, that hint is for normal insert and has no visible effect on inserting into global temporary tables. To verify that, you can run it with the event 10046, level 12 and see that there are no visible differences in the trace file.
-- http://mgogala.freehostia.comReceived on Mon Sep 07 2009 - 12:57:35 CDT