A conventional path load executes SQL INSERT statements to populate tables in an Oracle database. 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.To start SQL*Loader in direct path load mode, set the DIRECT parameter to true on the command line or in the parameter file, if used, in the format:
DIRECT=true
By default, the loading method is Conventional.
The script, which creates the views used by the Direct Path, is: catldr.sql (OH\rdbms\admin)
(This script is run by catalog.sql, by default).
Conventional Path | Direct Path |
SQL*Loader uses the SQL INSERT statement and bind array buffer to load data. | It passes on the data to the Load Engine of the database, which creates a Column Array structure. |
Makes use of the Database buffer cache and may increase contention for the resources among other users. | Avoids buffer cache and writes directly to the Disk. Can make use of Asynchronous I/O if available/supported on the OS. |
Slower since the SQL INSERT statements have to be generated, passed to Oracle, and executed. | Faster since, the Load Engine converts the column array structure directly to Oracle Data Blocks and adds it to the Table’s existing segment. |
While loading the data, searches for blocks with enough free space, into which the rows can be inserted. | Does not search the existing blocks. New blocks are 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 satisfying the constraint are rejected and written into the BAD file. | The constraints are disabled during the load. It explicitly executes an ‘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 Global indexes | Direct path cannot be used to load into a particular partition of the table if the table has a global index defined on it. |
Cannot be used for loading data in Parallel. But, you can use multiple load session concurrently inserting into the same table. | Parallel loading of data is possible. |
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. |
Indexes | |
Unique Index on the table is in a valid state after the load. The uniqueness of the data for the index column is maintained. Records violating the uniqueness are rejected and written into the BAD file. | The Uniqueness of the data is not validated. The unique index is in an ‘UNUSABLE’ state at the end of the load. |
If the table has any indexes, corresponding keys are added into the index for each new row inserted into the table. | After each block is formatted, the new index keys are put in 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. Also, no extra storage is required. But, since the index is updates for each new row, it increases the processing time. | The index needs to be re-built at the end of the load. The old index, new index and sort segment all require storage space until the indexes are merged. |
Loading into Objects | |
If the type has a User-defined constructor matching the arguments of the attribute-value constructor, conventional path calls the User-defined constructor. | Direct path calls the Argument-value constructor. |
If the type has a User-defined constructor not matching the arguments of the attribute-value constructor, you can invoke the user-defined using an SQL Expression. | It is not possible to invoke the user-defined constructor in direct path loading. |
Can be used to load into Parent and child table at the same time. | Cannot be used to load into Parent and child table at the same time |
No comments:
Post a Comment