If you have control over the format of the data to be loaded, then you can use the following hints to improve load performance:
Make logical record processing efficient.
Use one-to-one mapping of physical records to logical records (avoid using
Make it easy for the software to identify physical record boundaries. Use the file processing option string
"FIX nnn" or
"VAR". If you use the default (stream mode), then on most platforms (for example, UNIX and NT) the loader must scan each physical record for the record terminator (newline character).
Make field setting efficient. Field setting is the process of mapping fields in the data file to their corresponding columns in the table being loaded. The mapping function is controlled by the description of the fields in the control file. Field setting (along with data conversion) is the biggest consumer of CPU cycles for most loads.
Avoid delimited fields; use positional fields. If you use delimited fields, then the loader must scan the input data to find the delimiters. If you use positional fields, then field setting becomes simple pointer arithmetic (very fast).
Do not trim whitespace if you do not need to (use
Make conversions efficient. SQL*Loader performs character set conversion and data type conversion for you. Of course, the quickest conversion is no conversion.
Use single-byte character sets if you can.
Avoid character set conversions if you can. SQL*Loader supports four character sets:
Client character set (
NLS_LANG of the client
Data file character set (usually the same as the client character set)
Database character set
Database national character set
Performance is optimized if all character sets are the same. For direct path loads, it is best if the data file character set and the database character set are the same. If the character sets are the same, then character set conversion buffers are not allocated.
Use direct path loads.
DEFAULTIF clauses. Each clause must be evaluated on each column that has a clause associated with it for every row loaded.
Use parallel direct path loads and parallel index creation when you can.
Be aware of the effect on performance when you have large values for both the
CONCATENATE clause and the
COLUMNARRAYROWS clause. See "Using CONCATENATE to Assemble Logical Records".