Re: switch redo logs off for one table

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
Received on Tue Jan 07 2003 - 13:00:25 CST