14億條記錄，12c 做不到2小時內變更表結構欄位型別?

概述

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;

總結

1. 總計執行耗時：95分鐘，不到2小時，效率上暫時能接受，如果有更好的辦法，求拍磚，謝謝。

• 檢查表定義 1秒
• 啟動重定義程式 10分鐘
• 複製依賴 54分鐘
• 非同步同步資料 28秒
• 執行結束任務 73秒
• 建立索引 13分鐘
• 收集統計資訊 4分鐘
• 取消並行檢查刪除臨時表 10分鐘