Oracle 分區索引全解析：Local vs Global 架構選型與語法實戰

分區索引是大型資料倉庫與高交易量 OLTP 系統實現資料生命週期管理、查詢裁剪的關鍵技術。本文系統梳理 Local/Global 分區索引的架構差異、建立語法、維護特性與選型指南。

核心架構對比

Local 分區索引語法變體

1. 最簡單：LOCAL (自動對應表分區表空間)

CREATE INDEX idx_local_simple ON part_table(col1) LOCAL;

2. 指定表空間清單：LOCAL STORE IN (ts1, ts2, ...)

CREATE INDEX idx_local_store ON part_table(col1)
LOCAL STORE IN (ts_idx1, ts_idx2, ts_idx3, ts_idx4);  -- 數量必須等於表分區數

僅適用 Hash 或 Composite 分區表。

3. 逐分區精細控制：LOCAL (PARTITION ...)

CREATE INDEX idx_local_detail ON part_table(col1)
LOCAL (
  PARTITION p1 TABLESPACE ts_idx1,
  PARTITION p2 TABLESPACE ts_idx2,
  PARTITION p3 TABLESPACE ts_idx3
);

支援子分區、儲存參數、LOGGING/NOLOGGING 等細節。

Global 分區索引語法

CREATE INDEX idx_global ON part_table(col2)
GLOBAL PARTITION BY RANGE (col2) (
  PARTITION g1 VALUES LESS THAN (1000) TABLESPACE ts_g1,
  PARTITION g2 VALUES LESS THAN (2000) TABLESPACE ts_g2,
  PARTITION g3 VALUES LESS THAN (MAXVALUE) TABLESPACE ts_g3
);

分區鍵 col2 可與表分區鍵完全無關。

關鍵維護操作：UPDATE GLOBAL INDEXES

執行表分區 DDL 時，**必須**加上此子句同步全域索引，否則索引標記為 UNUSABLE，後續查詢將報錯或全表掃描。

-- 正確：同步維護全域索引
ALTER TABLE part_table DROP PARTITION p_old UPDATE GLOBAL INDEXES;

-- 錯誤：全域索引將失效
ALTER TABLE part_table DROP PARTITION p_old;

效能代價：UPDATE GLOBAL INDEXES 會掃描全域索引所有分區清理已刪分區的 ROWID，大表耗時較久，可考慮 ASYNC 模式 (12c+) 延後維護。

選型決策樹

1. 表是否分區？
   → 否 → 僅能建 **Global** (或非分區索引)。
2. 是否需支援表分區 DROP/TRUNCATE/MERGE 等高頻維護？
   → 是 → **強烈建議 Local**，維護零負擔。
3. 是否需跨分區強制唯一性 (PK/UK)？
   → 是 → 若唯一鍵含分區鍵 → Local Unique；
       若唯一鍵**不含**分區鍵 → **必須 Global Unique**。
4. 查詢模式是否固定依特定欄位篩選 (非分區鍵)？
   → 是 → 考慮 **Global** 於該欄位分區，實現索引層面裁剪。
5. 是否為 Hash/Composite 分區表且需自訂索引表空間分佈？
   → 是 → 使用 LOCAL STORE IN 或逐分區定義。

常見陷阱

• Local Unique 索引缺少分區鍵：建立失敗 ORA-14039。
• Global 索引忘記 UPDATE GLOBAL INDEXES：維護窗口後發現索引 UNUSABLE，需 REBUILD 耗時極長。
• 分區索引統計資訊遺漏：需收集 **GLOBAL** 與 **PARTITION** 兩層級統計，DBMS_STATS.GATHER_INDEX_STATS(..., GRANULARITY=>'ALL')。