Redo and Undo - Expert Oracle Database Architecture - page 335

■ 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.
In addition, you must consider any indexes in place on your temporary tables. Index modifications will also
generate undo—which in turn generates redo. If you rerun the above example with these two indexes in place:

• A

EODA@ORA11GR2> create index perm_idx on perm(x);
Index created.

EODA@ORA11GR2> create index temp_idx on temp(x);
Index created.

You will find redo generated in the order of (for brevity, all of the code from the previous example is not repeated here):

...
19 end;
20 /

11,735,576 bytes of redo generated for "insert into perm "...
3,351,864 bytes of redo generated for "insert into temp "...

9,257,748 bytes of redo generated for "update perm set x = 2"...
5,465,868 bytes of redo generated for "update temp set x = 2"...

4,434,992 bytes of redo generated for "delete from perm"...
4,371,620 bytes of redo generated for "delete from temp"...

PL/SQL procedure successfully completed.

The numbers hold true from what we saw before—but you can see that the index definitely added to the redo
generated. The INSERT into the global temporary table went from generating almost no redo to generating 3.3MB of
redo. All of this additional redo was related to the undo produced for the index maintenance.

■ This is an exaggerated example. The index in question was on a CHAR(2000) column; the index key is much
larger than you'll normally see in real life. don't expect this much additional redo typically.

Note