Use SQL Loader to import a large amount of data to avoid frequent SQL writing.

Currently, You need to retrieve some fields from a data file recorded by row, and store these fields to the Oracle database by row. There are about 700 million data records and about m of raw data files. There are at least two ways to achieve this:

1. Read the original data file into the memory stream, parse a piece of data for each row, and then execute an SQL statement to insert the parsed data into the database table;

II .. read the original data file into the memory stream, parse a data record for each row, and write the parsed data into a temporary file data by row. tmp. After all data is parsed and written to a temporary file, SQL Loader is called to import all data from the temporary file to the database table at one time.

The first method is used for programming. It took about 150 minutes to import, parse, and complete the test on the local machine (Oracle server installed on the local machine). Problems occurred when deploying to the actual environment. It is found that the network is unstable (the server where the program is located is in a different LAN from the database server, which is separated by a firewall and routes several specific ports), and sometimes the database cannot be connected, when only a few thousands or even hundreds of data entries are imported, the database cannot be connected.

Later, we analyzed that using the first method for importing a large amount of data will lead to frequent database writes and the database should be opened every time (the connection pool is disabled, and the reason for disabling will be mentioned in subsequent articles ), it puts a lot of pressure on the database. In addition, the network environment is not stable enough, so it is easy to cause problems. So use the second method.

After the second method is used, the resolution takes about 90 minutes, and the import takes about 5 minutes. It seems that SQL Loader is very efficient.

The following describes the basic usage of SQL Loader.

1. Create an empty table in Oracle according to the imported data format

2. Compile a control file control. ctl with the following content:

LOAD DATA
INFILE 'e: \ test \ data. tmp'
BADFILE 'e: \ test \ data. bad'
DISCARDFILE 'e: \ test \ data. dsc'
DISCARDMAX 1000

APPEND
Into table "TB_TEST"
Fields terminated by '|'
TRAILING NULLCOLS
(
USER_ID,
USER_NAME,
REG_TIME DATE (20) "YYYY-MM-DD HH24: MI: SS"
)

Operation Type:

A. insert, which is the default mode. The table is required to be empty when data loading starts.
B. append: Add a new record to the table
C. replace: delete old records and replace them with newly loaded records.
D. truncate, same as above

3. Execute the following statement in the command line:

Sqlldr userid/password @ database e: \ test \ control. ctl

This article will introduce how to use SQL Loader in a program.