Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Sqlldr versus inserts

Re: Sqlldr versus inserts

From: Nigel Thomas <>
Date: Mon, 12 Mar 2007 05:37:11 -0700 (PDT)
Message-ID: <>

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, 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: 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 <> To: oracle-l <> 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

Received on Mon Mar 12 2007 - 07:37:11 CDT

Original text of this message