Oracle 分區表技術完整指南：分區策略、建立語法、維運優勢與匯出匯入實戰
自 Oracle 8 (1997) 引入分區表與分區索引以來，已成為超大型資料庫 (VLDB) 管理的核心機制。分區將邏輯上單一大表在物理上拆分為多個區段，每個分區可獨立管理儲存、索引、備份、載入與查詢。本文系統整理分區方法、建立語法、分區鍵選擇、維運優勢、匯出匯入技巧與最佳實踐。
分區表核心優勢
| 維度 | 優勢說明 |
|---|
| 查詢效能 | 分區修剪 只掃描相關分區，大幅減少 I/O；分區可分散於不同表空間/磁碟，平行 I/O。 |
| 資料載入 | 分區交換 秒級載入 TB 級資料 (元資料操作，無實際資料移動)；平行直徑載入。 |
| 索引維護 | 區域索引 僅重建受影響分區索引，避免全表索引重建鎖表、耗時。 |
| 備份恢復 | 分區級備份 僅備份熱分區；時間點恢復 只恢復特定分區。 |
| 資料生命週期 | 分區截斷/歸檔 秒級刪除舊分區 (TRUNCATE PARTITION)；資訊生命週期管理 (ILM) 自動分級儲存。 |
| 可用性 | 單一分區損壞不影響其他分區查詢；線上移動/分割/合併分區 (12c+ 無停機)。 |
六大分區方法 (12c/19c/21c 擴充)
| 方法 | 適用場景 | 分區鍵特徵 | 範例 |
|---|
| Range | 時間序列、連續數值範圍 | 日期、數值、可排序 | 依交易日期月份分區 |
| Hash | 無明確範圍、需均勻分散 | 任意欄位 (雜湊分散) | 依客戶 ID 雜湊 16 個分區 |
| List | 離散值、業務分群 | 字串、代碼、列舉值 | 依地區代碼 (TW, HK, CN, US) |
| Range-Hash | 先按時間再雜湊 | 複合鍵 | 月份 Range + 客戶 ID Hash |
| Range-List | 先按時間再業務分群 | 複合鍵 | 月份 Range + 地區 List |
| Interval (11g+) | 自動建立 Range 分區 | 日期/數值 | 自動按月建立新分區 |
| Reference (11g+) | 子表跟隨父表分區 | 外鍵關聯 | 訂單明細跟隨訂單主表 Range |
| System (12c+) | 無明確分區鍵、由應用控制 | 無 | 應用明確指定分區插入 |
| Auto (19c+) | 資料庫自動決定分區策略 | 自動分析 | 自動轉換 Range→Interval、建立子分區 |
分區建立語法範例
1. Range 分區 (最經典：時間序列)
CREATE TABLE sales (
sale_id NUMBER,
sale_date DATE,
cust_id NUMBER,
amount NUMBER(10,2)
)
PARTITION BY RANGE (sale_date)
INTERVAL (NUMTOYMINTERVAL(1,'MONTH')) -- 11g+ Interval 自動擴展
(
PARTITION p_2023_01 VALUES LESS THAN (TO_DATE('2023-02-01','YYYY-MM-DD')),
PARTITION p_2023_02 VALUES LESS THAN (TO_DATE('2023-03-01','YYYY-MM-DD')),
PARTITION p_2023_03 VALUES LESS THAN (TO_DATE('2023-04-01','YYYY-MM-DD'))
)
TABLESPACE ts_sales_data; -- 可指定預設表空間，各分區亦可個別指定
2. List 分區 (業務代碼分群)
CREATE TABLE customers (
cust_id NUMBER,
cust_name VARCHAR2(100),
region VARCHAR2(10)
)
PARTITION BY LIST (region)
(
PARTITION p_tw VALUES ('TW'),
PARTITION p_hk VALUES ('HK'),
PARTITION p_cn VALUES ('CN','MO'),
PARTITION p_us VALUES ('US','CA'),
PARTITION p_others VALUES (DEFAULT) -- 捕捉未列舉值
);
3. Hash 分區 (均勻分散平行度)
CREATE TABLE orders (
order_id NUMBER,
cust_id NUMBER,
order_dt DATE,
amount NUMBER
)
PARTITION BY HASH (cust_id)
PARTITIONS 16
STORE IN (ts_ord_01, ts_ord_02, ts_ord_03, ts_ord_04); -- 輪存於 4 個表空間
4. 複合分區 Range-Hash (時間 + 雜湊)
CREATE TABLE call_logs (
call_id NUMBER,
call_dt DATE,
caller_id NUMBER,
duration NUMBER
)
PARTITION BY RANGE (call_dt)
SUBPARTITION BY HASH (caller_id)
SUBPARTITIONS 8
(
PARTITION p_2024_01 VALUES LESS THAN (TO_DATE('2024-02-01','YYYY-MM-DD')),
PARTITION p_2024_02 VALUES LESS THAN (TO_DATE('2024-03-01','YYYY-MM-DD'))
);
分區鍵選擇黃金法則
- 查詢過濾欄位：WHERE 子句最常出現的等值/範圍欄位 (如
sale_date, region)。
- 資料生命週期邊界：備份/歸檔/截斷的自然切點 (如月份、季度)。
- 基數適中：Range/List 分區數建議 10~10,000 個；過多導致字典膨脹、最佳化器解析變慢。
- 均勻分布：Hash/Range 分區大小差異 < 10x，避免熱點分區。
- 避免頻繁更新分區鍵：更新分區鍵會導致列跨分區移動 (行遷移)，產生大量 REDO/UNDO。
分區維運常用指令
| 操作 | 語法範例 | 說明 |
|---|
| 新增分區 | ALTER TABLE sales ADD PARTITION p_2024_01 VALUES LESS THAN (DATE '2024-02-01'); | Range/Interval 自動新增 |
| 截斷分區 | ALTER TABLE sales TRUNCATE PARTITION p_2023_01 DROP STORAGE; | 秒級刪除，釋放空間，不產生 REDO |
| 分割分區 | ALTER TABLE sales SPLIT PARTITION p_2024_01 INTO (PARTITION p_2024_01_a VALUES LESS THAN (DATE '2024-01-16'), PARTITION p_2024_01_b) ONLINE; | 12c+ ONLINE 無鎖 |
| 合併分區 | ALTER TABLE sales MERGE PARTITIONS p_2023_01, p_2023_02 INTO PARTITION p_2023_q1; | 合併相鄰分區 |
| 移動分區 | ALTER TABLE sales MOVE PARTITION p_2023_01 TABLESPACE ts_archive COMPRESS FOR OLTP ONLINE; | 跨表空間、壓縮、線上 |
| 交換分區 | ALTER TABLE sales EXCHANGE PARTITION p_2024_01 WITH TABLE sales_staging INCLUDING INDEXES WITHOUT VALIDATION; | 秒級載入/卸載，元資料操作 |
| 重建索引 | ALTER INDEX sales_idx REBUILD PARTITION p_2024_01 ONLINE; | 區域索引單獨重建 |
分區表匯出/匯入 (Exp/Imp vs Expdp/Impdp)
傳統 Exp/Imp (單執行緒、舊版相容)
# 全表匯出
exp system/pwd@db FILE=sales_full.dmp TABLES=sales
# 單一分區匯出
exp system/pwd@db FILE=sales_p202401.dmp TABLES=sales:p_2024_01
# 全表匯入
imp system/pwd@db FILE=sales_full.dmp FULL=Y
# 分區匯入 (需先建立同結構分區表)
imp system/pwd@db FILE=sales_p202401.dmp TABLES=sales:p_2024_01 IGNORE=Y
Data Pump Expdp/Impdp (平行、可壓縮、可網路模式、11g+ 標準)
# 全表匯出 (平行度 4)
expdp system/pwd@db DIRECTORY=dp_dir DUMPFILE=sales_%U.dmp TABLES=sales PARALLEL=4 COMPRESSION=ALL
# 分區匯出
expdp system/pwd@db DIRECTORY=dp_dir DUMPFILE=sales_p202401.dmp TABLES=sales:p_2024_01
# 全表匯入 (重映射表空間/Schema)
impdp system/pwd@db DIRECTORY=dp_dir DUMPFILE=sales_%U.dmp REMAP_TABLESPACE=ts_sales_data:ts_target REMAP_SCHEMA=sales_owner:target_owner TABLE_EXISTS_ACTION=REPLACE
# 分區匯入 (使用 PARTITION_OPTIONS)
impdp system/pwd@db DIRECTORY=dp_dir DUMPFILE=sales_p202401.dmp PARTITION_OPTIONS=DEPARTITION TABLE_EXISTS_ACTION=APPEND
-- DEPARTITION 將分區表匯入為非分區表，便於跨版本/平台遷移
實用查詢：檢視分區資訊
-- 表是否分區
SELECT table_name, partitioning_type, subpartitioning_type, partition_count
FROM user_part_tables;
-- 分區清單與大小
SELECT table_name, partition_name, high_value, num_rows, blocks, tablespace_name
FROM user_tab_partitions
WHERE table_name = 'SALES'
ORDER BY partition_position;
-- 索引分區
SELECT index_name, partition_name, status, tablespace_name
FROM user_ind_partitions
WHERE index_name = 'SALES_IDX';
最佳實踐檢核表
- ☐ 表大小 > 2GB 或預期快速增長 → 評估分區
- ☐ 含明確時間/業務維度且有歸檔需求 → Range/Interval + List 複合
- ☐ 分區鍵不更新、基數適中、分布均勻
- ☐ 區域索引預設，全域索引僅用於 PK/UK (需支援分區修剪)
- ☐ 定期
DBMS_STATS.GATHER_TABLE_STATS(..., GRANULARITY=>'PARTITION') 收集分區級統計
- ☐ 實作 ILM 政策：熱分區 SSD/壓縮、溫分區 HDD/壓縮、冷分區歸檔/唯讀
- ☐ 備份策略：每日增量備份熱分區、每週完整備份、分區級恢復演練
分區表不僅是效能工具，更是大型資料庫可維運、可擴展、可治理的架構基石。正確選擇分區策略並配合自動化維運腳本，能讓 TB/PB 級資料庫在有限資源下穩定服務業務增長。
