Overview

This test is conducted to check the amount of Redo generated depending on the presence or absence of the Append hint during Insert operations, in order to reduce the load in the production environment.

Method

Test Scenario

ALL_OBJECTS data is loaded into the test table while changing the table's LOGGING option and the APPEND hint respectively, performing INSERT operations and checking the amount of Redo generated. Additionally, the test is executed identically in both NOARCHIVELOG mode and ARCHIVELOG mode to compare the results.

Creating the Test Table

CREATE TABLE TEST_TABLE AS
SELECT *
FROM ALL_OBJECTS
WHERE 1 = 0;

Data Insert Test

Table logging, without using append hint

ALTER TABLE TEST_TABLE LOGGING;
INSERT INTO TEST_TABLE
SELECT * FROM ALL_OBJECTS;

Table logging, using append hint

TRUNCATE TABLE TEST_TABLE;
ALTER TABLE TEST_TABLE LOGGING;
INSERT /*+ APPEND */ INTO TEST_TABLE 
SELECT * FROM ALL_OBJECTS;

Table nologging, using append hint

TRUNCATE TABLE TEST_TABLE;
ALTER TABLE TEST_TABLE NOLOGGING;
INSERT /*+ APPEND */ INTO TEST_TABLE 
SELECT * FROM ALL_OBJECTS;

Table nologging, without using append hint

TRUNCATE TABLE TEST_TABLE;
ALTER TABLE TEST_TABLE NOLOGGING;
INSERT INTO TEST_TABLE 
SELECT * FROM ALL_OBJECTS;

Test Results

 -- Query to measure redo generation
 select * from v$sysstat where name like 'redo log size';

Conclusion

• NOARCHIVELOG mode recommends using the APPEND hint during INSERT to reduce Redo generation.

• ARCHIVELOG mode recommends changing the table to NOLOGGING and using the APPEND hint together.

• However, note that the INSERT INTO ... VALUES syntax does not support the APPEND hint, so caution is required when applying it.