Oracle数据迁移[数据修改]-数据调优

Question

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

系统:Oracle11g

表信息

表名: Tab1 Tot行: 520.000.000 AVG行连: 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. Insert + delete:选择现有数据，插入需要修改的新记录，并删除旧记录--这是最慢的方法~21h
2. merge :使用merge命令更新现有数据--作为最快的方法~16h
3. 更新:更新现有数据~18h

使用上面的解决方案，我遇到了一些问题:如果使用/*+并行(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)

ChildTable

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)

Answer 1

方法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块对合并进行批处理。这些都是逻辑上并置的，因此应该更快一些。

Answer 2

哇，5.2亿行！然而，更新其中250万份只占0.5%，这应该是可行的。由于不知道您的数据，我的第一个假设是，合并中的Tab1 x Tab1的自连接占用了大部分时间。可能还有许多连接到迁移和child_tables。而索引T_IN_1、2和3也需要维护。

正如您所说的，要更新的行是固定的，我会尝试准备繁重的工作。这不会锁定桌子，也不会计入停机时间：

CREATE TABLE migration_temp NOLOGGING AS
SELECT t.t_id, 
       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;

我省略了旧/新product_ids的部分，因为我不完全理解它应该如何工作，但希望这不是一个问题。

方法1是通过主键连接：

ALTER TABLE migration_temp ADD CONSTRAINT pk_migration_temp PRIMARY KEY(t_id);
EXEC DBMS_STATS.GATHER_TABLE_STATS(null,'migration_temp');

MERGE INTO Tab1 t USING migration_temp m ON (t.t_id = m.t_id)
 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);

我不喜欢批量更新。由于您有时间估计，看起来您有一个测试系统。我建议试一试，一次试一次。

Answer 3

如果方法1和方法2仍然太慢，您可以遵循分区的想法。例如，引入一个列来区分要迁移的行。由于DEFAULT ... NOT NULL，这将是非常快的：

ALTER TABLE Tab1 ADD (todo NUMBER DEFAULT 0 NOT NULL);

现在，将您的表划分为两个部分，一个部分包含迁移数据，另一个部分包含您不会接触的其他部分。在应用程序运行时，我没有引入分区的丰富经验，但我认为它是可以解决的，例如在线重新定义或

ALTER TABLE Tab1 MODIFY 
  PARTITION BY LIST (todo) (
  PARTITION pdonttouch VALUES (0),
  PARTITION pmigration VALUES (1) 
) ONLINE UPDATE INDEXES (
  T_ID_PK GLOBAL, T_IN_1  GLOBAL,
  T_IN_2  GLOBAL, T_IN_3  GLOBAL
);

现在，您可以标识要移动的行。这可以逐行完成，不影响其他进程，不应计入停机时间。迁移行将从分区pdonttouch移动到分区pmigration，因此需要启用行移动。

ALTER TABLE Tab1 ENABLE ROW MOVEMENT;
UPDATE Tab1 SET todo=1 WHERE .... JOIN ...;

现在您可以在分区PMIGRATION上工作，并在那里更新数据。这应该比原始表上的快得多，因为分区的大小只占整个表的0.5%。不过，我不知道这些索引。

理论上，您可以创建一个具有与PMIGRATION相同的结构和数据的表，在表上工作，一旦完成，就可以用EXCHANGE PARTITION交换分区和工作表。再说一遍，我不知道索引。