摘要：Oracle 12c 能否在2小時(shí)內(nèi)在線完成一張14億條記錄的表結(jié)構(gòu)字段類型變更

概述

1. 前面分享過(guò)Oracle大表在線修改的腳本（在線重定義），經(jīng)過(guò)幾輪的測(cè)試發(fā)現(xiàn)，都存在些缺陷，效率始終不是很滿意。這次把索引和統(tǒng)計(jì)信息拆出來(lái)后發(fā)現(xiàn)效率相對(duì)算是最高的。

2. 在線重定義的目標(biāo)，是對(duì)在線業(yè)務(wù)影響最小，通過(guò)最短的鎖表時(shí)間來(lái)實(shí)現(xiàn)表結(jié)構(gòu)的變更，鎖表只發(fā)生在finish_redef_table過(guò)程中，正式切換前先執(zhí)行sync_interim_table過(guò)程異步同步數(shù)據(jù)，以盡可能減少業(yè)務(wù)影響。

3. 由于是要對(duì)客戶的核心業(yè)務(wù)變更，按管理要求沒(méi)辦法提前執(zhí)行finish_redef_table前的過(guò)程，且維護(hù)窗口時(shí)間有限，業(yè)務(wù)又不能完全停掉，才有了這次的測(cè)試。

4. 主要測(cè)試常見(jiàn)的2種場(chǎng)景，如下:

• 場(chǎng)景1：
  復(fù)制全部依賴 - COPY_TABLE_DEPENDENTS（索引 + 約束 + 統(tǒng)計(jì)信息），觸發(fā)器和權(quán)限這種基本沒(méi)有，就沒(méi)有復(fù)制。

• 優(yōu)點(diǎn)：操作方便，腳本直接把原表所有依賴全部復(fù)制過(guò)去，改后的表直接使用，不需要額外處理，適合百萬(wàn)或千萬(wàn)的表，且對(duì)效率沒(méi)要求可用。

• 缺點(diǎn)：上億的表測(cè)試發(fā)現(xiàn)效率非常低。

• 場(chǎng)景2：
  有主鍵的表只復(fù)制約束 - COPY_TABLE_DEPENDENTS（會(huì)復(fù)制主鍵和唯一索引），其它索引和統(tǒng)計(jì)信息等重定義完成后再開(kāi)并行重建和收集，這里要補(bǔ)充說(shuō)明一下為什么要復(fù)制約束，因?yàn)閯?chuàng)建主鍵不能并行操作，等重定義完成數(shù)據(jù)轉(zhuǎn)換后，相當(dāng)于在普通大表上創(chuàng)建主鍵，效率非常低。

• 優(yōu)點(diǎn)：目前針對(duì)10億以上的表測(cè)試發(fā)現(xiàn)效率是最高的，14億的表全部弄完約2小時(shí)左右。

• 缺點(diǎn)：操作過(guò)程稍微麻煩一點(diǎn)，別的還好。

• 復(fù)制規(guī)則，如下：

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

由于場(chǎng)景1的效率比較差，我這里就只列舉場(chǎng)景2的測(cè)試過(guò)程，后續(xù)實(shí)際業(yè)務(wù)變更也是在場(chǎng)景2中進(jìn)行，以下是整個(gè)變更過(guò)程：

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

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

• 內(nèi)存：500 GB

• 存儲(chǔ)：華為某型號(hào)

• 數(shù)據(jù)庫(kù)軟件：Oracle 12.2 Nocdb RAC，未打補(bǔ)丁。

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

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

1. 創(chuàng)建臨時(shí)表，有35個(gè)分區(qū)，部份省略了，主鍵、索引等都不要建。

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. 定義參數(shù)，設(shè)置并行和行遷移

define USERNAME = 'CUSTINFO'; --用戶名define SOURCE_TAB = 'OM_OFFERING_INST_TEST';-- 原表名define INT_TAB = 'INT_OM_OFFERING_INST_TEST';-- 臨時(shí)表名，需要手工提前創(chuàng)建define PARALLELS = 35; --并行數(shù)，這里設(shè)的分區(qū)數(shù)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; --臨時(shí)表開(kāi)啟行遷移

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. 映射字段類型，啟動(dòng)重定義進(jìn)程，用了近10分鐘，稍微有點(diǎn)慢。從這里開(kāi)始到結(jié)束， 如果中途有錯(cuò)誤，想要重來(lái)，需要調(diào)abort_redef_table過(guò)程取消任務(wù)。

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. 復(fù)制依賴對(duì)象，這里只復(fù)制了主鍵約束，耗時(shí)54分鐘，如果全部復(fù)制，我在測(cè)試跑了3個(gè)小時(shí)沒(méi)有結(jié)果，只接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. 異步同步數(shù)據(jù)，耗時(shí)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. 完成在線重定義，結(jié)束任務(wù),耗時(shí)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. 創(chuàng)建索引，這個(gè)分區(qū)表上的索引不多，就3個(gè)普通索引，開(kāi)53個(gè)并行，平均每個(gè)耗時(shí)4分鐘左右，累計(jì)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. 收集統(tǒng)計(jì)信息,同樣也是開(kāi)35個(gè)并行，耗時(shí)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. 取消表、索引上的并行度，檢查字段是否修改成功，刪除臨時(shí)表，至此整個(gè)修改過(guò)程結(jié)束，這里耗時(shí)約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;    
--刪除臨時(shí)表drop table &INT_TAB;