在oracle 11gR2版本中,对大表增加带默认值的字段,需要拆分成多个步骤,否则会长时间锁表。如下图:
对260万数据的表加带默认值的字段,执行超过2分钟。
我们的规范做法步骤为:
(1)加字段
alter table T_ORDER add tstatus varchar2(20);
(2)批量更新数据
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)增加默认值属性
alter table TABLE_NAME modify tstatus default '1' not null;
在19c中不再需要如此繁冗的操作了,添加带默认值的字段可以瞬间完成:
实验准备:
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;
--重复执行insert操作,插入200万数据
insert into test select * from test;
commit;
SQL> select count(1) from test;
COUNT(1)
----------
3461376
oracle11gR2版本:
会话1
会话2
结论1:在oracle11gR2版本中,进行添加列、修改列的默认值操作时,如果其他会话中没有未提交的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
结论2:在oracle11gR2中,直接添加带默认值的列,执行时间和表的数据量相关
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
结论3:当有DML操作未提交时,添加带默认值的列将报错(获取独占锁失败)。添加列不带默认值时,会等待dml操作提交(释放行级锁)后才可执行完成。
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 >
结论4:添加默认值带默认值、非空约束时,如果没有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版本
实验准备操作相同,数据量基本一致
SQL> SQL> select count(1) from test;
COUNT(1)
----------
3479400
会话1
会话2
结论5:在19c版本中,增加带默认值、无非空约束的列,可以瞬间完成。如果有DML操作未结束,仍需等待该操作完成才可以结束。
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官方文档中有如下描述:
直译为
11.2版本中 alter table add column with default value的操作正常不会阻塞,但在使用supplemental log时会降级为阻塞操作。
实际测试该操作,将数据库开启最小辅助日志、表开启辅助日志(
alter table testu.test2 add supplemental log data(all) columns;),然后对test表使用OGG同步。操作都可以瞬间完成。这里还没太明白,后续有进展再补充。
总结:
在11gR2版本中增加带默认值的列时,需要指定not null属性,即可瞬间完成;否则锁表时间较长。
在19c版本中
增加带默认值的列时,不管是否包含not null属性,都可瞬间完成。
操作需要在业务低峰期操作,避免操作时有dml操作对其造成阻塞。
感悟:
数据库中的DML操作,主要需要避免大事务造成的锁表时间长、占用redo及undo等资源巨大、资源不足时回滚操作不可控等问题;
DDL操作时除了操作时间,还需要考虑表的独占锁对其他操作的阻塞问题。
随着数据库版本的迭代,功能愈加完善,很多经验已经不再适用了。适用新版本数据库前,应该对规范、操作手册进行测试,与时俱进,提高效率。