20210324-技術-Oracle-Online新增及調整Table (Sub)Partition欄位+填入資料作法

需求邏輯

(用概念記錄一下這次的開發邏輯，順便做的思緒的統整)

這一次的處理，可達到以下需求：

• TableA新增一個欄位 ColumnB。
• 依照TableY的資料，填入ColumnB。
• 將原本TableA的Subpartition的欄位依據，從ColumnA 改為 ColumnB。
• 一切要 線上調整，不可以downtime.

參考文

最終採用 DBMS_REDEFINITION 實作：

1.  Performing a large correlated update – Learning is not a spectator sport
• 這篇直接達到我的需求，使用REDEFINITION調整Schema外，還同時可以「填資料」！
2. dbms_redefinition example
• 一篇標準的 DBMS_REDEFINITION 的使用方式，說明各步驟的用意。
3. DBMS_REDEFINITION (官網)
   

• 各函式的參數說明

更新大量資料(update data)的作法 參考文：

1. Efficient way to UPDATE bulk of records in Oracle Database - Oracle Database
• 做了各類Update的效能比較(裡面有6類的寫法，採「直接update」效率最差 )。
2. Oracle update tuning tips
• 各類的update說明
  
3. Update very large Oracle table tips
• 使用CTAS (Create Table As Select) 的速度最快
• (此篇沒有細部講做法)
  

前言

(隨性寫寫這次的背景故事吧)

當初在 加欄位後補上資料 的這個需求時，用基本的update處理，但PRD 上有 上億筆 的資料，讓我必需重新考慮 Table Lock 及效能的議題。

而原本調整 Table Subpartition Column的實作方式，是採用 DBMS_REDEFINITION 這 Utility處理。

(DBMS_REDEFINITION 最大優勢：Online Handle !!!)

所以開始思考，它能不能 在調整Schema 時，又 同時 做掉填資料呢？ 最後 找到了 “參考文” 所述的作法。

另外， 最快的作法 是 CTAS ：於另Table整理好資料後，再 做exchange.

實作時的各個”雷區”

授權

(https://t.ly/R4xi)

GRANT ALL ON SYS.DBMS_REDEFINITION TO TEST;
GRANT CREATE ANY TABLE, ALTER ANY TABLE, DROP ANY TABLE, LOCK ANY TABLE, SELECT ANY TABLE TO TEST;

事前準備

1. 建立_INT的表格  (Interim)
• 從CTAS方式建立，包括 調整為正確的subpartition column.
2. 要手動補上其Subpartition資訊，不然在Redefinition時會死掉 !
• 我是自寫一個loop，依據 TableA 去建立 TableA_INT 的Subparitition.
• 從這Table取得資訊：USER_TAB_SUBPARTITIONS
3. 建立好的TableA_INT Table，要將它其下的Constraint 移除 (這是從CTAS方式建立時產生)
• 這是在執行「COPY_TABLE_DEPENDENTS」時會發生的錯誤。
• 自寫一個Loop，從這Table：USER_constraints 取得其相關資訊，再將它移掉
• 語法：ALTER TABLE  XXX DROP CONSTRAINT YYY;
  
  

建立Function

    這是為了讓DBMS_REDEFINITION的定義，可以”填資料”

•     最重要的是 Function要加上 "DETERMINISTIC" 才可以被運作

   CREATE OR REPLACE FUNCTION GetXXXX( ….) RETURN DATE DETERMINISTIC AS ~~ BEGIN ~~END;

處理的Key依據

        官網(各函式說明)：https://t.ly/VnV9

        DBMS_REDEFINITION.CONS_USE_PK
         DBMS_REDEFINITION.CONS_USE_ROWID

• 這要留意的是使用「ROWID」，則在 執行 DBMS_REDEFINITION 後，它會產出「unused」的欄位！這要手動移除
• 語法：ALTER TABLE XXX DROP UNUSED COLUMNS;

CAN_REDEF_TABLE

• 檢查Table是否可以執行DBMS_REDEFINITION ?

START_REDEF_TABLE

• 在這一步，就已將資料copy 至 TableA_INT的表格了
• 額外取得資料填入，於此步驟定義。
• 未執行完成，則要用 ABORT_REDEF_TABLE 釋放。不然會刪除不掉_INT的表格。

COPY_TABLE_DEPENDENTS

• 此Table原本的約束(Index/FK/PK……)，靠此函式同步保留住的
• 出現這種錯誤：ORA-00904: "XXXColumn": invalid identifier，(我是無解)， 將它的Ignore Error 屬性設為true.

SYNC_INTERIM_TABLE

• 這設定，是當處理 DBMS_REDEFINITION 時，若有 其它的Session 異動 TableA時，它會自動同步到 TableA_INT

FINISH_REDEF_TABLE

• 這一步，才會真正將 TableA 與TableA_INT 做交換。
• 結束整個 DBMS_REDEFINITION 流程

刪除INT 表格及Unused 欄位

• INT 表格也是實際占空間的，要刪除；語法：DROP TABLE xxxx CASCADE CONSTRAINTS PURGE;
• Unused欄位 要記得刪除。

結論

       

使用 DBMS_REDEFINITION，目的就是 這些 Online 異動 全交給它，

這次是頭一次使用這Utility。到時再記錄跑「上億」筆的異動要多久了，

然後，這一次自寫客製的Scripts，很多，將它分成「準備」「執行」那步驟，並寫個「Revert」的流程，以便於重新再次執行 (便於反覆測試)

後續更新

1.

處理的資料量大時，不要使用「CONS_USE_ROWID」，會產生大量的LOG(??)

使用「CONS_USE_PK」讓其對應，不用再產生ROWID欄位了。

2.

照理是可以Online 操作，而最後一步「FINISH_REDEF_TABLE」 則是真正的切換動作。

若怕它造成「Package Invalid 」等意外狀況，可以獨自於「離峰」時期執行。

(THE END)