如何使用ALTERTABLE…DROPPARTITION刪除分區和資料 - PolarDB

ALTER TABLE ... DROP PARTITION 用於從一個分區表中刪除指定的分區及其包含的全部資料。此操作會永久移除資料且不可恢複，執行前需確認目標資料分割已備份或不再需要。

文法介紹

ALTER TABLE [schema.]table_name DROP PARTITION partition_name;

參數說明

參數名稱	參數要求	參數說明	參數樣本
`table_name`	必填	要刪除分區的目標資料分割表的名稱。	`sales_records`
`partition_name`	必填	要刪除的分區的名稱。	`p_2023_q1`

注意事項

執行此命令的使用者需是該表的所有者或者是高許可權使用者。
不能刪除最後一個分區，目標資料分割表至少要保留一個分區。
DROP PARTITION 會擷取 AccessExclusiveLock（表級獨佔鎖定），阻塞該表的所有 DML 和大部分 DDL 操作。建議在業務低峰期執行，並預留足夠的時間視窗。

使用樣本

環境準備

-- 建立一個按銷售日期範圍分區的表
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'))
);

-- 建立一個全域唯一索引
CREATE UNIQUE INDEX idx_sale_id_global ON sales_records(sale_id) GLOBAL;

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

前置檢查

刪除任何分區之前，建議完成以下檢查和準備工作，以防止資料誤刪和業務意外中斷。

確認分區存在。

-- 檢查分區資訊
SELECT PARTITION_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = 'SALES_RECORDS';
-- 確認 p_2023_q1 在列表中

（可選）建立資料備份。
為防止誤操作，建議在執行刪除前對錶或整個資料庫進行備份，詳情請參考備份操作說明。
評估業務影響。
檢查當前是否有正在訪問該表的長事務或關鍵業務。刪除操作會請求表級獨佔鎖定，阻塞所有讀寫請求。
```
-- 查看當前是否有活躍的鎖
SELECT locktype, relation::regclass, mode, granted, pid
FROM pg_locks
WHERE relation = 'SALES_RECORDS'::regclass;
```

執行操作

完成所有檢查和準備後，在業務低峰期執行 ALTER TABLE ... DROP PARTITION 命令，刪除指定分區。

ALTER TABLE sales_records DROP PARTITION P_2023_Q1;

結果驗證

操作完成後，需驗證分區和資料是否已按預期刪除。

檢查分區是否已被移除

查詢中繼資料視圖，確認分區列表中已不存在被刪除的分區。

SELECT partition_name
FROM ALL_TAB_PARTITIONS
WHERE table_name = 'SALES_RECORDS' and partition_name = 'P_2023_Q1';
-- 預期結果：返回為空白。

確認資料已被刪除

嘗試查詢原分區對應的資料，確認查詢結果為空白。

SELECT * 
FROM sales_records 
WHERE sale_date < '2023-04-01';
-- 預期結果：返回為空白。

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

常見問題

Q1：執行刪除分區後，報partition "..." of relation "..." does not exist異常。

分區名不存在或大小寫不匹配。分區名大小寫敏感，請先查詢 USER_TAB_PARTITIONS確認分區名稱，或使用雙引號精確引用。

Q2：執行刪除分區後，報permission denied for table ... 或 must be owner of table ...異常。

執行操作的使用者權限不足，需切換到表所有者或高許可權使用者執行。

Q3：執行刪除分區後，報cannot drop partition due to dependent objects異常。

存在其他資料庫物件（如外鍵約束、視圖）依賴於此分區。預設的 RESTRICT 行為會阻止刪除。可以手動識別並刪除這些依賴對象。

Q4：操作長時間未完成或卡住。

執行 DROP PARTITION 時，資料庫會請求 AccessExclusiveLock 鎖。若該鎖被其他活躍事務阻塞，操作將進入等待狀態，可能導致逾時或執行延遲。可通過查詢鎖資訊擷取會話（blocking_pid），在通過以下語句查詢具體會話資訊。

-- 根據pid查詢阻塞線程的使用者資訊
SELECT 
    pid,
    usename AS username,           -- 資料庫使用者名稱
    application_name,              -- 應用程式名稱（如 JDBC, psql）
    client_addr,                   -- 用戶端 IP 位址
    client_hostname,               -- 用戶端主機名稱
    client_port,                   -- 用戶端連接埠
    backend_start,                 -- 串連開始時間
    xact_start,                    -- 事務開始時間
    query_start,                   -- 當前查詢開始時間
    state_change,                  -- 狀態變更時間
    state,                         -- 狀態（active, idle, idle in transaction）
    wait_event_type,               -- 等待類型（如 Lock, IO）
    wait_event,                    -- 具體等待事件
    query                          -- 當前正在執行或最近執行的 SQL
FROM 
    pg_stat_activity
WHERE 
    pid in 'blocking_pid';

PolarDB：ALTER TABLE…DROP PARTITION