One thing which is slowing down your PL/SQL cursor is the "for update"
clause - this causes a lock to be placed on every row of the table meeting
the criteria in your WHERE clause.
<cosmin_ioan_at_msn.com> wrote in message news:884g7d$i4k$1_at_nnrp1.deja.com...
> hello all,
>
> I am trying to do a batch update (on a single table) on perhaps 5-10%
> data out of a 30 mill. rec. table and I am comparing two methods:
>
> 1) doing a straight update sql statement (the 'where' clause is a bit
> complex) therefore a full table scan is performed; entire operation
> takes less then 5 minutes;
> 2) taking the same operation under a 'cursor c ... select
> rowid ....for update of ...' pl/sql statement and then updating the
> records in the body of the procedure. This operation took around 7
> minutes.
>
> a) Does that mean that one should, if at all possible, use pl/sql only
> when absolutely necessary!?
> b) Is there any way to do a large sql update and commit every x records
> (and not use a huge rollback segment) or is that possible only in
> pl/sql!?
>
> thanks much,
>
> Cosmin
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Sun Feb 13 2000 - 13:05:08 CST
