Re: fastest way to insert/update i million rows

From: Connor McDonald <connor_mcdonald_at_yahoo.com>
Date: Thu, 02 Sep 1999 18:47:53 +0800
Message-ID: <37CE55D9.5D06@yahoo.com>

nandagopalj_at_hotmail.com wrote:
>
> Hello everyone:
>
> I have a need to update about 1 million rows of a table and insert
> another million rows into two other tables. I am constrained by the time
> to be able to perform all the above operations of about 12 hours.
> Therefore I need the fastest means to do this.
>
> I have the following ideas:
>
> 1. Use sqlloader for inserts
> Pro-c/c++ host arrays to update the records.
>
> 2. Write a PLSQL function to insert and update the records and then call
> it using Pro-c/c++ code using host arrays.
>
> 3. Write a perl script to generate all the insert and update SQL
> statements into a ascii file and then use sqlplus to process the
> ascii files.
>
> I intend to have "multiple processes" processing about 50,000 records
> simultaneously.
>
> I would appreciate any responses to following questions:
>
> 1. Which is the best method of the three methods above ? Or is
> there a better method ?
>
> 2. Can disabling the constraints increase the speed of updates?
> If yes, how complex is the process of enabling the constraints
> back again ? ( I am sure that there will be no other process
> updating the data and my updates do not violate integrity of
> the data.)
>
> Thanks
> Nan.
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.

Some options to consider:

Turn off archive logging, turn back on (and backup) once done
Drop all indexes before inserts, rebuild with unrecoverable (nologging)
Ensure large log buffer and sufficient rollbacks to cope with all of your parallel jobs running

Connor McDonald
"These views mine, no-one elses etc etc" connor_mcdonald_at_yahoo.com

"Some days you're the pigeon, and some days you're the statue." Received on Thu Sep 02 1999 - 05:47:53 CDT