Oracle 11g 分區功能深度解析：Interval、Reference、Virtual Column、Partition Advisor

分區演進史

四大核心分區類型回顧

• Range：依鍵值範圍分佈（如月份）
• Hash：雜湊演算法分佈，I/O 平衡，不適合範圍查詢
• List：依離散值列表分佈（如地區、國家）
• Composite：兩層分區，如 Range-Hash、List-Range 等六種組合

11g 三大創新分區技術

1. Interval 分區：Range 自動化延伸

自動建立等寬範圍分區，無需預先定義所有分區：

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 轉換
• 區間定義使用 NUMTOYMINTERVAL 或 NUMTODSINTERVAL

2. Reference 分區：外鍵關係繼承分區

明細表透過 PK-FK 關係自動繼承主表分區策略，無需在明細表儲存分區鍵：

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

CREATE TABLE order_items (
  item_id NUMBER,
  order_id NUMBER REFERENCES orders(order_id)
) PARTITION BY REFERENCE (orders_fk);

優點：維護簡單、Join 效能最佳（Partition-wise Join）、避免資料重複。

3. Virtual Column 分區：運算式作為分區鍵

虛擬欄位僅存元資料，不佔儲存空間：

CREATE TABLE accounts (
  acc_no NUMBER(10) NOT NULL,
  acc_name VARCHAR2(50) NOT NULL,
  acc_loc VARCHAR2(5),
  acc_branch NUMBER(2) GENERATED ALWAYS AS (TO_NUMBER(SUBSTR(TO_CHAR(acc_no),1,2)))
)
PARTITION BY LIST (acc_branch);

應用：從帳號前兩碼自動推導分行代碼分區，無需觸發器維護。

Partition Advisor：智慧分區建議

• 整合於 SQL Access Advisor
• 分析工作負載，推薦分區策略與預估效益
• 自動生成建立分區 DDL 腳本，可手動或透過 EM 執行