Thanks for the replies Testing shows that load times for sqlldr direct load and external table/select append are about the same. Oracle version is 10.2. I believe that direct load sqlldr takes an exclusive lock on the table. Is that correct? and does external file/insert append do the same? John ________________________________ From: rajendra.pande@xxxxxxx [mailto:rajendra.pande@xxxxxxx] Sent: 12 March 2007 13:54 To: John Dunn; oracle-l@xxxxxxxxxxxxx Subject: RE: Sqlldr versus inserts John Questions that you should look at : How much of data massaging is done to the raw data - at this time once it is loaded via sqlldr What is the source of data. Is there any optimization possible in getting the source via say pipes or queues In the end if there is a lot of sql that is involved then you are better off using sqlldr and then plsql. If not then java should work fine. ________________________________ From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of John Dunn Sent: Monday, March 12, 2007 7:53 AM To: oracle-l 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 Please do not transmit orders or instructions regarding a UBS account by e-mail. The information provided in this e-mail or any attachments is not an official transaction confirmation or account statement. For your protection, do not include account numbers, Social Security numbers, credit card numbers, passwords or other non-public information in your e-mail. Because the information contained in this message may be privileged, confidential, proprietary or otherwise protected from disclosure, please notify us immediately by replying to this message and deleting it from your computer if you have received this communication in error. Thank you. UBS Financial Services Inc. UBS International Inc.