更新 600k 行，大多数列是 Varchar2(45).此更新花费的时间太长(30 分钟).有没有更好的方法来编写更新

Question

--使用更正地址更新地址表中的地址字段

临时表中的列是地址表中的列，是地址表中要修改的列。此更新需要 30 分钟，不适用于我们的生产环境。如何编写此代码以使其运行得更快？

DECLARE
       --Fetching data from temp table created for address update
       CURSOR My_Cursor
       IS
            --Fetching records which have a valid error code which is 2,4,5,20,46
            SELECT A.*
              FROM Fix_Dba.Temp_Address_Cleanup A, Fix_Dba.Addresses B
             WHERE A.Address_Id = B.Address_Id
          ORDER BY A.Address_Id;
   TYPE Plt_Cursor IS TABLE OF My_Cursor%ROWTYPE
      INDEX BY BINARY_INTEGER;
   Plt_Cursorarr     Plt_Cursor;            --Array declaration for the cursor
   --Track the address_ids which are not found in address_table
   CURSOR Record_Not_Found
   IS
        SELECT A.Address_Id
          FROM Fix_Dba.Temp_Address_Cleanup A
         WHERE A.Address_Id NOT IN (SELECT B.Address_Id
                                      FROM Fix_Dba.Addresses B)
      ORDER BY A.Address_Id;
   --declaring counter to commit records at certain intervals
   Counter           NUMBER := 0;
   V_Address_Id      NUMBER := 0;
   V_Err_Message     VARCHAR2 (5000);
   V_Efacts_Log_Id   NUMBER;
BEGIN
   OPEN My_Cursor;                                               --Open cursor
   FETCH My_Cursor BULK COLLECT INTO Plt_Cursorarr; --Fetching data which is required to update address fields
   CLOSE My_Cursor;
   IF Plt_Cursorarr.COUNT = 0 --Return if the count is zero and there are no records to process
   THEN
      RETURN;
   END IF;
   FOR I IN Plt_Cursorarr.FIRST .. Plt_Cursorarr.LAST --For loop declaration to update records
   LOOP
      BEGIN
         --Updating address fileds in address table after fetching correct data from temporary tables
         UPDATE Fix_Dba.Addresses A
            SET A.Address1 = Plt_Cursorarr (I).Address1_New, --Updating address line 1
                A.Address2 = Plt_Cursorarr (I).Address2_New, --Updating address line 2
                A.City = Plt_Cursorarr (I).City_New,           --Updating city
                A.State_Code = Plt_Cursorarr (I).State_Code_New, --Updating State
                A.Zip_Code = Plt_Cursorarr (I).Zip_Code_Addon, --Updating Zip code
                A.Update_Date = SYSDATE,
                A.Sysuser_Id_Updated_By = 1,
                A.Update_Source = 'LOG17690'
          WHERE A.Address_Id = Plt_Cursorarr (I).Address_Id; --Where clause for update
         Counter := Counter + 1; --Increment the counter to account for number of records
         IF MOD (Counter, 10000) = 0
         THEN                                    -- Commit every 10000 records
            COMMIT;
            Counter := 0; --Putting the counter value back to zero so that the counter for commit can restart
         END IF;
      EXCEPTION      --Handling exceptions... do we need to track address_ids?
         WHEN OTHERS
         THEN
            V_Err_Message := 'ERROR: ' || SQLCODE || ';' || SQLERRM;
            Fix_Dba.Dbpc_Blaster_Inserts.Write_Log (
               NULL,
               NULL,
               NULL,
               17690,
               'ADDRESS',
               V_Err_Message,
               'E',
               'AddressID',
               Plt_Cursorarr (I).Address_Id,
               V_Efacts_Log_Id);
      --           DBMS_OUTPUT.Put_Line ('Updated ' || SQL%ROWCOUNT || ' rows.');
      --           DBMS_OUTPUT.Put_Line ('Error occurred with error code: ' || SQL%BULK_EXCEPTIONS (I).ERROR_CODE);
      END;
   END LOOP;
   COMMIT;
   BEGIN
      OPEN Record_Not_Found;
      LOOP
         FETCH Record_Not_Found INTO V_Address_Id;
         EXIT WHEN Record_Not_Found%NOTFOUND;
         V_Err_Message := 'Address Id not found';
         Fix_Dba.Dbpc_Blaster_Inserts.Write_Log (NULL,
                                                 NULL,
                                                 NULL,
                                                 17690,
                                                 'ADDRESS',
                                                 V_Err_Message,
                                                 'E',
                                                 'AddressID',
                                                 V_Address_Id,
                                                 V_Efacts_Log_Id);
      END LOOP;
  CLOSE Record_Not_Found;

 END;
END;
/

Answer 1

您可以通过单个 MERGE 语句进行更新，例如：

MERGE INTO fix_dba.addresses tgt
  USING fix_dba.temp_address_cleanup src
    ON (tgt.address_id = src.address_id)
WHEN MATCHED THEN
  UPDATE SET tgt.address1 = src.address1_new,
             tgt.address2 = src.address2_new,
             tgt.city = src.city_new,
             tgt.state_code = src.state_code,
             tgt.zip_code = src.zip_code_new,
             tgt.update_date = SYSDATE,
             tgt.sysuser_id_updated_by = 1,
             tgt.update_source = 'LOG17690';

如果您担心日志记录错误，则始终可以使用 DML 错误日志记录来捕获错误(尽管对此有限制(。但是，我希望您对地址表的任何约束也位于清理表上，因此数据应该可以按原样合并。