Redo and Undo - Expert Oracle Database Architecture - page 334

Then I ran equivalent INSERT s, UPDATE s, and DELETE s against both the PERM and TEMP tables:

EODA@ORA11GR2> set serveroutput on format wrapped
EODA@ORA11GR2> begin
2 do_sql( 'insert into perm
3 select 1,1,1
4 from all_objects
5 where rownum <= 500' );
6
7 do_sql( 'insert into temp
8 select 1,1,1
9 from all_objects
10 where rownum <= 500' );
11 dbms_output.new_line;
12
13 do_sql( 'update perm set x = 2' );
14 do_sql( 'update temp set x = 2' );
15 dbms_output.new_line;
16
17 do_sql( 'delete from perm' );
18 do_sql( 'delete from temp' );
19 end;
20 /

3,313,088 bytes of redo generated for "insert into perm "...
72,584 bytes of redo generated for "insert into temp "...

3,268,384 bytes of redo generated for "update perm set x = 2"...
1,946,432 bytes of redo generated for "update temp set x = 2"...

3,245,112 bytes of redo generated for "delete from perm"...
3,224,460 bytes of redo generated for "delete from temp"...

PL/SQL procedure successfully completed.

As you can see,

INSERT into the “real” table generated a lot of redo, while almost no redo was generated
for the temporary table. This makes sense—there is very little undo data generated for INSERT s
and only undo data is logged for temporary tables.

• The

UPDATE of the real table generated about twice the amount of redo as the temporary table.
Again, this makes sense. About half of that UPDATE , the “before image,” had to be saved. The
“after image” (redo) for the temporary table did not have to be saved.

• The

DELETE s each took about the same amount of redo space. This makes sense, because the
undo for a DELETE is big, but the redo for the modified blocks is very small. Hence, a DELETE
against a temporary table takes place very much in the same fashion as a DELETE against a
permanent table.

• The