Re: large delete

From: Robert Klemme <bob.news_at_gmx.net>
Date: Wed, 21 Jun 2006 12:53:16 +0200
Message-ID: <4fsmosF1kg0etU1@individual.net>

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 Received on Wed Jun 21 2006 - 05:53:16 CDT