Oracle 11g 分區功能新革命：Interval、Reference、虛擬欄位與分區建議器深度解析

分區演進里程碑

11g 四大核心增強詳解

1. Interval Partitioning：告別手動維護分區

痛點：Range 分區需預建分區，忘記添加導致 ORA-14400 插入失敗。

方案：定義起始分區 + 間隔，Oracle 自動按需建立等寬分區。

CREATE TABLE sales_part (
  time_id NUMBER, region_id NUMBER, order_id NUMBER,
  order_date DATE, sales_qty NUMBER(10,2), sales_amount NUMBER(12,2)
)
PARTITION BY RANGE (order_date)
INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))  -- 每月一個分區
( PARTITION p_first VALUES LESS THAN ('01-JAN-2006') );

• 僅首個分區為永久元數據，後續分區隨資料到來自動建立
• 本地索引同步自動建立
• 既有 Range 表可用 ALTER TABLE SET INTERVAL(...) 轉型

2. Reference Partitioning：外鍵關聯自動繼承分區策略

場景：訂單表 (父) 按日期 Range 分區，明細表 (子) 需同樣分區以支援 Partition-Wise Join。

傳統痛點：明細表須冗存分區鍵 (order_date)，維護成本高、資料冗餘。

11g 解法：

-- 父表
CREATE TABLE orders (
  order_id NUMBER PRIMARY KEY, order_date DATE, ...
) PARTITION BY RANGE (order_date) (...);

-- 子表：無需存 order_date，直接引用父表 PK
CREATE TABLE order_items (
  item_id NUMBER, order_id NUMBER, ...
  CONSTRAINT fk_oi_o FOREIGN KEY (order_id) REFERENCES orders(order_id)
) PARTITION BY REFERENCE (fk_oi_o);

• 子表自動擁有與父表完全一致的分區結構
• 分區裁剪、Partition-Wise Join 零成本生效
• 支援多層級遞歸引用 (孫表引用子表)

3. Virtual Column Partitioning：運算式分區無儲存成本

突破：分區鍵不再需為實體欄位，可為虛擬欄位運算式。

CREATE TABLE accounts (
  acc_no NUMBER(10) NOT NULL,
  acc_name VARCHAR2(50) NOT NULL,
  acc_loc VARCHAR2(5),
  -- 虛擬欄位：從帳號前 2 碼衍生分支代碼
  acc_branch NUMBER(2) GENERATED ALWAYS AS 
    (TO_NUMBER(SUBSTR(TO_CHAR(acc_no), 1, 2)))
)
PARTITION BY LIST (acc_branch) (...);

• 虛擬欄位僅存元數據，零磁碟空間
• 支援任意確定性運算式 (函數、CASE、運算子)
• 可作為任何分區類型 (Range/List/Hash) 的鍵

4. Partition Advisor：SQL Access Advisor 整合

• 分析工作負載 (SQL Tuning Set、AWR、Cursor Cache)
• 推薦最佳分區策略 (類型、鍵、粒度)
• 量化預估效能提升 (I/O 減少、查詢加速比)
• 自動生成 ALTER TABLE ... PARTITION 實施腳本
• 可透過 Enterprise Manager 或 DBMS_ADVISOR 包調用

組合分區矩陣擴展

11g 完善 6 種二級組合：Range-Range、Range-List、Range-Hash、List-Range、List-List、List-Hash，滿足多維度裁剪需求。

遷移建議

1. 現有手動管理 Range 分區表 → 改用 Interval 自動化
2. 父子表冗存分區鍵 → 改用 Reference Partitioning
3. 需按運算式分區 (如地區代碼衍生) → 採用 Virtual Column
4. 新專案分區設計 → 先跑 Partition Advisor 獲取基準建議