Re: switch redo logs off for one table

"Richard Foote" <richard.foote_at_bigpond.com> wrote in message news:WHCO9.10160$jM5.29434_at_newsfeeds.bigpond.com...
> All this can be a trap for the unwary and that's why I said claims that
temp
> tables don't generate redo is a little deceptive.
>

[snip]
>
> How people word things is important. I picked up on the statement(s) that
> temp tables should be considered "as they generate no redo". As can be
seen,
> this is misleading and "myth promoting" so if this tread has cleared
things
> up for some people out there in Oracleland, then it's been worth it.

Not that is necessarily worth going round the houses, but the statement that temporary tables don't generate redo isn't misleading or a myth. It's completely and utterly true, because they themselves don't. It's just not quite the whole picture.

And as I said somewhere else, full marks to you for reminding us of the bigger picture.

And yes, what *is* misleading, I guess, is to imagine that temporary tables are somehow 'free' or a solution for every woe without thinking of the whole system.

Regards
HJR
> Cheers
>
> Richard
> "Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message
> news:BcxO9.9948$jM5.28462_at_newsfeeds.bigpond.com...
> >
> > "Karen Abgarian" <abvk_at_ureach.com> wrote in message
> > news:3E0A2711.7A77258B_at_ureach.com...
> > > 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,
> >
> > But they don't!
> >
> > So what they claim is true.
> >
> > If you did an update on a regular table, and it generated (say) 100K of
> > redo, the same update on a temporary table would generate (say) 30K of
> redo.
> > That 30K is the redo related to the rollback segment. It means that in
the
> > first case, 70K of redo was generated by the table itself. By using a
> > temporary table, you have switched off 70K of redo generation occasioned
> by
> > the table itself.
> >
> > >and never mentions that rollback segments
> > > thing.
> >
> > Well, it's a matter of emphasis, I suppose.
> >
> > It is well-known (or ought to be, at any rate) that -bar discrete
> > transactions- it is impossible to switch of undo/rollback. It's
frequently
> > asked for here, and the answer has usually gone along the lines of 'you
> > can't switch of rollback because it's not just your rollback: others may
> > need it to generate read-consistent images of data, and Oracle needs it
> for
> > much the same reason internally'. So if it is assumed that one knows
> > rollback can't ever be switched off, it logically follows that rollback
> must
> > be generated by temporary tables.
> >
> > And given an understanding of what a segment is, it therefore follows
that
> > updates to rollback segments must generate redo. The same redo that
would
> be
> > generated by the rollback generated for transactions on ordinary tables.
> >
> > I suppose they (ie, marketing) don't mention it because *that* redo must
> > always be generated, whatever type of table you have, and that's always
> been
> > the case. The 'new' feature (new in 8i anyway) is that the redo
occasioned
> > by updates to the table segment itself *can* be switched off if the
table
> is
> > a temporary one.
> >
> > HJR
> >
> >
> > >
> > >
> > > "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 Thu Dec 26 2002 - 13:32:21 CST