需求邏輯
(用概念記錄一下這次的開發邏輯，順便做的思緒的統整)
這一次的處理，可達到以下需求：
- TableA新增一個欄位 ColumnB。
- 依照TableY的資料，填入ColumnB。
- 將原本TableA的Subpartition的欄位依據，從ColumnA 改為 ColumnB。
- 一切要 線上調整，不可以downtime.
參考文
最終採用 DBMS_REDEFINITION 實作：
- Performing a large correlated update – Learning is not a spectator sport
- 這篇直接達到我的需求，使用REDEFINITION調整Schema外，還同時可以「填資料」！
- dbms_redefinition example
- 一篇標準的 DBMS_REDEFINITION 的使用方式，說明各步驟的用意。
- DBMS_REDEFINITION (官網)
- 各函式的參數說明
更新大量資料(update data)的作法 參考文：
- Efficient way to UPDATE bulk of records in Oracle Database - Oracle Database
- 做了各類Update的效能比較(裡面有6類的寫法，採「直接update」效率最差 )。
- Oracle update tuning tips
- 各類的update說明
- 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.
實作時的各個”雷區”
授權
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;
事前準備
- 建立_INT的表格 (Interim)
- 從CTAS方式建立，包括 調整為正確的subpartition column.
- 要手動補上其Subpartition資訊，不然在Redefinition時會死掉 !
- 我是自寫一個loop，依據 TableA 去建立 TableA_INT 的Subparitition.
- 從這Table取得資訊：USER_TAB_SUBPARTITIONS
- 建立好的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)
