INSERT SQL with /*+Append*/ Hint    Oct 11. 2011

if you write the following statement

INSERT /*+ Append*/ into dept values (60,'COMPUTING','AMMAN');

What will happen

1) If your tablespace is created with NOLOGGING, then the ROWS inserted will be appended to the end of the TABLE and will  NOT be logged to the REDO LOG files.  This means that if you are operating in ARCHIVE mode, and at one point,  you need to recover your database, the APPENDED rows will not be recovered and you will need to add them again. This scenario is similar to Direct PATH LOAD (ie SQL*Loader). However, if the tablespace is created with LOGGING, then the APPEND will be ignored

2) Buffers are bypassed

3) referential integrity is ignored

4) Oracle obtains an exclusive lock on the TABLE, therefore, other users cannot perform DML operations to the table

5) Since APPEND really appends data to the end of the table (after the High Water Mark) , Oracle does not utilize the existing space in the Free list.

Consequently, one should note an increase in performance.

Caution:  As mentioned above, the INSERT Append operation does not log the data into the REDO logs. Therefore, you may want to consider taking a full backup after such operations.