Use default values to import data from Oracle sqlldr and improve performance

In practice, a large amount of data often needs to be imported to the current database table. The sqlldr Command provided by Oracle has a good meet requirement. During the use process, we found that if the default value is set in the Column on the table end of the database, but sqlldr does not work, that is, the default value is not set successfully, so when you need to set the default value, we will write the default value to the ctl file. The ctl file is as follows:

Load data
Infile 'd: \ temp \ buser.txt'
Append
Into table tmp_temp
TRAILING NULLCOLS
(
Phonenumber,
Addtime sysdate,
Remark "suit"
)

When importing a large amount of data, I, sqlldr is often unsatisfactory, and there are efficiency problems during the import. You can use several methods to improve the speed of sqlldr.

1. Use direct = Y, which is the fastest way to increase speed
2. Use the large readsize/streamsize to increase the buffer size for read/write data.
The maximum value of readsize is 21971520 (20 M). The maximum value of streamszie is unclear. I usually set it to 21971520.
3. Use a large bindsize (only valid for the conventional path load) to increase the data volume submitted at a time.
4. The maximum value of columnarrayrows. Oracle9i is between 752.16-800000,
Number of rows for direct path column array improves the efficiency of direct load,
5. When using parallel load, it is best to run two different sqlldr programs, specifying that each ctl file loads different content.
6. Delete the index and constraints of the loading table first. The effect is not clear, but it can definitely increase the loading speed.
7. It is best to set the loading table to nologging/unrecoverable, which is useful for conventional path load and reduces the write of redo logs.
8. Increase the size of date_cache, and increase the loading speed when loading the table with date fields.
Date_cache -- size (in entries) of date conversion cache (Default 1000)