文法介紹
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 子分區鏈的末尾追加，無法在中間插入。