Oracle temporary table Undo Generation resolution

The redo generation of a temporary table is much less than that of a normal table, but undo is produced no less than a normal table.

A simple example illustrates:

Sql> Create global temporary table T_temp

2 (ID number, name VARCHAR2 (30))

3 on commit preserve rows;

Table has been created.

Sql> CREATE TABLE T_normal

2 (ID number, name VARCHAR2 (30));

Table has been created.

Sql> Select SID

2 from V$mystat

3 where rownum = 1;

Sid

----------

133

Sql> Select Value

2 from V$statname A, V$sesstat b

3 Where a.statistic# = b.statistic#

4 and A.name = ' undo change vector Size '

5 and B.sid = 133;

No rows selected

Sql> Select Value

2 from V$statname A, V$sesstat b

3 Where a.statistic# = b.statistic#

4 and A.name = ' undo change vector Size '

5 and B.sid = 133;

VALUE

----------

3988

sql> INSERT INTO T_normal

2 Select RowNum, object_name

3 from Dba_objects;

49081 lines have been created.

Sql> commit;

Submit completed.

Sql> Select Value

2 from V$statname A, V$sesstat b

3 Where a.statistic# = b.statistic#

4 and A.name = ' undo change vector Size '

5 and B.sid = 133;

VALUE

----------

135232

sql> INSERT INTO T_temp

2 Select RowNum, object_name

3 from Dba_objects;

49081 lines have been created.

Sql> commit;

Submit completed.

Sql> Select Value

2 from V$statname A, V$sesstat b

3 Where a.statistic# = b.statistic#

4 and A.name = ' undo change vector Size '

5 and B.sid = 133;

VALUE

----------

254240

Sql> Select 254240-135232 temp_table, 135232-3988 normal_table from dual;

Temp_table normal_table

---------- ------------

119008 131244

This column more highlights: http://www.bianceng.cn/database/Oracle/

As you can see, there is not much difference between the undo data produced by the temporary table and the normal table, and the redo information that is actually inserted by the temporary table is also the redo of the undo information.

sql> Insert/*+ append/into T_temp

2 SELECT *

3 from T_temp;

49081 lines have been created.

Sql> commit;

Submit completed.

Sql> Select Value

2 from V$statname A, V$sesstat b

3 Where a.statistic# = b.statistic#

4 and A.name = ' undo change vector Size '

5 and B.sid = 133;

VALUE

----------

254408

sql> Insert/*+ append/into T_normal

2 SELECT *

3 from T_normal;

49081 lines have been created.

Sql> commit;

Submit completed.

Sql> Select Value

2 from V$statname A, V$sesstat b

3 Where a.statistic# = b.statistic#

4 and A.name = ' undo change vector Size '

5 and B.sid = 133;

VALUE

----------

256468

Sql> Select 254408-254240 temp_table, 256468-254408 normal_table from dual;

Temp_table normal_table

---------- ------------

168 2060

For append inserts, both normal and temporary tables produce a small amount of undo, while temporary tables are relatively less.