How to use DML on Oracle temporary table without generating much undo log

Question

Using an Oracle temporary table does not generate much redo log as a normal table. However, the undo log is still generated. Thus, how can I write insert, update, or delete statement on a temporary table but Oracle will not generate undo log or generate as little as possible?

Moreover, using /+append/ in the insert statement will generate little undo log. Am I correct? If not, could anyone explain me about using the hint /+append/?

INSERT /*+APPEND*/ INTO table1(...) VALUES(...);

Answer 1

Oracle needs UNDO information to rollback the DML in the transaction. As Gary puts it in his comment:

"The UNDO is needed to rollback the effects of a single statement if it fails partway through. It is also needed to provide for a ROLLBACK TO SAVEPOINT or a ROLLBACK (though for GLOBAL TEMPORARY TABLES the latter would only be relevant for session duration GTTs)."

This UNDO information itself generates REDO. There is nothing you can do about this situation: temporary tables need UNDO and that's the end of it.

To minimize the amount of UNDO is quite simple: just insert records and select records. INSERT generates the smallest amount of UNDO, because rolling back an INSERT requires simply the rowid. Conversely DELETE statements generate the most UNDO, because the database has to store the entire record. Basically, to rollback an INSERT issue a DELETE, to rollback a DELETE issue an INSERT. An UPDATE generates a variable amount of UNDO, because we need the old versions of the changed columns; the more columns changed and the bigger they are, the larger the amount of UNDO generated.

Demonstration

In session one a user will insert a lot of records into a temporary table and then delete them. In session two a DBA will monitor the transaction's UNDO usage.

SSN1> insert into gtt23
  2      select * from big_table
  3  /

553928 rows created.

SSN1>

Undo usage:

SSN2> select space, noundo, used_ublk, used_urec from v$transaction
   2  /

SPA NOU  USED_UBLK  USED_UREC
--- --- ---------- ----------
NO  NO         257      10816

SSN2>

Now the deletion:

SSN1> delete from gtt23
   2  /

553928 rows deleted.

SSN1>

Undo usage (several samples during a long running statement)::

SSN2> r
   1* select space, noundo, used_ublk, used_urec from v$transaction

SPA NOU  USED_UBLK  USED_UREC
--- --- ---------- ----------
NO  NO       11123     435605

SSN2> r
   1* select space, noundo, used_ublk, used_urec from v$transaction

SPA NOU  USED_UBLK  USED_UREC
--- --- ---------- ----------
NO  NO       13413     525452

SSN2> r
   1* select space, noundo, used_ublk, used_urec from v$transaction

SPA NOU  USED_UBLK  USED_UREC
--- --- ---------- ----------
NO  NO       14552     570567

SSN2>

Commit (the temporary table has transaction scope i.e. DELETE ROWS)

SSN1> commit
   2  /

Commit complete.

SSN1>

Undo usage:

SSN2> r
   1* select space, noundo, used_ublk, used_urec from v$transaction

no rows selected

SSN2>

The undo usage is accumulative:

SSN1> insert into gtt23
   2      select * from big_table
   3  /

553928 rows created.

SSN1> delete from gtt23
   2  /

553928 rows deleted.

SSN1> insert into gtt23
   2      select * from big_table
   3  /

553928 rows created.

SSN1>

Undo usage

SSN2> r
   1* select space, noundo, used_ublk, used_urec from v$transaction

SPA NOU  USED_UBLK  USED_UREC
--- --- ---------- ----------
NO  NO         258      10816

SSN2> r
   1* select space, noundo, used_ublk, used_urec from v$transaction

SPA NOU  USED_UBLK  USED_UREC
--- --- ---------- ----------
NO  NO       14766     579495

SSN2> r
   1* select space, noundo, used_ublk, used_urec from v$transaction

SPA NOU  USED_UBLK  USED_UREC
--- --- ---------- ----------
NO  NO       14819     581685

SSN2>

Summary

So, to minimise the impact of UNDO which a temporary table generates make sure you insert the right data, once. Avoid applying updates to it and especially avoid deleting large numbers of records from them. If you are using a temporary table with a transaction scope there really should be no need to delete records from it. If your temporary table has a session duration and you need to clear it out, it would be better to use TRUNCATE, if possible, rather than DELETE.