Oracle 索引優化設計：從組織管理到聚簇應用的實戰指南

索引雙刃劍：用對加速，用錯拖累

索引將邏輯值映射至 RowID，實現快速定位。但大表建索引不當會：佔用 SGA 緩衝區排擠熱點資料塊、觸發頻繁物理 I/O、DML 維護開銷大。核心原則：**分區大表配合分區索引、或干脆不建索引走全表掃描 + 並行查詢**。

策略一：索引組織表 (IOT) 與分區索引

• IOT： 資料直接儲存在 B-Tree 索引結構中 (主鍵順序)，省去 Heap Table → Index 雙重存取，適合「主鍵單點查詢佔 90%+」的查詢密集型表。
• 分區索引： 大表分區後，建立 LOCAL 分區索引 (每分區獨立索引段) 或 GLOBAL 跨分區索引。Local 索引維護成本低 (Truncate Partition 瞬間完成)，Global 索引查詢效率高但維護重。依據查詢模式選擇。

策略二：聚簇 — 多表共存加速連接

聚簇 將多個表依共同鍵值 (Cluster Key) 混合儲存在同一 Oracle 區塊。
優勢：單次 I/O 同時取得父表與子表資料，減少區塊讀取、天然預連接。
適用：頻繁 Join、鍵值重複率高、資料修改較少 的主從表 (如訂單/訂單明細)。
限制：不支援分區、全表掃描變慢、DML 開銷大、不可單獨 Truncate 其中一表。

策略三：讓優化器「用得上」索引的兩大鐵律

1. 複合索引前導欄位必須出現在 WHERE： 索引 (A,B,C) 若 WHERE 只有 B,C → 索引失效 (除非 Index Skip Scan)。
2. 索引欄位不可參與運算/函數/隱性轉換： WHERE UPPER(name)='ABC'、WHERE id+1=100、WHERE char_col = 123 皆導致索引失效。需建立函數索引 (FBI) 或修正 SQL。

補充：索引維護最佳實踐清單

• 定期 ANALYZE INDEX ... VALIDATE STRUCTURE 或 DBMS_STATS 收集統計，防止優化器誤判。
• 監控 INDEX_STATS 中 HEIGHT、LF_BLKS、DEL_LF_ROWS，重建條件：高度 > 4、刪除行 > 20%、碎片率高。
• 大批量載入時：DISABLE INDEX → LOAD → REBUILD INDEX 比逐行維護快數量級。
• 利用 V$SEGMENT_STATISTICS / DBA_HIST_SEG_STAT 找出「從未被使用」的索引，果斷 DROP 釋放空間與 DML 開銷。