Re: update about 100K records

From: MaryAnn Atkinson <maryann_30_at_yahoo.com>
Date: Tue, 08 Jul 2003 15:36:08 -0700
Message-ID: <F001.005C3E86.20030708152925@fatcity.com>

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).

Received on Tue Jul 08 2003 - 17:36:08 CDT