"Michael" <mikeeria_at_interia.pl> wrote in message
news:875e251b.0307070637.620d8c49_at_posting.google.com...
> 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.
>
The answer should be determined by the logical requirements, not the
physical.
If you want feedback on progress on an uncommitted long-running transaction,
there are plenty of ways to provide that. But how long is a half-million row
insert taking anyway?
Regards,
Paul
Received on Mon Jul 07 2003 - 14:26:43 CDT
