RE: update about 100K records

From: Regis Biassala <Regis.Biassala_at_datalex.ie>
Date: Wed, 09 Jul 2003 02:36:12 -0700
Message-ID: <F001.005C40DA.20030709022424@fatcity.com>

Ryan is right do this:

DECLARE

TYPE myRef                      IS REF CURSOR;
myCursor                        myRef;
TYPE myType             IS TABLE OF Table_Name.PKid%TYPE INDEX BY BINARY
INTEGER;
p_limit                         PLS_INTEGER := 1000;
P_PKid                          myType;
p_flag                  PLS_INTEGER := 0;

p_BigRollbackSegment VARCHAR2(30) := 'YOUR_BIG_RBS'; BEGIN

     DBMS_TRANSACTION.USE_ROLLBACK_SEGMENT(UPPER(p_BigRollbackSegment));
     OPEN myCursor FOR
     'SELECT PKid FROM Table_name WHERE delete_flag <> :1' USING p_flag ;
        LOOP
        FETCH myCursor BULK COLLECT INTO P_PKid LIMIT p_limit;
        FOR i IN 1..P_PKid.COUNT LOOP
         UPDATE Table_Name
           SET delete_flag= p_flag WHERE PKid = P_PKid;
      END LOOP; 
        COMMIT;
        DBMS_TRANSACTION.USE_ROLLBACK_SEGMENT(UPPER(p_BigRollbackSegment));
        EXIT WHEN myCursor%NOTFOUND;            
        END LOOP;     
        CLOSE myCursor ;

END; The above code will commit after each 1000 record and should run very fast...

Regis

-----Original Message-----
Sent: Wednesday, July 09, 2003 12:19 AM
To: Multiple recipients of list ORACLE-L

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.
----- 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).
*********************************************************************
This electronic transmission is strictly confidential and intended solely
for the addressee. It may contain information which is covered by legal,
professional or other privilege. If you are not the intended addressee,
you must not disclose, copy or take any action in reliance of this
transmission. If you have received this transmission in error, 
please notify the sender as soon as possible.

This footnote also confirms that this message has been swept
for computer viruses.
**********************************************************************

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Regis Biassala
  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 Wed Jul 09 2003 - 04:36:12 CDT