首页 » MySQL, ORACLE 9i-23c, PostgreSQL/GaussDB, 达梦 » Oracle、MySQL、PostgreSQL、openGauss、达梦数据库比较系列(十九): 增加列default value会表重写吗?

Oracle、MySQL、PostgreSQL、openGauss、达梦数据库比较系列(十九): 增加列default value会表重写吗?

之前曾经在《oracle add column xx default value 增强(二)》记录过,在日常运维中增加column是常见的操作,对于大表增加列时是否会导致回写表还是只修改数据字典影响DDL的执行时间和停机窗口长短。之前也在《“alter table ” modify column in Oracle、MySQL、PostGreSQL(数据库比较系列十三)》记录修改column时不同库的表现, 这里简单记录测试一下当前主流的几个库对于add column的现象。
Oracle DATABASE
oracle不在重复演示,参考https://www.anbob.com/archives/6965.html

增加列默认值 10g 11g 12c+
add column default 回写表数据 回写表数据 只增加数据字段定义
不更新表数据
add column default  not null 回写表数据 只增加数据字段定义
不更新表数据
只增加数据字段定义
不更新表数据

MySQL8

[root@oel7db1 ~]# mysql -uroot -pwww.anbob.com -h127.0.0.1 -P3306  anbob
mysql: [Warning] Using a password on the command line interface can be insecure.
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.20-commercial MySQL Enterprise Server - Commercial

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


MYSQL_root@127.0.0.1 [anbob]> drop table test_mod;
Query OK, 0 rows affected (0.06 sec)

MYSQL_root@127.0.0.1 [anbob]> create table test_mod(id numeric(10),name varchar(20), CONSTRAINT idx_test_mod primary key (id));
Query OK, 0 rows affected (0.06 sec)

MYSQL_root@127.0.0.1 [anbob]>  delimiter $$
MYSQL_root@127.0.0.1 [anbob]> create procedure add_data(in num int)
    -> begin
    -> declare i int default 1;
    -> while i do
    ->  set i=i+1;
    ->  insert into test_mod values (i,'anbob');
    -> end while;
    -> end $$
MYSQL_root@127.0.0.1 [anbob]> delimiter ;
MYSQL_root@127.0.0.1 [anbob]>  call add_data(10000);
Query OK, 1 row affected (2 min 27.11 sec)

MYSQL_root@127.0.0.1 [anbob]> select * from information_schema.INNODB_TABLES where name='anbob/test_mod';
+----------+----------------+------+--------+-------+------------+---------------+------------+--------------+
| TABLE_ID | NAME           | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS |
+----------+----------------+------+--------+-------+------------+---------------+------------+--------------+
|     1134 | anbob/test_mod |   33 |      5 |    77 | Dynamic    |             0 | Single     |            0 |
+----------+----------------+------+--------+-------+------------+---------------+------------+--------------+
1 row in set (0.07 sec)

MYSQL_root@127.0.0.1 [anbob]> alter table test_mod add city varchar(50) default 'SJZ';
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

MYSQL_root@127.0.0.1 [anbob]> select * from information_schema.INNODB_TABLES where name='anbob/test_mod';
+----------+----------------+------+--------+-------+------------+---------------+------------+--------------+
| TABLE_ID | NAME           | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS |
+----------+----------------+------+--------+-------+------------+---------------+------------+--------------+
|     1134 | anbob/test_mod |   33 |      6 |    77 | Dynamic    |             0 | Single     |            2 |
+----------+----------------+------+--------+-------+------------+---------------+------------+--------------+
1 row in set (0.00 sec)

MYSQL_root@127.0.0.1 [anbob]> alter table test_mod add org varchar(50) default 'China' NOT NULL;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

MYSQL_root@127.0.0.1 [anbob]> select * from information_schema.INNODB_TABLES where name='anbob/test_mod';
+----------+----------------+------+--------+-------+------------+---------------+------------+--------------+
| TABLE_ID | NAME           | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS |
+----------+----------------+------+--------+-------+------------+---------------+------------+--------------+
|     1134 | anbob/test_mod |   33 |      7 |    77 | Dynamic    |             0 | Single     |            2 |
+----------+----------------+------+--------+-------+------------+---------------+------------+--------------+
1 row in set (0.00 sec)

MYSQL_root@127.0.0.1 [anbob]> select * from information_schema.INNODB_COLUMNS where table_id=1134;
+----------+------+-----+-------+----------+-----+-------------+------------------------------+
| TABLE_ID | NAME | POS | MTYPE | PRTYPE   | LEN | HAS_DEFAULT | DEFAULT_VALUE                |
+----------+------+-----+-------+----------+-----+-------------+------------------------------+
|     1134 | id   |   0 |     3 |   525814 |   5 |           0 | 0x                           |
|     1134 | name |   1 |    12 | 16711695 |  80 |           0 | 0x                           |
|     1134 | city |   2 |    12 | 16711695 | 200 |           1 | 0x353334613561               |
|     1134 | org  |   3 |    12 | 16711951 | 200 |           1 | 0x34333638363936653631       |
+----------+------+-----+-------+----------+-----+-------------+------------------------------+
4 rows in set (0.78 sec)

MYSQL_root@127.0.0.1 [anbob]> select * from information_schema.INNODB_TABLES where name='anbob/test_mod';
+----------+----------------+------+--------+-------+------------+---------------+------------+--------------+
| TABLE_ID | NAME           | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS |
+----------+----------------+------+--------+-------+------------+---------------+------------+--------------+
|     1134 | anbob/test_mod |   33 |      7 |    77 | Dynamic    |             0 | Single     |            2 |
+----------+----------------+------+--------+-------+------------+---------------+------------+--------------+
1 row in set (0.00 sec)

MYSQL_root@127.0.0.1 [anbob]> ALTER TABLE test_mod modify column name varchar(50) not null;
Query OK, 0 rows affected (0.59 sec)
Records: 0  Duplicates: 0  Warnings: 0

MYSQL_root@127.0.0.1 [anbob]> select * from information_schema.INNODB_TABLES where name='anbob/test_mod';
+----------+----------------+------+--------+-------+------------+---------------+------------+--------------+
| TABLE_ID | NAME           | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS |
+----------+----------------+------+--------+-------+------------+---------------+------------+--------------+
|     1135 | anbob/test_mod |   33 |      7 |    78 | Dynamic    |             0 | Single     |            0 |
+----------+----------------+------+--------+-------+------------+---------------+------------+--------------+
1 row in set (0.00 sec)

MYSQL_root@127.0.0.1 [anbob]> ALTER TABLE test_mod modify column name varchar(50)  null;
Query OK, 0 rows affected (1.39 sec)
Records: 0  Duplicates: 0  Warnings: 0

MYSQL_root@127.0.0.1 [anbob]> select * from information_schema.INNODB_TABLES where name='anbob/test_mod';
+----------+----------------+------+--------+-------+------------+---------------+------------+--------------+
| TABLE_ID | NAME           | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS |
+----------+----------------+------+--------+-------+------------+---------------+------------+--------------+
|     1136 | anbob/test_mod |   33 |      7 |    79 | Dynamic    |             0 | Single     |            0 |
+----------+----------------+------+--------+-------+------------+---------------+------------+--------------+
1 row in set (0.00 sec)

MYSQL_root@127.0.0.1 [anbob]> ALTER TABLE test_mod modify column name varchar(50) not null , ALGORITHM=INSTANT;
ERROR 1845 (0A000): ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE.

MYSQL_root@127.0.0.1 [anbob]> ALTER TABLE test_mod modify column name varchar(50) not null , ALGORITHM=INPLACE;
Query OK, 0 rows affected (0.74 sec)
Records: 0  Duplicates: 0  Warnings: 0

MYSQL_root@127.0.0.1 [anbob]> select * from information_schema.INNODB_TABLES where name='anbob/test_mod';
+----------+----------------+------+--------+-------+------------+---------------+------------+--------------+
| TABLE_ID | NAME           | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS |
+----------+----------------+------+--------+-------+------------+---------------+------------+--------------+
|     1137 | anbob/test_mod |   33 |      7 |    80 | Dynamic    |             0 | Single     |            0 |
+----------+----------------+------+--------+-------+------------+---------------+------------+--------------+
1 row in set (0.01 sec)

Note:
MySQL 8中add column default 无认有没有not null 都没有重建表, 这点在MYSQL 官方整理的比较详细对于online DDL不同版本可能有不同的表现。另外注意对于不同的字符集,在mysql中修改的varchar长度的字节如果发生length bytes改变也只能使用algorithm=copy方式,如0-255是1 length byte,256开始是2 length bytes.

PostgreSQL 13.2

[postgres@oel7db1 ~]$ sh pgstart.sh
waiting for server to start..... done
server started
[postgres@oel7db1 ~]$ psql
psql (13.2)
Type "help" for help.


[local]:5432 postgres@postgres=# \c anbob
You are now connected to database "anbob" as user "postgres".

[local]:5432 postgres@anbob=# create table test_mod(id numeric(10),name varchar(20));
CREATE TABLE
[local]:5432 postgres@anbob=# insert into test_mod
 select 1,'anbob.com'
 from generate_series(1,10000);
INSERT 0 10000
[local]:5432 postgres@anbob=# create index idx_test_mod on test_mod(id);
CREATE INDEX
[local]:5432 postgres@anbob=#  select pg_relation_filepath('test_mod');
 pg_relation_filepath
----------------------
 base/16447/27574
(1 row)

[local]:5432 postgres@anbob=# select pg_relation_filepath('idx_test_mod');
 pg_relation_filepath
----------------------
 base/16447/27577
(1 row)

[local]:5432 postgres@anbob=# ALTER TABLE TEST_MOD add city varchar2(50) default 'SJZ';
ALTER TABLE
[local]:5432 postgres@anbob=# select pg_relation_filepath('idx_test_mod');
 pg_relation_filepath
----------------------
 base/16447/27577
(1 row)

[local]:5432 postgres@anbob=#  select pg_relation_filepath('test_mod');
 pg_relation_filepath
----------------------
 base/16447/27574
(1 row)

[local]:5432 postgres@anbob=# ALTER TABLE TEST_MOD add org varchar2(50) default 'China' not null;
ALTER TABLE
[local]:5432 postgres@anbob=#  select pg_relation_filepath('test_mod');
 pg_relation_filepath
----------------------
 base/16447/27574
(1 row)

[local]:5432 postgres@anbob=# select pg_relation_filepath('idx_test_mod');
 pg_relation_filepath
----------------------
 base/16447/27577
(1 row)

[local]:5432 postgres@anbob=# \d test_mod
                          Table "public.test_mod"
 Column |         Type          | Collation | Nullable |      Default
--------+-----------------------+-----------+----------+-------------------
 id     | numeric(10,0)         |           |          |
 name   | character varying(20) |           |          |
 city   | varchar2(50)          |           |          | 'SJZ'::varchar2
 org    | varchar2(50)          |           | not null | 'China'::varchar2
Indexes:
    "idx_test_mod" btree (id)

[local]:5432 postgres@anbob=# alter table test_mod alter  column name set  not null;
ALTER TABLE
[local]:5432 postgres@anbob=# SELECT c.oid,
  n.nspname,
  c.relname
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname OPERATOR(pg_catalog.~) '^(test_mod)$' COLLATE pg_catalog.default
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 2, 3;
  oid  | nspname | relname
-------+---------+----------
 27574 | public  | test_mod
(1 row)


[local]:5432 postgres@anbob=# SELECT a.attname,
anbob-#   pg_catalog.format_type(a.atttypid, a.atttypmod),
anbob-#   (SELECT pg_catalog.pg_get_expr(d.adbin, d.adrelid, true)
anbob(#    FROM pg_catalog.pg_attrdef d
anbob(#    WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef),
anbob-#   a.attnotnull,
anbob-#   (SELECT c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type t
anbob(#    WHERE c.oid = a.attcollation AND t.oid = a.atttypid AND a.attcollation <> t.typcollation) AS attcollation,
anbob-#   a.attidentity,
anbob-#   a.attgenerated
anbob-# FROM pg_catalog.pg_attribute a
anbob-# WHERE a.attrelid = '27574' AND a.attnum > 0 AND NOT a.attisdropped
anbob-# ORDER BY a.attnum;
 attname |      format_type      |    pg_get_expr    | attnotnull | attcollation | attidentity | attgenerated
---------+-----------------------+-------------------+------------+--------------+-------------+--------------
 id      | numeric(10,0)         |                   | f          |              |             |
 name    | character varying(20) |                   | f          |              |             |
 city    | varchar2(50)          | 'SJZ'::varchar2   | f          |              |             |
 org     | varchar2(50)          | 'China'::varchar2 | t          |              |             |
(4 rows)


[local]:5432 postgres@anbob=# alter table test_mod alter  column name set  default 'anbob';
ALTER TABLE
[local]:5432 postgres@anbob=# SELECT c.oid,
  n.nspname,
  c.relname
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname OPERATOR(pg_catalog.~) '^(test_mod)$' COLLATE pg_catalog.default
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 2, 3;
  oid  | nspname | relname
-------+---------+----------
 27574 | public  | test_mod
(1 row)

Note:
PG13.2中增加列带default值,都没有发现数据重写,包括像MYSQL的set null 也未重写表。其实这个特性是从pg11引入的,在系统表 pg_catalog.pg_attribute 中添加了两个字段:atthasmissing 和 attmissingval。

— PG 11–
Allow ALTER TABLE to add a column with a non-null default without a table rewrite

注意default值如果是 volatile 类型的函数也会重写表如在pg 16中增加default random()

select pg_relation_filepath('test_mod');
pg_relation_filepath
base/5/16384
ALTER TABLE TEST_MOD add passwd varchar(50) default random();
select pg_relation_filepath('test_mod');
pg_relation_filepath
base/5/16391

另外PG的drop column都可以不用回写表。在vacuum full前表列还可以通过特殊手段更新字典恢复。

ALTER TABLE TEST_MOD drop passwd;
select attname, attmissingval, atthasmissing FROM pg_attribute 
WHERE attnum > 0 and attrelid = 'test_mod'::regclass;
attname attmissingval atthasmissing
id null f
name null f
city null f
org null f
……..pg.dropped.5…….. null f

PostgreSQL V9.6

postgres@anbob=# select pg_relation_filepath('test_mod');

pg_relation_filepath
--------------------
base/12404/16387

postgres@anbob=# ALTER TABLE TEST_MOD add city varchar(50) default 'SJZ';
postgres@anbob=# select pg_relation_filepath('test_mod');

pg_relation_filepath
-----------------------
base/12404/16389

postgres@anbob=# ALTER TABLE TEST_MOD add org varchar(50) default 'China' not null;
postgres@anbob=# select pg_relation_filepath('test_mod');

pg_relation_filepath
--------------------------
base/12404/16394

postgres@anbob=# ALTER TABLE TEST_MOD add addr varchar(50) ;
postgres@anbob=# select pg_relation_filepath('test_mod');

pg_relation_filepath
-----------------------
base/12404/16399

Note:
postgresql v11之前版本,add column 带default值都是需要重写表的。

MogDB v5( openGAUSS)

[omm@mogdb1 ~]$ gsql -r
gsql ((MogDB 5.0.1 build ae6d2ada) compiled at 2023-08-16 09:07:43 commit 0 last mr 1804 )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.


db1=# drop table test_mod;
DROP TABLE
db1=# create table test_mod(id numeric(10),name varchar(20));
CREATE TABLE
db1=# insert into test_mod
db1-# select 1,'anbob.com'
db1-# from generate_series(1,10000);
INSERT 0 10000
db1=# create index idx_test_mod on test_mod(id);
CREATE INDEX
db1=# select pg_relation_filepath('test_mod');
 pg_relation_filepath
----------------------
 base/18596/40646
(1 row)

db1=#  select pg_relation_filepath('idx_test_mod');
 pg_relation_filepath
----------------------
 base/18596/40649
(1 row)

db1=# ALTER TABLE TEST_MOD add city varchar2(50) default 'SJZ';
ALTER TABLE
db1=#  select pg_relation_filepath('idx_test_mod');
 pg_relation_filepath
----------------------
 base/18596/40649
(1 row)

db1=# select pg_relation_filepath('test_mod');
 pg_relation_filepath
----------------------
 base/18596/40646
(1 row)

db1=# ALTER TABLE TEST_MOD add org varchar2(50) default 'China' not null;
ALTER TABLE
db1=#  select pg_relation_filepath('idx_test_mod');
 pg_relation_filepath
----------------------
 base/18596/40649
(1 row)

db1=# select pg_relation_filepath('test_mod');
 pg_relation_filepath
----------------------
 base/18596/40646
(1 row)

db1=# \d test_mod
                           Table "public.test_mod"
 Column |         Type          |                  Modifiers
--------+-----------------------+---------------------------------------------
 id     | numeric(10,0)         |
 name   | character varying(20) |
 city   | character varying(50) | default 'SJZ'::character varying
 org    | character varying(50) | not null default 'China'::character varying
Indexes:
    "idx_test_mod" btree (id) TABLESPACE pg_default

db1=# alter table test_mod modify name not null;
ALTER TABLE
db1=# select pg_relation_filepath('test_mod');
 pg_relation_filepath
----------------------
 base/18596/40646
(1 row)

db1=#  select pg_relation_filepath('idx_test_mod');
 pg_relation_filepath
----------------------
 base/18596/40649
(1 row)

Note:
虽然openGauss是基于postgreSQL v9版本,但是它的分支持目前add column default值不需要重写表。修改not null约束也不会。

达梦数据库V8

[dmdba@oel7db1 ~]$ sh dm_connect.sh

服务器[LOCALHOST:5236]:处于普通打开状态
登录使用时间 : 6.564(ms)
disql V8
SQL> create table test_mod(id numeric(10),name varchar(20));
操作已执行
已用时间: 225.516(毫秒). 执行号:500.

SQL> insert into test_mod
select 1,'anbob.com' from dual connect by rownum<=10000; 
2 影响行数 10000 已用时间: 225.268(毫秒). 执行号:502. 

SQL> select owner,object_name,object_id,data_object_id from dba_objects where object_name='TEST_MOD';

行号     OWNER  OBJECT_NAME OBJECT_ID DATA_OBJECT_ID
---------- ------ ----------- --------- --------------
1          SYSDBA TEST_MOD    1149      NULL

已用时间: 26.004(毫秒). 执行号:504.

SQL>  select OWNER,SEGMENT_NAME,SEGMENT_TYPE,HEADER_FILE,HEADER_BLOCK from dba_segments 
where segment_name='TEST_MOD';

行号     OWNER  SEGMENT_NAME SEGMENT_TYPE HEADER_FILE HEADER_BLOCK
---------- ------ ------------ ------------ ----------- ------------
1          SYSDBA TEST_MOD     TABLE        0           2848

已用时间: 729.380(毫秒). 执行号:508.
SQL> ALTER TABLE TEST_MOD add city varchar2(50) default 'SJZ';
操作已执行
已用时间: 279.053(毫秒). 执行号:509.
SQL> select OWNER,SEGMENT_NAME,SEGMENT_TYPE,HEADER_FILE,HEADER_BLOCK from dba_segments 
where segment_name='TEST_MOD';

行号     OWNER  SEGMENT_NAME SEGMENT_TYPE HEADER_FILE HEADER_BLOCK
---------- ------ ------------ ------------ ----------- ------------
1          SYSDBA TEST_MOD     TABLE        0           2912

已用时间: 779.916(毫秒). 执行号:510.
SQL> select owner,object_name,object_id,data_object_id from dba_objects where object_name='TEST_MOD';

行号     OWNER  OBJECT_NAME OBJECT_ID DATA_OBJECT_ID
---------- ------ ----------- --------- --------------
1          SYSDBA TEST_MOD    1149      NULL

已用时间: 2.170(毫秒). 执行号:511.
SQL> ALTER TABLE TEST_MOD add org varchar2(50) default 'China' not null;
操作已执行
已用时间: 163.547(毫秒). 执行号:512.
SQL> select owner,object_name,object_id,data_object_id from dba_objects where object_name='TEST_MOD';

行号     OWNER  OBJECT_NAME OBJECT_ID DATA_OBJECT_ID
---------- ------ ----------- --------- --------------
1          SYSDBA TEST_MOD    1149      NULL

已用时间: 3.116(毫秒). 执行号:513.
SQL> select OWNER,SEGMENT_NAME,SEGMENT_TYPE,HEADER_FILE,HEADER_BLOCK from dba_segments 
where segment_name='TEST_MOD';

行号     OWNER  SEGMENT_NAME SEGMENT_TYPE HEADER_FILE HEADER_BLOCK
---------- ------ ------------ ------------ ----------- ------------
1          SYSDBA TEST_MOD     TABLE        0           2992

已用时间: 337.806(毫秒). 执行号:514.
SQL> ALTER TABLE TEST_MOD add ADDR VARCHAR2(20);
操作已执行
已用时间: 94.861(毫秒). 执行号:515.
SQL> select OWNER,SEGMENT_NAME,SEGMENT_TYPE,HEADER_FILE,HEADER_BLOCK from dba_segments 
where segment_name='TEST_MOD';

行号     OWNER  SEGMENT_NAME SEGMENT_TYPE HEADER_FILE HEADER_BLOCK
---------- ------ ------------ ------------ ----------- ------------
1          SYSDBA TEST_MOD     TABLE        0           3072

已用时间: 344.988(毫秒). 执行号:516.

SQL> desc dba_extents
[-20001]:无效的对象名
-20001: OBJ_IS_EXISTS line 199
-20001: SHOW_PARA_INFO line 302 .
已用时间: 104.655(毫秒). 执行号:0.
SQL>

Note:
这让我很不能理解,不只是add column带default值会导致segment header变化,也就是数据库重生成了,连增加一个列不带default都会导致数据重写。只能找达梦原厂的网友求助。原来还有alter_table_opt参数这个后门。
alter_table_opt参数

SQL> alter system set 'alter_table_opt'=3;
DMSQL 过程已成功完成

SQL> select name ,type,value,sys_value,file_value,description from v$parameter where upper(name) like upper('%&1%');
输入 1的值:alter_table
原值 1:select name ,type,value,sys_value,file_value,description from v$parameter where upper(name) like upper('%&1%');
新值 1:select name ,type,value,sys_value,file_value,description from v$parameter where upper(name) like upper('%alter_table%');

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


SQL> select OWNER,SEGMENT_NAME,SEGMENT_TYPE,HEADER_FILE,HEADER_BLOCK from dba_segments where segment_name='TEST_1';

行号     OWNER  SEGMENT_NAME SEGMENT_TYPE HEADER_FILE HEADER_BLOCK
---------- ------ ------------ ------------ ----------- ------------
1          SYSDBA TEST_1       TABLE        0           2912

SQL> ALTER TABLE TEST_MOD add ADDR VARCHAR2(20);
操作已执行
已用时间: 104.502(毫秒). 执行号:528.

SQL> select OWNER,SEGMENT_NAME,SEGMENT_TYPE,HEADER_FILE,HEADER_BLOCK from dba_segments where segment_name='TEST_MOD';

行号     OWNER  SEGMENT_NAME SEGMENT_TYPE HEADER_FILE HEADER_BLOCK
---------- ------ ------------ ------------ ----------- ------------
1          SYSDBA TEST_MOD     TABLE        0           2912

已用时间: 332.158(毫秒). 执行号:530.
SQL>  ALTER TABLE TEST_MOD add city1 varchar2(50) default 'SJZ';
操作已执行
已用时间: 17.726(毫秒). 执行号:531.
SQL> select OWNER,SEGMENT_NAME,SEGMENT_TYPE,HEADER_FILE,HEADER_BLOCK from dba_segments where segment_name='TEST_MOD';

行号     OWNER  SEGMENT_NAME SEGMENT_TYPE HEADER_FILE HEADER_BLOCK
---------- ------ ------------ ------------ ----------- ------------
1          SYSDBA TEST_MOD     TABLE        0           2912

已用时间: 336.116(毫秒). 执行号:532.
SQL> ALTER TABLE TEST_MOD add org1 varchar2(50) default 'China' not null;
操作已执行
已用时间: 17.416(毫秒). 执行号:533.
SQL> select OWNER,SEGMENT_NAME,SEGMENT_TYPE,HEADER_FILE,HEADER_BLOCK from dba_segments where segment_name='TEST_MOD';

行号     OWNER  SEGMENT_NAME SEGMENT_TYPE HEADER_FILE HEADER_BLOCK
---------- ------ ------------ ------------ ----------- ------------
1          SYSDBA TEST_MOD     TABLE        0           2912

已用时间: 344.293(毫秒). 执行号:534.

SQL> alter system set 'alter_table_opt'=2;
DMSQL 过程已成功完成

SQL>  ALTER TABLE TEST_MOD add city2 varchar2(50) default 'SJZ';
 操作已执行
已用时间: 284.366(毫秒). 执行号:537.
SQL> select OWNER,SEGMENT_NAME,SEGMENT_TYPE,HEADER_FILE,HEADER_BLOCK from dba_segments where segment_name='TEST_MOD';

行号     OWNER  SEGMENT_NAME SEGMENT_TYPE HEADER_FILE HEADER_BLOCK
---------- ------ ------------ ------------ ----------- ------------
1          SYSDBA TEST_MOD     TABLE        0           2848

SQL> ALTER TABLE TEST_MOD add org2 varchar2(50) default 'China' not null;
操作已执行
已用时间: 302.368(毫秒). 执行号:539.
SQL> select OWNER,SEGMENT_NAME,SEGMENT_TYPE,HEADER_FILE,HEADER_BLOCK from dba_segments where segment_name='TEST_MOD';

行号     OWNER  SEGMENT_NAME SEGMENT_TYPE HEADER_FILE HEADER_BLOCK
---------- ------ ------------ ------------ ----------- ------------
1          SYSDBA TEST_MOD     TABLE        0           3168

SQL>  ALTER TABLE TEST_MOD add addr2 varchar2(50);
操作已执行
已用时间: 14.927(毫秒). 执行号:543.
SQL> select OWNER,SEGMENT_NAME,SEGMENT_TYPE,HEADER_FILE,HEADER_BLOCK from dba_segments where segment_name='TEST_MOD';

行号     OWNER  SEGMENT_NAME SEGMENT_TYPE HEADER_FILE HEADER_BLOCK
---------- ------ ------------ ------------ ----------- ------------
1          SYSDBA TEST_MOD     TABLE        0           3168


SQL> select owner,object_name,object_id,data_object_id from dba_objects where object_name='TEST_MOD';

行号     OWNER  OBJECT_NAME OBJECT_ID DATA_OBJECT_ID
---------- ------ ----------- --------- --------------
1          SYSDBA TEST_MOD    1149      NULL

已用时间: 3.046(毫秒). 执行号:542.

SQL> alter system set 'alter_table_opt'=1;
DMSQL 过程已成功完成
已用时间: 23.082(毫秒). 执行号:545.
SQL>  ALTER TABLE TEST_MOD add city3 varchar2(50) default 'SJZ';
操作已执行
已用时间: 231.681(毫秒). 执行号:546.
SQL> select OWNER,SEGMENT_NAME,SEGMENT_TYPE,HEADER_FILE,HEADER_BLOCK from dba_segments where segment_name='TEST_MOD';

行号     OWNER  SEGMENT_NAME SEGMENT_TYPE HEADER_FILE HEADER_BLOCK
---------- ------ ------------ ------------ ----------- ------------
1          SYSDBA TEST_MOD     TABLE        0           2848


SQL>  ALTER TABLE TEST_MOD add org3 varchar2(50) default 'China' not null;
操作已执行
已用时间: 952.860(毫秒). 执行号:549.
SQL> select OWNER,SEGMENT_NAME,SEGMENT_TYPE,HEADER_FILE,HEADER_BLOCK from dba_segments where segment_name='TEST_MOD';

行号     OWNER  SEGMENT_NAME SEGMENT_TYPE HEADER_FILE HEADER_BLOCK
---------- ------ ------------ ------------ ----------- ------------
1          SYSDBA TEST_MOD     TABLE        0           3056


SQL> ALTER TABLE TEST_MOD add addr3 varchar2(50);
操作已执行
已用时间: 232.952(毫秒). 执行号:551.
SQL> select OWNER,SEGMENT_NAME,SEGMENT_TYPE,HEADER_FILE,HEADER_BLOCK from dba_segments where segment_name='TEST_MOD';

行号     OWNER  SEGMENT_NAME SEGMENT_TYPE HEADER_FILE HEADER_BLOCK
---------- ------ ------------ ------------ ----------- ------------
1          SYSDBA TEST_MOD     TABLE        0           3472

SQL> select owner,object_name,object_id,data_object_id from dba_objects where object_name='TEST_MOD';

行号     OWNER  OBJECT_NAME OBJECT_ID DATA_OBJECT_ID
---------- ------ ----------- --------- --------------
1          SYSDBA TEST_MOD    1149      NULL

SQL> select dbms_random.random() from dual;

行号     DBMS_RANDOM.RANDOM()
---------- --------------------
1          -802100670

SQL> alter table test add pass varchar2(30) default dbms_random.random(); 
alter table test add pass varchar2(30) default dbms_random.random(); 
第1 行附近出现错误[-2670]:对象[PASS]DEFAULT约束表达式无效. 已用时间: 0.862(毫秒). 执行号:0.

Note:
在当前版本的达梦数据库中alter table的形为受参数’alter_table_opt’影响,默认为0实际值作用似乎和1是一样。alter_table_opt值的作用:
1, alter_table_opt=1时,alter table add column 无论是否带default值,都重建数据表,但table object_id不变,类似oracle move;
2, alter_table_opt=2时,alter table add column 对于带default值,使用重建数据表,无default值,直接修改数据字典,table object_id不变;
3,alter_table_opt=3时,alter table add column 无论是否带default值,都不重建数据表,table object_id不变,类似oracle 12c以后的特性。

打赏

,

目前这篇文章还没有评论(Rss)

我要评论