使用ALTERTABLE…DROPSUBPARTITION刪除子分區及儲存在子分區內的資料 - PolarDB

ALTER TABLE ... DROP SUBPARTITION 用於從複合分區表中刪除指定的子分區及其資料，此操作會永久移除資料，執行前需確認目標資料分割已備份或不再需要。

文法介紹

ALTER TABLE table_name DROP SUBPARTITION subpartition_name;

參數說明

參數名稱	參數要求	參數說明	參數樣本
`table_name`	必填	包含要刪除子分區的複合分區表的名稱。	`customer_data`
`subpartition_name`	必填	要刪除的子分區的名稱。	`p_2022_asia`

注意事項

執行此命令的使用者必須是該表的所有者或者是高許可權使用者。
不支援刪除表中的最後一個子分區。
DROP SUBPARTITION會擷取AccessExclusiveLock（表級獨佔鎖定），阻塞該表的所有 DML 和大部分 DDL 操作。建議在業務低峰期執行，並預留足夠的時間視窗。

使用樣本

本樣本示範如何在一個按註冊年份（RANGE）和使用者所在地區（LIST）進行複合分區的客戶表中，刪除2022年度亞洲地區的使用者資料子分區，以釋放儲存空間。

環境準備

本步驟將建立一個複合分區表 customer_data，該表按註冊年份進行定界分割，再按使用者所在地區進行列表子分區。

-- 建立複合分區表
CREATE TABLE customer_data (
    customer_id  INT NOT NULL,
    region       VARCHAR2(10),
    reg_date     DATE
)
PARTITION BY RANGE (reg_date)
SUBPARTITION BY LIST (region)
(
    PARTITION p_2022 VALUES LESS THAN (TO_DATE('2023-01-01', 'YYYY-MM-DD'))
    (
        SUBPARTITION p_2022_asia    VALUES ('Asia'),
        SUBPARTITION p_2022_europe  VALUES ('Europe')
    ),
    PARTITION p_2023 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD'))
    (
        SUBPARTITION p_2023_asia    VALUES ('Asia'),
        SUBPARTITION p_2023_europe  VALUES ('Europe')
    )
);

-- 插入資料
INSERT INTO customer_data VALUES (101, 'Asia', TO_DATE('2022-06-10', 'YYYY-MM-DD'));
INSERT INTO customer_data VALUES (102, 'Europe', TO_DATE('2022-07-20', 'YYYY-MM-DD'));
INSERT INTO customer_data VALUES (103, 'Asia', TO_DATE('2023-06-10', 'YYYY-MM-DD'));
INSERT INTO customer_data VALUES (104, 'Europe', TO_DATE('2023-07-20', 'YYYY-MM-DD'));

前置檢查

在執行刪除操作前，確認資料已備份，並檢查待刪除的子分區結構。

-- 重要：執行刪除操作前，請確保已對相關資料進行備份。

-- 檢查子分區結構，確認待刪除的子分區 p_2022_asia 存在
SELECT SUBPARTITION_NAME 
FROM USER_TAB_SUBPARTITIONS 
WHERE TABLE_NAME = 'CUSTOMER_DATA';

執行操作

執行 DROP SUBPARTITION 命令刪除 p_2022_asia 子分區。

ALTER TABLE customer_data DROP SUBPARTITION p_2022_asia;

結果驗證

驗證子分區和其對應的資料均已被刪除。

-- 結構驗證：確認子分區已被刪除
SELECT SUBPARTITION_NAME FROM USER_TAB_SUBPARTITIONS 
WHERE TABLE_NAME = 'CUSTOMER_DATA';
-- 查詢結果中已不包含 p_2022_asia

-- 資料驗證：確認子分區內的資料已被刪除
SELECT COUNT(*) FROM customer_data WHERE customer_id = 101;
-- 查詢結果為 0

更新表統計資訊
刪除分區後，建議立即更新表的統計資訊，以確保查詢最佳化工具能夠產生準確的執行計畫。
```
ANALYZE CUSTOMER_DATA;
```

常見錯誤

Q1：為何出現ORA-00942: table or view does not exist 錯誤？

此錯誤通常由兩個原因導致：1. 目前使用者缺少對目標表的 ALTER 許可權。2. 表名或 Schema名稱拼字錯誤。請檢查許可權配置和對象名稱的準確性。

Q2：為何出現ORA-14006: invalid partition name 錯誤？
這表明指定的子分區名稱不存在或拼字錯誤。可查詢 USER_TAB_SUBPARTITIONS視圖，以核實目標子分區的正確名稱。注意，若名稱未用雙引號括起，資料庫通常會將其轉換為大寫字母進行匹配。

Q3：為何出現ORA-01031: insufficient privileges錯誤？
這是因為執行該命令的使用者不具備目標表的 ALTER 許可權，需要聯絡資料庫管理員授予相應許可權。

Q4：執行DROP SUBPARTITION後，資料可以恢複嗎？
不可以，因為 DROP SUBPARTITION 是一項物理刪除操作且不會進入資源回收筒，恢複資料的唯一方法是使用執行此操作前的Database Backup進行還原。

PolarDB：ALTER TABLE…DROP SUBPARTITION