Conventional and Direct Path Loads in SQL Loader OR General INSERT Statement

SQL*Loader provides two methods for loading data:

Note: These 2 methods are applicable for normal insert into statement as well.

• conventional path load

• direct path load

Direct path loads can be significantly faster than conventional path loads.

Database Writes on SQL*Loader Direct Path and Conventional Path

In above pic, we can observe that direct path is bypassing "SQL Command Processing" Step. It means it will directly write data to data blocks.

A direct path load eliminates much of the Oracle database overhead by formatting Oracle data blocks and writing the data blocks directly to the database files. A direct load does not compete with other users for database resources, so it can usually load data at near disk speed. 

Conventional path load (the default) uses the SQL INSERT statement and a bind array buffer to load data into database tables. This method is used by all Oracle tools and applications.

When SQL*Loader performs a conventional path load, it competes equally with all other processes for buffer resources. This can slow the load significantly. Extra overhead is added as SQL statements are generated, passed to Oracle, and executed.

Direct Path basically wont re use free space in the table(eg: space which available after deleting records) and it wont write data to Database buffer cache and data will  directly go to data files and appends after existing data.

But in Conventional Path , It re used free space and first it goes to buffer cache and then it goes to datafiles ans appends wherever free space left.

By default SQL Loader will take Conventional path so to Run sql loader in direct path load, we need to set "DIRECT=TRUE"  in command prompt while running sql loader or you can set it in par file if you're using parameter file.