| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: update about 100K records
Ryan's method is pretty hi-tech for me,
and he is flying above the clouds and does not want
to get down to poor little me, even after the inviation... :-)
But this one here,
--- Mark Richard <[EMAIL PROTECTED]> wrote:
> Yeah, it sounds pretty high-tech.
> Create a loop and place the following within it:
> update table
> set flag = new_value
> where condition and flag <> new_value and rownum <= commit_limit;
> n_rows_updated := SQL%ROWCOUNT;
> commit;
>
> The exit condition is when n_rows_updated = 0, ie: nothing left to
> update.
But this one here, I dont know, how are you getting to the next set? What if one set does not have anything to update at all? Then rows_updated will be 0, and we'll exit.
>
> This approach will cause a few more scans than the ideal option, but
> it
> might get the job done. Increasing the commit_limit value as high as
> possible will reduce the number of iterations. 1,000 rows sounds
> very
> small - even if your row length was 1,000 bytes you are only talking
> about
> 1MB of data. Have you tried all 100,000 rows at once? How about
> 10,000 at
> a time?
>
>
>
>
>
>
> MaryAnn Atkinson
>
> <[EMAIL PROTECTED] To: Multiple
> recipients of list ORACLE-L <[EMAIL PROTECTED]>
> .com> cc:
>
> Sent by: Subject: Re: update
> about 100K records
> [EMAIL PROTECTED]
>
> .com
>
>
>
>
>
> 09/07/2003 09:29
>
> Please respond to
>
> ORACLE-L
>
>
>
>
>
>
>
>
>
> huh???
>
> --- Ryan <[EMAIL PROTECTED]> wrote:
> > bulk collect the flag into a pl/sql table. forall with a limit
> clause
> > and then commit after hitting each limit.
> > this is on asktom.
>
> anything more down the earth for me please?
>
> thx
> maa
>
>
>
> > ----- Original Message -----
> > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
> > Sent: Tuesday, July 08, 2003 7:09 PM
> >
> >
> > > I have a table of about one million records.
> > >
> > > About 100,000 of them have a flag which I need it set to 0.
> > >
> > > Because of the size of the rollback segment, I cannot
> > > update them all and then commit, I need to do it in
> > > sets of 1000 records.
> > >
> > > Do I need a cursor for this?
> > >
> > > I had something like that:
> > > DECLARE
> > > I BINARY_INTEGER;
> > >
> > >
> > > Cursor MyCursor IS
> > > SELECT *
> > > FROM (Table_Name)
> > > WHERE (Condition)
> > > FOR UPDATE;
> > >
> > > BEGIN
> > > I := 0;
> > > FOR MyRec IN MyCursor LOOP
> > > UPDATE (Table_name)
> > > SET delete_flag = 1
> > > WHERE CURRENT OF MyCursor;
> > >
> > > I := I + 1;
> > > IF MOD(I, 1000) = 0 THEN
> > > COMMIT;
> > > END IF;
> > >
> > > END LOOP;
> > > END;
> > > /
> > >
> > > But "FOR UPDATE" does not really work well, and at the 1000th
> > record
> > > when it reaches the commit, its dropping out of the loop.
> > >
> > > Can I use ROWNUM to update them in batches of 1000 per time?
> > >
> > > Thanks,
> > > maa
> > >
> > > --
> > > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > > --
> > > Author: Maryann Atkinson
> > > INET: [EMAIL PROTECTED]
> > >
> > > Fat City Network Services -- 858-538-5051
> http://www.fatcity.com
> > > San Diego, California -- Mailing list and web hosting
> > services
> > >
> >
> ---------------------------------------------------------------------
> > > To REMOVE yourself from this mailing list, send an E-Mail message
> > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and
> in
> > > the message BODY, include a line containing: UNSUB ORACLE-L
> > > (or the name of mailing list you want to be removed from). You
> may
> > > also send the HELP command for other information (like
> > subscribing).
> > >
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: Ryan
> > INET: [EMAIL PROTECTED]
> >
> > Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> > San Diego, California -- Mailing list and web hosting
> services
> >
> ---------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like
> subscribing).
>
>
> __________________________________
> Do you Yahoo!?
> SBC Yahoo! DSL - Now only $29.95 per month!
> http://sbc.yahoo.com
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: MaryAnn Atkinson
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
>
>
>
>
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: MaryAnn Atkinson INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Tue Jul 08 2003 - 18:15:55 CDT
 |
 |