This atrticle explains how Advanced ETL Processor and Visual Importer load data into Oracle  Database.

There are two ways to load Data into Oracle                                                                                                    

• Conventional path load
• Direct path load
  
  
  
• 
  

 Conventional path load

A conventional path load executes SQL INSERT statements to populate tables in an Oracle database.

When Advanced ETL Processor or Visual Importer performs a conventional path load, it competes equally with all other processes for buffer resources,

which can significantly slow the load.

Let's consider loading data into customers table using conventional path loading.

Some of the data is already inserted and there are dupilcates in our source data.  

• insert into customers
• (customer_id,customer_name)
• values
• (:customer_id,:customer_name)

 What would happen if primary key is violated? 

If we run all our inserts within one transaction we won't be able to load any data

• lots of inserts
• commit

or we can insert one record at the time committing after every insert 

• insert one record
  
• commit
• insert one record
  
• commit
• ETC

This approach allows us to load as much as we can, however there is a problem with that it is slow because it involves a lot of OCI calls and consumes a lot of server resources.

We use slightly different approach at DB Software Laboratory.

We build an array of records in memory than insert entire array in one go.

• insert 100 records
• commit
• insert 100 records
• commit
• insert 100 records
• commit

if insert fails OCI lets us know that record number 52 within the array violates primary key.

• our insert is rolled back
• insert records from 1 till 51
• commit
• insert records from 53 till 100
• commit

If second insert failed we attempt to insert records one by one

• our insert is rolled back
• insert record 53
• commit
• insert record 54
• commit
• ...
• Insert record 100
• commit

Once we inserted 100 records one by one, we build array and try to insert entire array again

 Direct path load 

A direct path load eliminates much of the database overhead by formatting Oracle data blocks and

writing the data blocks directly to the database files.

It does not compete with other users for database resources,

so it can usually load data at near disk speed.

There are several restrictions to using direct path load

During a direct path load

•  CHECK constraints,
•  Referential constraints (FOREIGN KEYS),
•  Insert triggers

are automatically disabled

and

•  NOT NULL
•  UNIQUE
•  PRIMARY KEY (unique-constraints on not-null columns)

enabled 

 
Note:

• NOT NULL constraints are checked at column array build time.
• Any row that violates the NOT NULL constraint is rejected.
• UNIQUE constraints are verified when indexes are rebuilt at the end of the load.
• The index will be left in an Index Unusable state if a violation of a UNIQUE constraint is detected.