Oracle 更新具有百万条记录的表中的列

嗨，

我已经浏览了您的论坛，了解如何更新具有数百万条记录的表

方法1-要创建临时表并进行必要的更改，请删除原始表并将临时表重命名为原始表。我没有采用这种方法，因为我不确定删除表时的依赖关系，而且表非常重要，不允许删除和创建新的表。

方法2-使用带限制的批量收集，并使用forall进行更新。我已经写了下面的脚本，它将在数据库升级或恢复条件期间调用。此脚本仅在从应用程序版本升级到另一个或将数据从以前的应用程序版本恢复到新的期间运行。因此，不会有来自应用程序的同时dml操作。这将在纯数据库迁移期间使用。

我已经写了下面的脚本。请验证并告诉我在哪里可以改进以获得更好的性能，记录。在回滚的情况下，我不确定我是否需要它，因为我可以更新我的记录会很好。

我有两个表，需要更新几列。对于少数列，我创建了类似结构 (键/值) 的地图对，从中填充数据。

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;

需要考虑的几件事

1) 您的PL/SQL表映射可以使用CASE语句或DECODE轻松完成，这意味着您不需要转换循环，即:

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

或在执行提取的初始游标定义上进行解码/大小写。

2) 假设您已经采取了所有可以提高选择/更新性能的步骤 (例如索引禁用等)，那么您可能需要考虑使用并行DML或DBMS_PARALLEL_EXECUTE并行进行工作

这让我印象深刻，因为整个操作可能很简单:

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');

类似的警报