Re: switch redo logs off for one table

This is, of course, obvious, and there is not need to explain what are, er, segments, and how updates on them, er, generate redo. What I meant to mean, is that Oracle marketing always mentions that temporary tables don't generate redo, and never mentions that rollback segments thing.

"Howard J. Rogers" wrote:

> "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 - 18:02:13 CST