Hi,

I have gone through your forums on how to update a table with millions of records

Approach 1 - To create a temporary table and make the necessary changes, drop the original table and rename temporary table to original table. I have not gone by this approach because i'm not sure of the dependencies of the table when it is dropped and moreover the tables are very important and dropping and creating new will not be allowed.

Approach 2 - To use bulk collect with limit and use forall for update. I have written the below script which will be invoked during database upgrade or restore condition.This script runs only during upgrade from application version to another or restoring data from previous application version to new only. So there will not be simultaneous dml operations coming from application. This will be used during pure database migration.

I have written the below script. Kindly validate and tell where i can improve for better performance, logging. In case of rollback, i'm not sure whether i need it because as my records i can update will be good.

I have two tables in which i need to update few columns. For few of the columns, i have created map like structure(key/value) pairs from which data will be populated.

SET SERVEROUTPUT ON;

DECLARE
 E_CATEGORYNAME EVENT.CATEGORY_VALUE%TYPE;   
 E_PRODUCTFAMILY EVENT.PRODUCTFAMILY%TYPE; 
 A_CATEGORYNAME ALARM.CATEGORY_VALUE%TYPE;   
 A_PRODUCTFAMILY ALARM.PRODUCTFAMILY%TYPE;

 -- SELECT THE ROWS FROM EVENT TABLE
 CURSOR C_EVENT IS
 SELECT ROWID, CATEGORY_VALUE, PRODUCTFAMILY, CATEGORY_ORDINAL
 FROM   EVENT WHERE CATEGORY_VALUE IN ('Autonomous AP','Cisco UCS Series','Routers','Switches and Hubs','Wireless Controller');

 -- CREATE OBJECT TYPE TO HOLD THE ROWS FROM EVENT TABLE
 TYPE C_EVENT_TAB IS TABLE OF C_EVENT%ROWTYPE
 INDEX BY BINARY_INTEGER;
 C_EVENT_ROWS C_EVENT_TAB;

 -- SELECT THE ROWS FROM ALARM TABLE
 CURSOR C_ALARM IS
 SELECT ROWID, CATEGORY_VALUE, PRODUCTFAMILY, CATEGORY_ORDINAL
 FROM   ALARM WHERE CATEGORY_VALUE IN ('Autonomous AP','Cisco UCS Series','Routers','Switches and Hubs','Wireless Controller');

 -- CREATE OBJECT TYPE TO HOLD THE ROWS FROM ALARM TABLE
 TYPE C_ALARM_TAB IS TABLE OF C_ALARM%ROWTYPE;
 C_ALARM_ROWS C_ALARM_TAB;

 -- DECLARE OBJECT TYPE TO HOLD MAPPING BETWEEN PRODUCT FAMILY AND DEFAULT CATEGORY
 TYPE PRODUCT_FAMILY_MAP IS TABLE OF EVENT.CATEGORY_VALUE%TYPE INDEX BY VARCHAR2(255);
 PRODUCT_FAMILY_MAPPING PRODUCT_FAMILY_MAP;


 -- DECLARE OBJECT TYPE TO HOLD MAPPING BETWEEN DEFAULT CATEGORY AND ORDINAL VALUE
 TYPE CATEGORY_ORDINAL_MAP IS TABLE OF EVENT.CATEGORY_VALUE%TYPE INDEX BY VARCHAR2(255);
 CATEGORY_ORDINAL_MAPPING CATEGORY_ORDINAL_MAP;
  
 
BEGIN
 -- ADDING ELEMENTS TO THE DEFAULT CATEGORY AND PRODUCT FAMILY MAPPING TABLE
 PRODUCT_FAMILY_MAPPING('Autonomous AP') := 'AP';
 PRODUCT_FAMILY_MAPPING('Cisco UCS Series') := 'Compute Servers';
 PRODUCT_FAMILY_MAPPING('Routers') := 'Switches and Routers';
 PRODUCT_FAMILY_MAPPING('Switches and Hubs') := 'Switches and Routers';
 PRODUCT_FAMILY_MAPPING('Wireless Controller') := 'Controller';

 -- ADDING ELEMENTS TO THE CATEGORY AND ORDINAL MAPPING TABLE
 CATEGORY_ORDINAL_MAPPING('AP') := 1;
 CATEGORY_ORDINAL_MAPPING('Compute Servers') := 20;
 CATEGORY_ORDINAL_MAPPING('Switches and Routers') := 25;
 CATEGORY_ORDINAL_MAPPING('Controller') := 13;  


 DBMS_OUTPUT.PUT_LINE('BEGINNING UPGRADE SCRIPT PRODUCTFAMILYMIGRATION.SQL');

 DBMS_OUTPUT.PUT_LINE('START TIME -:'||TO_CHAR (SYSTIMESTAMP)); 
 -- UPDATE EVENT TABLE DATA
 OPEN C_EVENT;

 LOOP
  FETCH C_EVENT BULK COLLECT INTO C_EVENT_ROWS LIMIT 300;
  EXIT WHEN C_EVENT_ROWS.COUNT = 0;
  FOR INDX IN 1 .. C_EVENT_ROWS.COUNT
  LOOP

     E_CATEGORYNAME := C_EVENT_ROWS(INDX).CATEGORY_VALUE;
     C_EVENT_ROWS(INDX).PRODUCTFAMILY := E_CATEGORYNAME;
     C_EVENT_ROWS(INDX).CATEGORY_VALUE := PRODUCT_FAMILY_MAPPING(E_CATEGORYNAME);
     C_EVENT_ROWS(INDX).CATEGORY_ORDINAL := CATEGORY_ORDINAL_MAPPING(PRODUCT_FAMILY_MAPPING(E_CATEGORYNAME));
     DBMS_OUTPUT.PUT_LINE( 'LOOPING, C_EVENT%ROWCOUNT = ' || C_EVENT%ROWCOUNT ); 
    EXIT WHEN C_EVENT%NOTFOUND;
     BEGIN
    FORALL INDX IN 1 .. C_EVENT_ROWS.COUNT SAVE EXCEPTIONS
    UPDATE "WCSDBA"."EVENT" SET PRODUCTFAMILY = C_EVENT_ROWS(INDX).PRODUCTFAMILY, CATEGORY_VALUE = C_EVENT_ROWS(INDX).CATEGORY_VALUE, CATEGORY_ORDINAL=C_EVENT_ROWS(INDX).CATEGORY_ORDINAL WHERE ROWID = C_EVENT_ROWS(INDX).ROWID;
    EXCEPTION   
         WHEN OTHERS
         THEN   
            DBMS_OUTPUT.PUT_LINE (DBMS_UTILITY.FORMAT_ERROR_STACK);   
            DBMS_OUTPUT.PUT_LINE ('UPDATED ' || SQL%ROWCOUNT || ' rows.');   

            FOR INDX IN 1 .. SQL%BULK_EXCEPTIONS.COUNT   
            LOOP   
        DBMS_OUTPUT.PUT_LINE (   
       'ERROR '   
           || INDX   
           || ' OCCURRED ON INDEX '   
           || SQL%BULK_EXCEPTIONS (INDX).ERROR_INDEX   
           || '  WITH ERROR CODE '   
           || SQL%BULK_EXCEPTIONS (INDX).ERROR_CODE);   
          END LOOP;   
     END;
     COMMIT;
  END LOOP;
  COMMIT;
 END LOOP;
 IF C_EVENT%ROWCOUNT = 0 THEN
 DBMS_OUTPUT.PUT_LINE('NO DATA PRESENT IN EVENT TABLE FOR MIGRATION. HENCE NOT POPULATING PRODUCT FAMILY COLUMN');
 ELSE 
 DBMS_OUTPUT.PUT_LINE('SUCCESSFULLY UPDATED '||C_EVENT%ROWCOUNT || ' EVENT RECORDS');
 END IF;
 CLOSE C_EVENT;      
 
 OPEN C_ALARM;
 
  LOOP
   FETCH C_ALARM BULK COLLECT INTO C_ALARM_ROWS LIMIT 100;
   EXIT WHEN C_ALARM_ROWS.COUNT = 0;
   FOR INDX IN 1 .. C_ALARM_ROWS.COUNT
   LOOP
 
    A_CATEGORYNAME := C_ALARM_ROWS(INDX).CATEGORY_VALUE;
    C_ALARM_ROWS(INDX).PRODUCTFAMILY := A_CATEGORYNAME;
    C_ALARM_ROWS(INDX).CATEGORY_VALUE := PRODUCT_FAMILY_MAPPING(A_CATEGORYNAME);
    C_ALARM_ROWS(INDX).CATEGORY_ORDINAL := CATEGORY_ORDINAL_MAPPING(PRODUCT_FAMILY_MAPPING(A_CATEGORYNAME));
    DBMS_OUTPUT.PUT_LINE( 'LOOPING, C_ALARM%ROWCOUNT = ' || C_ALARM%ROWCOUNT ); 
   EXIT WHEN C_ALARM%NOTFOUND;
    BEGIN
     FORALL INDX IN 1 .. C_ALARM_ROWS.COUNT SAVE EXCEPTIONS
     UPDATE "WCSDBA"."ALARM" SET PRODUCTFAMILY = C_ALARM_ROWS(INDX).PRODUCTFAMILY, CATEGORY_VALUE = C_ALARM_ROWS(INDX).CATEGORY_VALUE, CATEGORY_ORDINAL=C_ALARM_ROWS(INDX).CATEGORY_ORDINAL WHERE ROWID = C_ALARM_ROWS(INDX).ROWID;
     EXCEPTION   
         WHEN OTHERS
         THEN   
            DBMS_OUTPUT.PUT_LINE (DBMS_UTILITY.FORMAT_ERROR_STACK);   
            DBMS_OUTPUT.PUT_LINE ('UPDATED ' || SQL%ROWCOUNT || ' rows.');   
 
            FOR INDX IN 1 .. SQL%BULK_EXCEPTIONS.COUNT   
            LOOP   
        DBMS_OUTPUT.PUT_LINE (   
       'ERROR '   
           || INDX   
           || ' OCCURRED ON INDEX '   
           || SQL%BULK_EXCEPTIONS (INDX).ERROR_INDEX   
           || '  WITH ERROR CODE '   
           || SQL%BULK_EXCEPTIONS (INDX).ERROR_CODE);   
     END LOOP;   
    END;
    COMMIT;
   END LOOP;
   COMMIT;
  END LOOP;
  IF C_ALARM%ROWCOUNT = 0 THEN
   DBMS_OUTPUT.PUT_LINE('NO DATA PRESENT IN ALARM TABLE FOR MIGRATION. HENCE NOT POPULATING PRODUCT FAMILY COLUMN');
   ELSE 
   DBMS_OUTPUT.PUT_LINE('SUCCESSFULLY UPDATED '||C_ALARM%ROWCOUNT || ' ALARM RECORDS');
  END IF;
 CLOSE C_ALARM;  
 
 DBMS_OUTPUT.PUT_LINE('END TIME -:'||TO_CHAR (SYSTIMESTAMP));

 EXCEPTION WHEN OTHERS THEN
 DBMS_OUTPUT.PUT_LINE('ERROR IN PRODUCTFAMILYMIGRATION.SQL - ENDED' || ':' || SQLERRM) ;
END;
/
SET SERVEROUTPUT OFF;

UPDATE "WCSDBA"."ALARM" 
SET ... ,
   CATEGORY_VALUE = decode(C_ALARM_ROWS(INDX).CATEGORY_VALUE,'Autonomous AP','AP',etc)

alter session enable parallel dml;

update /*+ parallel */ EVENT
set 
PRODUCTFAMILY = category_name,
CATEGORY_VALUE = decode(category_namem,'Autonomous AP','AP', etc etc )
CATEGORY_ORDINAL= decode(....)
WHERE CATEGORY_VALUE IN ('Autonomous AP','Cisco UCS Series','Routers','Switches and Hubs','Wireless Controller');