Michael wrote:
> Hye !
>
>
> I have a query which selects from several tables and inserts into a
> table X. Two ways of writing this query are (A) fast but risky (B)
> slow but certain
>
>
> (A) Writing this select in one bulk INSERT SELECT statement will be
> the fastest way, however we only commit once at the end. If something
> wrong happens then nothing will be stored in the new table and in
> addition we should wait a period of time for the database to rollback
> its work.
>
> (B) Now performing the same INSERT in a cursor and COMMITing after
> each INSERT is slower but more certain, because if something happens
> which will stop
> the query, then it is possible to restart from where we stopped.
>
> What I am looking for is a solution in between, that will balance
> speed and risk for example to commit every chunk of records. The
> reason for that is not space but time. When this query is run the
> database should be turned offline and the downtime is minimal. By
> introducing more commits, it will be much simpler to forecast how much
> time will it take to perform this INSERT, better feedback of what is
> happening. Any ideas??
>
> I am using Oracle 8i and the INSERT varies from 100000 to 500000
> records.
>
>
> TIA
> Michal
I've done inserts via selects over a database link; about 7 million
records in 5 to 10 minutes.
Commit after all is done (hence: scenario A); B is hard to restart,
and anything in between a certain recipe for ora-1555
--
Regards, Frank van Bortel
Received on Mon Jul 07 2003 - 14:22:49 CDT
