Oracle FAQ Your Portal to the Oracle Knowledge Grid

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

RE: Sqlldr versus inserts

From: John Dunn <>
Date: Mon, 12 Mar 2007 15:01:00 -0000
Message-ID: <2F802216565E44489600A03F763219A81888B4@mia.SEFASBRISTOL>

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?      


From: [] Sent: 12 March 2007 13:54
To: John Dunn;
Subject: RE: Sqlldr versus inserts


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.

[] 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?        


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 - 10:01:00 CDT

Original text of this message