Use of the sql*loader of the [Oracle] Common toolset

Sql*loader is an Oracle-provided tool for data loading, which is more suitable for business analysis type databases (data warehouses), can handle multiple format flat files, and bulk data loading is more efficient than traditional data insertion

The schematic diagram is as follows:

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

Parameter file (optional)

Data files: Flat file where source data is placed

Error files: Putting data that cannot be loaded correctly into an error file (for example, data format problems, data type problems, etc.) when the data is loaded

Discard file (optional): Some data, although there is no problem with data format, data type, but it is filtered out by logical conditions (set in the control file), will be put into the lost file

Log files: Record the Sql*loader data loading process

Direct Path Loading
In Sql*loader, direct path loading (set direct = TRUE) is generally used to improve performance.

Oracle provides two types of INSERT statements: Conventional Insert (conventional insert) and direct path insertion (Direct-path insert), where direct path insertion at the expense of part of the functionality in exchange for efficient loading of large amounts of data.

Direct path loading inserts the data directly into the high water level (HWM) above the segment to be modified, this generates the smallest undo (only the undo of the data dictionary is generated, not the undo of the data in the block), and does not pass through the cache (SGA), so its performance is better than regular inserts.

Direct path loading restrictions include:
1 A table can only have a direct path insertion, so it is not suitable for small data inserts, only for large quantities of data loading;
2 The free space under the HWM will not be exploited;
3) A table cannot do anything with the same session while doing direct path insertion (select cannot);
4 only Insert Inot ... select ... Statements, merge statements, and applications that use the OCI direct path interface are available.