Re: switch redo logs off for one table

From: Richard Foote <richard.foote_at_bigpond.com>
Date: Thu, 19 Dec 2002 20:32:46 +1000
Message-ID: <L7gM9.6297$jM5.17207@newsfeeds.bigpond.com>

"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message news:XL3M9.5895$jM5.16983_at_newsfeeds.bigpond.com...
> The answer is no.
>
> If a transaction generates redo (and every normal insert, delete or update
> always does, regardless of your setting for NOLOGGING), then that redo
will
> be written to the online logs, and online logs get archived, so your
> transactions are in the archived redo logs.
>
> As someone else has mentioned, you might find that 'create global
temporary
> table BLAH (col1 number, col2 char(5)) etc' fits the bill, as transactions
> in global temporary tables are never logged... largely because the data
> inserted into such a table is only visible to the session that put them
> there.

Hi Howard, Jim and all,

Suggestions that transactions to temporary tables are never logged is not quite correct and is a little deceptive. It's kinda right but not quite. In actual fact changes to temporary tables can produce a significant amount of redo.

Let me give a simple demo then I'll try to explain.

SQL> create global temporary table bowie_test on commit preserve rows as select * from dba_source;

Table created.

SQL> select * from v$log;

    GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS

---------- ---------- ---------- ---------- ---------- --- ----------------

FIRST_CHANGE# FIRST_TIM
------------- ---------

         1          1         64    1048576          1 NO  INACTIVE
     19490665 19/DEC/02

         4          1         65    1048576          1 NO  INACTIVE
     19490834 19/DEC/02

         5          1         66    1048576          1 NO  CURRENT
     19490837 19/DEC/02

SQL> update bowie_test
  2 set text = 'BOWIE';

145115 rows updated.

SQL> select * from v$log;

    GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS

---------- ---------- ---------- ---------- ---------- --- ----------------

FIRST_CHANGE# FIRST_TIM
------------- ---------

         1          1        139    1048576          1 NO  ACTIVE
     19504906 19/DEC/02

         4          1        140    1048576          1 NO  CURRENT
     19505046 19/DEC/02

         5          1        138    1048576          1 NO  INACTIVE
     19504746 19/DEC/02

I am the only user on my little baby DB and yet changes to just a simple little temporary table has produced about 74M of redo (the small redo logs are there purely for effect ;)

The key point is this. Changes to temporary tables produces *undo* as I might decide to rollback the changes above. Although the changes to the temporary table itself are not logged, the changes to the undo segments *are* logged. This is because we may need to recover a specific undo datafile and need to determine what is what. Therefore changes to temporary tables can produce a significant amount of redo.

An important point that I thought needed clarifying.

Cheers

Richard Received on Thu Dec 19 2002 - 04:32:46 CST