Re: Temporary table use and the redo log
Date: Tue, 8 Sep 2009 11:25:28 +0000 (UTC)
Message-ID: <h85ev8$d9b$1_at_solani.org>
Na Tue, 08 Sep 2009 01:34:41 -0700, Randolf Geist napisao:
> 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.
Of course it is. UNDO is always covered by redo and everything that participates in transactions must be covered by UNDO. One can bypass the undo restrictions for the table by using the APPEND hint, but the indexes will still generate UNDO information. I used the word "similar". APPEND doesn't generate UNDO information because it uses only the empty blocks (above the HWM).
>
> 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.
And there is also a significan difference between the conventional DML on the normal table and conventional DML on the temporary table. For one, in the case of DML on the temporary table, the buffer cache is not involved. Things go from the PGA to the temporary object and vice versa. Bypassing the buffer cache and all of its synchronization mechanisms (free lists, latches, pins) is, in my opinion, the largest benefit from the APPEND hint.
>
> 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.
Oh, there are many restrictions. Among my pet peeves are unique constraints enforced by non-unique indexes and triggers which both prevent direct path inserts. The effect of the APPEND hint is mostly placebo effect. People think that they've pushed the "make apps faster" button and they never check "direct file write" events in 10046 trace.
-- http://mgogala.freehostia.comReceived on Tue Sep 08 2009 - 06:25:28 CDT