Oracle SQL Loader - Conventional Path vs. Direct Path

Oracle SQL Loader: Conventional Path vs.
Direct Path
SQL Loader: Conventional Path vs. Direct Path

Conventional Path Direct Path
SQL*Loader uses the SQL INSERT statement and bind It passes on the data to the Load Engine of the database,
array buffer to load data. which creates a Column Array structure.
Makes use of the Database buffer cache and may increase Avoids buffer cache and writes directly to the Disk.
contention for the resources among other users. Can make use of Asynchronous I/O if available/supported
on the OS.
Slower since the SQL INSERT statements have to be Faster since, the Load Engine converts the column array
generated, passed to Oracle, and executed. structure directly to Oracle Data Blocks and adds it to the
Table’s existing segment.
While loading the data, searches for blocks with enough Does not search the existing blocks. New blocks are
free space, into which the rows can be inserted. formatted and added to the existing segment.
Does not lock the table being loaded into. Locks table in Exclusive mode. Hence, should not be used
if concurrent access to the table is required during the load.
The 10046 trace output shows:
‘Lock table <table_name> exclusive mode nowait;’
Can be used to load into Clustered tables Cannot be used to load into a cluster.
Check constraints are enabled during the load. Records not The constraints are disabled during the load. It explicitly
satisfying the constraint are rejected and written into the executes an
BAD file. ‘alter table <table_name> disable constraint
<constraint_name>’
statement before loading into the table.
Can be used to load into Varrays Cannot be used to load into Varrays
Can be used to load into BFILE columns. Cannot be used to load into BFILE columns.
Can be used to load into a Single partition of a table having Direct path cannot be used to load into a particular partition
Global indexes of the table if the table has a global index defined on it.
Cannot be used for loading data in Parallel. But, you can Parallel loading of data is possible.
use multiple load session concurrently inserting into the
same table.
Automatically inserts default values for the columns, if any. The default value specified for the column is not inserted.
If it is a ‘null’ column, it inserts a null.
Unique Index on the table is in a valid state after the load. The Uniqueness of the data is not validated. The unique
The uniqueness of the data for the index column is index is in an ‘UNUSABLE’ state at the end of the load.
maintained. Records violating the uniqueness are rejected
and written into the BAD file.
If the table has any indexes, corresponding keys are added After each block is formatted, the new index keys are put in
Indexes
into the index for each new row inserted into the table. a sort (temporary) segment. The old index and the new
keys are merged at load finish time to create the new index.
The index does not require a re-build at the end of the load. The index needs to be re-built at the end of the load. The
Also, no extra storage is required. But, since the index is old index, new index and sort segment all require storage
updates for each new row, it increases the processing time. space until the indexes are merged.
If the type has a User-defined constructor matching the Direct path calls the Argument-value constructor.
arguments of the attribute-value constructor, conventional
path calls the User-defined constructor.
Loading into If the type has a User-defined constructor not matching the It is not possible to invoke the user-defined constructor in
Objects arguments of the attribute-value constructor, you can direct path loading.
invoke the user-defined using a SQL Expression.
Can be used to load into Parent and child table at the same Cannot be used to load into Parent and child table at the
time. same time
The important thing to remember about direct path load is that it is fast but has restrictions, including the following:
 Indexes are rebuilt at the end of a direct path load. If unique constraint violations are found, the unique index
is left in an unusable state. To correct the index, you must find and remove the constraint violations and then
rebuild the index.
 Direct path load cannot occur if there are active transactions against the table being loaded.
 Triggers do not fire during direct path loads.
 Direct path loading into clustered tables is not supported.
 During direct path loads, foreign key constraints are disabled at the beginning of the load and then re-enabled
after the load.
 Only primary key, unique, and NOT NULL constraints are enforced.
 Direct path load prevents other users from making changes to the table while the direct load operation is in
progress.

Oracle SQL Loader - Conventional Path vs. Direct Path

Uploaded by

Copyright:

Available Formats

Oracle SQL Loader - Conventional Path vs. Direct Path

Uploaded by

Document Information

Original Description:

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Oracle SQL Loader - Conventional Path vs. Direct Path

Uploaded by

Copyright:

Available Formats

Oracle SQL Loader: Conventional Path vs.

SQL Loader: Conventional Path vs. Direct Path

array buffer to load data. which creates a Column Array structure.

Table’s existing segment.

if concurrent access to the table is required during the load.

The 10046 trace output shows:

‘Lock table <table_name> exclusive mode nowait;’

BAD file. ‘alter table <table_name> disable constraint

statement before loading into the table.

If it is a ‘null’ column, it inserts a null.

maintained. Records violating the uniqueness are rejected

and written into the BAD file.

arguments of the attribute-value constructor, conventional

path calls the User-defined constructor.

invoke the user-defined using a SQL Expression.

time. same time

You might also like