使用ALTERTABLE…TRUNCATESUBPARTITION從指定的子分區中刪除所有資料 - PolarDB

ALTER TABLE ... TRUNCATE SUBPARTITION 用於清空複合分區表中指定子分區內的全部資料，此操作會永久移除資料但保留子分區結構，執行前需確認資料不再需要或已備份。

文法介紹

ALTER TABLE table_name 
  TRUNCATE SUBPARTITION subpartition_name 
  [{DROP|REUSE} STORAGE]

參數說明

參數名稱	參數要求	參數說明	參數樣本
`table_name`	必填	目標複合分區表的名稱。	`customer_data`
`subpartition_name`	必填	要清空的子分區的名稱。	`p_2022_asia`
`{DROP\|REUSE} STORAGE`	選填	子句僅為相容 Oracle 文法而存在，會被解析但實際忽略，不影響儲存行為。	`DROP STORAGE`/ `REUSE STORAGE`

注意事項

執行此命令的使用者需是該表的所有者，或擁有對該表的 ALTER 許可權。
該操作會擷取AccessExclusiveLock（表級獨佔鎖定），阻塞該表的所有 DML 和大部分 DDL 操作。建議在業務低峰期執行，並預留足夠的時間視窗。
TRUNCATE SUBPARTITION是一項DDL操作，會隱式提交當前事務，一旦執行，被刪除的資料無法通過 ROLLBACK 復原。請在操作前完成資料備份。
TRUNCATE SUBPARTITION是中繼資料操作+檔案刪除，即使億級資料也可秒級完成，I/O 和CPU/記憶體開銷極低。

使用樣本

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

環境準備

本步驟將建立一個複合分區表 customer_data，該表按註冊年份進行定界分割（p_2022），再按使用者所在地區進行列表子分區（p_2022_asia 和 p_2022_europe），並向這兩個子分區插入測試資料。

-- 建立複合分區表
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')
    )
);

-- 插入資料
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'));

前置檢查

在執行清空操作前，確認資料已備份，並檢查目標子分區的結構和資料。

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

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

-- 檢查子分區資料，確認分區記憶體在資料
SELECT COUNT(*) FROM customer_data SUBPARTITION (p_2022_asia);

執行操作

執行 TRUNCATE SUBPARTITION 命令清空 p_2022_asia 子分區。

ALTER TABLE customer_data TRUNCATE SUBPARTITION p_2022_asia;

結果驗證

驗證子分區結構被保留，且資料已被清空。

-- 結構驗證：確認子分區定義仍然存在
SELECT SUBPARTITION_NAME FROM USER_TAB_SUBPARTITIONS 
WHERE TABLE_NAME = 'CUSTOMER_DATA';
-- 查詢結果中應包含 p_2022_asia

-- 資料驗證：確認子分區內的資料已被清空
SELECT COUNT(*) FROM customer_data SUBPARTITION (p_2022_asia);
-- 查詢結果為 0

常見問題

Q1：TRUNCATE SUBPARTITION 和 DROP SUBPARTITION 有什麼區別？

TRUNCATE SUBPARTITION僅清空子分區內的資料但保留其定義，而DROP SUBPARTITION 會將子分區定義和資料一併刪除。

Q2：執行 TRUNCATE SUBPARTITION 後，資料可以恢複嗎？

不可以，因為 TRUNCATE 是一項DDL操作，它會永久刪除資料且預設不記錄在交易記錄中，無法通過常規手段復原或閃回。

Q3：TRUNCATE SUBPARTITION 和 DELETE FROM ... SUBPARTITION 有什麼區別？

TRUNCATE SUBPARTITION 是高效的DDL操作，通過釋放資料區塊直接刪除資料且無法復原，而DELETE是逐行刪除的DML操作，會記錄日誌、可復原並觸發刪除觸發器。

Q4：執行此命令為什麼提示 ORA-02149：relation ... does not exist？

子分區名不存在或大小寫不匹配。名稱大小寫敏感，請先前置檢查確認名稱。

Q5：執行此命令為什麼提示 ORA-01031: insufficient privileges？

這是因為執行該命令的使用者不具備目標表的 ALTER 許可權，需要聯絡資料庫管理員授予相應許可權。

PolarDB：ALTER TABLE…TRUNCATE SUBPARTITION