Frequent data saves resulting from a small ROWS
value adversely affect the performance of a direct path load. A small ROWS
value can also result in wasted data block space because the last data block is not written to after a save, even if the data block is not full.
Because direct path loads can be many times faster than conventional loads, the value of ROWS
should be considerably higher for a direct load than it would be for a conventional load.
During a data save, loading stops until all of SQL*Loader's buffers are successfully written. You should select the largest value for ROWS
that is consistent with safety. It is a good idea to determine the average time to load a row by loading a few thousand rows. Then you can use that value to select a good value for ROWS
.
For example, if you can load 20,000 rows per minute, and you do not want to repeat more than 10 minutes of work after an interruption, then set ROWS
to be 200,000 (20,000 rows/minute * 10 minutes).