如何用ALTERTABLE…TRUNCATEPARTITION從分區刪資料 - PolarDB

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

文法介紹

ALTER TABLE table_name TRUNCATE PARTITION partition_name
  [{DROP|REUSE} STORAGE]

參數說明

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

注意事項

執行此命令的使用者需是該表的所有者或者是高許可權使用者。
該操作會擷取AccessExclusiveLock（表級獨佔鎖定），阻塞該表的所有 DML 和大部分 DDL 操作。建議在業務低峰期執行，並預留足夠的時間視窗。
TRUNCATE PARTITION是中繼資料操作+檔案刪除，即使億級資料也可秒級完成，I/O 和CPU/記憶體開銷極低。
如果表上定義了ON TRUNCATE 觸發器，該操作會觸發它們。請確保瞭解這些觸發器可能執行的商務邏輯，避免意外的連鎖反應。

使用樣本

本樣本示範如何清空一個銷售記錄表中的歷史季度分區。此操作常用於資料生命週期管理，即刪除舊資料但保留分區結構，以便將來載入同一時期的新資料。

環境準備

本步驟將建立一個按銷售日期進行定界分割的表 sales_records，並建立兩個分區p_2023_q1，p_2023_q2分別用於儲存一季度和二季度的資料。

-- 建立分區表
CREATE TABLE sales_records (
    sale_id    INT NOT NULL,
    product_id INT NOT NULL,
    sale_date  DATE NOT NULL
)
PARTITION BY RANGE (sale_date) (
    PARTITION p_2023_q1 VALUES LESS THAN (TO_DATE('2023-04-01', 'YYYY-MM-DD')),
    PARTITION p_2023_q2 VALUES LESS THAN (TO_DATE('2023-07-01', 'YYYY-MM-DD'))
);

-- 插入資料
INSERT INTO sales_records VALUES (1, 101, TO_DATE('2023-01-15', 'YYYY-MM-DD'));
INSERT INTO sales_records VALUES (2, 102, TO_DATE('2023-04-20', 'YYYY-MM-DD'));
COMMIT;

前置檢查

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

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

-- 檢查分區結構，確認待清空的分區 p_2023_q1 存在
SELECT PARTITION_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = 'SALES_RECORDS';

-- 檢查分區資料，確認分區記憶體在資料
SELECT COUNT(*) FROM sales_records PARTITION (p_2023_q1);

執行操作

執行 TRUNCATE PARTITION 命令清空 p_2023_q1 分區。

ALTER TABLE sales_records TRUNCATE PARTITION p_2023_q1;

結果驗證

驗證分區結構被保留、資料被清空。

-- 結構驗證：確認分區定義仍然存在
SELECT PARTITION_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = 'SALES_RECORDS';
-- 查詢結果中仍包含 p_2023_q1

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

清空操作會使表的統計資訊過時，影響查詢最佳化工具的判斷。操作完成後應立即執行 ANALYZE。

ANALYZE sales; -- 更新父表的統計資訊

常見問題

Q1：TRUNCATE PARTITION 和 DROP PARTITION 有什麼區別？
TRUNCATE PARTITION 僅清空分區內的資料但保留分區定義，而 DROP PARTITION 會將分區定義和資料一併刪除。

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

Q3：執行此命令為什麼提示 RA-02149: specified partition does not exist？
分區名不存在或大小寫不匹配。分區名大小寫敏感，請先通過查詢確認分區名稱，或使用雙引號精確引用。

Q4：執行此命令為什麼提示 ORA-01031: insufficient privileges？
這是因為執行該命令的使用者不具備目標表的 ALTER 許可權，需要聯絡資料庫管理員授予相應許可權。

Q5：執行此命令為什麼提示ERROR: cannot truncate partition of table because it has global indexes？

有全域索引時，執行 TRUNCATE PARTITION 會失敗。全域索引跨所有分區維護唯一性和查詢路徑，TRUNCATE 無法在不破壞索引一致性的前提下快速清理索引條目。

PolarDB：ALTER TABLE…TRUNCATE PARTITION