How to use DML for an Oracle temporary table without generating a large number of undo logs

Question

Using a temporary table Oracle does not generate many redo logs like a regular table. However, the cancellation log is maintained. Thus, how can I write an insert, update, or delete statement in a temporary table, but Oracle will not generate a undo log or generate as little as possible?

Also, using / + append / in the insert statement will result in a small undo log. I'm right? If not, can someone explain to me about using the hint / + append /?

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

Answer 1

Oracle needs UNDO information to roll back DML in a transaction. As Gary says in his comment:

“UNDO is needed to rollback the effects of a single statement if it fails. It is also necessary to provide a ROLLB SAVEPOINT or ROLLBACK (although for GLOBAL TEMPORARY TABLES only GTT duration sessions will be relevant).

This UNDO information itself generates REDO. You can do nothing in this situation: temporary tables need UNDO at the end of this.

To minimize the number of UNDOs, it's simple enough: just insert records and select records. INSERT generates the least amount of UNDO because rolling back INSERT requires just a rowid. Conversely, DELETE statements generate most UNDOs because the database must store the entire record. Basically, for an INSERT rollback, a DELETE problem so that a DELETE rollback returns INSERT. UPDATE generates a UNDO variable amount because we need older versions of the modified columns; the more columns are changed and the larger they are, the greater the number of UNDOs will be.

Demonstration

In the first session, the user inserts many records into the temporary table, and then deletes them. In the second session, the DBA will track the use of the UNDO transaction.

 SSN1> insert into gtt23 2 select * from big_table 3 / 553928 rows created. SSN1> 

Cancel use:

 SSN2> select space, noundo, used_ublk, used_urec from v$transaction 2 / SPA NOU USED_UBLK USED_UREC --- --- ---------- ---------- NO NO 257 10816 SSN2> 

Now removal:

 SSN1> delete from gtt23 2 / 553928 rows deleted. SSN1> 

Cancel use (some examples while working with a large number of operators) ::

 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 a transaction scope, i.e. DELETE ROWS)

 SSN1> commit 2 / Commit complete. SSN1> 

Cancel use:

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

Using cancellation is cumulative:

 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> 

Cancel use

 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

Thus, to minimize the impact of the UNDO that the temporary table generates, make sure you insert the data you want once. Avoid applying updates to it and especially avoid deleting a large number of entries from them. If you are using a temporary table with a transaction scope, there really should not be a need to delete records from it. If your temporary table has a session duration and you need to clear it, it would be better to use TRUNCATE, if possible, rather than DELETE.

Answer 2

Found this on AskTom :

regular path inserts generate UNDO. they must, you must be capable of rollback, you must be capable of supporting multiple versions.

UNDO is always protected by repetition.

If you direct the path to the global temporary table (insert / * + APPEND * /) you can bypass the ON THE TABLE undo - but not by indexes. Consequently, you can reduce (usually the minimum indices that most generate the most undo) are the number of repetitions, but you cannot eliminate it.

Answer 3

I believe that you can also take advantage of NOLOGGING .

The append hint suggests that Oracle should use DIRECT-PATH operations, which can lead to faster inserts. If I remember correctly, you should have exclusive access to the table. It is important to insert after insertion so that you can select information from it.