ALTER TABLE…DROP PARTITION
语法格式
ALTER TABLE [vc_name.][database_names.]tbl_name
alter_specification [, alter_specification] ...
alter_specification:
DROP PARTITION partition_names
说明
分区表的分区条件为RANGE 或LIST;
不能删除全部分区,确保至少存在1 个分区;
不能删除子分区。
示例
gbase> show create table pt\G
*************************** 1. row ***************************
Table: pt
Create Table: CREATE TABLE "pt" (
"i" int(11) DEFAULT NULL,
GBase 8a MPP Cluster 产品手册
5 数据库管理指南
文档版本953(2022-04-10)
南大通用数据技术股份有限公司
983
"c" varchar(10) DEFAULT NULL,
"d" date DEFAULT NULL
) ENGINE=EXPRESS DEFAULT CHARSET=utf8
TABLESPACE='sys_tablespace'
PARTITION BY RANGE (i)
(PARTITION p0 VALUES LESS THAN (10) TABLESPACE = 'sys_tablespace'
ENGINE = EXPRESS,
PARTITION p1 VALUES LESS THAN (20) TABLESPACE = 'sys_tablespace'
ENGINE = EXPRESS,
PARTITION p2 VALUES LESS THAN (30) TABLESPACE = 'sys_tablespace'
ENGINE = EXPRESS,
PARTITION p3 VALUES LESS THAN MAXVALUE TABLESPACE =
'sys_tablespace' ENGINE = EXPRESS)
1 row in set (Elapsed: 00:00:00.00)
gbase> alter table pt drop partition p3;
Query OK, 0 rows affected (Elapsed: 00:00:00.11)
Records: 0
Duplicates: 0
Warnings: 0
gbase> alter table pt drop partition p1;
Query OK, 0 rows affected (Elapsed: 00:00:00.12)
Records: 0
Duplicates: 0
Warnings: 0
gbase> show create table pt\G
*************************** 1. row ***************************
Table: pt
Create Table: CREATE TABLE "pt" (
"i" int(11) DEFAULT NULL,
"c" varchar(10) DEFAULT NULL,
"d" date DEFAULT NULL
) ENGINE=EXPRESS DEFAULT CHARSET=utf8
TABLESPACE='sys_tablespace'
PARTITION BY RANGE (i)
(PARTITION p0 VALUES LESS THAN (10) TABLESPACE = 'sys_tablespace'
ENGINE = EXPRESS,
GBase 8a MPP Cluster 产品手册
5 数据库管理指南
文档版本953(2022-04-10)
南大通用数据技术股份有限公司
984
PARTITION p2 VALUES LESS THAN (30) TABLESPACE = 'sys_tablespace'
ENGINE = EXPRESS)
1 row in set (Elapsed: 00:00:00.01)
注意
删除分区表分区时,若只有1 个分区时,报错;
删除分区表分区时,若同时删除所有分区,报错;
删除分区表分区时,若是非RANGE 或LIST 分区,报错。