2021/03/24

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)

0 意見 :

張貼留言