Oracle Sql*loader Detailed (1) overview

Sql*loader principle

Sql*loader is a tool provided by Oracle for data loading, it is more suitable for business analysis type database (Data Warehouse), can handle multiple format flat files, bulk data loading is more efficient than traditional data insertion. The schematic diagram is as follows:

Control file (. ctl): A file (the most important file) used to control how data is imported

Parameter file (optional) (Parameter file) (. Par): You can write parameters directly in the control file, or you can write a separate parameter file

Data files: Flat files that place source data (you can import multiple data files at once)

Bad file (. Wicked): Put data that cannot be loaded correctly into an error file (for example, data format, data type problem, etc.) when data is loaded

Discard file (optional) (discard file) (. DSC): Some data, although the data format, data type is not a problem, but it is filtered out by the logical conditions (by the control file when set), will be put into the lost file

Log file (. log): Record Sql*loader Data loading process

Sql*loader Data Loading method

Sql*loader supports 3 kinds of data loading methods, respectively:

Traditional path Loading (Direct=false): equivalent to insert statement

Direct path Loading (direct=true): Bypass the SGA, direct the data directly into the high watermark (HWM) above, can set up parallel loading, performance is higher than the traditional path loading, but also more restrictive

External table loading (less): first create an external table on the data file, and then insert the data from the external table into the destination table

Record format for data files

From a sql*loader point of view, data in the data file is organized into records, grouped into the following three record formats (set by the infile parameter of the control file):

Fixed-length record format

This approach is the least flexible, but the performance is best, and its syntax is as follows:

INFILE datafile_name "Fix n"

Indicates that each record in the data file has a fixed length of n bytes.

Variable length record format

INFILE "Datafile_name" "var n"

The variable length record is divided into two parts: the length of the record and the record itself, see the following example: