General Performance Improvement Hints

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 CONTINUEIF and CONCATENATE).

    • 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 PRESERVE BLANKS).

  • 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 sqlldr process)

      • 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.

  • Use the SORTED INDEXES clause.

  • Avoid unnecessary NULLIF and 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".