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.