當oracle單表資料量上億時,對表進行alter table aa add column_1 varchar2(2) defalut 'Y';時,效率及安全性是必須考慮的因素。
本帖以2億的資料表aa舉例:
alter table aa add column_1 varchar2(2) defalut 'Y';
Table altered
Executed in 3600 seconds
直接用了一個小時,而且在這過程中是加上了表級鎖,也就是連查詢都是會被等待的,這是相當危險的操作。
為什麼會這樣,原來oracle在執行上面語句時不僅要更新資料字典,還會重新整理全部的記錄。一次update 2 億條資料當然需要一個小時,并且還會導緻undo空間暴漲。
如果我們把更新資料字典和更新字段值分開,會不會好一點?
alter table aa add column_1 varchar2(2);
Table altered Executed in 0.016 seconds
alter table aa modify column_1 varchar2(2) defalut 'Y';
Executed in 0.003 seconds
答案是快100萬倍,那效果呢?經測試發現在第二種方法不會更新以前的資料,我們可以自己寫一個匿名塊循環update,一次commit 10000條,達到效率與安全的完美結合。
結論:alter table add 語句加上defalut時會重新整理存量資料并産生表級鎖,需慎用。特别是大表,生産環境,業務産生期間就應該禁止此操作。
改為add table add不帶預設值,接着來個alter table aa modify column_1 varchar2(2) defalut 'Y';更新存量資料可放到業務較少的淩晨跑。