John
You don't say whether you are using conventional or direct load, or what version of Oracle. JDBC, etc.
In conventional mode, SQL*Loader should behave similarly to a straightforward SQL insert - as long as you use an array (bulk) bind. I believe you can do that with JDBC (using update batching - see http://download-west.oracle.com/docs/cd/B13789_01/java.101/b10979/oraperf.htm#sthref1837, or by passing a collection as input to a PL/SQL procedure which can use bulk binds) - however also check out this example OTN thread which reports performance problems: http://forums.oracle.com/forums/thread.jspa?threadID=481786&tstart=0
Using an external table should give performance comparable to SQL*Loader... and also save your Java developers from having to work out how to parse the file you're reading from. Your insert can then become a simple insert into ... select from... and you would also have the option of using insert /*+ append */, and/or joining the external table to other tables to look up codes/ids/references etc in a set-oriented way.
Cheers Nigel
----- Original Message ----
From: John Dunn <JDunn@sefas.com>
To: oracle-l <oracle-l@freelists.org>
Sent: Monday, March 12, 2007 11:52:43 AM
Subject: Sqlldr versus inserts
We currently load large amounts of data into a table using sqlldr which is run from a unix script initiated from a java stored procedure.
The application designers want to eliminate the script and load the data directly using java and jdbc, which I presume will mean using insert statements.
Sound like a bad idea to me from a performance point of view, but am I correct? If not using sqllldr what oprions are there to read a flat file and inseet the data. Will external files give the same performance as sqlldr?
John
--
http://www.freelists.org/webpage/oracle-l
Received on Mon Mar 12 2007 - 07:37:11 CDT
