语法介绍
ALTER TABLE table_name
MODIFY PARTITION partition_name
ADD SUBPARTITION { list_subpartition | range_subpartition };
-- LIST 子分区定义
SUBPARTITION subpartition_name
VALUES (value[, value]...)
[TABLESPACE tablespace_name]
-- RANGE 子分区定义
SUBPARTITION subpartition_name
VALUES LESS THAN (value[, value]...)
[TABLESPACE tablespace_name]
参数说明
参数名称 | 参数要求 | 参数说明 | 参数示例 |
table_name
| 必填 | 目标复合分区表的名称。 | sales_data
|
partition_name
| 必填 | 要添加子分区的目标一级分区的名称。 | p_2023
|
subpartition_name
| 必填 | 新创建的子分区的名称。 | sp_q1_asia
|
VALUES
| 必填 | 定义新子分区的边界值。
- 对于 LIST 子分区，value 是一个或多个具体的列表值。
- 对于 RANGE 子分区，使用 LESS THAN 子句定义上边界。 | VALUES('Asia') 或
VALUES LESS THAN (TO_DATE(...))
|
TABLESPACE tablespace_name
| 可选
默认值：表的默认表空间 | 指定存储新子分区的表空间。若省略，则使用该表的默认表空间。 | TABLESPACE users_tbs
|
注意事项
新添加的子分区类型（LIST或 RANGE）需与该父分区下已有的子分区类型完全一致。
子分区名称subpartition_name在表的所有分区和子分区中必须是唯一的。
添加RANGE子分区时，其VALUES LESS THAN (...)定义的边界值必须大于所有现有子分区的最大边界值。即RANGE子分区仅支持在分区范围内按升序追加至末尾。如需在中间插入，可使用 ALTER TABLE ... SPLIT SUBPARTITION语句对现有子分区进行划分。
添加LIST子分区时，其VALUES (...)列表中的值不能与该父分区下任何已有子分区的值重复。
ADD SUBPARTITION会获取AccessExclusiveLock（表级排他锁），阻塞该表的所有 DML和大部分DDL操作。务必在业务低峰期执行，并预留足够的时间窗口。
添加子分区时，子分区数量无语法限制，但受系统资源约束。建议单表总分区数不超过1000，以保障性能与可管理性。
不能使用ADD SUBPARTITION语句把分区添加到带有MAXVALUE或 DEFAULT规则的表中。
如果对表进行了索引设置，那么索引将创建在新的子分区上。
执行此命令的用户需是表的拥有者，或具备相应的管理员权限。
新创建的子分区初始统计信息为空。为确保查询优化器生成正确的执行计划，建议在操作完成后立即收集表的统计信息。
使用示例
为RANGE-LIST复合分区表添加LIST子分区
本示例演示如何在一个按销售年份（RANGE）和销售地区（LIST）进行复合分区的表中，为2023年度分区新增一个“非洲”地区子分区，以支持新业务的开展。
环境准备
本步骤将创建一个 RANGE-LIST 复合分区表 sales_data，该表按销售年份进行范围分区，再按销售地区进行列表子分区。
-- 创建 RANGE-LIST 复合分区表
CREATE TABLE sales_data (
sale_id INT,
region VARCHAR2(20),
sale_date DATE
)
PARTITION BY RANGE (sale_date)
SUBPARTITION BY LIST (region)
(
PARTITION p_2023 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD'))
(
SUBPARTITION sp_2023_asia VALUES ('Asia'),
SUBPARTITION sp_2023_europe VALUES ('Europe')
)
);
前置检查
在执行添加操作前，检查目标一级分区的存在性及其当前的子分区列表。
-- 检查一级分区 p_2023 存在
SELECT PARTITION_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = 'SALES_DATA';
-- 检查 p_2023 下的现有子分区，确认新值 'Africa' 不存在
SELECT SUBPARTITION_NAME FROM USER_TAB_SUBPARTITIONS
WHERE TABLE_NAME = 'SALES_DATA' AND PARTITION_NAME = 'P_2023';
执行操作
执行 ADD SUBPARTITION 命令，在 p_2023 分区下添加值为 'Africa' 的新子分区。
ALTER TABLE sales_data MODIFY PARTITION p_2023 ADD SUBPARTITION sp_2023_africa VALUES('Africa');
结果验证
验证新子分区已成功创建，并且可以向其中插入符合其边界定义的数据。
-- 结构验证：确认新子分区已添加
SELECT SUBPARTITION_NAME FROM USER_TAB_SUBPARTITIONS
WHERE TABLE_NAME = 'SALES_DATA' AND PARTITION_NAME = 'P_2023';
-- 查询结果中应包含 sp_2023_africa
-- 数据验证：尝试向新子分区插入数据并验证
INSERT INTO sales_data VALUES (101, 'Africa', TO_DATE('2023-09-15', 'YYYY-MM-DD'));
SELECT COUNT(*) FROM sales_data SUBPARTITION (sp_2023_africa);
-- 查询结果应为 1
为RANGE-RANGE复合分区表添加RANGE子分区
本示例演示如何在一个按订单年份（RANGE）和订单日期（RANGE）进行复合分区的表中，为2023年度分区添加第二季度（Q2）的子分区。
环境准备
本步骤将创建一个 RANGE-RANGE 复合分区表 order_history，该表按订单年份进行范围分区，再按订单日期进行范围子分区。
-- 假设已存在名为 archive_tbs 的表空间
-- CREATE TABLESPACE archive_tbs DATAFILE 'archive_tbs.dbf' SIZE 10M;
-- 创建 RANGE-RANGE 复合分区表
CREATE TABLE order_history (
order_id INT,
order_date DATE
)
PARTITION BY RANGE (order_date)
SUBPARTITION BY RANGE (order_date)
(
PARTITION p_2023 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD'))
(
SUBPARTITION sp_2023_q1 VALUES LESS THAN (TO_DATE('2023-04-01', 'YYYY-MM-DD'))
)
);
前置检查
检查目标一级分区的存在性及其当前的子分区边界，确保新边界不冲突。
-- 检查一级分区 p_2023 存在
SELECT PARTITION_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = 'ORDER_HISTORY';
-- 检查 p_2023 下的现有子分区及其边界
SELECT SUBPARTITION_NAME, HIGH_VALUE FROM USER_TAB_SUBPARTITIONS
WHERE TABLE_NAME = 'ORDER_HISTORY' AND PARTITION_NAME = 'P_2023';
执行操作
执行 ADD SUBPARTITION 命令，在 p_2023 分区下添加上边界为 2023-07-01 的新子分区。
ALTER TABLE order_history MODIFY PARTITION p_2023
ADD SUBPARTITION sp_2023_q2 VALUES LESS THAN (TO_DATE('2023-07-01', 'YYYY-MM-DD'));
结果验证
验证新子分区已成功创建，并可以向其中插入数据。
-- 结构验证：确认新子分区已添加及其表空间
SELECT * FROM USER_TAB_SUBPARTITIONS
WHERE TABLE_NAME = 'ORDER_HISTORY' AND SUBPARTITION_NAME = 'SP_2023_Q2';
-- 查询结果应显示 SP_2023_Q2 和 ARCHIVE_TBS
-- 数据验证：尝试向新子分区插入数据并验证
INSERT INTO order_history VALUES (201, TO_DATE('2023-05-20', 'YYYY-MM-DD'));
SELECT COUNT(*) FROM order_history SUBPARTITION (sp_2023_q2);
-- 查询结果应为 1
常见问题
Q1: 执行时报错 ORA-14321: subpartition ... already exists
这是因为您试图添加的子分区的VALUES 定义与该一级分区下的某个现有子分区冲突，需要为新子分区指定一个不重叠的边界值。
Q2: 执行时报错 ORA-02269: partition does not exist
这是因为在MODIFY PARTITION子句中指定的partition_name 不存在，请通过查询USER_TAB_PARTITIONS视图核对正确的一级分区名称。
Q3: 执行此命令为什么提示ORA-14150: subpartitioning is not specified
你尝试对一个未定义子分区策略的分区表执行ADD SUBPARTITION操作，该命令仅适用于定义了子分区策略的复合分区表。
Q4: 执行此命令为什么提示 ORA-01031: insufficient privileges？
这是因为执行该命令的用户不具备目标表的ALTER 权限，需要联系数据库管理员授予相应权限。
Q5：执行此命令为什么提示ORA-14074: partition bound must collate higher than that of the last partition？
ADD SUBPARTITION 命令仅支持在RANGE 子分区链的末尾追加，无法在中间插入。