Download SQL*Loader Control File

Transcript
Determining the Size of the Bind Array
Table 5–4 Variable-length fields
Datatype
Default Size
Maximum Length
Specified (L)
VARCHAR
4Kb
L+S
CHAR (delimited)
DATE (delimited)
numeric EXTERNAL (delimited)
255
L+S
Minimizing Memory Requirements for the Bind Array
Pay particular attention to the default sizes allocated for VARCHAR,
VARGRAPHIC, and the delimited forms of CHAR, DATE, and numeric
EXTERNAL fields. They can consume enormous amounts of memory—especially
when multiplied by the number of rows in the bind array. It is best to specify the
smallest possible maximum length for these fields. For example:
CHAR(10) TERMINATED BY ","
uses (10 + 2) * 64 = 768 bytes in the bind array, assuming that the length indicator is
two bytes long. However:
CHAR TERMINATED BY ","
uses (255 + 2) * 64 = 16,448 bytes, because the default maximum size for a delimited
field is 255. This can make a considerable difference in the number of rows that fit
into the bind array.
Multiple INTO TABLE Statements
When calculating a bind array size for a control file that has multiple INTO TABLE
statements, calculate as if the INTO TABLE statements were not present. Imagine all
of the fields listed in the control file as one, long data structure — that is, the format
of a single row in the bind array.
If the same field in the data record is mentioned in multiple INTO TABLE clauses,
additional space in the bind array is required each time it is mentioned. So, it is
especially important to minimize the buffer allocations for fields like these.
SQL*Loader Control File Reference 5-79