The undo information that is generated by inserting data into the nologging table and the logging table in Oracle non-archive mode is more __oracle

First look at my own experimental environment is in what kind of archiving mode:
Sql> Select T.log_mode from V$database t; Log_mode------------Noarchivelog
Two. After determining that it is in the non-archive mode, I first create a regular table t_test_nologging
1. Create a table, the so-called general is in the creation process does not specify this table is nologging
Sql> CREATE TABLE t_test_nologging as SELECT * from T_down_log where 1=0;   Table created sql> Select COUNT (*) from T_down_log; COUNT (*)----------660618
2. Routinely insert t_test_nologging all T_down_log records sql> SELECT a.name,round (b.value/1024, 3) | | KB ' current undo size (in kilobytes) "2 from V$statname A, V$mystat B 3 WHERE a.statistic# = b.statistic# 4 and lower (a.name) like '%' || Lower (' Redo size ') | | '%'; NAME cur_size_kb------------------------------------------------------------------------------- ----Redo Size 38884.543KB
--Insert T_down_log all records sql> INSERT INTO t_test_nologging select * from T_down_log; 660618 rows inserted sql> commit; Commit complete--computes the undo size again sql> SELECT a.name,round (b.value/1024, 3) | | KB ' current undo size (in kilobytes) "2 from V$statname A, V$mystat B 3 WHERE a.statistic# = b.statistic# 4 and lower (a.name) like '%' || Lower (' Redo size ') | | '%';                  NAME cur_size_kb---------------------------------------------------------------------Redo SIZE           77660.121KB sql> Select 77660.121-38884.543 from dual; 77660.121-38884.543------------------- 38775.578
The way the above is inserted creates a 38775.578KB undo.

--sql> Insert/*+ append/into the t_test_nologging select * from T_down_log when inserted in the Insert Append mode; 660618 rows inserted sql> commit; Commit Complete sql> SELECT a.name,round (b.value/1024, 3) | | KB ' current undo size (in kilobytes) "2 from V$statname A, V$mystat B 3 WHERE a.statistic# = b.statistic# 4 and lower (a.name) like '%' || Lower (' Redo size ') | | '%'; NAME cur_size_kb-----------------------------------------------------Redo SIZE 77771.629KB sql& Gt Select 77771.629-77660.121 from dual; 77771.629-77660.121-------------------111.508 with insert/*+ append/into but only 111.508KB undo table space is generated
It can be concluded that the undo data produced when inserting data into a non-nologging table in the form of insert/*+ append/into in a non-archival mode is less than inserted directly into the form.


Three. After the non-archive mode, we set the table created above to the Nologging table
sql> ALTER TABLE t_test_nologging nologging; Table altered sql> sql> SELECT a.name,round (b.value/1024, 3) | | KB ' Current undo size (in kilobytes) "  2  from V$statname A, V$mystat b   3  WHERE a.statistic# = b.statistic#   4  and Lower (a.name) like '% ' | | Lower (' Redo size ') | | '%'; name                Current undo size (in kilobytes)-- ---------------------------------------Redo size             77773.738KB sql> INSERT INTO t_test_nologging select * from T_down_log; 660618 rows inserted sql> commit; Commit complete sql> sql> SELECT a.name,round (b.value/1024, 3) | | KB ' Current undo size (in kilobytes) "  2  from V$statname A, V$mystat b   3  WHERE a.statistic# = b.statistic#   4  and Lower (a.name) like '% ' | | Lower (' Redo size ') | | '%'; name                        Current undo size (in kilobytes)----------------------------------------------- ------------------Redo size                      116393.234KB sql> Select 116393.234-  77773.738 | | ' KB ' from dual; 116393.234-77773.738| | ' KB '--------------------------38619.496KB sql> insert/*+ append/into t_test_nologging select * from T_down_log; 660618 rows inserted sql> sql> SELECT a.name,round (b.value/1024, 3) | | KB ' Current undo size (in kilobytes) "  2  from V$statname A, V$mystat b   3  WHERE a.statistic# = b.statistic#   4  and Lower (a.name) like '% ' | | Lower (' Redo size ') | | '%'; name                                                               Current undo size (in kilobytes)------------------------------------------------------------------------------------------------ ----------Redo size                                                           116417.566KB sql> commit; Commit complete sql> Select 116417.566-116393.234 | | ' KB ' from dual;

116417.566-116393.234| | ' KB '
---------------------------
24.332KB
The above experiment shows that although I changed the table to nologging mode, it was only possible to reduce the resulting undo by the way of insert/*+ append/into.
Four. Directly t_test_nologging, and then build a new name also for t_test_nologging nologging table
sql> drop table t_test_nologging purge; Table dropped sql> CREATE TABLE t_test_nologging nologging as SELECT * from t_down_log where 1 = 0; Table created sql> SELECT a.name,round (b.value/1024, 3) | | KB ' Current undo size (in kilobytes) "  2  from V$statname A, V$mystat b   3  WHERE a.statistic# = b.statistic#   4  and Lower (a.name) like '% ' | | Lower (' Redo size ') | | '%'; name                             Current undo size (in kilobytes)------------------------------------ ----------------------------Redo size                 &n