Re: Temporary table use and the redo log
From: Robert Klemme <shortcutter_at_googlemail.com>
Date: Mon, 07 Sep 2009 21:22:29 +0200
Message-ID: <7gl4rlF2q9lf5U3_at_mid.individual.net>
On 07.09.2009 19:57, Mladen Gogala wrote:
> On Mon, 07 Sep 2009 09:23:31 -0700, codefragment_at_googlemail.com wrote:
>
>
>
> 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.
Date: Mon, 07 Sep 2009 21:22:29 +0200
Message-ID: <7gl4rlF2q9lf5U3_at_mid.individual.net>
On 07.09.2009 19:57, Mladen Gogala wrote:
> 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.
But there is a bit of redo because of the undo:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:15826034070548#50549336675394
(Hopefully that link works...)
Kind regards
robert
-- remember.guy do |as, often| as.you_can - without end http://blog.rubybestpractices.com/Received on Mon Sep 07 2009 - 14:22:29 CDT