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

        给表新增字段是一项日常需求,数据库在这种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.

  • 1
    点赞
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值