oracle 大批量数据更新

 

现场如下：

有表

A(ID,CODE,C....C1，C2,C3)，300W数据。C1,C2,C3是空的

B(ID,CODE,C.....C1，C2,C3)，1万数据

现在要将A表中的C1到C3几个字段通过关联查询B表更新。

自己尝试了几个方案：不仅速度慢，还容易报错：ORA-01555: 快照过旧: 回退段号 6352 (名称为 "_SYSSMU6352_1445123658$") 过小

 

方案1：直接关联查询更新：

MERGE INTO YTRUT.T_RUT_WAYBILL_SUM A
USING (SELECT K.CODE,
              K.MANAGEMENT_AREA,
              K.REGION_MANAGE,
              K.TRANSPORT_CENTER,
              K.BRANCH,
              K.SUB_DEPARTMENT
         FROM YTMDM.MV_MDM_ORG_RPT K) B
ON (A.ORIG_ORG = B.CODE)
WHEN MATCHED THEN
  UPDATE
     SET A.DEST_MANAGEMENT_AREA  = B.MANAGEMENT_AREA,
         A.DEST_REGION_MANAGE    = B.REGION_MANAGE,
         A.DEST_TRANSPORT_CENTER = B.TRANSPORT_CENTER,
         A.DEST_BRANCH           = B.BRANCH,
         A.DEST_SUB_DEPARTMENT   = B.SUB_DEPARTMENT;
COMMIT;

这样发现效率太慢，事物太大，========================

方案2：

用指针，

又分2中情况：

1是在来源表用CURSOR,

DECLARE
  CURSOR CUR_ORG IS
    SELECT K.CODE,
           K.MANAGEMENT_AREA,
           K.REGION_MANAGE,
           K.TRANSPORT_CENTER,
           K.BRANCH,
           K.SUB_DEPARTMENT
      FROM YTMDM.MV_MDM_ORG_RPT K;
  C_ROW CUR_ORG%ROWTYPE;
BEGIN

  FOR C_ROW IN CUR_ORG LOOP
    UPDATE YTRUT.T_RUT_WAYBILL_SUM A
       SET A.DEST_MANAGEMENT_AREA  = C_ROW.MANAGEMENT_AREA,
           A.DEST_REGION_MANAGE    = C_ROW.REGION_MANAGE,
           A.DEST_TRANSPORT_CENTER = C_ROW.TRANSPORT_CENTER,
           A.DEST_BRANCH           = C_ROW.BRANCH,
           A.DEST_SUB_DEPARTMENT   = C_ROW.SUB_DEPARTMENT
     WHERE A.ORIG_ORG = C_ROW.CODE
       AND A.TARGET_DAY = TRUNC(SYSDATE) - 4;
    COMMIT;
  
    UPDATE YTRUT.T_RUT_WAYBILL_SUM A
       SET A.DEST_MANAGEMENT_AREA  = C_ROW.MANAGEMENT_AREA,
           A.DEST_REGION_MANAGE    = C_ROW.REGION_MANAGE,
           A.DEST_TRANSPORT_CENTER = C_ROW.TRANSPORT_CENTER,
           A.DEST_BRANCH           = C_ROW.BRANCH,
           A.DEST_SUB_DEPARTMENT   = C_ROW.SUB_DEPARTMENT
     WHERE A.DEST_ORG = C_ROW.CODE
       AND A.TARGET_DAY = TRUNC(SYSDATE) - 4;
    COMMIT;
  
  END LOOP;
END;

 

2，是在目标表用CURSOR

DECLARE
  CURSOR CUR_D IS
    SELECT DISTINCT T.DEST_ORG
      FROM YTRUT.T_RUT_WAYBILL_SUM T
     WHERE T.TARGET_DAY = TRUNC(SYSDATE) - 3;
  C_ROW CUR_D%ROWTYPE;
BEGIN
  --目的网点的  
  FOR C_ROW IN CUR_D LOOP
    MERGE INTO YTRUT.T_RUT_WAYBILL_SUM A
    USING (SELECT K.CODE,
                  K.MANAGEMENT_AREA,
                  K.REGION_MANAGE,
                  K.TRANSPORT_CENTER,
                  K.BRANCH,
                  K.SUB_DEPARTMENT
             FROM YTMDM.MV_MDM_ORG_RPT K
            WHERE K.CODE = C_ROW.DEST_ORG) B
    ON (A.DEST_ORG = B.CODE AND A.TARGET_DAY = TRUNC(SYSDATE) - 4)
    WHEN MATCHED THEN
      UPDATE
         SET A.DEST_MANAGEMENT_AREA  = B.MANAGEMENT_AREA,
             A.DEST_REGION_MANAGE    = B.REGION_MANAGE,
             A.DEST_TRANSPORT_CENTER = B.TRANSPORT_CENTER,
             A.DEST_BRANCH           = B.BRANCH,
             A.DEST_SUB_DEPARTMENT   = B.SUB_DEPARTMENT;
    COMMIT;
  END LOOP;
END;

 效率都很慢，

 

方案4：

使用中间表代替，先关联查询select将结果集放入中间表，再将原表数据清除，将中间表数据Insert进去

DECLARE
BEGIN
--STEP 1,清空临时表
  EXECUTE IMMEDIATE 'TRUNCATE TABLE YTRUT.T_RUT_WAYBILL_SUM_WCL';
  --TRUNCATE TABLE T_RUT_WAYBILL_SUM_WCL;
  --CREATE TABLE YTRUT.T_RUT_WAYBILL_SUM_WCL AS SELECT * FROM YTRUT.T_RUT_WAYBILL_SUM WHERE 1 = 2;
--STEP2，像临时表插入关联查询数据
  INSERT INTO YTRUT.T_RUT_WAYBILL_SUM_WCL
    SELECT T.ORIG_ORG,
           T.DEST_ORG,
           T.TOTAL_COUNT,
           T.TOTAL_WEIGHT,
           T.TARGET_DAY,
           T.ORIG_AREA,
           T.DEST_AREA,
           T.ORIG_PROVINCE,
           T.DEST_PROVINCE,
　　　　　　......
           T.COUNT_48H,
           T.WEIGHT_48H,
           K1.MANAGEMENT_AREA,
           K1.REGION_MANAGE,
           K1.TRANSPORT_CENTER,
           K1.BRANCH,
           K1.SUB_DEPARTMENT,
           K2.MANAGEMENT_AREA,
           K2.REGION_MANAGE,
           K2.TRANSPORT_CENTER,
           K2.BRANCH,
           K2.SUB_DEPARTMENT
      FROM YTRUT.T_RUT_WAYBILL_SUM T,
           YTMDM.MV_MDM_ORG_RPT    K1,
           YTMDM.MV_MDM_ORG_RPT    K2
     WHERE K1.CODE = T.ORIG_ORG
       AND K2.CODE = T.DEST_ORG
       AND T.TARGET_DAY = TRUNC(SYSDATE) - 2;
       
       commit;
       ----STEP3，将原表数据清除，
       DELETE FROM  YTRUT.T_RUT_WAYBILL_SUM T WHERE T.TARGET_DAY = TRUNC(SYSDATE) -2;
       commit;
       --STEP4，将临时表数据插入原表
       INSERT INTO YTRUT.T_RUT_WAYBILL_SUM SELECT * FROM YTRUT.T_RUT_WAYBILL_SUM_WCL ;
       commit;
END;

效果杠杠的！速度嗖嗖的！只用了不到半个小时就把几百万数据更新好了。

 

 

总结：

此类更新操作

无非是使用 CURSOR,LOOP,等循环批量操作数据，由于update是比较消耗资源的方法，会有undo和redo操作，因此换用insert效率上会快很多。