SQL*Loader

SQL*Loader
	Conventional path		Direct path
	·         Uses the database buffer cache		·         Avoid the database buffer cache
Method of Saving Data	Loads use SQL processing and a database COMMIT operation for saving data. The insertion of an array of records is followed by a COMMIT operation. Each data load may involve several transactions.		·         Use data saves to write blocks of data to Oracle data files.  ·         faster than the conventional path
The following features differentiate a data save from COMMIT: •         During a data save, only full database blocks are written to the database. •         The blocks are written after the high-water mark (HWM) of the table. •         After a data save, the HWM is moved. •         Internal resources are not released after a data save. •         A data save does not end the transaction. •         Indexes are not updated at each data save.
Logging Changes		generates undo and redo data (Always generates redo entries ) ------------------------------------------------ loading generates redo entries that are similar to any DML statement	No undo data is generated, Ability to switch off the redo Generates redo only under specific conditions
Enforcing Constraints		All enabled constraints are enforced in the same way that they are during any DML operation. Use INSERT statements (Enforces all constraints)	Table is locked for DML Only below constraints can be enforced; ·         UNIQUE ·         PRIMARY KEY ·         NOT NULL
Firing the INSERT Triggers		Fires INSERT triggers The WHILE INSERT triggers are fired during conventional loads	Does not fire INSERT triggers They are disabled before a direct path load and re enabled at the end of the load
Loading into Clustered Tables		Clustered tables can be loaded with conventional path loads only	Direct loads cannot be used to load rows into clustered tables
Locking		Allows other users to modify tables during load operation	Prevents other users from making changes to tables during load operation While a direct path load is in progress, other transactions cannot make changes to the tables that are being loaded. The only exception to this rule is when several parallel direct load sessions are used concurrently.