Oracle 索引類型選型指南：B-tree、Bitmap、Function-based 與 Domain 索引實戰

索引類型對比

Bitmap 索引：三大黃金場景

1. 低基數欄位

性別 (M/F)、婚姻狀態 (S/M/D/W)、部門代碼 (＜50 個值)

CREATE BITMAP INDEX idx_emp_gender ON emp(gender);
CREATE BITMAP INDEX idx_emp_dept ON emp(deptno);

2. 多條件 AND/OR 組合

Oracle 可直接對 Bitmap 做位元運算，極快：

SELECT * FROM emp 
WHERE gender = 'F' AND deptno = 10 AND status = 'ACTIVE';

三個 Bitmap 索引位元 AND 運算，無需回表即可定位 ROWID。

3. 查詢 NULL 值

B-tree 不索引全 NULL 鍵值，Bitmap 索引包含 NULL：

CREATE BITMAP INDEX idx_emp_id_null ON emp(id_card);
SELECT * FROM emp WHERE id_card IS NULL; -- 走 Bitmap 索引

Bitmap 索引禁忌

• ❌ OLTP 高併發寫入：Bitmap 片段鎖導致嚴重爭用
• ❌ 高基數欄位：Bitmap 膨脹、效能劣於 B-tree
• ❌ 範圍查詢 (＞、＜、BETWEEN)：Bitmap 不支援範圍掃描
• ❌ 單表小資料量：全表掃描更快

B-tree 索引最佳實踐

複合索引欄位順序

• 等值條件欄位前置 (WHERE dept=10 AND sal>5000 → dept 在前)
• 高選擇性欄位前置
• 避免「前導欄位未使用」導致索引失效

覆蓋索引

CREATE INDEX idx_emp_cover ON emp(deptno, empno, ename, sal);
SELECT ename, sal FROM emp WHERE deptno = 10; -- Index Only Scan

Function-based Index 實戰

-- 大小寫不敏感查詢
CREATE INDEX idx_emp_upper_name ON emp(UPPER(ename));
SELECT * FROM emp WHERE UPPER(ename) = 'SMITH';

-- 日期運算
CREATE INDEX idx_orders_year ON orders(EXTRACT(YEAR FROM order_date));
SELECT * FROM orders WHERE EXTRACT(YEAR FROM order_date) = 2023;

-- 算術運算
CREATE INDEX idx_sal_bonus ON emp(sal + NVL(comm,0));
SELECT * FROM emp WHERE (sal + NVL(comm,0)) > 5000;

需設定 QUERY_REWRITE_ENABLED=TRUE、QUERY_REWRITE_INTEGRITY=TRUSTED。

索引建立決策樹

1. 表列數 ＜ 1000 行？ → 不建索引，全表掃描最快
2. 欄位基數 / 總行數 ＜ 1%？ → 考慮 Bitmap (僅限 DW/唯讀)
3. WHERE 條件含函數/運算式？ → Function-based Index
4. 頻繁 JOIN 欄位？ → B-tree 索引 (外鍵必建)
5. ORDER BY/GROUP BY 欄位？ → B-tree 索引避免排序
6. 更新頻率極高？ → 極度謹慎建索引，評估 DML 代價

維護建議

• 定期 ANALYZE INDEX ... VALIDATE STRUCTURE 檢查碎片
• 碎片 ＞ 20% → ALTER INDEX ... REBUILD ONLINE
• 監控 V$OBJECT_USAGE (11g+) 識別未使用索引並移除
• 統計資訊收集：DBMS_STATS.GATHER_TABLE_STATS(..., METHOD_OPT=>'FOR ALL INDEXED COLUMNS')