Oracle 分區鍵上建立本地索引 vs 全域索引：CBO 成本計算陷阱與 9i/10g 差異

測試場景

• Range 分區表 T1，分區鍵 A (INT)，20 個分區，約 64 萬筆資料。
• 分別建立 Local Index 與 Global Index 在分區鍵 A 上。
• 查詢 SELECT * FROM t1 WHERE a = 1000 (落在 P02 分區)。

執行計劃對比

Local Index → 索引範圍掃描

TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'T1'
INDEX (RANGE SCAN) OF 'T1_IDX' (NON-UNIQUE)
Consistent Gets: 40

Global Index → 分區全表掃描 (錯誤選擇)

TABLE ACCESS (FULL) OF 'T1'  (Partition Pruned to P02)
Consistent Gets: 948

強制 Hint /*+ INDEX(t1) */ 後恢復索引掃描，Consistent Gets 僅 37。

根因：CBO 成本計算公式缺陷 (9i)

Cost = BLEVEL
     + CEIL(LEAF_BLOCKS * Effective Index Selectivity)
     + CEIL(CLUSTERING_FACTOR * Effective Table Selectivity)

• Global Index 的 CLUSTERING_FACTOR 為全表級 (639,968)，但 Effective Table Selectivity 卻取分區級 (0.001)。
• 回表成本 = 639,968 × 0.001 ≈ 640，遠大於分區掃描成本 (59)。
• 實際上該分區僅 32,000 筆，最壞情況回表僅需 32 次 I/O。

Local Index 避開此問題

• 分區剪枝後，索引與表統計皆為分區級，CLUSTERING_FACTOR = 32,000，成本正確評估為 33。

10g 改善

10g 在「索引鍵全等值條件」下，回表成本改用 DISTINCT_KEYS 計算選擇率，不再錯用分區級選擇率，避免此 Bug。

最佳實務建議

1. 分區鍵索引優先建 Local，天然配合 Partition Pruning，統計資訊一致。
2. OLTP 系統若需 Global Index (如唯一性跨分區約束)，需手動鎖定統計資訊或升級 10g+。
3. 定期 DBMS_STATS.GATHER_TABLE_STATS(..., GRANULARITY=>'PARTITION') 收集分區級統計。
4. 發現計劃異常時，先檢查 CLUSTERING_FACTOR 與分區選擇率是否匹配。

真實案例教訓

某省核心系統因分區鍵建 Global Index，導致高併發查詢全分區掃描，CPU 飆升，改為 Local Index 後效能提升 20 倍。