摘要：Oracle 12c 能否在2小時內在線完成一張14億條記錄的表結構字段類型變更

概述

1. 前面分享過Oracle大表在線修改的腳本（在線重定義），經過幾輪的測試發現，都存在些缺陷，效率始終不是很滿意。這次把索引和統計信息拆出來后發現效率相對算是最高的。

2. 在線重定義的目標，是對在線業務影響最小，通過最短的鎖表時間來實現表結構的變更，鎖表只發生在finish_redef_table過程中，正式切換前先執行sync_interim_table過程異步同步數據，以盡可能減少業務影響。

3. 由于是要對客戶的核心業務變更，按管理要求沒辦法提前執行finish_redef_table前的過程，且維護窗口時間有限，業務又不能完全停掉，才有了這次的測試。

4. 主要測試常見的2種場景，如下:

• 場景1：
  復制全部依賴 - COPY_TABLE_DEPENDENTS（索引 + 約束 + 統計信息），觸發器和權限這種基本沒有，就沒有復制。

• 優點：操作方便，腳本直接把原表所有依賴全部復制過去，改后的表直接使用，不需要額外處理，適合百萬或千萬的表，且對效率沒要求可用。

• 缺點：上億的表測試發現效率非常低。

• 場景2：
  有主鍵的表只復制約束 - COPY_TABLE_DEPENDENTS（會復制主鍵和唯一索引），其它索引和統計信息等重定義完成后再開并行重建和收集，這里要補充說明一下為什么要復制約束，因為創建主鍵不能并行操作，等重定義完成數據轉換后，相當于在普通大表上創建主鍵，效率非常低。

• 優點：目前針對10億以上的表測試發現效率是最高的，14億的表全部弄完約2小時左右。

• 缺點：操作過程稍微麻煩一點，別的還好。

• 復制規則，如下：

copy_indexes     => 0,
copy_triggers    => FALSE,
copy_constraints => TRUE,
copy_privileges  => FALSE,
ignore_errors    => FALSE,
num_errors       => num_errors,
copy_statistics  => FALSE);

由于場景1的效率比較差，我這里就只列舉場景2的測試過程，后續實際業務變更也是在場景2中進行，以下是整個變更過程：

1. 硬軟配置一般，如下：

• CPU：Intel? Xeon? CPU E7-4820 v3 @ 1.90GHz（物理4個，10核心，80個邏輯cpu）

• 內存：500 GB

• 存儲：華為某型號

• 數據庫軟件：Oracle 12.2 Nocdb RAC，未打補丁。

1. 我們先看一下原表數據行數,接近14億條，人工造的，表實際大小和生產相差1倍以上。

SQL> select /*+ parallel(40) */ count(*) from OM_OFFERING_INST_TEST;
  COUNT(*)----------1399999996
Elapsed: 00:00:17.39

1. 創建臨時表，有35個分區，部份省略了，主鍵、索引等都不要建。

CREATE TABLE "CUSTINFO"."INT_OM_OFFERING_INST_TEST" (    "BUSINESS_SEQ"               VARCHAR2(20),    "PROD_ID"                    NUMBER(20, 0),    "OFFERING_INST_ID"           NUMBER(20, 0),    "OFFERING_ID"                NUMBER(20, 0),    "OFFERING_NAME"              VARCHAR2(256),    "OFFERING_CODE"              VARCHAR2(50),    "CUST_TYPE"                  VARCHAR2(20),    "CUST_ID"                    NUMBER(20, 0),    "BRAND"                      VARCHAR2(50),
......    "RECORD_STATUS"              NUMBER(3, 0) DEFAULT 1)    PARTITION BY LIST ( "BE_ID" ) ( PARTITION "P_000" VALUES ( '000' ),        PARTITION "P_001" VALUES ( '001' ),        PARTITION "P_002" VALUES ( '002' ) ,        PARTITION "P_100" VALUES ( '100' ) ,        PARTITION "P_200" VALUES ( '200' ) ,
..........

1. 定義參數，設置并行和行遷移

define USERNAME = 'CUSTINFO'; --用戶名define SOURCE_TAB = 'OM_OFFERING_INST_TEST';-- 原表名define INT_TAB = 'INT_OM_OFFERING_INST_TEST';-- 臨時表名，需要手工提前創建define PARALLELS = 35; --并行數，這里設的分區數alter session enable parallel dml ;alter session force parallel dml parallel &PARALLELS;alter session force parallel query parallel &PARALLELS;alter table &INT_TAB enable row movement; --臨時表開啟行遷移

1. 檢查原表是否支持在線重定義，比較快，僅用了1秒不到。

SQL> begin
  2      dbms_redefinition.can_redef_table(uname        => '&USERNAME',  3                                          tname        => '&SOURCE_TAB',  4                                          options_flag => DBMS_REDEFINITION.CONS_USE_PK);
  5  end;
  6  /
PL/SQL procedure successfully completed
Executed in 0.027 seconds

1. 映射字段類型，啟動重定義進程，用了近10分鐘，稍微有點慢。從這里開始到結束， 如果中途有錯誤，想要重來，需要調abort_redef_table過程取消任務。

SQL> set timing on;
SQL> begin
  2    DBMS_REDEFINITION.START_REDEF_TABLE(uname        => '&USERNAME',  3                                        orig_table   => '&SOURCE_TAB',  4                                        int_table    => '&INT_TAB',  5                                         col_mapping  => 'to_number(owner_party_role_id) owner_party_role_id,
  7                                                      to_number(offering_inst_id) offering_inst_id,
  8                                                      to_number(subs_id) subs_id,
  9                                                      to_number(group_id) group_id,
  10                                                      to_number(apply_obj_id) apply_obj_id', --這里只列舉了需要變更的字段類型
  11                                        options_flag => DBMS_REDEFINITION.CONS_USE_PK);
  12  end;
  13  /
  
PL/SQL procedure successfully completed
Executed in 576.565 seconds

1. 復制依賴對象，這里只復制了主鍵約束，耗時54分鐘，如果全部復制，我在測試跑了3個小時沒有結果，只接Kill了。

SQL> DECLARE
  2      num_errors PLS_INTEGER;
  3  BEGIN
  4      DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(uname            => '&USERNAME',  5                                              orig_table       => '&SOURCE_TAB',  6                                              int_table        => '&INT_TAB',  7                                              copy_indexes     => 0,  8                                              copy_triggers    => FALSE,  9                                              copy_constraints => TRUE, 10                                              copy_privileges  => FALSE, 11                                              ignore_errors    => FALSE, 12                                              num_errors       => num_errors, 13                                              copy_statistics  => FALSE);
 14  END;
 15  /
 
 
PL/SQL procedure successfully completed
Executed in 3230.441 seconds

1. 異步同步數據，耗時28秒，比較快。

SQL> begin
  2      dbms_redefinition.sync_interim_table(uname        => '&USERNAME',  3                                             orig_table => '&SOURCE_TAB',  4                                             int_table  => '&INT_TAB');
  5  end;
  6  /
  
PL/SQL procedure successfully completed
Executed in 27.908 seconds

1. 完成在線重定義，結束任務,耗時73秒，也是比較快。

SQL> begin
  2  dbms_redefinition.finish_redef_table(uname      => '&USERNAME',  3                                       orig_table => '&SOURCE_TAB',  4                                       int_table  => '&INT_TAB');
  5  end;
  6  /
  
  
PL/SQL procedure successfully completed
Executed in 72.302 seconds

1. 創建索引，這個分區表上的索引不多，就3個普通索引，開53個并行，平均每個耗時4分鐘左右，累計13分鐘。

SQL> CREATE INDEX "CUSTINFO"."INX_OM_OFFERING_INST_TEST_CUSTID" ON "CUSTINFO"."OM_OFFERING_INST_TEST" ("CUST_ID") online parallel 35;
Index created
Executed in 257.138 seconds
SQL> CREATE INDEX "CUSTINFO"."INX_OM_OFFERING_INST_TEST_GROUPID" ON "CUSTINFO"."OM_OFFERING_INST_TEST" ("GROUP_ID") online parallel 35;
Index created
Executed in 244.853 seconds
SQL> CREATE INDEX "CUSTINFO"."INX_OM_OFFERING_INST_TEST_SUBSID" ON"CUSTINFO"."OM_OFFERING_INST_TEST" ("SUBS_ID") online parallel 35;
Index created
Executed in 261.665 seconds

1. 收集統計信息,同樣也是開35個并行，耗時4分鐘左右。CASCADE => true表示收集表、列、索引等。

SQL> exec dbms_stats.gather_table_stats(ownname => 'CUSTINFO',tabname => 'OM_OFFERING_INST_TEST',CASCADE => true,degree => 35);
PL/SQL procedure successfully completed.
Elapsed: 00:04:18.35

1. 取消表、索引上的并行度，檢查字段是否修改成功，刪除臨時表，至此整個修改過程結束，這里耗時約10分鐘左右。

--取消表上的并行alter table &SOURCE_TAB noparallel;--取消索引上的并行alter index INX_OM_OFFERING_INST_TEST_CUSTID noparallel;alter index INX_OM_OFFERING_INST_TEST_GROUPID noparallel;alter index INX_OM_OFFERING_INST_TEST_SUBSID noparallel;    
--刪除臨時表drop table &INT_TAB;