Oracle 索引建立策略與類型全解析：B-Tree、Bitmap、Reverse、Function-Based 實戰語法

索引建立核心策略

1. 先導入資料，再建索引：避免載入過程維護索引開銷
2. 小表勿建索引：全表掃描更快
3. 低基數欄位 (性別等)：使用 Bitmap 索引
4. 限制索引數量：每表建議 <5 個，DML 密集表更少
5. 設定適當 PCTFREE：預留更新空間減少分裂
6. 索引獨立表空間：隔離 I/O、備份彈性、跨磁碟平行

索引類型與建立語法

非唯一 B-Tree 索引

CREATE INDEX emp_ename ON employees(ename)
  TABLESPACE users
  STORAGE (...)
  PCTFREE 0;

唯一 B-Tree 索引

CREATE UNIQUE INDEX emp_email ON employees(email)
  TABLESPACE users;

Bitmap 索引 (低基數、DW 環境)

CREATE BITMAP INDEX emp_sex ON employees(sex)
  TABLESPACE users;

• 適用：基數 < 1% 表列數、多條件 AND/OR 組合查詢、資料倉庫
• 不適用：高併發 OLTP (鎖定粒度粗、DML 效能差)

Reverse Key 索引 (消除右側熱點)

CREATE UNIQUE INDEX order_reinx ON orders(order_num, order_date)
  TABLESPACE users
  REVERSE;

• 將索引鍵值位元組順序反轉儲存
• 解決遞增鍵值 (序列、時間戳) 導致右側葉區塊熱點
• 代價：無法支援範圍掃描 (WHERE col > 100)

Function-Based 索引 (FBI)

CREATE INDEX emp_substr_empno ON employees(SUBSTR(empno, 1, 2))
  TABLESPACE users;

• 可為 B-Tree 或 Bitmap
• 解決 WHERE UPPER(name) = 'SMITH'、WHERE SUBSTR(id,1,2) = 'AB' 索引失效問題
• 需 QUERY_REWRITE_ENABLED=TRUE、QUERY_REWRITE_INTEGRITY=TRUSTED
• 表達式結果必須確定性

複合索引限制

• 最多 32 個欄位 (12c+ 可擴展)
• 遵循最左前綴原則
• 考慮欄位順序：高選擇性優先、常用等值條件優先

索引維護監控

-- 索引使用統計
SELECT index_name, table_name, blevel, leaf_blocks, clustering_factor
FROM user_indexes WHERE table_name = 'EMPLOYEES';

-- 監控未使用索引 (11g+)
SELECT * FROM user_indexes WHERE last_analyzed IS NOT NULL 
  AND index_name IN (SELECT object_name FROM v$object_usage WHERE used = 'NO');