ORACLE TABLE有大量記錄,如果增加欄位帶default值需要很長時間

來源:互聯網
上載者:User

manpower.s_salary 375w記錄,需增加欄位add_sh number(7,2) default 0


1.先排除無人使用

SELECT P.SPID,s.STATUS,s.terminal,s.machine,s.program,s.osuser,s.CLIENT_INFO,s.LOGON_TIME FROM V$SESSION S,V$ACCESS A,V$PROCESS P
WHERE S.SID=A.SID
  AND S.PADDR=P.ADDR

  AND A.OBJECT='S_SALARY'


2.alter table manpower.s_salary add (add_sh number(7,2) default 0);
需要時間21分鐘。(中午下班時間執行)


分2步執行,效果顯著提高

alter table manpower.s_salary add (add_sh number(7,2));
需要時間0.25秒。

alter table manpower.s_salary modify (add_sh number(7,2) default 0);

需要時間0.25秒。

oracle版本為10.2.05


還是有的:
1) 在ALTER sql中有帶預設值,ORACLE會直接重新整理全部的記錄。
2) 在ALTER sql中沒有帶預設值,ORACLE只會影響到後來的記錄。(明白快是有隱秘的秘訣)

在選擇哪種方法時,要更加具體的情況來實施:是否有INDEX,是否在應用的高並發階段,等等


------------------------------------------以下為轉載http://yinzhihua2008.blog.163.com/blog/static/7943067201211241056654/----------------------------------

ORACLE資料表有很多資料如果增加欄位需要很長時間   關於建立的方式哪種更快,需要綜合考慮,由於樓主的庫是非歸檔模式的,所以估計是個測試庫。
對於測試環境,樓主的兩種載入資料的方式都可行,我測試環境沒有那麼大的表,只能拿一個有1500萬行,1G大小的表測試了一下,採用資料泵方式用了5分鐘(匯出加匯入),採用insert /*+append*/ 方式用時216秒(如果載入新增欄位為not null,需要260秒),看似insert /*+append*/的效率更高一些;不過有幾點要注意的:
1.在正式的生產環境,考慮到資料可恢複的問題,可能insert /*+append*/是不能被接受的
2.這兩種方式目標表的預設值只對新插入的資料有效,而對匯入自原表中的資料無效。
3.採用資料泵模式,新加的欄位不能為not null
對於可以接受停機的生產庫,為了保證資料的可恢複,建議採用資料泵或者直接路徑載入的sql loader試試
但是,現在越來越多的oracle充當企業關鍵資料庫,可能不會給予足夠的停機時間,此時可以採用線上重定義等高可用方式。
或者,按照以下步驟:
1.在表上增加一個新欄位,不帶預設值,這個操作應該很快
alter table test add test number;
2.把此欄位改為預設值,這樣,以後插入的資料就包含預設值了,但是修改之前的欄位值仍然為空白
alter table test modify test default 88888888;
3.如果需要將修改前的預設值加入相關的曆史資料,可以做一個批次更新的預存程序,定義比如每1000條一次commit,以減小生產庫的壓力。
4.更新完成後,可以將欄位設定為not null模式
總之,在生產庫的真實環境中(尤其是24X7庫),有時候速度並不是唯一要考慮的因素,還要考慮資料的可恢複性,操作時對資料庫的效能影響,以及停機時間,這就是為什麼oracle會提供速度並不突出的線上重定義的原因。有時為了減輕對生產庫的壓力,我也會故意在迴圈插入、更新和刪除的預存程序中加入延遲,以防治日誌過於頻繁的切換。
上述測試是基於10g的,在11g中,看來oracle已經意識到了這個問題,所以可以直接採用添加預設值的文法,這個更新應該時間很短,因為你查到以前資料上新加的欄位上已經有預設值了,但是這是通過oracle內建函式計算後顯示出來的,而不是真實存在於資料庫中的,只有以後加入的資料,預設值才會真實插入資料庫表中,所以11g中你可以使用下面語句:
alter table test add test number default 88888888 not null;


相關文章

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.