Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.misc -> Re: How to get data into Oracle over a mediocre network connection

Re: How to get data into Oracle over a mediocre network connection

From: Martin T. <>
Date: Mon, 04 Jun 2007 00:37:51 -0700
Message-ID: <>

On Jun 3, 9:37 am, wrote:
> On Sat, 02 Jun 2007 16:13:54 -0700, "Martin T."
> <> wrote:
> >Well, there were no earlier attempts with _SQLLoader_. There were the
> >attempts with x update stmts in a sql scripts and the exp/imp approach
> >which failed utterly. The SQLLoader approach just worked ...
> >If anyone likes to comment on why loading a table over the network via
> >SQLLoader is 1000x faster than via imp (or SQL) I'd be more than
> >happy ...
> So far, you have been less than clear about the exact route you
> followed: no exact statements etc.
> So how could anyone provide any comment?
> There are numerous factors at play. Imp will be slower if you don't
> specify the buffer= parameter, the commit=y parameter, etc, etc, etc.

1st try: plain sqlplus script
--> It seems (with hindsight) quite obvious that this will perform very slowly, since after each end every UPDATE statement there would be an ack from the DB to the client.

set scan off;
UPDATE TEMP_TEXTS (TXT_ID, TEXT) VALUES (123, 'One text of ten thousands');

2nd try: Use a begin / end; to encapsulate it --> With a few hundred of staments this seems the easiest way, but if the PL/SQL block does get too large it won't work anymore. (I think there's a limit on how large it's allowed to be.)

3rd try: Create the table locally and then exp/imp it remote --> This seemed to have the same dismal performance as the 1st variant. We went with the default parameters of imp (with TABLES=..), so that would make COMMIT=N ... as for the rest, I should probably read up on how the imp utility does its job (or rather how it differs from SQLLoader). (Reading the docs: The BUFFER param seems to be a thing that allows tweaking, but then the docs go on to say: "For tables containing (...), or DATE columns, rows are inserted individually." ... whatever that means.)

4th try: SQLLoader
--> Again, we pretty much went with the default params, and this time it 'just worked'.

All in all, I'm pretty happy with the SQL-Loader solution, it seems straightforward enough, I do everything remotely, and it's fast.

Martin Received on Mon Jun 04 2007 - 02:37:51 CDT

Original text of this message