Database Reference
In-Depth Information
This is not nearly as ominous as it seems. The primary SQL statements used against temporary tables are
INSERT
s
and
SELECT
s. Fortunately,
INSERT
s generate very little undo (you need to restore the block to “nothing,” and it doesn't
take very much room to store “nothing”), and
SELECT
s generate no undo. Hence, if you use temporary tables for
INSERT
s and
SELECT
s exclusively, this section means nothing to you. It is only if you
UPDATE
or
DELETE
that you might
be concerned about this.
I set up a small test to demonstrate the amount of redo generated while working with temporary tables, an
indication therefore of the amount of undo generated for temporary tables, since only the undo is logged for them.
To demonstrate, I'll take identically configured permanent and temporary tables, and then perform the same
operations on each, measuring the amount of redo generated each time. The tables I'll use are as follows:
EODA@ORA11GR2> create table perm
2 ( x char(2000) ,
3 y char(2000) ,
4 z char(2000) )
5 /
Table created.
EODA@ORA11GR2> create global temporary table temp
2 ( x char(2000) ,
3 y char(2000) ,
4 z char(2000) )
5 on commit preserve rows
6 /
Table created.
I set up a small stored procedure to allow me to perform arbitrary SQL and report the amount of redo
generated by that SQL. I'll use this routine to perform
INSERT
s,
UPDATE
s, and
DELETE
s against both the temporary
and permanent tables:
EODA@ORA11GR2> create or replace procedure do_sql( p_sql in varchar2 )
2 as
3 l_start_redo number;
4 l_redo number;
5 begin
6 l_start_redo := get_stat_val( 'redo size' );
7
8 execute immediate p_sql;
9 commit;
10
11 l_redo := get_stat_val( 'redo size' ) - l_start_redo;
12
13 dbms_output.put_line
14 ( to_char(l_redo,'99,999,999') ||' bytes of redo generated for "' ||
15 substr( replace( p_sql, chr(10), ' '), 1, 25 ) || '"...' );
16 end;
17 /
Procedure created.
