分區核心優勢
- 分區裁剪：查詢僅掃描相關分區，大幅降低 I/O
- 分區級維護：單獨備份/恢復/重建索引/移動表空間
- 並行度提升：跨分區並行查詢/DML
- 數據生命週期：歷史分區歸檔/刪除極快
四大基礎分區策略
1. 範圍分區 — 按值區間
-- 按主鍵哈希，適合無明顯範圍特徵的大表
CREATE TABLE large_table (
id NUMBER,
data VARCHAR2(100)
)
PARTITION BY HASH (id) (
PARTITION p1, PARTITION p2, PARTITION p3, PARTITION p4
);
3. 列表分區 — 離散值映射
-- 範圍-哈希：按日期範圍，每個範圍內哈希分佈
CREATE TABLE composite_sales (
sale_date DATE,
customer_id NUMBER,
amount NUMBER
)
PARTITION BY RANGE (sale_date)
SUBPARTITION BY HASH (customer_id)
SUBPARTITIONS 4 (
PARTITION sales_2023 VALUES LESS THAN (TO_DATE('2024-01-01','YYYY-MM-DD')),
PARTITION sales_2024 VALUES LESS THAN (MAXVALUE)
);
-- 範圍-列表：按日期範圍，每個範圍內按地區列表
CREATE TABLE composite_sales2 (
sale_date DATE,
region_code VARCHAR2(10),
amount NUMBER
)
PARTITION BY RANGE (sale_date)
SUBPARTITION BY LIST (region_code)
SUBPARTITION TEMPLATE (
SUBPARTITION sp_north VALUES ('BJ','TJ'),
SUBPARTITION sp_south VALUES ('GD','GX'),
SUBPARTITION sp_other VALUES (DEFAULT)
) (
PARTITION p_2023 VALUES LESS THAN (TO_DATE('2024-01-01','YYYY-MM-DD')),
PARTITION p_2024 VALUES LESS THAN (MAXVALUE)
);
分區查詢與驗證
| 操作 | 語法示例 | 說明 |
|---|
| 新增分區 | ALTER TABLE sales ADD PARTITION sales_2024_q1 VALUES LESS THAN (DATE '2024-04-01') | 範圍分區只能加在末尾（MAXVALUE 前） |
| 刪除分區 | ALTER TABLE sales DROP PARTITION sales_2023_q1 | 數據連同分區刪除，全局索引需 UPDATE GLOBAL INDEXES |
| 截斷分區 | ALTER TABLE sales TRUNCATE PARTITION sales_2023_q1 | 保留分區結構，清空數據，極快 |
| 分裂分區 | ALTER TABLE sales SPLIT PARTITION sales_2023_q4 AT (DATE '2023-11-01') INTO (PARTITION sales_2023_q4_a, PARTITION sales_2023_q4_b) | 一分為二，適用於熱分區 |
| 合併分區 | ALTER TABLE sales MERGE PARTITIONS sales_2023_q1, sales_2023_q2 INTO PARTITION sales_2023_h1 | 合併相鄰分區 |
| 交換分區 | ALTER TABLE sales EXCHANGE PARTITION sales_2023_q1 WITH TABLE staging_sales | 秒級載入/歸檔，無數據移動 |
| 移動分區 | ALTER TABLE sales MOVE PARTITION sales_2023_q1 TABLESPACE ts_archive | 遷移表空間，可壓縮 |
| 重命名 | ALTER TABLE sales RENAME PARTITION sales_2023_q1 TO sales_2023_01 | 元數據操作 |
全局索引維護關鍵
| 索引類型 | 定義 | 優點 | 缺點 |
|---|
| 本地索引 | CREATE INDEX idx_local ON sales(sale_date) LOCAL | 分區對齊，維護簡單，支援分區裁剪 | 唯一性約束需包含分區鍵 |
| 全局索引 | CREATE INDEX idx_global ON sales(customer_id) | 可強制唯一，跨分區查詢快 | 分區維護需手動 UPDATE GLOBAL INDEXES |
性能優化最佳實踐
- 分區鍵選擇：優先選 WHERE 條件高頻列、時間列、基數適中列
- 分區數量：單表 100-1000 個分區較佳，過多增加優化器負擔
- 統計信息：
DBMS_STATS.GATHER_TABLE_STATS(..., GRANULARITY=>'PARTITION')
- 分區裁剪驗證：每次釋放新分區查詢必看執行計劃
- 參考分區：子表按父表分區鍵分區（11g+），自動維護關聯
- 間隔分區：
PARTITION BY RANGE (dt) INTERVAL (NUMTODSINTERVAL(1,'DAY')) 自動創建新分區
避坑指南
- ❌ 分區鍵列上加函數（
TRUNC(sale_date)）會阻礙裁剪
- ❌ 全局索引未指定 UPDATE GLOBAL INDEXES 導致大量 UNUSABLE 索引
- ❌ MAXVALUE 分區長期不維護成為「垃圾桶」
- ❌ 子分區模板未定義導致新增分區無子分區
- ✅ 定期
ALTER INDEX ... REBUILD PARTITION 消除碎片
- ✅ 歸檔舊分區用
EXCHANGE PARTITION + 外部表/備份表
