首页 » MySQL/TiDB/GoldenDB, ORACLE 9i-23ai, 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<=num do
-> insert into test_mod values (i,'anbob');
-> set i=i+1;
-> 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以后的特性。
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。