Re: Cursor, loop and delete...

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 Received on Tue Aug 03 2004 - 04:14:24 CDT