达梦数据库表新增字段速度测试

        给表新增字段是一项日常需求，数据库在这种DDL的资源消耗还是很大的，达梦数据库在新增字段方面有些学习了oracle 11g的思路。

        达梦数据库提供了参数 ALTER_TABLE_OPT 来控制添加字段的行为，以下摘自达梦8系统管理员手册：

“添加或删除列，或修改现有的列的定义（列名、数据类型、长度、默认值）。其中， 对于添加列，当设置 INI 参数 ALTER_TABLE_OPT 为 1 时，添加列采用查询插入 实现，可能会导致 ROWID 的改变；ALTER_TABLE_OPT 为 2 时，系统开启快速加 列功能，对于没有默认值或者默认值为 NULL 的新列，系统内部会标记为附加列， 能够达到瞬间加列的效果，此时记录 ROWID 不会改变，若有默认值且默认值不为 NULL，则默认值的存储长度不能超过 4000 字节，此时仍旧采取查询插入实现； ALTER_TABLE_OPT 为 3 时，系统会开启快速加列功能，允许指定新增列的默认值， 系统会为该列设置附加列标记，查询表中已存在的数据时，会自动为记录设置追加 列默认值，此时记录 ROWID 不会改变”

针对上述表述，我的理解是：

1、参数ALTER_TABLE_OPT默认值是0

2、当ALTER_TABLE_OPT设置为1时，添加列的操作采用的类似于cast的做法，相当于新建一个空表，添加上相应的列，然后再从原表把数据插入新表。

3、当ALTER_TABLE_OPT 设置为2时，系统会开启优化模式，对于没有默认值或默认值为null的列，会瞬间加速，但是对于有默认值的列，没效果。

4、当ALTER_TABLE_OPT 设置为3时，对于有默认值的列，会开启优化模式，瞬间完成加列操作。

基于以上理解，我做了一些测试，一张4百万的表

SQL> select count(*) from test;

行号     COUNT(*)            
---------- --------------------
1          4456448

已用时间: 4.524(毫秒). 执行号:51221.

SQL> desc test;

行号     NAME            TYPE$        NULLABLE
---------- --------------- ------------ --------
1          OWNER           VARCHAR(128) Y
2          SEGMENT_NAME    VARCHAR(128) Y
3          PARTITION_NAME  VARCHAR(128) Y
4          SEGMENT_TYPE    VARCHAR(15)  Y
5          SEGMENT_SUBTYPE VARCHAR(1)   Y
6          TABLESPACE_NAME VARCHAR(128) Y
7          HEADER_FILE     SMALLINT     Y
8          HEADER_BLOCK    INTEGER      Y
9          BYTES           BIGINT       Y
10         BLOCKS          BIGINT       Y
11         EXTENTS         BIGINT       Y

行号     NAME            TYPE$      NULLABLE
---------- --------------- ---------- --------
12         INITIAL_EXTENT  BIGINT     Y
13         NEXT_EXTENT     BIGINT     Y
14         MIN_EXTENTS     SMALLINT   Y
15         MAX_EXTENTS     BIGINT     Y
16         MAX_SIZE        BIGINT     Y
17         RETENTION       VARCHAR(1) Y
18         MINRETENTION    VARCHAR(1) Y
19         PCT_INCREASE    VARCHAR(1) Y
20         FREELISTS       VARCHAR(1) Y
21         FREELIST_GROUPS VARCHAR(1) Y
22         RELATIVE_FNO    SMALLINT   Y

行号     NAME             TYPE$      NULLABLE
---------- ---------------- ---------- --------
23         BUFFER_POOL      VARCHAR(7) Y
24         FLASH_CACHE      VARCHAR(1) Y
25         CELL_FLASH_CACHE VARCHAR(1) Y

SQL> select * from v$parameter where name='ALTER_TABLE_OPT';

行号     ID          NAME            TYPE    VALUE SYS_VALUE FILE_VALUE DESCRIPTION                                                          
---------- ----------- --------------- ------- ----- --------- ---------- ---------------------------------------------------------------------
1          583         ALTER_TABLE_OPT SESSION 0     0         0          Whether to optimize ALTER TABLE operation(add, modify or drop column)

1、没有默认值
SQL> alter table test add column col1 varchar(10);        ==============》8s
操作已执行
已用时间: 00:00:08.541. 执行号:51225.

SQL> alter system set 'ALTER_TABLE_OPT'=1 both;
DMSQL 过程已成功完成
已用时间: 18.752(毫秒). 执行号:51226.

SQL>  alter table test add column col2 varchar(10);     ============》5s。
操作已执行
已用时间: 00:00:05.608. 执行号:51227.

SQL> alter system set 'ALTER_TABLE_OPT'=2 both;
DMSQL 过程已成功完成
已用时间: 20.548(毫秒). 执行号:51228.

SQL> alter table test add column col3 varchar(10);   ==============》14ms
操作已执行
已用时间: 14.899(毫秒). 执行号:51229.
  

SQL>  alter system set 'ALTER_TABLE_OPT'=3 both;
DMSQL 过程已成功完成
已用时间: 17.479(毫秒). 执行号:51233.
SQL> alter table test add column col4 varchar(10);  ==============》39ms
操作已执行
已用时间: 39.942(毫秒). 执行号:51234.

2、有默认值

SQL> alter system set 'ALTER_TABLE_OPT'=0 both;
DMSQL 过程已成功完成
已用时间: 2.843(毫秒). 执行号:51235.

SQL> alter table test add column col1 varchar(10) default 'aaaa'; ========》9s
操作已执行
已用时间: 00:00:09.051. 执行号:51237.

SQL>  alter system set 'ALTER_TABLE_OPT'=1 both;
DMSQL 过程已成功完成
已用时间: 6.496(毫秒). 执行号:51238.
SQL>  alter table test add column col2 varchar(10) default 'aaaa';   ========》7s
操作已执行
已用时间: 00:00:07.053. 执行号:51239.

SQL>  alter system set 'ALTER_TABLE_OPT'=2 both;
DMSQL 过程已成功完成
已用时间: 8.290(毫秒). 执行号:51240.
SQL>   alter table test add column col3 varchar(10) default 'aaaa';==========》6s
操作已执行
已用时间: 00:00:06.488. 执行号:51241.

SQL> alter system set 'ALTER_TABLE_OPT'=3 both; 
DMSQL 过程已成功完成
已用时间: 5.212(毫秒). 执行号:51242.
SQL> alter table test add column col4 varchar(10) default 'aaaa';  ==========》6ms
操作已执行
已用时间: 6.610(毫秒). 执行号:51243.

综上：

1、在没有默认值的情况下，添加字段，可以设置ALTER_TABLE_OPT为2或者3.

2、在存在默认值的情况下，添加字段，设置ALTER_TABLE_OPT为3.