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

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:

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

EODA@ORA12CR1> 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

In general, common sense prevails in estimating the amount of redo created. If the operation you perform causes
undo data to be created, then determine how easy or hard it will be to reverse (undo) the effect of your operation.
If you INSERT 2,000 bytes, the reverse of this is easy. You simply go back to no bytes. If you DELETE 2,000 bytes, the
reverse is INSERT ing 2,000 bytes. In this case, the redo is substantial.
Armed with this knowledge, you will avoid deleting from temporary tables. You can use TRUNCATE , bearing
in mind, of course, that TRUNCATE is DDL that will commit your transaction, and in Oracle9 i and before invalidate
your cursors. Or just let the temporary tables empty themselves automatically after a COMMIT or when your session
terminates. All of these methods generate no undo and, therefore, no redo. You should try to avoid updating a
temporary table unless you really have to for some reason. You should use temporary tables mostly as something
to be INSERT ed into and SELECT ed from. In this fashion, you'll make optimum use of their unique ability to not
generate redo.