Re: large delete

On Wed, 21 Jun 2006 12:53:16 +0200, Robert Klemme <bob.news_at_gmx.net> wrote:

>Sybrand Bakker wrote:
>> On Tue, 20 Jun 2006 16:21:27 -0700, Akimeu <Nospam_at_Email.com> wrote:
>>
>>> hello,
>>>
>>> i have done some looking around for this, but haven't really found a
>>> solid answer. we have an sp, to which we're passing in a table name,
>>> after which we execute immediate a delete statement. is there any way
>>> of committing within this particular delete?
>>>
>>> any insights/suggestions are appreciated. thanks.
>>>
>>> this is what i was thinking of doing:
>>>
>>> loop
>>> vsql:='delete from '||vschem||'.'||vtablename||
>>> ' where '||vcol||'='''||vid||''' and rownum <= 10000';
>>> execute immediate vsql;
>>> exit when sql%rowcount = 0;
>>> commit;
>>> end loop;
>>
>> do NOT commit every n records. You will be hitting 'snapshot too old'
>> because of this.
>
>Could you elaborate this a bit? Your answer isn't clear to me. It
>seems, that Tom suggests to do exactly this (i.e. working only on a
>subset of the data) to *avoid* ORA-01555 "Snapshot too old":
>
>"3. Run the processing against a range of data rather than the whole
>table. (Same reason as 1)."
>
>http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:275215756923
>
>Kind regards
>
> robert

Actually if you do read his article carefully /* quote */
and in the rare cases where
ORA-01555 is returned one of the solutions below must be used.

 CASE 1 - ROLLBACK OVERWRITTEN

1. Increase size of rollback segment which will reduce the likelihood of overwriting rollback information that is needed.
2. Reduce the number of commits (same reason as 1).
3. Run the processing against a range of data rather than the whole table. (Same reason as 1).
4. Add additional rollback segments. This will allow the updates etc. to be spread across more rollback segments thereby reducing the chances of overwriting required rollback information.
5. If fetching across commits, the code can be changed so that this is not done.
6. Ensure that the outer select does not revisit the same block at different times during the processing. This can be achieved by :
   • Using a full table scan rather than an index lookup
   • Introducing a dummy sort so that we retrieve all the data, sort it and then sequentially visit these data blocks.

/*unquote*/

So he recommends using *one of the solutions* below. Why should that be necessarily solution 3 according to you?

Actually the OP is already deleting a range of data (look at the first predicate in his delete statement), and as far as I am concerned he should just bite the bullet and accept his undo segments are going to grow.

Furthermore: the OP seems to split a *logical* transaction into several smaller physical ones. This is invariably a bad idea. In what state will the table be if his program crashes? At it's best the situation will be hybrid. m records would have been deleted, and the delete of n records would have been undone. The OPs table is no longer logically consistent, and it is unclear what has been deleted and what hasn't

--
Sybrand Bakker, Senior Oracle DBA