Difference between Direct Path Load and Conventional Load
So to begin, SQL*Loader provides 2 methods for loading data,
- Conventional Path Load (This is by default used)
- Direct Path load
Note:
1) Before performing load, You must be having INSERT privilege on the table to be loaded. Also you must be having DELETE privilege if you are also performing REPLACE or TRUNCATE to empty old data from the table.
2) SQL*Loader never creates tables so the table to be loaded should be already created inside the database.
Understanding Conventional path Load:
A conventional path load uses SQL INSERT statements & bind array buffer to load data in Oracle database. This method is by default used, and hence SQL*Loader has to fight for buffer (SGA memory) resources in order to proceed with the task. Statements are executed once SQL statements are generated and passed to Oracle.
During the conventional path load of a Single partition, SQL*Loader uses the partition-extended SYNTAX as shown below,
INSERT INTO TABLE T PARTITION (P) VALUES ...
When to Use a Conventional Path Load (Copied from Oracle Doc)
When accessing an indexed table concurrently with the load, or when applying inserts or updates to a non-indexed table concurrently with the load.
When loading data into a clustered table.
When loading a relatively small number of rows into a large indexed table.
When loading a relatively small number of rows into a large table with referential and column-check integrity constraints.
When loading records and you want to ensure that a record is rejected under any of the following circumstances:
- If the record, upon insertion, causes an Oracle error.
- If the record is formatted incorrectly, so that SQL*Loader cannot find field boundaries.
- If the record violates a constraint or tries to make a unique index non-unique.
Understanding Direct path Load:
Rather than using a bind array buffer & passing it to the Oracle database with a SQL INSERT statement, a direct path load uses the direct path API to pass the data to be loaded to the load engine in the server. The load engine then uses that passed data to build a column array structure which is further used to format Oracle data blocks and build index keys. The newly formatted database blocks are written directly to the database. The writes can be performed asynchronously to increase I/O load performance if the HOST platform support it.
Advantages of a Direct Path Load (Copied from Oracle Doc)
A direct path load is faster than the conventional path for the following reasons:
Partial blocks are not used, so no reads are needed to find them, and fewer writes are performed.
SQL*Loader need not execute any SQL INSERT statements; therefore, the processing load on the Oracle database is reduced.
A direct path load calls on Oracle to lock tables and indexes at the start of the load and releases them when the load is finished. A conventional path load calls Oracle once for each array of rows to process a SQL INSERT statement.
A direct path load uses multi-block asynchronous I/O for writes to the database files.
During a direct path load, processes perform their own write I/O, instead of using Oracle's buffer cache. This minimizes contention with other Oracle users.
The sorted indexes option available during direct path loads enables you to presort data using high-performance sort routines that are native to your system or installation.
When a table to be loaded is empty, the presorting option eliminates the sort and merge phases of index-building. The index is filled in as data arrives.
Protection against instance failure does not require redo log file entries during direct path loads. Therefore, no time is required to log the load when:
- The Oracle database has the SQL NOARCHIVELOG parameter enabled
- The SQL*Loader UNRECOVERABLE clause is enabled
- The object being loaded has the SQL NOLOGGING parameter set
Data Conversion During Direct Path Loads
During a direct path load, data conversion happens on the client side rather than on the server side. So in this case, NLS parameters defined in the initialization parameter file (DB server) will not be come in role. To fix this, you have 2 options,
1) You can specify a format mask in the SQL*Loader control file that is equivalent to the setting of the NLS parameter in the initialization parameter file. For example, you can set the date format as shown below in the SQL*LOADER control file,
LOAD DATA
INFILE 'loaddata.dat'
INSERT INTO TABLE test
FIELDS TERMINATED BY "|"
(
ID NUMBER(4) NOT NULL,
NAME CHAR(10),
JDATE DATE 'YYYYMMDD',
)
2) Set the appropriate environment variable as shown below,
% NLS_DATE_FORMAT='YYYYMMDD'
% export NLS_DATE_FORMAT
No comments:
Post a Comment