Monday, January 13, 2014

/*+ APPEND */ hint in serial & parallel mode in oracle.

/*+ APPEND */ hint enables the optimizer to use direct-path insert particularly in insert statements in oracle

In serial mode, direct path can be used only if you include the APPEND hint.
Conventional insert is the default in serial mode. 
It means that NO UNDO will be written. 
UNDO is backed by REDO, so the REDO volume will be accordingly reduced.

Direct path insert is the default in parallel mode. 
In parallel mode, conventional insert can be used only if you specify the /*+ NOAPPEND */ hint.
data is appended to the end of the table,rather than using existing space currently allocated to the table, means after HWM of the current block. 

Direct-path inserts are considerably faster than conventional inserts.