Very Computer

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.

Hi.

 In my experience its almost always better to use SQL instead of PL/SQL
 ( when using PL/SQL you have to pay "penalty" of context switches from
  SQL to PL/SQL and back ).

 Post yout UPDATE statement and it's EXPLAIN. It may be possible to
 avoid FULL table scan.

 HTH. Michael.

Sent via Deja.com http://www.deja.com/
Before you buy.

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.

Quote:> > 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.