SQL: Difference between Conventional and Direct Path Loader

Hi,

Conventional path load uses the default ‘insert’ in SQL. It uses a bind array data into the database tables. When the SQL loader performs this kind of load, it competes at equal levels with all other processes for resources of the buffer. By default, the SQL Loader uses the conventional path load but is slow on loading. On the other hand, the direct path load uses API to pass the data to be loaded, in contrary to the conventional path that fills a bind array and passes it to the oracle server with the ‘insert’ command. In the direct path load, data conversion takes place on the client side rather than the server side as in the conventional path load. The differences between these two are:

• The conventional path loader loads data into a clustered table while the direct path load does not support this.
• The conventional path load allows loading of small number of rows into a large indexed table while the direct path load the current index is copied only when merged with new index keys.

The direct path load of a partitioned or sub-partitioned table can be useful when you have tables that have many partitions or sub partitions. Restrictions of the direct path load include:

• No clustered tables;
• No active transactions pending in the awaiting tables to be loaded;
• There is no loading of VARRAYS;
• No loading of parent and child table together;
• No loading of BFILE columns.