Re: switch redo logs off for one table

"Karen Abgarian" <abvk_at_ureach.com> wrote in message news:3E095A02.8E9F3998_at_ureach.com...
> Actually it is great!
>

Well, actually it's well known. Or ought to be. Full marks to Richard for pointing it out, but it *ought* to be obvious.

The point of Richard's demonstration is that the temporary table does *not* generate redo. But nothing switches off rollback. And naturally enough, rollback segments are, er, segments, and always generate redo when they're modified.

It's not a particularly big deal. And the benefits are still there of (OK, let's be pedantic) 'much less' redo generation than otherwise.

> Oracle fools us again :-)
>

No, only a misunderstanding of Oracle leads fools to think anything other than this could be the result.

HJR
>
> Jim Kennedy wrote:
>
> > Thanks. Good demonstration.
> > Jim
> > "Richard Foote" <richard.foote_at_bigpond.com> wrote in message
> > news:L7gM9.6297$jM5.17207_at_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 Wed Dec 25 2002 - 05:56:25 CST