Re: switch redo logs off for one table

"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