Re: Temporary tables in memory

From: Tanel Poder <change_to_my_first_name_at_integrid.info>
Date: Mon, 1 Sep 2003 00:58:34 +0300
Message-ID: <3f526f8b$1_1@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 Sun Aug 31 2003 - 16:58:34 CDT