Yeah, it sounds pretty high-tech.
I might have a simpler method but I haven't tested this...
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.
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).
