Oracle FAQ Your Portal to the Oracle Knowledge Grid

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

RE: Sqlldr versus inserts

From: Bobak, Mark <>
Date: Mon, 12 Mar 2007 13:36:15 -0400
Message-ID: <>

Any direct path load (SQL*Loader or INSERT /*+ APPEND */ will take a TM enqueue in 'X' mode. That's because they both use the same direct path load mechanism internally.  


Mark J. Bobak 
Senior Oracle Architect 

"There are 10 types of people in the world:  Those who understand
binary, and those who don't." 


From: [] On Behalf Of John Dunn Sent: Monday, March 12, 2007 11:01 AM To: Subject: RE: Sqlldr versus inserts 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: [] Sent: 12 March 2007 13:54 To: John Dunn; 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: [] 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. --
Received on Mon Mar 12 2007 - 12:36:15 CDT

Original text of this message