Description
You can use this statement to modify the structure of an existing table. For example, you can use this statement to modify the table and its attributes, add columns to it, modify its columns and attributes, and delete its columns.
Syntax
alter_table_stmt:
ALTER TABLE table_name alter_table_action_list;
alter_table_action_list:
alter_table_action [, alter_table_action ...]
alter_table_action:
ADD {column_definition | (column_definition_list)}
| MODIFY [COLUMN] column_definition
| MODIFY CONSTRAINT constraint_name { ENABLE | DISABLE }
| DROP COLUMN column_name
| ADD [CONSTRAINT [constraint_name]] UNIQUE {INDEX | KEY} [index_name] index_desc
| ADD [CONSTRAINT [constraint_name]] FOREIGN KEY (column_name_list) references_clause
| ADD [CONSTRAINT [constraint_name]] CHECK (expr)
| ADD {INDEX | KEY} [index_name] index_desc
| ALTER INDEX index_name [VISIBLE | INVISIBLE]
| DROP {INDEX | KEY} index_name
| ADD PARTITION (range_partition_list)
| DROP {PARTITION | SUBPARTITION} partition_name_list [UPDATE GLOBAL INDEXES]
| REORGANIZE PARTITION name_list INTO partition_range_or_list
| TRUNCATE {PARTITION | SUBPARTITION} partition_name_list [UPDATE GLOBAL INDEXES]
| [SET] table_option_list
| RENAME [TO] table_name
| RENAME COLUMN old_col_name TO new_col_name
| DROP TABLEGROUP
| DROP CONSTRAINT constraint_name
rename_table_action_list:
rename_table_action [, rename_table_action ...]
rename_table_action:
table_name TO table_name
column_definition_list:
column_definition [, column_definition ...]
column_definition:
column_name data_type
[DEFAULT const_value] [AUTO_INCREMENT]
[NULL | NOT NULL] [[PRIMARY] KEY] [UNIQUE [KEY]] comment
index_desc:
(column_desc_list) [index_type] [index_option_list]
column_desc_list:
column_desc [, column_desc ...]
column_desc:
column_name [(length)] [ASC | DESC]
references_clause:
REFERENCES table_name [ (column_name, column_name ...) ] [ON DELETE {CASCADE}]]
index_type:
USING BTREE
index_option_list:
index_option [ index_option ...]
index_option:
[GLOBAL | LOCAL]
| block_size
| compression
| STORING(column_name_list)
| comment
table_option_list:
table_option [ table_option ...]
table_option:
| primary_zone
| replica_num
| table_tablegroup
| block_size
| compression
| AUTO_INCREMENT [=] INT_VALUE
| comment
| DUPLICATE_SCOPE [=] "none|zone|region|cluster"
partition_option:
PARTITION BY HASH(column_name_list)
[subpartition_option] hash_partition_define
| PARTITION BY RANGE (column_name_list)
[subpartition_option] (range_partition_list)
| PARTITION BY LIST (column_name_list)
[subpartition_option] (list_partition_list)
/*Subpartitioning with a template*/
subpartition_option:
SUBPARTITION BY HASH (column_name_list) hash_subpartition_define
| SUBPARTITION BY RANGE (column_name_list) SUBPARTITION TEMPLATE
(range_subpartition_list)
| SUBPARTITION BY LIST (column_name_list) SUBPARTITION TEMPLATE
(list_subpartition_list)
/*Subpartitioning without a template*/
subpartition_option:
SUBPARTITION BY HASH (column_name_list)
| SUBPARTITION BY RANGE (column_name_list)
| SUBPARTITION BY LIST (column_name_list)
subpartition_list:
(hash_subpartition_list)
| (range_subpartition_list)
| (list_subpartition_list)
hash_partition_define:
PARTITIONS partition_count [TABLESPACE tablespace] [compression]
| (hash_partition_list)
hash_partition_list:
hash_partition [, hash_partition, ...]
hash_partition:
partition [partition_name] [subpartition_list/*Only non-template-based subpartitions can be defined.*/]
hash_subpartition_define:
SUBPARTITIONS subpartition_count
| SUBPARTITION TEMPLATE (hash_subpartition_list)
hash_subpartition_list:
hash_subpartition [, hash_subpartition, ...]
hash_subpartition:
subpartition [subpartition_name]
range_partition_list:
range_partition [, range_partition ...]
range_partition:
PARTITION [partition_name]
VALUES LESS THAN {(expression_list) | (MAXVALUE)}
[subpartition_list/*Only non-template-based subpartitions can be defined.*/]
[ID = num] [physical_attribute_list] [compression]
range_subpartition_list:
range_subpartition [, range_subpartition ...]
range_subpartition:
SUBPARTITION subpartition_name
VALUES LESS THAN {(expression_list) | MAXVALUE} [physical_attribute_list]
list_partition_list:
list_partition [, list_partition] ...
list_partition:
PARTITION [partition_name]
VALUES (DEFAULT|expression_list)
[subpartition_list/*Only non-template-based subpartitions can be defined.*/]
[ID num] [physical_attribute_list] [compression]
list_subpartition_list:
list_subpartition [, list_subpartition] ...
list_subpartition:
SUBPARTITION [partition_name] VALUES (DEFAULT|expression_list) [physical_attribute_list]
expression_list:
expression [, expression ...]
column_name_list:
column_name [, column_name ...]
partition_name_list:
partition_name [, partition_name ...]
partition_count | subpartition_count:
INT_VALUE
Parameters
| Parameter | Description |
|---|---|
| ADD | Adds a column. You cannot add a primary key column. |
| MODIFY [COLUMN] | Modifies the column attribute. |
| MODIFY CONSTRAINT | Enables or disables the foreign key constraint or CHECK constraint. |
| DROP COLUMN | Drops a column. You cannot drop a primary key column or columns that contain indexes. |
| ADD [UNIQUE INDEX] | Adds a unique index. |
| ADD [INDEX] | Adds a normal index. |
| ALTER [INDEX] | Modifies index attributes. |
| ADD [PARTITION] | Adds a partition. |
| DROP {PARTITION | SUBPARTITION} | Drops partitions: * PARTITION: drops the specified RANGE or LIST partitions (all subpartitions that exist under these partitions will be dropped as well), including deleting the partition definitions and partition data, and maintains the local indexes defined on the partitioned table. * SUBPARTITION: drops the specified *-RANGE or *-LIST subpartitions, including deleting the subpartition definitions and subpartition data, and maintains the local indexes defined on the partitioned table. If you specify UPDATE GLOBAL INDEXES, the system updates global indexes when dropping the partition. If you do not specify UPDATE GLOBAL INDEXES, the global indexes on the partitioned table must be in an unusable state. Separate multiple partition names with commas (,). |
| REORGANIZE [PARTITION] | Reorganizes a partition. |
| TRUNCATE {PARTITION | SUBPARTITION} | Truncates partitions: * PARTITION: deletes all data in the specified RANGE or LIST partitions (data in all subpartitions that exist under these partitions will be deleted as well), and maintains the local indexes defined on the partitioned table. * SUBPARTITION: deletes all data in the specified *-RANGE or *-LIST subpartitions, and maintains the local indexes defined on the partitioned table. If you specify UPDATE GLOBAL INDEXES, the system updates global indexes when deleting the data. If you do not specify UPDATE GLOBAL INDEXES, the global indexes on the partitioned table must be in an unusable state. Separate multiple partition names with commas (,). |
| RENAME [TO] table_name | Renames a table. |
| DROP [TABLEGROUP] | Drops a table group. |
| DROP [CONSTRAINT] | Drops a constraint. |
| SET BLOCK_SIZE | Sets the block size of a table partition. |
| SET REPLICA_NUM | Specifies the number of replicas of the table. |
| SET COMPRESSION | Sets the compression mode of the table. |
| SET USE_BLOOM_FILTER | Specifies whether to use BloomFilter. |
| SET COMMENT | Sets comment information. |
| SET PROGRESSIVE_MERGE_NUM | Sets the number of progressive compaction steps. Value range: [1,64]. |
Example
- Example 1: Modifying the field type of Field d in Table t2
obclient>CREATE TABLE t2(d VARCHAR(3));
Query OK, 0 rows affected (0.04 sec)
obclient>ALTER TABLE t2 MODIFY d CHAR(10);
Query OK, 0 rows affected (0.04 sec)
Example 2: Adding and deleting a column
obclient> CREATE TABLE test (c1 NUMBER(30) PRIMARY KEY,c2 VARCHAR(50)); Query OK, 0 rows affected (0.07 sec)
- Example 3: Setting the number of replicas of Table test and adding Column c5:
obclient> ALTER TABLE test SET REPLICA_NUM=2, ADD c5 INT;
Query OK, 0 rows affected (0.02 sec)
- Add the p1_r4 subpartition to the non-template-based subpartition table named t_range_range1.
obclient>ALTER TABLE t_range_range1 MODIFY partition p1 ADD subpartition p1_r4 values less than (400);
Query OK, 0 rows affected (0.08 sec)
- Delete the p2_r1 subpartition from the non-template-based subpartition table named t_range_range1.
obclient>ALTER TABLE t_range_range1 DROP subpartition p2_r1;
Query OK, 0 rows affected (0.08 sec)
- Add the p4 partition to a non-template-based subpartition table named t_range_range1. You need to specify both the partition definition and the subpartition definition for this partition at the same time.
obclient>ALTER TABLE t_range_range1 ADD partition p4 values less than (500) (
subpartition p4_r1 values less than (100),
subpartition p4_r2 values less than (200),
subpartition p5_r3 values less than (300)
);
Query OK, 0 rows affected (0.08 sec)
- Add the partition p3 to a template-based subpartition table named t_range_range. You only need to specify the partition definition. The subpartition definition is filled in automatically based on the template.
obclient>CREATE TABLE t_range_range(c1 INT, c2 INT, PRIMARY KEY(c1,c2))
PARTITION BY RANGE(c1) SUBPARTITION BY RANGE(c2) SUBPARTITION TEMPLATE
(SUBPARTITION p0 VALUES LESS THAN (50),SUBPARTITION p1 VALUES LESS THAN (100))
(PARTITION p0 VALUES LESS THAN (100), PARTITION p1 VALUES LESS THAN (200),
PARTITION p2 VALUES LESS THAN (300));
Query OK, 0 rows affected (0.07 sec)
obclient>ALTER TABLE t_range_range ADD PARTITION p3 VALUES LESS THAN (400);
Query OK, 0 rows affected (0.07 sec)
- Truncate Partitions M202001 and M202002 of Table t_log_part_by_range.
obclient> CREATE TABLE t_log_part_by_range (
log_id number NOT NULL
, log_value varchar2(50)
, log_date date NOT NULL DEFAULT sysdate
) PARTITION BY RANGE(log_date)
(
PARTITION M202001 VALUES LESS THAN(TO_DATE('2020/02/01','YYYY/MM/DD'))
, PARTITION M202002 VALUES LESS THAN(TO_DATE('2020/03/01','YYYY/MM/DD'))
, PARTITION M202003 VALUES LESS THAN(TO_DATE('2020/04/01','YYYY/MM/DD'))
, PARTITION M202004 VALUES LESS THAN(TO_DATE('2020/05/01','YYYY/MM/DD'))
, PARTITION M202005 VALUES LESS THAN(TO_DATE('2020/06/01','YYYY/MM/DD'))
, PARTITION MMAX VALUES LESS THAN (MAXVALUE)
);
Query OK, 0 rows affected (0.08 sec)
obclient> ALTER TABLE t_log_part_by_range TRUNCATE PARTITION M202001, M202002 UPDATE GLOBAL INDEXES;
Query OK, 0 rows affected (0.05 sec)