Hi all!
I have a question regarding SQL *Loader. I want to know the difference between Conventional and Direct Path Loader. And also, what are the restrictions in the direct path loader?
Can you give a statement, so I can fully understand the logic of the differences between the two?
Thanks in advance.
Answered By
Jenny3
0 points
N/A
#99489
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.
SQL: Difference between Conventional and Direct Path Loader
The Conventional path loader loads data by using standard INSERT statement. You put data from disk into the buffer cache and rows are transferred to the evaluating buffer.
Direct path loader bypasses the logic and leads directly to the Oracle data files. It means that SQL command-processing layer can be bypassed. You will have clear data without data conversion.Â
If we talk about speed, conventional path is slower than direct path because direct path reads data directly from disc.
This actually means that you don't get any redo created but you need to do a backup after your load is complete because you don't want to lose any data.
Some of the restrictions with direct path loads are:
– There will be no replicated data
– Can't always use SQL strings for column processing in the control fileÂ
You can find more information.
