SQL*Loader

SQL*Loader (SQLLDR) is an Oracle utility used in load service to load data from CSV files into the target tables of an Oracle database.

The next section lists commonly used SQL*Loader Options in Hyperscale jobs in addition to usage information.

For a complete list of SQL*Loader options, refer to this Oracle Database Utilities Guide.

SQLLDR options usage

Option Usage Hyperscale default SQL*Loader default Affects Larger value benefits Risks of too large

READSIZE

 Specifies the size (in bytes) of the read buffer used when reading data from the input file (control or data file). None 1048576 bytes or 1 MB File read efficiency Fewer disk reads High memory usage
STREAMSIZE The size (in bytes) of the stream buffer used for communication between SQL*Loader and the Oracle server. 256000 bytes 256000 bytes or 256 KB DB server communication Fewer round-trips, faster loading High PGA usage, swapping
COLUMNARRAYROWS The number of rows loaded before data is transferred to Oracle in array inserts (for both conventional and direct path loads). 20000 rows 5000 rows Insert array size Fewer insert operations, better throughput Memory pressure per array

 

In Hyperscale, you can define sqlldr_options through the APIs listed below. When multiple values conflict, the following order of precedence applies:

  1. Create DataSet API: values provided in sqlldr_options element inside target element.

  2. Create DataSet API: values provided in target element directly like stream_size or column_array_rows

  3. Create Job Config API: values provided inside sqlldr_options

  4. If none of the above elements (from 1 to 3) provide values, the default Hyperscale settings listed in the SQLLDR table are applied.