Investigating Redo - Oracle Database Transactions and Locking Revealed - page 139

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

■ If you see the temporary table generating more redo than the permanent table with the INSERT statement,
you are observing a product issue in the database that is fixed in at least Oracle 9.2.0.6 and 10.1.0.4 patch releases
and above.

Note

Therefore, the following generalizations can be made regarding DML activity on temporary tables:

INSERT will generate little to no undo/redo activity.

• An

UPDATE will generate about half the redo as with a permanent table. • An

• DELETE will generate the same amount of redo as with a permanent table.
There are notable exceptions to the next to last statement. For example, if I UPDATE a column that is entirely NULL
with 2,000 bytes of data, there will be very little undo data generated. This UPDATE will behave like the INSERT . On
the other hand, if I UPDATE a column with 2,000 bytes of data to be NULL , it will behave like the DELETE as far as redo
generation is concerned. On average, you can expect an UPDATE against a temporary table to produce about 50 percent
of the undo/redo you'd experience with a permanent table.

A