Compare the amount of redo produced by append insert data

--Version InformationSELECT *  fromv$version;oracleDatabase10g Enterprise Edition Release10.2.0.5.0 -PRODPL/SQL Release10.2.0.5.0 -Productioncore10.2.0.5.0Productiontns for  +-bitWindows:version10.2.0.5.0 -Productionnlsrtl Version10.2.0.5.0 -Production--See if archivingSelectName,log_mode fromv$Database;1ORCL Noarchivelog or sqlplus:archiveLOGLIST;--Create a view that produces a redo sizeCreate or Replace ViewRedo_size asSelectvalue fromV$mystat, V$statnamewherev$mystat.statistic#=v$statname.statistic# andV$statname.name= 'Redo Size';

1. In non-archive mode :

--!!!!!!!!!!! Redo!!!!!!!!!!!!!!!!!! generated by non-archive mode--==============nologging Table ================SELECT *  fromsys.redo_size;--redo_size:0Create TableTest_nolog nologging as Select *  fromDba_objectswhere 1=0;--redo_size:17932Insert  intoTest_nologSelect *  fromdba_objects;--redo_size:5772780Insert /*+ APPEND*/   intoTest_nologSelect *  fromdba_objects;--redo_size:5782548Select(5782548-5772780) Redo_append, (5772780-17932) Redo_normal fromdual;9768    5754848--================logging============================--redo_size:11778596Create TableTest_log as Select *  fromDba_objectswhere 1=0;--redo_size:11799284Insert  intoTest_logSelect *  fromdba_objects;--redo_size:17555812Insert /*+ APPEND*/   intoTest_logSelect *  fromdba_objects;--redo_size:17565544Select(17565544-17555812) Redo_append, (17555812-11799284) Redo_normal fromdual;9732    5756528

Conclusion: non-archiving mode, just append can reduce the production of redo, if not add append, even the nologing table will produce as much redo;

2. In archive mode :

--Modify to archive modeSetOracle_sid=Orclsqlplus/  asSysdbasys@ORCL>ArchiveLogList Database log mode non-archive mode AutoArchive disable archive endpoint use_db_recovery_file_dest oldest online log sequence544Current log sequence546SYS@ORCL> SelectLog_mode fromv$Database; Log_mode------------Noarchivelogsys@ORCL> shutdownThe immediate database is closed. The database has been uninstalled. The ORACLE routine has been closed. SYS@ORCL>The startup Mountoracle routine has been started. Total System Global Area1258291200bytesfixed Size1304848bytesvariable Size201328368bytesDatabaseBuffers1048576000Bytesredo Buffers7081984The bytes database is loaded. SYS@ORCL> Alter DatabaseARCHIVELOG; The database has changed. SYS@ORCL>ArchiveLogList database log mode archive Mode AutoArchive enable archive endpoint use_db_recovery_file_dest oldest online log sequence544next archived log sequence546Current log sequence546SYS@ORCL> Alter Database Open; the database has changed. 

--!!!!!!!!!!! Redo!!!!!!!!!!!!!!!!!! generated by archive mode--==============nologging Table ================SELECT *  fromredo_size;--redo_size:0Insert  intoTest_nologSelect *  fromdba_objects;--redo_size:5729772Insert /*+ APPEND*/   intoTest_nologSelect *  fromdba_objects;--redo_size:5739436Select(5739436-5729772) Redo_append, (5729772-0) Redo_normal fromdual;9664  5729772--================loging============================--redo_size:5729772Insert  intoTest_logSelect *  fromdba_objects;--redo_size:11355620Insert /*+ APPEND*/   intoTest_logSelect *  fromdba_objects;--redo_size:17123736Select(17123736-11355620) Redo_append, (11355620-5729772) Redo_normal fromdual;5768116  5625848

Conclusion: archive mode, append and table for nologging to reduce the production of redo, the rest of the situation has no effect;

Compare the amount of redo produced by append insert data