溫馨提示×

溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×
登錄注冊×
其他方式登錄
點擊 登錄注冊 即表示同意《億速云用戶服務(wù)條款》

oracle增加字段帶默認(rèn)值

發(fā)布時間：2020-08-07 21:17:42 來源：ITPUB博客 閱讀：379 作者：to_be_Dba 欄目：關(guān)系型數(shù)據(jù)庫

在oracle 11gR2版本中，對大表增加帶默認(rèn)值的字段，需要拆分成多個步驟，否則會長時間鎖表。如下圖：

對260萬數(shù)據(jù)的表加帶默認(rèn)值的字段，執(zhí)行超過2分鐘。

oracle增加字段帶默認(rèn)值

我們的規(guī)范做法步驟為：

（1）加字段

alter table  T_ORDER add tstatus varchar2(20);

（2）批量更新數(shù)據(jù)

declare

  n_count number;

begin

  select ceil(count(1)/100000) into n_count

  from T_ORDER where tstatus is null;

  for i in 1..n_count loop

    update T_ORDER set tstatus='1' where tstatus is null and rownum<=100000;

    commit;

  end loop;

end;

/

（3）增加默認(rèn)值屬性

alter table TABLE_NAME modify tstatus default '1' not null;

在19c中不再需要如此繁冗的操作了，添加帶默認(rèn)值的字段可以瞬間完成：

oracle增加字段帶默認(rèn)值

實驗準(zhǔn)備：  create table test(
owner varchar2(30),
object_name varchar2(128),
object_type varchar2(30),
created date
);
insert into test 
select owner,object_name,object_type ,to_Date('20190101','yyyymmdd')+60*dbms_random.value from all_objects;
commit;
--重復(fù)執(zhí)行insert操作，插入200萬數(shù)據(jù)
insert into test select * from test;
commit;

SQL> select count(1) from test;

  COUNT(1)
----------
   3461376
oracle11gR2版本：


會話1 會話2
結(jié)論1：在oracle11gR2版本中，進(jìn)行添加列、修改列的默認(rèn)值操作時，如果其他會話中沒有未提交的ddl、dml操作，則可以瞬間完成。 SQL> set timing on
SQL> alter table test add col2 varchar2(10) ;

Table altered.

Elapsed: 00:00:00.00
SQL> 
SQL> 
SQL> alter table test modify col2 default '1';

Table altered.

Elapsed: 00:00:00.00
SQL> 
SQL> select count(1) from test where col2='1';

  COUNT(1)
----------
         0

Elapsed: 00:00:00.04
結(jié)論2：在oracle11gR2中，直接添加帶默認(rèn)值的列，執(zhí)行時間和表的數(shù)據(jù)量相關(guān) SQL> alter table test add col3 varchar2(10) default '1';

Table altered.

Elapsed: 00:01:49.02
SQL> SQL> SQL> 
SQL> alter table test add col4 date default sysdate;

Table altered.

Elapsed: 00:02:04.62
結(jié)論3：當(dāng)有DML操作未提交時，添加帶默認(rèn)值的列將報錯(獲取獨占鎖失?。?。添加列不帶默認(rèn)值時，會等待dml操作提交（釋放行級鎖）后才可執(zhí)行完成。
SQL> set time on
15:17:50 SQL> delete from test where rownum=1;

1 row deleted.
SQL> set time on
15:18:11 SQL> alter table test add col5 varchar2(10) default '1';
alter table test add col5 varchar2(10) default '1'
            *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired Elapsed: 00:00:00.00
15:18:16 SQL>  alter table test add col5 varchar2(10);

15:17:54 SQL> commit;

Commit complete.
15:18:43 SQL>
Table altered.

Elapsed: 00:00:20.35
15:18:43 >
結(jié)論4：添加默認(rèn)值帶默認(rèn)值、非空約束時，如果沒有DML阻塞，可以瞬間完成；如果有DML操作未提交，則需等待直到DML操作提交才可完成 15:24:50 SQL> alter table test add col6 varchar2(10) default '1' not null;

Table altered.

Elapsed: 00:00:00.01

15:27:55 SQL>  delete from test where rownum=1;

1 row deleted.
15:28:01 SQL> alter table test add col7 varchar2(10) default '1' not null;

15:28:47 SQL> commit;

Commit complete.

15:29:04 SQL>
Table altered.

Elapsed: 00:00:09.27
15:29:04 SQL> 



oracle19c版本 實驗準(zhǔn)備操作相同，數(shù)據(jù)量基本一致
SQL> SQL>  select count(1) from test;

  COUNT(1)
----------
   3479400

會話1 會話2
結(jié)論5：在19c版本中，增加帶默認(rèn)值、無非空約束的列，可以瞬間完成。如果有DML操作未結(jié)束，仍需等待該操作完成才可以結(jié)束。 SQL> set timing on
SQL> alter table test add col3 varchar2(10) default '1';

Table altered.

Elapsed: 00:00:00.01
SQL> 
SQL> alter table test add col4 date default sysdate;

Table altered.

Elapsed: 00:00:00.02

SQL> set time on
15:43:01 SQL> delete from test where rownum=1;

1 row deleted.
15:43:07 SQL> alter table test add col5 varchar2(10) default '1';


15:43:03 SQL> commit;

Commit complete.

15:43:24 SQL> 
Table altered.

Elapsed: 00:00:05.76
15:43:24 SQL> 15:43:24 SQL>

在19c官方文檔中有如下描述：

https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Automatic-and-Manual-Locking-Mechanisms-During-SQL-Operations.html#GUID-1B08DE66-5ED8-4BEF-893B-B887E3A82D50

oracle增加字段帶默認(rèn)值

直譯為

11.2版本中 alter table add column with default value的操作正常不會阻塞，但在使用supplemental log時會降級為阻塞操作。

實際測試該操作，將數(shù)據(jù)庫開啟最小輔助日志、表開啟輔助日志（ alter table testu.test2 add supplemental log data(all) columns;），然后對test表使用OGG同步。操作都可以瞬間完成。這里還沒太明白，后續(xù)有進(jìn)展再補(bǔ)充。

總結(jié)：

在11gR2版本中增加帶默認(rèn)值的列時，需要指定not null屬性，即可瞬間完成；否則鎖表時間較長。

在19c版本中 增加帶默認(rèn)值的列時，不管是否包含not null屬性，都可瞬間完成。

操作需要在業(yè)務(wù)低峰期操作，避免操作時有dml操作對其造成阻塞。

感悟：

數(shù)據(jù)庫中的DML操作，主要需要避免大事務(wù)造成的鎖表時間長、占用redo及undo等資源巨大、資源不足時回滾操作不可控等問題；

DDL操作時除了操作時間，還需要考慮表的獨占鎖對其他操作的阻塞問題。

隨著數(shù)據(jù)庫版本的迭代，功能愈加完善，很多經(jīng)驗已經(jīng)不再適用了。適用新版本數(shù)據(jù)庫前，應(yīng)該對規(guī)范、操作手冊進(jìn)行測試，與時俱進(jìn)，提高效率。

向AI問一下細(xì)節(jié)

免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點不代表本網(wǎng)站立場，如果涉及侵權(quán)請聯(lián)系站長郵箱：is@yisu.com進(jìn)行舉報，并提供相關(guān)證據(jù)，一經(jīng)查實，將立刻刪除涉嫌侵權(quán)內(nèi)容。

AI
助
手