Re: SQLLDR or UTL_FILE package? Which is faster?

  • From: "Tim Hall" <timhall1@xxxxxxxxx>
  • To: srinivasanram2004@xxxxxxxxx, Oracle-L <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 14 Jun 2007 16:19:20 -0700

FWIW, we load about 140 million records (not so many columns) via SQL*Loader
every couple of weeks (on Oracle 9.2.0.6, Solaris).  It takes an hour or two
to perform the SQL*Loader portion of this job.  I haven't actually tried
UTL_FILE loads for this many records, but extrapolating from our experience
with loads of < 1 million rows, I'd expect that UTL_FILE would take an order
of magnitude longer than SQL*Loader.

Also, IIRC, you'd be likely to run into some limitations with UTL_FILE on
8i.  I'm not positive, but I think the line-size limitation was 1023 bytes
back then?

So... Given a choice, and assuming the input file is in a relatively sane
format and you need to do relatively little manipulation to the data on the
way in, I'd be very inclined to try SQL*Loader.

Hope this helps --
Tim



On 6/14/07, Ram Srinivasan <srinivasanram2004@xxxxxxxxx> wrote:

All:
  Is SQLLDR faster or UTL_FILE utility faster?
  For loads of 130 million rows with about 200 columns, which tool is
better?
  We are on 8.1.7 on Sun server.

  Thanks for your input.

--
Sincerely
Ram Srinivasan
Charlottesville, VA.

Other related posts: