【干货攻略】达梦优化之-ALTER TABLE快速添加列

达梦E学 2024-02-02

2189

前提

最近项目上刚好遇到一个业务需求，需求说明如下。

情况：多张大表含已有字段上百个，需求在线增加字段(alter table xxx add column)；

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

难点：生产环境数据量7千万，业务为7*24小时高并发。

优化方法

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

参数名	缺省值	属性	说明
ALTER_TABLE_OPT	0	动态，会话级	是否对加列、修改列、删除列操作进行优化，0：全部不优化；1：全部优化；2：打开快速加列，对于删除列和修改列与1等效；3：打开快速加列，允许指定快速列默认值，其他功能与2时相同

--查询目前值
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.