Re: switch redo logs off for one table

vob wrote:

> the oracle like solution is, do it
> in one sql-statement,
> use inline views, use with clause
>
> than this temp table stuff is not
> necessary ...
>
> <ctcgag_at_hotmail.com> schrieb im Newsbeitrag
> news:20030102193310.148$Ez_at_newsreader.com...
> > "Richard Foote" <richard.foote_at_bigpond.com> wrote:
> > >
> > > SQL> create global temporary table bowie_test on commit preserve rows as
> > > select * from dba_source;
> > >
> > ...
> > >
> > > 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.
> >
> > I would be very grateful if Oracle implemented a
> > 'create global very temporary table...' feature, for which
> > 'preserve rows' would not be an option (and checkpointing
> > would also not be available). Then, as there isn't a possibility of
> > rolling back changes without totally obliterating the associated
> > temporary data, there would be no fundamental need for undo (and undo
> redo)
> > that I can think of. Actually, I guess there is one possible need
> > for it that I can think of.
> >
> > Actually, maybe I can think of a reason. If a DML statement fails in the
> > middle, Oracle implicitly rolls back the statement, apparently as if it
> > did a checkpoint just before the DML and a rollback to that checkpoint
> > on failure. So I guess my method would also forbid continuance of the
> > transaction once any error, even an potentially anticipated on, occurs
> > on the temporary table. This doesn't seem like much of a restriction,
> > however, as the biggest anticipated errors are unique constraint
> violations
> > resulting from race conditions, and race conditions shouldn't happen in a
> > table with only one accessor.
> >
> > > This is because we may need to recover a specific undo
> > > datafile and need to determine what is what.
> >
> > Is undo data for temporary and permanent tables mingled at the
> > block level? If not, it seems that knowing that the block was used
> > for temporary table undo (and therefore it's contents
> > are irrelevant) would suffice, and that this would make for extremely
> > little redo logging.
> >
> >
> > Xho
> >
> > --
> > -------------------- http://NewsReader.Com/ --------------------
> > Usenet Newsgroup Service New Rate! $9.95/Month 50GB

Worse than not necessary. It hurts performance and scalability.

Daniel Morgan Received on Wed Jan 08 2003 - 10:56:05 CST