Re: Updating Millions of records with forall

From: Jim Kennedy <kennedy-downwithspammersfamily_at_attbi.net>
Date: Tue, 23 Mar 2004 02:42:40 GMT
Message-ID: <AuN7c.61661$Cb.937326_at_attbi_s51>


"Windsurfer" <nsingh49_at_yahoo.com> wrote in message news:14bd0e63.0403221142.b6323c0_at_posting.google.com...
> Updating Millions of records with forall
>
> I have around 7 Million records which needs to be updated. This update
> logic is that, if it is the first row of the table the column let's
> say c10 will have a value 'I', hence forth the current records value
> is will depend on the previous records value, let's say in c7. So
> there are few if then elses..
>
> The crux of the matter is that each record needs to be updated on the
> previous records value. The entire table is sorted in a specific way.
>
> I have a procedure which works just fine and updates the way it need
> to update, but for a small numbers of records only. If the number of
> records goes up, lets say 50K it takes for ever, and one can imagine
> about updating 7 millions records.
>
> I have tried to use forall and update batch of 1000 records in a
> single shot, but still takes days and days which is not acceptable.
>
> Is there a better way to update these many rows, in such a situation??
>
> Thanks in advance..

The forall does it in memory so 7 million is probably too many at a time (swap out ram for disk). You could split the table into sections and do it in parallel of 50K or so records at a time. You could use the dbms_job to schedule multiple runs at a time.
Jim Received on Tue Mar 23 2004 - 03:42:40 CET

Original text of this message