Oracle 函數索引實戰指南：部分索引、約束保障與常見陷阱避坑

為何需要函數索引？

查詢含函數運算列（如 WHERE UPPER(NAME)='SMITH'）時，普通索引失效，需全表掃描執行函數。函數索引預計算函數值存入索引，查詢直接走索引範圍掃描，性能提升數量級。

代價與收益平衡

• DML 開銷：INSERT/UPDATE 該列時需額外計算函數值維護索引。
• 無關更新零成本：更新未建函數索引的列，零額外開銷。

經典應用模式

1. 部分索引：只索引關鍵少數

場景：狀態列 STATUS 僅 'Y'/'N'，99% 為 'Y'，業務只查 'N'。

CREATE INDEX IDX_T_STATUS_N ON T(DECODE(STATUS,'N','N'));

• 利用 B 樹索引不存 NULL特性：DECODE 將 'Y' 映射為 NULL，不進索引；'N' 映射為 'N' 入索引。
• 索引體積縮減 99%，查詢 WHERE STATUS='N' 改寫為 WHERE DECODE(STATUS,'N','N')='N' 秒級返回。

2. 複雜業務約束：條件唯一性

場景：專案表，STATUS='ACTIVE' 時專案名唯一，INACTIVE 允許重名。

CREATE UNIQUE INDEX UX_PROJ_ACTIVE_NAME ON T(DECODE(STATUS,'ACTIVE',NAME));

• 'ACTIVE' 時索引鍵為專案名，強制唯一；'INACTIVE' 映射為 NULL，不入索引，允許重複。
• 完美替代觸發器實現條件約束，聲明式、高效、無併發死鎖風險。

B 樹索引「不存 NULL」的連鎖效應

高頻避坑清單

1. 外鍵列必建索引：防止父表刪除/更新鎖定子表全表（TM 鎖升級）。
2. 索引跳躍式掃描：前導列基數極低（如性別 M/F），優化器自動拆分為 WHERE X=5 AND Y='M' UNION ALL WHERE X=5 AND Y='F' 走索引。
3. 隱式轉換殺索引： WHERE NUM_COL='123' 觸發 TO_NUMBER，索引失效。代碼中嚴禁字符串比較數字列。
4. 日期截斷改寫： TRUNC(DT)=TRUNC(SYSDATE) → DT >= TRUNC(SYSDATE) AND DT < TRUNC(SYSDATE)+1，利用範圍掃描。
5. 統計信息失真：定期 DBMS_STATS.GATHER_TABLE_STATS，優化器依賴統計信息決策，非 Bug。
6. 復合索引優於單列：多列過濾條件組合建復合索引，列序遵循「等值在前、範圍在後、基數高在前」。