达梦对数据表添加列和删除列优化测试

背景需求

近期项目中碰到一个问题，涉及到应用版本更新，每次更新，就需要对业务系统中上千个表进行增加列或删除列的操作，每个表数据量都比较大，对一个表增加一个列就需要几分钟，导致整个升级需要十几个小时，而同样的在oracle只需要半个小时完成，如果涉及到大版本更新，一次跟新就需要往表添加或删除多个字段，会导致升级时间不可控。 达梦有ALTER_TABLE_OPT参数可以对列操作进行优化。

参数详解

1 对于添加列，当设置INI参数ALTER_TABLE_OPT为1时，添加列采用查询插入实现，可能会导致ROWID的改变；

2 ALTER_TABLE_OPT 为 2 时，系统开启快速加列功能，对于没有默认值或者默认值为 NULL 的新列，系统内部会标记为附加列，能够达到瞬间加列的效果，此时记录 ROWID 不会改变，若有默认值且默认值不为NULL，则默认值的存储长度不能超过 4000 字节，此时仍旧采取查询插入实现；

3 ALTER_TABLE_OPT 为 3 时，系统会开启快速加列功能，允许指定新增列的默认值，系统会为该列设置附加列标记，查询表中已存在的数据时，会自动为记录设置追加列默认值，此时记录 ROWID 不会改变。

现本地进行测试，当ALTER_TABLE_OPT设置不同值时，增加列和删除列的效果

数据库版本 1-3-62-2023.11.27-209835-20067-SEC

--创建测试表

drop table if exists emp;

create table emp (eid int primary key identity(1,1),ename varchar(200),age int,hiredate date,deptno int);

declare i int;

begin

for i in 1..5000000 loop

insert into emp (ename,age,hiredate,deptno)

select dbms_random.string('2',trunc(dbms_random.value(2,8))),

trunc(dbms_random.value(1,100)),

 ADD_DAYS(sysdate(),dbms_random.value(-10000,-10)),

trunc(dbms_random.value(1,6)) from dual;

end loop;

if  mod(i,5000)=0 then

commit;

end if;

end;

当默ALTER_TABLE_OPT认值0 时，执行时间如下

--增加字段

--删除字段

ALTER_TABLE_OPT=1时, 执行时间如下

sp_set_para_value(1,'ALTER_TABLE_OPT',1);

--增加字段

--删除字段

ALTER_TABLE_OPT=2时，执行时间如下

sp_set_para_value(1,'ALTER_TABLE_OPT',2);

--增加字段

--删除字段

ALTER_TABLE_OPT=3时，执行时间如下

sp_set_para_value(1,'ALTER_TABLE_OPT',3);

--增加字段

--删除字段

ALTER_TABLE_OPT重新改成0 时，执行时间如下：

sp_set_para_value(1,'ALTER_TABLE_OPT',0);

 

经过测试结果得到结论如下：

当只要ALTER_TABLE_OPT设置为3时，对于表添加列有非常明显的效果，但设置1，2，3时对删除表中列均没有效果，而且比默认值更慢。