Re: Temporary table use and the redo log
Date: Tue, 8 Sep 2009 01:34:41 -0700 (PDT)
Message-ID: <4b3fdc8a-6ad1-4275-8d5a-200ecf45bdf0_at_a21g2000yqc.googlegroups.com>
On Sep 7, 7:57 pm, Mladen Gogala <mla..._at_bogus.email.invalid> 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.
As already pointed out by others I don't think this description is entirely correct.
The data itself written to the temporary table is not protected by REDO, but for conventional DML this is still covered by UNDO, and UNDO is protected by REDO. This is also what Tom is talking about / demonstrating in his books I think.
So there IS a significant difference between conventional DML (including INSERTs) and a direct-path INSERT into a temporary table since the latter is not generating UNDO and therefore almost no REDO.
However, if indexes have been defined on the temporary table, all kind of modifications regardless of the mode will generate UNDO for the index maintenance and again REDO for the UNDO.
Note that there are restrictions for direct-path operations, most of them are not applicable to global temporary tables but if you e.g. add triggers to the temporary tables then the direct-path insert mode will be disabled.
Regards,
Randolf
Oracle related stuff blog:
http://oracle-randolf.blogspot.com/
SQLTools++ for Oracle (Open source Oracle GUI for Windows):
http://www.sqltools-plusplus.org:7676/
http://sourceforge.net/projects/sqlt-pp/
Received on Tue Sep 08 2009 - 03:34:41 CDT