Oracle 索引深度解析：B*Tree、Bitmap、Reverse、Compress 與 Clustering Factor 實戰

B*Tree 索引結構與高度

平衡 B*Tree 所有葉塊位於同一層級。BLEVEL (Branch Level) 表示分支層數 (不含葉層)，HEIGHT = BLEVEL + 1。百萬級資料通常 2-3 層。

SQL> SELECT index_name, blevel, num_rows FROM dba_indexes WHERE table_name='TM_VEHICLE';
INDEX_NAME                    BLEVEL   NUM_ROWS
TM_VEHICLE_LEAVING_DATE          2    3461864

SQL> ANALYZE INDEX TM_VEHICLE_LEAVING_DATE VALIDATE STRUCTURE;
SQL> SELECT height, name FROM index_stats;
HEIGHT NAME
     3 TM_VEHICLE_LEAVING_DATE

索引壓縮

• CREATE INDEX ... COMPRESS n (n 為前綴欄位數)。
• 優點：降低樹高、減少葉塊、提高緩衝區命中率、降低物理 I/O。
• 代價：CPU 開銷增加 (解壓縮、比較前綴)、DML 維護成本略增。
• 適用：前導欄位重複度高、查詢多為範圍掃描的索引。

反轉鍵索引

• CREATE INDEX ... REVERSE 將鍵值位元組順序反轉存儲。
• 消除單調遞增鍵 (如 Sequence) 導致的最右葉塊熱點爭用。
• RAC 環境顯著減少跨實例塊傳輸 (GC CR Block)。
• 限制：無法支援範圍掃描 (BETWEEN, >, <)。

遞減索引

CREATE INDEX ... DESC 讓指定欄位以降序存儲，配合 ORDER BY col DESC 避免排序操作。

Clustering Factor (CF) — 索引與表資料排序一致性指標

• CF ≈ 表塊數 : 索引順序與表資料物理順序高度一致，範圍掃描極少隨機 I/O。
• CF ≈ 表行數 : 索引順序與表資料完全隨機，範圍掃描產生大量隨機 I/O。
• 查詢：SELECT index_name, clustering_factor, blocks, num_rows FROM user_indexes i JOIN user_tables t ON i.table_name=t.table_name;

實戰對比：有序 vs 無序表

-- 有序表 COLOCATED (PK=x 遞增)
CF = 1,190  (≈ blocks 1,252)
SELECT * FROM colocated WHERE x BETWEEN 10000 AND 20000;
-- Plan: INDEX RANGE SCAN -> TABLE ACCESS BY ROWID  Cost=142

-- 無序表 DISORGANIZED (依 y 排序建表，PK=x 相同)
CF = 99,905 (≈ num_rows 100,094)
SELECT * FROM disorganized WHERE x BETWEEN 10000 AND 20000;
-- Plan: FULL TABLE SCAN  Cost=271

相同索引結構，CF 差異導致執行計劃從索引掃描退化為全表掃描，成本差近 2 倍。

Bitmap 索引與 Bitmap Join 索引

• 適用低基數欄位 (性別、狀態碼)、資料倉庫、唯讀/少量 DML 環境。
• 單一鍵值對應多行 (位圖)，支援 AND/OR/NOT 位元運算快速合併。
• 儲存 NULL 值，B*Tree 不存 NULL。
• Bitmap Join Index (9i+)：預先 Join 維度表，查詢時直接掃描位圖避免 Join 開銷。

維護建議

1. 定期 ANALYZE INDEX ... VALIDATE STRUCTURE 監控 HEIGHT、LF_BLKS、DEL_LF_ROWS。
2. ALTER INDEX ... REBUILD 或 COALESCE 整理碎片 (DEL_LF_ROWS > 20%)。
3. 監控 V$SEGMENT_STATISTICS 中 logical reads、physical reads 判斷索引效益。
4. 無用索引 (長期未被使用) 及時 DROP 或 UNUSABLE 節省 DML 開銷。

結語

索引不是銀彈。理解 B*Tree 結構、CF 意義、壓縮/反轉/遞減/位圖各類型適用場景，配合持續監控與精準維護，才能在 OLTP 與 DW 雙重負載下榨乾儲存與 I/O 效能。