•During conventional insert operations, Oracle reuses free space in the table During such operations, Oracle also maintains referential integrity constraints.
Eg : INSERT INTO TABLE T
•During direct-path INSERT operations, Oracle appends the inserted data after existing data in the table. Data is written directly into datafiles, bypassing the buffer cache. Free space in the existing data is not reused, and referential integrity constraints are ignored. These procedures combined can enhance performance.
Eg : INSERT /*+ append */ INTO TABLE T
When we insert a rows into a data block, Oracle has the choice of re-using a data block from the freelist or grabbing a "brand new" empty data block, extending the table into the tablespace and raising the high water mark (HWM), usually in chunks of five data blocks
Direct path inserts - These are performed with blocks above the table high water mark
Append - Append tells Oracle to extend the table and grab new, empty data blocks for the insert
Existing free space is not re-used. So, if you direct load insert a bunch of rows, then delete them, and insert them again, the space made by the delete will not be re-used. •Referential Integrity (Foreign Key) constraints and triggers must be disabled before running Direct Path Insert
To make sure your SQL is using Direct Load Insert, run it through Explain Plan. You should see a line reading LOAD AS SELECT.
•Direct Load Insert cannot occur on: ◦Index Organised Tables ◦Tables with LOB Columns ◦Tables with Object Columns ◦Clustered Tables
Direct Load Insert is a faster way of running an INSERT statement. It is particularly useful for inserting large numbers of rows ----
