- It is used to load flat files(fixed-width or delimited) into the database tables.
- Load from multiple data files into 1 or more tables in the same load session
Files Involved -
-Data File
-Control File
Info about the data to be loaded, how to parse and intrept the data and where to insert the data
INFILE clause is used to specify the data
-Bad File
Contains records rejected by SQL Loader or Oracle Database
-Discard File
Contains records that were not processed/filtered out because of the record selection criteria specified in the control file
Types of Load -
Conventional -
- It builts an array of rows and uses INSERT statemetn to load the data. Input record is parsed according to field specification and each data field is copied to corresponding bind array.
Use conventional load in the following cases
- Accessing a indexed table concurrently.
- Concurrent DML operations during the load
- Loading data in a clustered table
- Loading a few records into large table with indexes or referential/check constraint
Direct -
- Parses input data and converts the input data into column datatype and builds column array. Column array is passed to block formatter, which creates data blocks. The blocks are directly saved into extents. They write directly into the datafiles bypassing much of the data processing.
Parallel Direct Path -
- mutliple direct path loads sessions to concurrently load the same data segments
- Load from multiple data files into 1 or more tables in the same load session
Files Involved -
-Data File
-Control File
Info about the data to be loaded, how to parse and intrept the data and where to insert the data
INFILE clause is used to specify the data
-Bad File
Contains records rejected by SQL Loader or Oracle Database
-Discard File
Contains records that were not processed/filtered out because of the record selection criteria specified in the control file
Types of Load -
Conventional -
- It builts an array of rows and uses INSERT statemetn to load the data. Input record is parsed according to field specification and each data field is copied to corresponding bind array.
Use conventional load in the following cases
- Accessing a indexed table concurrently.
- Concurrent DML operations during the load
- Loading data in a clustered table
- Loading a few records into large table with indexes or referential/check constraint
Direct -
- Parses input data and converts the input data into column datatype and builds column array. Column array is passed to block formatter, which creates data blocks. The blocks are directly saved into extents. They write directly into the datafiles bypassing much of the data processing.
Parallel Direct Path -
- mutliple direct path loads sessions to concurrently load the same data segments
Comments