您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 資料庫相關知識

Oracle 分區表維護管理大全：SPLIT/MERGE/EXCHANGE/MOVE/DROP 實戰與索引同步

編輯：Oracle 資料庫相關知識

分區表是大數據量業務「分而治之」的核心武器。本文系統梳理列表/範圍/哈希/複合分區的拆分、合併、交換、移動、刪除、截斷、添加全生命周期操作，重點講解全局/本地索引同步維護與統計信息傳承，附生產級避坑清單。

一、分區表 DDL 核心操作速查表

操作	語法關鍵字	適用分區類型	索引影響	典型場景
拆分	`SPLIT PARTITION ... INTO (..., ...)`	Range/List	新分區索引 UNUSABLE，需 `UPDATE INDEXES`	歷史分區過大、需按月/季拆分
合併	`MERGE PARTITIONS p1, p2 INTO PARTITION p_new`	Range/List	同拆分	歸檔舊分區、減少分區數
交換	`EXCHANGE PARTITION p WITH TABLE t [INCLUDING INDEXES] [WITHOUT VALIDATION]`	所有	全局索引 UNUSABLE（除非 `UPDATE GLOBAL INDEXES`）	數據歸檔/載入/清洗，秒級完成
移動	`MOVE PARTITION p [TABLESPACE ts] [UPDATE INDEXES]`	所有	ROWID 變更導致所有索引 UNUSABLE	分區遷移表空間、壓縮、重組
截斷	`TRUNCATE PARTITION p [UPDATE INDEXES]`	所有	全局索引對應分區 UNUSABLE	定期清理滾動窗口數據
刪除	`DROP PARTITION p [UPDATE INDEXES]`	Range/List	同截斷	永久移除歷史分區
添加	`ADD PARTITION p VALUES LESS THAN (val)`	Range	新分區索引自動創建（本地），全局需手動維護	時間窗口滾動新增

二、實戰案例：列表分區表 `T3` 全生命周期演示

1. 創建與數據載入

CREATE TABLE t3 (id NUMBER, city VARCHAR2(10))
PARTITION BY LIST (city) (
  PARTITION p1 VALUES ('SH','JS','ZJ'),
  PARTITION p2 VALUES ('BJ','TJ','HB'),
  PARTITION p3 VALUES ('GZ','SZ'),
  PARTITION p_others VALUES (DEFAULT)
);

-- 載入 10,000 行測試數據（省略 PL/SQL 循環）

2. SPLIT：將 P1 拆出 JS

ALTER TABLE t3 SPLIT PARTITION p1 VALUES ('JS')
INTO (PARTITION p1_1, PARTITION p1_2)
UPDATE INDEXES;  -- 同步維護全局索引

3. MERGE：合併回 P1

ALTER TABLE t3 MERGE PARTITIONS p1_1, p1_2 INTO PARTITION p1
UPDATE INDEXES;

4. ADD VALUES：向 P3 增加新城市 ZQ

ALTER TABLE t3 MODIFY PARTITION p3 ADD VALUES ('ZQ');

三、分區索引維護：本地 vs 全局

1. 本地索引（Local）

分區與表分區一一對應，DDL 自動同步，無 UNUSABLE 風險。
重建單分區：ALTER INDEX idx_local REBUILD PARTITION p1;
子分區重建：ALTER INDEX idx_local REBUILD SUBPARTITION p1_sub1;

2. 全局索引（Global）

跨分區，DDL 導致受影響分區 UNUSABLE。
必加 UPDATE INDEXES / UPDATE GLOBAL INDEXES（12c+ 同義）同步維護，代價：DDL 耗時增加、產生大量 Redo/Undo。
範圍分區全局索引拆分：
ALTER INDEX idx_global SPLIT PARTITION p2 AT (2000) INTO (PARTITION p3, PARTITION p_max);
哈希分區全局索引：CREATE INDEX ... GLOBAL PARTITION BY HASH(col) PARTITIONS 4;

3. 一鍵修復所有 UNUSABLE 索引

SELECT 'ALTER INDEX ' || index_name || ' REBUILD;' FROM user_indexes WHERE status = 'UNUSABLE'
UNION ALL
SELECT 'ALTER INDEX ' || index_name || ' REBUILD PARTITION ' || partition_name || ';' FROM user_ind_partitions WHERE status = 'UNUSABLE'
UNION ALL
SELECT 'ALTER INDEX ' || index_name || ' REBUILD SUBPARTITION ' || subpartition_name || ';' FROM user_ind_subpartitions WHERE status = 'UNUSABLE';

四、分區交換（EXCHANGE）深度解析

「元數據級移動」，不搬運數據，**秒級完成**，適用：

數據載入：外部表/平臺表載入後 EXCHANGE 進主表分區。
歸檔：EXCHANGE 出歷史分區到獨立表 → EXPDP → DROP。
清洗：EXCHANGE 出問題分區 → 清洗 → EXCHANGE 回去。

關鍵子句

INCLUDING INDEXES：同步交換本地索引（目標表需有同結構索引）。
WITHOUT VALIDATION：跳過數據滿足分區鍵檢查，**極快但危險**，數據不符會導致查詢結果錯誤。**生產嚴禁**，除非 100% 確定數據乾淨。
UPDATE GLOBAL INDEXES (12c+)：同步維護全局索引，避免 UNUSABLE。

五、實戰案例：滾動窗口歸檔（保留最近 2 年）

-- 1. 拆分出新年分區
ALTER TABLE ware SPLIT PARTITION p_max AT ('2008')
INTO (PARTITION p_2007, PARTITION p_max) UPDATE INDEXES;

-- 2. 交換出 2007 年歸檔
ALTER TABLE ware EXCHANGE PARTITION p_2007
WITH TABLE ware_2007 INCLUDING INDEXES WITHOUT VALIDATION;

-- 3. 交換出最舊 2005 年並導出
ALTER TABLE ware EXCHANGE PARTITION p_2005
WITH TABLE ware_2005 INCLUDING INDEXES WITHOUT VALIDATION;
ALTER TABLE ware DROP PARTITION p_2005 UPDATE INDEXES;
-- EXPDP ware_2005 → 刪除表

六、統計信息傳承：解決新分區「無統計/Out-of-Range」

-- 新增分區後，從相似舊分區複製統計信息
EXEC DBMS_STATS.COPY_TABLE_STATS(
  ownname => USER,
  tabname => 'TEST_TAB1',
  srcpartname => 'P_202312',   -- 來源分區
  dstpartname => 'P_202401'    -- 目標新分區
);

避免 CBO 因無統計信息產生劣質計劃（如全表掃描代替分區剪枝）。
若表級統計為 GLOBAL_STATS，新分區會導致 OUT_OF_RANGE，複製統計可解。

七、常用分區元數據查詢視圖

DBA_TAB_PARTITIONS / DBA_TAB_SUBPARTITIONS
DBA_IND_PARTITIONS / DBA_IND_SUBPARTITIONS
DBA_PART_KEY_COLUMNS：分區鍵列
DBA_SEGMENTS：段空間佔用

八、生產級避坑清單

DDL 鎖等待：分區操作需獨佔 DDL 鎖，避開業務高峰，或使用 DBMS_REDEFINITION 在線重定義。
全局索引 UNUSABLE：強制加 UPDATE INDEXES，或 12c+ 設置 ALTER SESSION SET DDL_LOCK_TIMEOUT=300; 等待鎖釋放。
WITHOUT VALIDATION **嚴禁常態化**：只允許在「數據已經過 ETL 驗證、校驗和清洗」的受控流程中使用。
MAXVALUE 分區拆分：SPLIT PARTITION p_max AT (val) 必須指定具體值，不可 MAXVALUE。
參考分區：子表分區隨父表 DDL 自動同步，操作父表即可，禁止直接操作子表分區。