Oracle 位圖索引陷阱實錄：Thomas Kyte 親授的並發鎖死教訓

故事背景

Tom Kyte（Oracle 首席技術專家，《Expert Oracle Database Architecture》作者）某日接獲 Java 開發團隊求助：JSP+EJB+Oracle 三層架構系統上線後效能極差，EJB SQL 由工具自動產生，無法用 SQL Trace 調優。Tom 遠端連線測試庫，查 V$LOCK、V$SQL 發現可疑點：一個建立在 PROCESS_FLAG 欄位上的位圖索引。

問題現場還原

PROCESS_FLAG 只有兩值：N（未處理）、Y（已處理）。新增記錄預設 N，背景程式讀取 N 處理後更新為 Y。開發人員辯稱：「參考書說基數低的欄位適合建位圖索引，能快速找到 N 記錄。」

Tom 的現場示範：一條 INSERT 就鎖死全表

SQL> CREATE TABLE t (processed_flag VARCHAR2(1));
SQL> CREATE BITMAP INDEX t_idx ON t (processed_flag);
SQL> INSERT INTO t VALUES ('N');  -- Session 1 執行，未提交

-- Session 2 嘗試同樣操作：
SQL> INSERT INTO t VALUES ('N');  -- 直接掛住，等待 Session 1 提交

現象：Session 2 永久等待，直到 Session 1 COMMIT 或 ROLLBACK。

根因深度解析

位圖索引確實適合低基數、極少更新的欄位（如性別、狀態碼）。但關鍵限制常被忽略：

當更新/插入某索引鍵值的記錄時，Oracle 會鎖定該鍵值對應的所有記錄（可能成百上千筆）。其他 Session 無法同時修改相同鍵值的記錄，必須排隊等待。

本案中，PROCESS_FLAG 高頻從 N→Y，每次更新都鎖定所有 N 或 Y 記錄，導致嚴重並發競爭。

解決方案：函數索引替代

Tom 建議：移除位圖索引，改建函數索引，僅為 N 值建立 B-Tree 索引：

CREATE INDEX idx_process_flag_n ON t (CASE WHEN processed_flag = 'N' THEN 'N' END);

測試驗證後，並發鎖死問題徹底解決。

Tom 的反思：資料庫不「爛」，是你不懂它

開發團隊事後抱怨 Oracle「爛」。Tom 回信：

應用從 Database A 遷移到 B 時常抱怨 B「爛」。事實是 B 只是運作方式不同。沒有爛資料庫，只有不了解架構特性的開發者。理解位圖索引適用條件，你還會說 Oracle 爛嗎？

並建議 3 天集中訓練。訓練後開發者感嘆：「原來 Oracle 不適合建臨時表」、「物化視圖能簡化備份代碼」、「CONNECT BY 這麼強」。

核心心法

1. 位圖索引 = 低基數 + 極少 DML（資料倉庫、DSS 系統）
2. OLTP 高頻更新欄位嚴禁位圖索引
3. 函數索引 / 部分索引是 OLTP 低基數欄位的正確選擇
4. 深入理解資料庫內核機制，比責怪工具更有效