That's true, Tanel. We had this discussion a while back, and Richard Foote
pointed it out at the time. The temporary table itself doesn't generate
redo, but it does generate undo, and as you show nicely, undo segments do
generate redo.
Regards
HJR
"Tanel Poder" <change_to_my_first_name_at_integrid.info> wrote in message
news:3f526f8b$1_1_at_news.estpak.ee...
> > Conclusion: temporary tables do not get cached in the PGA
> (sort_area_sized)
> > but, regardless of size, are written directly to disk. They don't
generate
> > redo, however.
>
> Just a small addition - rollback is created for conventional mode DML on
> temporary tables and their rollback is always logged in redo (one might
want
> to use direct path insert to reduce rollback -> redo information). Here's
a
> continuation of Howard's example:
>
> SQL> insert into x select 34 from sys.obj$;
>
> 31663 rows created.
>
> 3942104 redo size
>
> SQL> rollback;
>
> Rollback complete.
>
> SQL> insert /*+ append */ into x select 34 from sys.obj$;
>
> 31663 rows created.
>
> 224 redo size
>
> If anyone is wondering why would we need rollback ability on temporary
> tables, well one reason is rollback to savepoint functionality, other
reason
> might be that Oracle just hasn't implemented true "lightweight" temporary
> tables in current versions yet.
>
> Tanel.
>
>
Received on Mon Sep 01 2003 - 04:42:50 CDT
