I am very confident (but have not specifically tested today) that during
each iteration it will keep searching until if finds 1000 rows meeting the
update criteria. Since the new flag value is checked in the where clause
it will not update rows multiple times and will therefore search until it
find "commit_limit" rows to update. It's clunky because it involves
searching over updated records (depending on indexing) but it might solve
your problem. ROWNUM is incremented within an UPDATE for each row that
meets all criteria for update, not each row inspected - it won't increment
until it finds rows to update and therefore won't stop until it has updated
commit_limit number of rows.
The reason I haven't used it personally is that I cannot afford to
repeatedly scan the table due to volume differences... I'm currently
working on an update of 150-200 million rows, which is considerably worse
than your scenario.
Regards,
Mark.
PS: Technically you can save one iteration by checking n_rows_updated <
commit_limit in your exit condition rather than = 0 since it will only be
less than commit_limit if it ran out of rows to update. I just wanted to
keep it simple for yourself.
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:59
Please respond to
ORACLE-L
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).
>
>
>
>
>
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
> Privileged/Confidential information may be contained in this
> message.
> If you are not the addressee indicated in this message
> (or responsible for delivery of the message to such person),
> you may not copy or deliver this message to anyone.
> In such case, you should destroy this message and kindly notify the
> sender
> by reply e-mail or by telephone on (61 3) 9612-6999.
> Please advise immediately if you or your employer does not consent
> to
> Internet e-mail for messages of this kind.
> Opinions, conclusions and other information in this message
> that do not relate to the official business of
> Transurban City Link Ltd
> shall be understood as neither given nor endorsed by it.
>
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
>
>
>
>
>
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
> Privileged/Confidential information may be contained in this message.
> If you are not the addressee indicated in this message (or
> responsible for delivery of the message to such person), you may not
> copy or deliver this message to anyone.
> In such a case, you should destroy this message and kindly notify the
> sender by reply e-mail or by telephone on (03) 9612-6999 or (61) 3
> 9612-6999.
> Please advise immediately if you or your employer does not consent to
> Internet e-mail for messages of this kind.
> Opinions, conclusions and other information in this message that do
> not relate to the official business of Transurban Infrastructure
> Developments Limited and CityLink Melbourne Limited shall be
> understood as neither given nor endorsed by them.
>
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Mark Richard
> 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).
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Privileged/Confidential information may be contained in this message.
If you are not the addressee indicated in this message
(or responsible for delivery of the message to such person),
you may not copy or deliver this message to anyone.
In such case, you should destroy this message and kindly notify the sender
by reply e-mail or by telephone on (61 3) 9612-6999.
Please advise immediately if you or your employer does not consent to
Internet e-mail for messages of this kind.
Opinions, conclusions and other information in this message
that do not relate to the official business of
Transurban City Link Ltd
shall be understood as neither given nor endorsed by it.
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Privileged/Confidential information may be contained in this message.
If you are not the addressee indicated in this message (or responsible for delivery of
the message to such person), you may not copy or deliver this message to anyone.
In such a case, you should destroy this message and kindly notify the sender by reply
e-mail or by telephone on (03) 9612-6999 or (61) 3 9612-6999.
Please advise immediately if you or your employer does not consent to Internet e-mail
for messages of this kind.
Opinions, conclusions and other information in this message that do not relate to the
official business of Transurban Infrastructure Developments Limited and CityLink
Melbourne Limited shall be understood as neither given nor endorsed by them.
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Mark Richard
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).
