Oracle 11g 分區新特性全解析：Interval、Reference、Virtual Column、Advisor

分區演進脈絡

核心新特性詳解

1. Interval Partitioning (區間自動擴展)

解決 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')  -- 唯一永久分區
);

• NUMTOYMINTERVAL(1,'MONTH') 將數值轉為 INTERVAL YEAR TO MONTH
• 現有 Range 分區表可 ALTER TABLE ... SET INTERVAL (...) 升級
• 元數據級管理，性能極佳

2. Reference Partitioning (外鍵引用分區)

子表通過 FK 關聯父表，**無需冗余存儲分區鍵**，自動繼承父表分區策略。

-- 父表
CREATE TABLE ORDERS (ORDER_ID NUMBER, ORDER_DATE DATE, ...)
PARTITION BY RANGE (ORDER_DATE) (...);

-- 子表：無 ORDER_DATE 列
CREATE TABLE ORDER_ITEMS (ITEM_ID NUMBER, ORDER_ID NUMBER, ...)
PARTITION BY REFERENCE (FK_ORDER_ITEMS_ORDERS);  -- 引用 FK 名稱

• 維護簡單：父表分區操作 (TRUNCATE/DROP/EXCHANGE) 自動級聯子表
• 節省存儲：子表不存分區鍵列
• 查詢剪枝：連接父表時自動利用分區剪枝

3. Virtual Column Partitioning (虛擬列分區)

分區鍵可源自**虛擬列** (僅存元數據，不佔物理空間)，打破「分區鍵必須為實體列」限制。

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) (...);

• 虛擬列定義：GENERATED ALWAYS AS (表達式)
• 表達式不可包含 PL/SQL 函數、不可引用其他行
• 極適合「業務鍵隱含分區邏輯」場景 (如帳號前綴代表機構)

4. Partition Advisor (分區建議器)

• 整合於 SQL Access Advisor
• 輸入：工作負載 (SQL Tuning Set / AWR / 用戶表)
• 輸出：推薦分區策略、預估收益、生成實施腳本
• 可通過 Enterprise Manager 或 DBMS_ADVISOR 包調用

索引組織表 (IOT) 分區增強

10g 支援 Range/List/Hash，11g 新增 **Composite Partitioning** 支援 (Range-Range, Range-List, List-Range, List-List, List-Hash, Range-Hash)。

選型建議

• 時間序列數據、固定周期歸檔 → **Interval**
• 主從表強關聯、父表已分區 → **Reference**
• 分區邏輯衍生自現有列表達式 → **Virtual Column**
• 新系統分區設計不知從何下手 → **Partition Advisor** 輔助決策