Re: what is faster sqlloader or import

Syed,

I am not a DBA, but will give some advice here ...

I see from your script that you have a DMP file with you, which is compressed. I
will not recommend SQL* Loader, because you will first have to generate an ASCII
file with a select statement, and then run sql* loader to upload the target table. By that time, your import will finish.

Follow these points:

1. Keep the *.dmp file uncompressed beforehand. You are trying to uncompress and send the output to import, this will load the CPU dramatically. So first uncompress completely, then import - don't pipe it. I hope space is not the constraint...
2. Keep the buffer high. 64000 is TOO LOW. I do not know what memory your DB is using or your system has, but keep as high as possible - suggest at least 4-6 MB (buffer = 4000000)
3. disable/drop the index during import. when import is finished, re-create it. Index creation will take some time, but keeping an index active when importing 40 million rows is a performance killer.
4. Fire your import job at a time when system is relatively free - both CPU and memory. This could be a night job or something.
5. Do keep commit=Y and indexes=n options as they are...

Hope this helps,
Regards,
Deepak

vlakhanpal_at_indiainfo.com (Vijay) wrote in message news:<8d556fe.0107242153.503f9e8f_at_posting.google.com>...
> Hi Syed,
> U can give a try to sqldr. it is a very good utility. to generate a
> ascii file from ur database, ui just have to writea select statement
> and spool the output in a fle. that will wirk as an input file.
> i hope it will hwlp.
> thanks
> vijay
>
> shussain44_at_yahoo.com (syed hussain) wrote in message news:<c189c239.0107241534.7ddea33d_at_posting.google.com>...
> > i am trying to import a table with 40 million records. the way it's
> > going it will take 4 days to complete . following script I have used
> > to import the table
> > mknod /tmp/imp_pipe_lnk p
> > uncompress < explnk.dmp.Z > /tmp/imp_pipe_lnk &
> > imp file=/tmp/imp_pipe_lnk userid=system/manager fromuser=lnk
> > touser=lnk log=imp_lnk.log commit=y ignore=y indexes=n
> > buffer=64000. at first I have created the full table with primary key
> > and then istart the import. it was doing 4000 record /min. Then I
> > killed the import process and start running it by dropping the index
> > and index=n parameter. Still it's running very slow. what should I do.
> > Is sql loader is faster then import. If I user sqlloader, then how
> > can I create asci file .
> > Thanks
> > Syed
Received on Wed Jul 25 2001 - 12:07:47 CEST