Friday, November 20, 2009

Global Temporary Tables generate REDO and UNDO

Many people believe that Global Temporary Tables don't generate REDO or UNDO.
They do generate REDO and UNDO, but not as much as permanent tables.

# First a GTT :-

SQL> create global temporary table source as select * from dba_source where rownum <>

Table created.

SQL> set autotrace on statistics
SQL> insert into source select * from dba_source where rownum <>

99 rows created.


Statistics
----------------------------------------------------------
1 recursive calls
8 db block gets
771 consistent gets
0 physical reads
564 redo size
830 bytes sent via SQL*Net to client
818 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
99 rows processed


# And now a permanent table.

SQL> drop table source;

Table dropped.

SQL>
SQL> create table source as select * from dba_source where rownum <>


Table created.

SQL> SQL> set autotrace on statistics;
SQL> insert into source select * from dba_source where rownum <>

99 rows created.


Statistics
----------------------------------------------------------
721 recursive calls
67 db block gets
1687 consistent gets
11 physical reads
14208 redo size
838 bytes sent via SQL*Net to client
818 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
13 sorts (memory)
0 sorts (disk)
99 rows processed