时间戳

首页 > 解决方案 > Oracle 数据迁移【数据修改】——数据调优

sql - Oracle 数据迁移【数据修改】——数据调优

问题描述

我面临数据迁移，我的目标是在不到 8 小时内更新 2.5M 行，这是因为客户有一个有限的时间窗口可以停用服务。而且在这个执行过程中表不能被锁定，因为被其他程序使用，我只能锁定记录。执行将通过批处理完成。在这种情况下，迁移可能不是正确的词，最好说“改变数据”......

系统：Oracle 11g

表信息

表名：Tab1 总行数：520.000.000 平均行数：57

DESC Tab1;
Name             Null?    Type
---------------- -------- -----------
t_id             NOT NULL NUMBER
t_fk1_id                  NUMBER
t_fk2_id                  NUMBER
t_start_date     NOT NULL DATE
t_end_date                DATE
t_del_flag       NOT NULL NUMBER(1)
t_flag1          NOT NULL NUMBER(1)
f_falg2          NOT NULL NUMBER(1)
t_creation_date           DATE
t_creation_user           NUMBER(10)
t_last_update             DATE
t_user_update             NUMBER(10)
t_flag3                   NUMBER(1)

索引是：

  1. T_ID_PK [t_id] UNIQUE
  2. T_IN_1 [t_fk2_id,t_fk1_id,t_start_date,t_del_flag] NONUNIQUE
  3. T_IN_2 [t_last_update,t_fk2_id] NONUNIQUE
  4. T_IN_3 [t_fk2_id,t_fk1_id] NONUNIQUE

目前我已经想到了一些可能的解决方案，其中大部分我已经测试过：

  1. 插入+删除：选择现有数据，插入需要修改的新记录并删除旧记录[这个结果是最慢的方法~21h]
  2. 合并：使用合并命令更新现有数据[这个结果是最快的方法~16h]
  3. 更新：更新现有数据 [~18h]

使用上述解决方案，我遇到了一些问题，例如：如果使用 /*+ parallel(x) / 选项执行表被锁定， / + RESULT_CACHE */ 似乎在所有选择时间都没有影响。我的最后一个想法是按一个新列对表进行分区，并使用它来避免表锁定并继续解决方案 1。

这里用于 Merge 选项的查询（对于其他两个或多或少相同）：

DECLARE
v_recordset NUMBER;
v_row_count NUMBER;
v_start_subset NUMBER;
v_tot_loops NUMBER;
BEGIN
--set the values manually for example purpose, I've use the same values
v_recordset := 10000;
v_tot_loops := 10000;
  BEGIN
    SELECT NVL(MIN(MOD(m_id,v_recordset)), 99999)
    INTO v_start_subset 
    FROM MIGRATION_TABLE
    WHERE m_status = 0; -- 0=not migrated , 1=migrated
  END;
  FOR v_n_subset IN v_start_subset..v_tot_loops
    LOOP
      BEGIN
        MERGE INTO Tab1 T1
        USING (
          SELECT m.m_new_id, c2.c_id, t.t_id
          FROM MIGRATION_TABLE m
            JOIN Tab1 t ON t.t_fk_id = m.m_old_id
            JOIN ChildTable c ON c.c_id = t.t_fk2_id
            JOIN ChildTable c2 ON c.c_name = c2.c_name --c_name is an UNIQUE index of ChildTable
          WHERE MOD(m.m_id,v_recordset) = v_n_subset 
            AND c.c_fk_id = old_product_id --value obtained from another subsystem
            AND c2.c_fk_id = new_product_id --value obtained from another subsystem
            AND t.t_del_flag = 0 --not deleted items
        ) T2
        ON (T1.t_id = T2.t_id)
        WHEN MATCHED THEN
          UPDATE T1.t_fk_id = T2.m_new_id, T1.t_fk2_id = T2.c_id, T1.t_last_update = trunc(sysdate)
        ;
        --Update the record as migrated and proceed
        COMMIT;
      EXCEPTION WHEN OTHERS THEN
        ROLLBACK;
      END;
  END LOOP;
END;

在上面的脚本中，我删除了并行和缓存选项，但我已经测试了两者，但我没有获得任何错误结果。

任何人，请！你们能帮我解决这个问题吗，在一个多星期内我无法达到预期的时间，有什么想法吗？

MIGRATION_TABLE

CREATE TABLE MIGRATION_TABLE(
 m_customer_from VARCHAR2(5 BYTE),
 m_customer_to VARCHAR2(5 BYTE),
 m_old_id NUMBER(10,0) NOT NULL,
 m_new_id NUMBER(10,0) NOT NULL,
 m_status VARCHAR2(100 BYTE),
 CONSTRAINT M_MIG_PK_1
 (
  m_old_id 
 )
 ENABLE
)
CREATE UNIQUE INDEX M_MIG_PK_1 ON MIGRATION_TABLE (m_old_id ASC)

子表

CREATE TABLE ChildTable(
 c_id NUMBER(10, 0) NOTE NULL,
 c_fk_id NUMBER(10, 0),
 c_name VARCHAR2(100 BYTE),
 c_date DATE,
 c_note VARCHAR2(100 BYTE),
 CONSTRAINT C_CT_PK_1
 (
  c_id
 )
 ENABLE
)
CREATE UNIQUE INDEX C_CT_PK_1 ON ChildTable (c_id ASC)
CREATE UNIQUE INDEX C_CT_PK_2 ON ChildTable (c_name ASC, c_fk_id ASC)

标签： sqldatabaseoracleoracle11gmigration

解决方案

方法 2 与方法 1 类似，但它使用的是 ROWID 而不是主键。理论上，它应该因此更快一些。

CREATE TABLE migration_temp NOLOGGING AS
SELECT t.t_id, 
       t.rowid    AS rid,
       m.m_new_id AS new_fk1_id, 
       c2.c_id    AS new_fk2_id 
  FROM MIGRATION_TABLE m
  JOIN Tab1 t        ON t.t_fk1_id = m.m_old_id
  JOIN ChildTable c1 ON c1.c_id = t.t_fk2_id
  JOIN ChildTable c2 ON c1.c_name = c2.c_name
 WHERE t.t_del_flag = 0
 ORDER BY t.rowid;
EXEC DBMS_STATS.GATHER_TABLE_STATS(null,'migration_temp');

MERGE INTO Tab1 t USING migration_temp m ON (t.rowid = m.rid)
 WHEN MATCHED THEN UPDATE SET 
      t.t_fk1_id = m.new_fk1_id,
      t.t_fk2_id = m.new_fk2_id,
      t.t_last_update = trunc(sysdate);

您可以考虑基于 ROWID 块对 MERGE 进行批处理。这些往往在逻辑上位于同一位置，因此应该更快一些。

推荐阅读