Re: Oracle error 1555 snapshot too old

No further comment needed on the
Snapshot too old problem, and the
code solution is often perfectly adequate, but it can become an issue in its own right.

As it stands (loop deleting 50,000 rows to eliminate 1,000,000), it may do 20 full table scans of a large table.

For various reasons this could lead to
extreme performance issues - much more
significant than simply the cost of the tablescan.

To cope with this, I put together the mechanism (described on my web page under

    'developer tricks -> big update'
)
to handle such problems by rowid range
separation:

loosely:

    generate list of covering rowid ranges     for each rowid range - process relevant rows

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

Connor McDonald wrote in message <384D091F.3908_at_yahoo.com>...
>Eric DUCHET wrote:
>>
>> Hi,
>>
>> I try to delete about 1 000 000 rows in my table with the following
command

>
>If your rollbacks are big enough, remove the commits...If they are not,
>try this:
>
>begin
>loop
> delete FROM ITEM WHERE (ITEMCODE, CODE, INSERTTIME,
> RECORDTYPE) IN
> (SELECT ITEMCODE, CODE, INSERTTIME, RECORDTYPE FROM
> TMPSPEITEMDEL)
> and rownum < 50000;
> exit when sql%rowcount = 0;
> commit;
>end loop;
>end;
>
Received on Wed Dec 08 1999 - 01:44:41 CST