索引雖能大幅提升查詢效能，但**選錯類型反而拖垮效能**。本文以「員工資訊表」為例，深度剖析 B-tree 索引在低基數欄位的失效原因，並證明 Bitmap 索引在低基數、多條件 AND/OR、NULL 查詢三大場景的優勢與限制。
一、 基數概念與 B-tree 失效場景
- 基數：欄位不重複值的個數。性別(男/女)=2、婚姻狀態(未/已/離)=3、部門代碼≈50
- B-tree 機制：儲存 (索引值, ROWID) 有序對，適合**高選擇性**（回傳少量列）
- 失效案例：查詢「所有女員工」
表 10 萬列，女性 5 萬列 → 選擇率 50%
B-tree 掃描 5 萬索引項 + 5 萬 Table Access by ROWID，**比 Full Table Scan 更慢**
二、 Bitmap 索引內部機制與優勢
1. 結構原理
- 對每個唯一值建立一張**位圖**，位圖長度 = 表總列數
- 每位對應一列，1=包含該值，0=不包含
- 透過**映射函數**將位圖位置轉換為 ROWID
2. 邏輯運算加速複合條件
-- 查詢：已婚女性
SELECT * FROM userinfo
WHERE marital = '已婚' AND sex = 0;
- Bitmap 索引：直接對「已婚位圖」與「女性位圖」做 **AND 位運算**，結果位圖 1 的個數即符合列數
- B-tree：需先掃描其中一個索引取回 ROWID，再回表過濾另一條件，**無法直接邏輯運算**
3. 原生支援 NULL 查詢
- B-tree **不索引全 NULL 鍵值**，
WHERE col IS NULL 無法用索引
- Bitmap **將 NULL 視為一個獨特值**建立位圖，
WHERE id_card IS NULL 可直接走索引
三、 Bitmap 索引適用性決策表
| 場景 | 推薦索引 | 理由 |
|---|
| 基數極低 (< 100) + 高頻查詢 | Bitmap | 位圖極小，邏輯運算極快 |
| WHERE 條件多重 AND/OR 組合 | Bitmap | 位運算替代多次回表 |
| 需查詢 NULL 值 | Bitmap | 唯一支援 NULL 索引的類型 |
| 高基數主鍵/唯一鍵 | B-tree | Bitmap 會極大、維護成本高 |
| 頻繁 DML (INSERT/UPDATE/DELETE) | B-tree | Bitmap 鎖定整個值對應所有列，併發極差 |
| 函數運算欄位 (UPPER, SUBSTR) | Function-based B-tree | Bitmap 不支援函數索引 |
四、 併發鎖定實測警示
-- Session 1
INSERT INTO t(processed_flag) VALUES ('N');
-- 未提交
-- Session 2
INSERT INTO t(processed_flag) VALUES ('N');
-- **阻塞等待**，因 Bitmap 索引鎖定所有 'N' 值對應的列
**結論**：高併發寫入表嚴禁在頻繁更新欄位建 Bitmap 索引，改用 Function-based B-tree 索引 CASE WHEN processed_flag='N' THEN 1 END 僅索引待處理列。
五、 綜合選型建議
- 先算基數：
SELECT COUNT(DISTINCT col) FROM t;
- 評估 DML 頻率：高寫入 → B-tree；唯讀/低寫入 → 考慮 Bitmap
- 分析 WHERE 條件：多 AND/OR + 低基數 → Bitmap 首選
- 是否查 NULL：是 → Bitmap 或 Function-based 索引
NVL(col,'NULL')
- 混合負載：核心查詢上 Bitmap，寫入熱點欄位上 B-tree
索引選型無銀彈，**依據基數、併發、查詢模式三維決策**，才能讓索引真正成為效能助推器而非負擔。
- 上一篇文章： Oracle 11g 閃回資料歸檔：Total Recall 完整部署與實戰驗證
- 下一篇文章： Oracle Bitmap 索引特性深度解析：適用場景、限制與混合索引策略
Copyright ©
程式碼之家 All Rights Reserved
