Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Cursor, loop and delete...

Re: Cursor, loop and delete...

From: <sybrandb_at_yahoo.com>
Date: 3 Aug 2004 08:11:31 -0700
Message-ID: <a1d154f4.0408030711.c579cf9@posting.google.com>


Albert Dupond <ad_at_ad.com> wrote in message news:<410f577b$0$1891$636a15ce_at_news.free.fr>...
> Sybrand Bakker wrote:
> > 1) use the for update clause on the select statement
> > 2) replace the where clause by where current of mycursor
> > 3 ) do NOT commit every individual delete
> > 4 ) do NOT commit inside a loop
> >
> > question is why you think you need this. delete where value = 'TEST'
> > woul be much more efficient. you're going to kill
> > performance,especially when you leave this code unchanged.
>
> Sybrand, thanks for your answer.
>
> Of course, my problem is a bit more complex than this example. I am
> "exploding" rows of my table (1 row will produce many rows after a
> not-discussed-here treatment). The resulting rows are excluded of the
> select criteria.
>
> The row that has been exploded has to be deleted. And for that reason I
> would have prefered to delete it immediately after the treatment (inside
> the loop), but according to your answer, I will mark it as to be
> deleted, and delete all them after the loop.
>
> About point 3), I am nearly obliged to commit every single change
> because this procedure will apply to a very large number of records and
> a have sometimes problems of rollback segment size...
>
> About point 4), I would have really appreciated to be able to commit
> inside the loop the new rows that results of the treatment. Is it
> definitly not possible ?
>
> Thanks again.
>
> AD

with respect to 3
You are not obliged. You only think you are. If you are running out of rollback segments: disk is cheap. Set up larger rollback segments and/or a special rollback segment for this transaction and use 'set transaction rollback segment ' etc.
Otherwise you will:
a) kill instance performance, because LGWR and DBWR won't be able to cope.
b) create an enormous amount of overhead, due to the fact every transaction has overhead. Consequently you'll generate *more* redo == disk space.
So what do you 'save'?
c) increase the chance of getting ORA-1555, snapshot too old. There are *numerous* articles on the web, explaining why you will get ora-1555, the main cause however is *committing inside a loop*

4) Your approach defies the concept of a logical transaction. In fact you are splitting up your logical transaction in numerous physical mini-transactions (every commit will start a new transaction) If your procedure still fails, you will end up with a logically inconsistent database.

If only people would think conceptually, instead of sticking to their technical 'solutions', which are only panaceas, and symptom fighting, and actually don't resolve anything, but result in bigger messes.

Regards

-- 
Sybrand Bakker 
Senior Oracle DBA
Received on Tue Aug 03 2004 - 10:11:31 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US