Re: switch redo logs off for one table

Hi Xho,

Note that the same applies if you choose to 'on commit delete rows' ...

I know what you mean with your suggestion but because of the way undo is generated, the redo thing remains an issue.

A suggestion I would make to any Oracle kernel developers out there would be to implement undo for temp tables via a "on the fly" temp undo segment, aka deferred rollback segments but consisting of temp extents.

Have a transaction use a dedicated undo segment that is allocated on the fly within the temp tablespace that then doesn't really have to have redo generated as a result. The data and undo is self contained if you like within the temp tablespace.

But then again .........

Richard
<ctcgag_at_hotmail.com> wrote in message
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 Fri Jan 03 2003 - 08:42:36 CST