In article <sae07hbjivs125_at_corp.supernews.com>,
"Steve Halko" <steveh_at_redshift.com> wrote:
> 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
> >
I agree with Steve, the performance degration you see is due to the
fact you are selecting the data to lock it, then you are updateing it.
You are comparing apples to oranges since you changed the SQL
statement. You should perform the same SQL from SQL*Plus and within
pl/sql to compare the times. Of course there is no real reason to use
pl/sql in a case like this since you do not need any of the extra
capabilities pl/sql provides like the ability to trap errors or perform
an if test and base you actions on the result of the test.
--
Mark D. Powell -- The only advice that counts is the advice that
you follow so follow your own advice --
Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Mon Feb 14 2000 - 08:36:39 CST
