Re: How/Where do I use COMMIT in SQL*Loader

From: Lokesh <biyer3037_at_my-deja.com>
Date: Fri, 06 Aug 1999 04:11:49 GMT
Message-ID: <7odn9u$f8u$1@nnrp1.deja.com>

OPTIONS (ROWS=100) commiting after inserting each row is very slow. Use a 100 row commit as generic setting. This is what the SQL Loader Docco says

Performance Implications
To minimize the number of calls to Oracle and maximize performance, large bind arrays are preferable. In general, you gain large improvements in performance with each increase in the bind array size up to 100 rows. Increasing the bind array size above 100 rows generally delivers more modest improvements in performance. So the size (in bytes) of 100 rows is typically a good value to use.

bind array size = (number of rows) * (maximum row length)

where:

(maximum row length) = SUM(fixed field lengths) + SUM(maximum varying field lengths) +
SUM(overhead for varying length fields)

Hope this helps

-Lokesh

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Thu Aug 05 1999 - 23:11:49 CDT