修改表

表创建完成后，用户可根据需求对表的定义进行修改，修改的范围包括：增加列、删除列、修改列名称、数据类型、数据长度、增加约束、删除约束等。

注意：
修改表是一种比较危险的做法，可能造成数据的破坏。因此对表做修改时，特别是存在大量数据的表，需经过数据库管理员审核，且建议对该表进行数据备份。

主要语法结构

语法格式

sql

altertablestmt::=
ALTER TABLE sche_name.tbl_name alter_specification

参数说明

ALTER TABLE：SQL语句的关键字，用于修改现有的表结构。
sche_name：表所属的模式（schema）的名称。
tbl_name：要修改的表的名称。
alter_specification：具体的修改操作，可以包含多种不同的操作类型：
- 添加列
- 删除列
- 修改列
- 添加、修改和删除列的组合操作
- 约束操作
- 更改表的所有者
- 设置表的状态
- 启用或禁用操作权限
- 分区操作
- 重建堆表
- 设置慢速修改模式

说明：
修改表结构为DDL操作，将会对对象加排他锁，此时需保证无其他用户使用操作该对象。
修改表结构的操作中，删除表列、增加带有默认值非空列将重构对象与数据，后台将创建一张临时表对原表进行结构与数据复制，数据量越大消耗时间越长，故该类操作需经过慎重考虑再进行。
对表添加唯一值索引后，会自动在表上创建对应的唯一值约束，此时若需删除该索引，只能通过删除对应约束的方式进行删除，不能直接删除唯一值索引。
针对分区表，删除与清理分区将导致全局索引失效，需重建索引，所以在分区表上不建议使用全局索引，尽量使用局部分区索引，避免分区操作带来的重建索引开销；若需重建索引可指定REBUILD参数。
数据库系统中非空表不允许进行非兼容类型间的数据类型转换；定长字符CHAR进行精度扩展将重构数据，建议创表或更改类型时使用VARCHAR数据类型；NUMERIC字段类型进行精度扩展时，由于不重构数据，故针对历史记录的修改超过原类型精度则数据无法保证正确性，建议存在该操作的表记录变更采用删除再插入的方式进行。
CASCADE关键字用于存在依赖关系的表对象变更，此时将忽略对象依赖关系，但是可能导致依赖对象失效或被系统删除，所以对存在依赖关系的对象进行变更操作需确认各对象依赖关系，操作完成后应检查依赖对象的有效性。

添加列add_columns

语法格式

sql

add_columns [CASCADE | RESTRICT] [NOWAIT | WAIT | WAIT ICONST]

add_columns::=
    ADD [COLUMN] table_element[,table_element]
|   ADD [COLUMN] ( table_element[,table_element] )

table_element::=
    col_name type [IDENTITY(B,S)] [col_qual_list] [COLLATE colid] [COMMENT SCONST]
|   [CONSTRAINT name] constraint_elem

col_qual_list::=
    col_qual_list [CONSTRAINT name] col_constraint_elem
    
col_constraint_elem::=
    NOT NULL
|   NULL
|   UNIQUE
|   PRIMARY KEY
|   CHECK ( bool_expr ) 
|   DEFAULT expr
|   REFERENCES name_space [(index_col_name,...)] [ON UPDATE {CASCADE | SET NULL | NO ACTION | SET DEFAULT}] [ON DELETE {CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
|   FOREIGN KEY opt_fk_name REFERENCES name_space [(index_col_name,...)] [ON UPDATE {CASCADE | SET NULL | NO ACTION | SET DEFAULT}] [ON DELETE {CASCADE | SET NULL | NO ACTION | SET DEFAULT}]

constraint_elem::=
    CHECK ( bool_expr )
|   UNIQUE ( columnList )
|   PRIMARY KEY ( columnList )
|   FOREIGN KEY opt_fk_name ( columnList ) REFERENCES name_space [(index_col_name,...)] [ON UPDATE {CASCADE | SET NULL | NO ACTION | SET DEFAULT}] [ON DELETE {CASCADE | SET NULL | NO ACTION | SET DEFAULT}]

参数说明

[CASCADE | RESTRICT]：控制是否级联修改依赖对象。
- CASCADE：自动应用更改到所有依赖对象。
- RESTRICT：如果有依赖对象，则阻止更改。
[NOWAIT | WAIT | WAIT ICONST]：控制等待锁的行为。
- NOWAIT：如果无法立即获取锁，则立即返回错误。
- WAIT：等待直到能够获取锁。
- WAIT ICONST：等待直到满足特定条件。
ADD [COLUMN]：添加一个或多个新列。
col_name: 列的名称。
type: 列的数据类型。
[IDENTITY(B,S)]：是否为标识列及其生成方式。
[col_qual_list]：列的约束条件列表。
[COLLATE colid]：列的排序规则。
[COMMENT SCONST]：列的注释。
[CONSTRAINT name]：约束的名称。

删除列drop_columns

语法格式

sql

drop_columns [CASCADE | RESTRICT] [NOWAIT | WAIT | WAIT ICONST]

drop_columns::=
    DROP COLUMN name_list

参数说明

DROP COLUMN：删除一个或多个列。
name_list：要删除的列名列表。

修改列alter_columns

语法格式

sql

alter_columns [CASCADE | RESTRICT] [FORCE] [NOWAIT | WAIT | WAIT ICONST]

alter_columns::=
    ALTER [COLUMN] alt_columns
|   ALTER [COLUMN] ( alt_columns )
|   MODIFY [COLUMN] alt_columns
|   MODIFY [COLUMN] ( alt_columns )

alt_columns::=
    alter_column_item
|   col_name type [IDENTITY(B,S)] [col_qual_list] [COLLATE colid] [COMMENT SCONST]
|   alt_columns ',' alter_column_item
|   alt_columns ',' col_name type [IDENTITY(B,S)] [col_qual_list] [COLLATE colid] [COMMENT SCONST]

alter_column_item::=
    colid SET DEFAULT [ON NULL] b_expr
|   colid DROP DEFAULT
|   colid SET NOT NULL
|   colid SET NOTNULL
|   colid SET NULL
|   colid DROP NOT NULL	
|   colid DROP NOTNULL

参数说明

ALTER [COLUMN]：修改一个或多个列。
MODIFY [COLUMN]：修改一个或多个列。

添加、修改和删除列的组合操作

语法格式

sql

add_columns drop_columns [CASCADE | RESTRICT] [NOWAIT | WAIT | WAIT ICONST]
| add_columns alter_columns [CASCADE | RESTRICT] [FORCE] [NOWAIT | WAIT | WAIT ICONST]
| add_columns alter_columns drop_columns [CASCADE | RESTRICT] [FORCE] [NOWAIT | WAIT | WAIT ICONST]
| alter_columns drop_columns [CASCADE | RESTRICT] [FORCE] [NOWAIT | WAIT | WAIT ICONST]

约束操作

语法格式

sql

MODIFY CONSTRAINT colid {ENABLE | DISABLE}
| ENABLE CONSTRAINT name_list [NOWAIT | WAIT | WAIT ICONST]
| DISABLE CONSTRAINT name_list [NOWAIT | WAIT | WAIT ICONST]
| DROP CONSTRAINT name_list [KEEP INDEX | DROP INDEX] [CASCADE | RESTRICT] [NOWAIT | WAIT | WAIT ICONST]

参数说明

MODIFY CONSTRAINT：修改约束的状态。
ENABLE CONSTRAINT：启用一个或多个约束。
DISABLE CONSTRAINT：禁用一个或多个约束。
DROP CONSTRAINT：删除一个或多个约束。
[KEEP INDEX | DROP INDEX]：控制是否保留或删除相关的索引。

更改表的所有者

语法格式

sql

OWNER TO userid [NOWAIT | WAIT | WAIT ICONST]

参数说明

OWNER TO userid：更改表的所有者。

设置表的状态

语法格式

sql

SET ONLINE [NOWAIT | WAIT | WAIT ICONST]
| SET OFFLINE [NOWAIT | WAIT | WAIT ICONST]

参数说明

SET ONLINE：将表设置为在线状态。
SET OFFLINE：将表设置为离线状态。

启用或禁用操作权限

语法格式

sql

ENABLE operation_commalist [NOWAIT | WAIT | WAIT ICONST]
| DISABLE operation_commalist [NOWAIT | WAIT | WAIT ICONST]

operation_commalist::=
    operation[,operation]    
    
operation::=
    SELECT
|   INSERT
|   UPDATE
|   DELETE
|   EXECUTE
|   REFERENCES
|   ALTER
|   DROP
|   INDEX
|   TRIGGER

参数说明

ENABLE operation_commalist：启用一个或多个操作权限。
DISABLE operation_commalist：禁用一个或多个操作权限。
operation_commalist：操作权限列表，如 SELECT，INSERT，UPDATE。

分区操作

语法格式

sql

SET PARTITION colid ONLINE [NOWAIT | WAIT | WAIT ICONST]
| SET PARTITION colid OFFLINE [NOWAIT | WAIT | WAIT ICONST]
| DROP PARTITION colid [REBUILD GLOBAL INDEX] [NOWAIT | WAIT | WAIT ICONST]
| TRUNCATE PARTITION colid [REBUILD GLOBAL INDEX] [NOWAIT | WAIT | WAIT ICONST]
| ADD PARTITION colid VALUES (parti_values) [NOWAIT | WAIT | WAIT ICONST]
| ADD PARTITION colid VALUES LESS THAN (parti_values) [NOWAIT | WAIT | WAIT ICONST]

参数说明

SET PARTITION ONLINE：将分区设置为在线状态。
SET PARTITION OFFLINE：将分区设置为离线状态。
DROP PARTITION：删除分区。
- [REBUILD GLOBAL INDEX]：控制是否重建全局索引。
TRUNCATE PARTITION: 截断分区。
- [REBUILD GLOBAL INDEX]：控制是否重建全局索引。
ADD PARTITION：添加分区。
- VALUES (parti_values)：指定分区的值。
- VALUES LESS THAN (parti_values)：指定分区的值范围。

重建堆表

语法格式

sql

REBUILD HEAP [NOWAIT | WAIT | WAIT ICONST]

参数说明

REBUILD HEAP：重建表的堆结构，用于优化存储或解决碎片问题。

说明：
自V12.7版本开始，重建堆表时也会将表中的BLOB、CLOB类型等大对象存储进行重建。

设置慢速修改模式

语法格式

sql

SET SLOW MODIFY ON [NOWAIT | WAIT | WAIT ICONST]
| SET SLOW MODIFY OFF [NOWAIT | WAIT | WAIT ICONST]

参数说明

SET SLOW MODIFY ON：设置表的慢速修改模式。
SET SLOW MODIFY OFF：取消表的慢速修改模式。

示例

示例1
sql
```
ALTER TABLE test_tab ADD COLUMN new_col VARCHAR(20) NOT NULL DEFAULT '000';
```
该示例表示对表test_tab增加一列带默认值000的非空列new_col，此时将对该表进行重构，若原表中记录量越大，变更操作时间将越长。
示例2
sql
```
ALTER TABLE test_tab ADD CONSTRAINT test_tab_uk UNIQUE(kid,name);
```
该示例表示对表test_tab增加一个名为test_tab_uk的唯一值约束，约束键由kid与name组成。
示例3
sql
```
ALTER TABLE test_tab2 ADD PARTITION part_n VALUES LESS THAN('2022-05-20 10:10:10');
```
该示例表示对列表分区表test_tab2增加一个分区名为part_n的分区，分区键值为“2022-05-20 10:10:10”。
- 非分区表与哈希分区表不允许添加分区。
- 拥有MAXVALUES与OTHERVALUES的分区表不允许添加分区。
示例4
sql
```
ALTER TABLE test_tab ALTER COLUMN name VARCHAR(20);
```
该示例表示将test_tab表的name字段的数据类型变更为VARCHAR(20)，若不修改数据类型仅将精度更改为CHAR(20)，将重构数据，数据量越大时间开销越大。
示例5
sql
```
ALTER TABLE test_tab DROP COLUMN new_col CASCADE;
```
该示例表示强制删除test_tab表的new_col字段，此时将重构数据，数据量越大时间开销越大。
示例6
sql
```
ALTER TABLE test_tab DROP CONSTRAINT test_tab_uk;
```
该示例表示删除test_tab的test_tab_uk约束。
示例7
sql
```
ALTER TABLE test_tab DROP PARTITION part1 REBUILD GLOBAL INDEX;
```
该示例表示删除表test_tab的分区，分区名为part1，并重建该表全局索引。
说明：
- 删除分区时不允许删除异常分区（MAXVALUES/OTHERVALUES）。
- 哈希分区表的分区不允许删除。
- 删除分区将导致表的全局索引失效，此时需重建索引或在删除时指定REBUILD参数。
- 不允许删除分区表的所有分区，必须至少保留一个分区。
示例8
sql
```
ALTER TABLE test_tab DISABLE INSERT;
```
该示例表示将表test_tab置为不可插入状态，即该表不接受插入操作。
示例9
sql
```
ALTER TABLE test_tab ENABLE INSERT;
```
该示例表示将表test_tab置为允许插入状态，其含义同DISABLE INSERT相反。
示例10
sql
```
ALTER TABLE sysdba.test_tab RENAME TO test_tab_new;
```
该示例表示将sysdba模式下的表test_tab重命名为test_tab_new。
示例11
sql
```
ALTER TABLE test_tab_new RENAME kid TO keyid;
```
该示例表示将test_tab_new的列名kid改为keyid。
示例12
sql
```
ALTER TABLE test_tab_new OWNER TO guest;
```
该示例表示将test_tab_new的属主改为guest，此时用户guest拥有该表的所有权，可进行所有操作，其他用户访问该表需带上guest模式信息。
示例13
sql
```
ALTER TABLE guest.test_tab_new ALTER COLUMN PROC SET DEFAULT '北京';
```
该示例表示对guest模式下的test_tab_new表中的proc字段设置默认值，指定默认值为北京。
示例14
sql
```
ALTER TABLE guest.test_tab_new TRUNCATE partition part2;
```
该示例表示清除guest模式下的test_tab_new表的part2分区数据，此时该表上的全局索引将被置为失效，若该索引为唯一值，则会导致主键/唯一值约束失效，无法进行数据插入，需重建索引。

示例15

sql

ALTER TABLE test_tab REBUILD HEAP;

该示例表示重整表，重整表功能主要用于回收无效存储空间。当表数据通过 DELETE 操作删除非连续存储数据后，会产生无法再利用的无效存储，此部分存储只有通过重整表功能回收后，才能再次使用。

注意：
重整表将重新转移表的所有数据到新存储，对于数据量较大的表，此操作耗时较长，请谨慎操作。

数据库运维人员可以通过下列语句查询无效存储比例，评估是否需要执行重整表，当表存储碎片率超过 0.5（50%）时，建议执行重整表操作，以提升数据查询效率与存储利用率。

sql

-- 创建查询表中存储碎片率的存储函数
  CREATE OR REPLACE FUNCTION GET_TABLE_FRAGMENTATION_RATE(
   arg_db_name VARCHAR,
   arg_schema_name VARCHAR,
   arg_table_name VARCHAR
) RETURN DOUBLE AS
   TYPE ret_type IS RECORD(del_rows BIGINT, all_rows BIGINT);
   ret ret_type;
BEGIN
   SELECT
   sum(del_num) del_rows,
   sum(row_num)+del_rows all_rows
   INTO ret
   FROM sys_databases db
   INNER JOIN sys_schemas scm ON db.db_id=scm.db_id
   INNER JOIN sys_tables tb ON tb.schema_id=scm.schema_id and tb.db_id =scm.db_id
   INNER JOIN sys_gstores gsto ON tb.gsto_no=gsto.head_no
   INNER JOIN sys_stores sto ON gsto.gsto_no=sto.gsto_no
   WHERE db_name=arg_db_name
   AND schema_name=arg_schema_name
   AND table_name=arg_table_name;
   RETURN ret.del_rows/ret.all_rows;
END;

-- 创建表并插入 1000 条数据
CREATE TABLE tb_rebuild_heap(id INT IDENTITY(1,1),c1 CHAR(8192));
BEGIN
    FOR i IN 1..1000 LOOP
        INSERT INTO tb_rebuild_heap VALUES(DEFAULT,'test');
    END LOOP;
END;

-- 删除表中 id 为偶数的 500 条数据
DELETE FROM tb_rebuild_heap WHERE MOD(id,2)=0;

-- 查询表中存储碎片率，此时为 0.5（50%）
SELECT GET_TABLE_FRAGMENTATION_RATE('SYSTEM','SYSDBA','tb_rebuild_heap');

-- 执行重整表
ALTER TABLE tb_rebuild_heap REBUILD HEAP;

-- 查询表中存储碎片率，此时为 0（0%）
SELECT GET_TABLE_FRAGMENTATION_RATE('SYSTEM','SYSDBA','tb_rebuild_heap');

修改表 ​

主要语法结构 ​

语法格式 ​

参数说明 ​

添加列add_columns ​

语法格式 ​

参数说明 ​

删除列drop_columns ​

语法格式 ​

参数说明 ​

修改列alter_columns ​

语法格式 ​

参数说明 ​

添加、修改和删除列的组合操作 ​

语法格式 ​

约束操作 ​

语法格式 ​

参数说明 ​

更改表的所有者 ​

语法格式 ​

参数说明 ​

设置表的状态 ​

语法格式 ​

参数说明 ​

启用或禁用操作权限 ​

语法格式 ​

参数说明 ​

分区操作 ​

语法格式 ​

参数说明 ​

重建堆表 ​

语法格式 ​

参数说明 ​

设置慢速修改模式 ​

语法格式 ​

参数说明 ​

示例 ​

修改表

主要语法结构

语法格式

参数说明

添加列add_columns

语法格式

参数说明

删除列drop_columns

语法格式

参数说明

修改列alter_columns

语法格式

参数说明

添加、修改和删除列的组合操作

语法格式

约束操作

语法格式

参数说明

更改表的所有者

语法格式

参数说明

设置表的状态

语法格式

参数说明

启用或禁用操作权限

语法格式

参数说明

分区操作

语法格式

参数说明

重建堆表

语法格式

参数说明

设置慢速修改模式

语法格式

参数说明

示例