达梦数据库的快速加列参数ALTER_TABLE_OPT使用_达梦 新增列

达梦数据库的表数据量较大时，在线直接修改表结构新增字段会很慢

现象：开发人员测试环境增加一个字段耗时7分钟，数据量仅仅2千万；

为了解决这一问题，达梦数据库提供一个动态会话级参数：ALTER_TABLE_OPT，该参数默认为：0。

--查询目前值
SELECT PARA_NAME,PARA_VALUE,FILE_VALUE FROM V$DM_INI
 WHERE PARA_NAME='ALTER_TABLE_OPT';
 
--修改该参数为3
SP_SET_PARA_VALUE(1,'ALTER_TABLE_OPT',3);
--或
ALTER SYSTEM SET 'ALTER_TABLE_OPT'=3 BOTH;

说明：该参数修改后，只对新建立连接的会话生效，原本存在的会话无效。

参数优化调整后，在线添加字段从原来的7分钟提升至毫秒级完成。

参数详解

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

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

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

PS：如果需要快速加列，可以将此参数设置为3。

模拟测试

1）模拟600万数据的基础表

创建t_objects表，插入约600W的数据。

create table t_objects as 
select level logid, * from sysobjects 
connect by rownum <=180000;
 
begin
    for i in 1..5
    loop
        insert into t_objects 
        select * from t_objects;
        commit;
    end loop;
end;

（2）增加列无默认值时，ALTER_TABLE_OPT不同取值时的效率测试。

往t_objects表中增加字段logtime，datetime类型，没有默认值。

这里更改当前测试会话中ALTER_TABLE_OPT的不同取值。

 
SQL> show parameter ALTER_TABLE_OPT
 
行号     PARA_NAME       PARA_VALUE
---------- --------------- ----------
1          ALTER_TABLE_OPT 0
 
SQL> select count(*) from t_objects;
 
行号     COUNT(*)            
---------- --------------------
1          5995562
 
SQL> alter table t_objects add column logtime datetime;
操作已执行
已用时间: 00:00:41.351. 执行号:5104.
 
SQL> alter table t_objects drop column logtime;
操作已执行
已用时间: 00:00:40.518. 执行号:5105.
SQL> alter session set 'ALTER_TABLE_OPT'=1;
SQL> alter table t_objects add column logtime datetime;
操作已执行
已用时间: 00:00:30.657. 执行号:5108.
 
SQL> alter table t_objects drop column logtime;
操作已执行
已用时间: 00:00:30.152. 执行号:5109.
SQL> alter session set 'ALTER_TABLE_OPT'=2;
SQL> alter table t_objects add column logtime datetime;
操作已执行
已用时间: 30.249(毫秒). 执行号:5111.
 
SQL> alter table t_objects drop column logtime;
操作已执行
已用时间: 00:00:28.772. 执行号:5112.
SQL> alter session set 'ALTER_TABLE_OPT'=3;
SQL> alter table t_objects add column logtime datetime;
操作已执行
已用时间: 18.768(毫秒). 执行号:5114.
 
SQL> alter table t_objects drop column logtime;
操作已执行
已用时间: 00:00:29.880. 执行号:5115.

可以看出，对于增加列没有设置列默认值的情况，ALTER_TABLE_OPT为2和3时效率都很快（毫秒级），设置为1也有较小的效率提升（提升约25%左右），对于删除列也有小幅的效率提升（提升约25%左右）。

3）增加列有默认值时，ALTER_TABLE_OPT不同取值时的效率测试。

t_objects表中增加字段logtime，datetime类型，默认值sysdate。

SQL> alter session set 'ALTER_TABLE_OPT'=0;
SQL> alter table t_objects add column logtime datetime default sysdate;
操作已执行
已用时间: 00:00:41.261. 执行号:5117.
 
SQL> alter table t_objects drop column logtime;
操作已执行
已用时间: 00:00:41.453. 执行号:5118.
SQL> alter session set 'ALTER_TABLE_OPT'=1;
SQL> alter table t_objects add column logtime datetime default sysdate;
操作已执行
已用时间: 00:00:34.462. 执行号:5120.
 
SQL> alter table t_objects drop column logtime;
操作已执行
已用时间: 00:00:33.902. 执行号:5121.
SQL> alter session set 'ALTER_TABLE_OPT'=2;
SQL> alter table t_objects add column logtime datetime default sysdate;
操作已执行
已用时间: 00:00:29.403. 执行号:5123.
 
SQL> alter table t_objects drop column logtime;
操作已执行
已用时间: 00:00:27.927. 执行号:5124.
SQL> alter session set 'ALTER_TABLE_OPT'=3;
SQL> alter table t_objects add column logtime datetime default sysdate;
操作已执行
已用时间: 40.009(毫秒). 执行号:5126.
 
SQL> alter table t_objects drop column logtime;
操作已执行
已用时间: 00:00:29.169. 执行号:5127.

可以看出，对于增加列有默认值的情况，ALTER_TABLE_OPT为3时效率最快（毫秒级），相对于0的情况，设置为1和2也有较小的效率提升（为0时新增列41秒，为1是是34秒，为2时29秒，提升20%-30%），对于删除列也有小幅的效率提升（提升20%-30%）。

注意 ：

1）对于ALTER TABLE添加列，ALTER_TABLE_OPT为3的情况下，不管新增列有没有默认值，效率都是最快的（毫秒级）。

2）对于ALTER_TABLE_OPT为0的情况，大表新增和删除列都是最慢的。

3）ALTER_TABLE_OPT为1，2，3的情况下，新增列都有不同程度的效率提升，删除列的也有小幅的效率提升。新增列无默认值的情况下，ALTER_TABLE_OPT为2和3效率都比较高（毫秒级），新增列有默认值的情况下，仅ALTER_TABLE_OPT为3时最快（毫秒级）。

4）对于删除列，ALTER_TABLE_OPT为0时效率最低，ALTER_TABLE_OPT为1，2，3时有小幅的效率提升（提升20%-30%）。

5）对于生产系统，如涉及在大表使用以上方法添加列，请联系达梦数据库技术服务团队，在其相关建议下进行操作。