Oracle 索引類型全解析：B-Tree、Bitmap、Reverse Key、Function-Based、Cluster、Global/Local Partitioned

Oracle 索引架構與七大類型完整指南：選型、創建語法與適用場景

索引是查詢性能的核心。本文系統梳理 Oracle 七大索引類型的底層結構、創建語法、適用場景與限制，幫助 DBA 根據業務特徵精準選型。

一、索引概述

• 索引是獨立於表的模式對象，提供有序鍵值 + ROWID 映射；
• 加速 WHERE、JOIN、ORDER BY、GROUP BY，但增加 DML 開銷；
• 創建語法通用框架：

  CREATE [UNIQUE|BITMAP] INDEX [schema.]index_name
    ON [schema.]table_name (col|expr [ASC|DESC], ...)
    [TABLESPACE tbs] [STORAGE ...] [LOGGING|NOLOGGING]
    [COMPRESS n] [NOSORT|REVERSE] [PARTITION ...];

二、B-Tree 索引 (默認、最常用)

• 結構：平衡樹，根/分支/葉節點，葉節點存 (鍵值, ROWID)；
• 類型：
  1. 普通索引：CREATE INDEX idx_name ON tbl(col) TABLESPACE tbs;
  2. 唯一索引：CREATE UNIQUE INDEX ...，主鍵/唯一約束自動創建；
  3. 復合索引：CREATE INDEX idx ON tbl(col1, col2) TABLESPACE tbs;，遵循最左前綴原則。

三、位圖索引

• 適用：低基數列 (性別、狀態、類型)，DISTINCT 值 / 總行數 < 1%；
• 機制：每個鍵值對應一個位圖，多列查詢通過位圖 AND/OR 快速定位；
• 限制：不支援唯一、不支援鍵壓縮、DML 鎖定嚴重 (不適合高併發 OLTP)；
• 創建：CREATE BITMAP INDEX idx_sex ON tbl(sex) TABLESPACE tbs; (需 Enterprise Edition)。

四、反向鍵索引

• 原理：將鍵值字節反轉後存入 B-Tree (如 2009 → 9002)；
• 適用：序列/自增主鍵導致右側葉塊熱點爭用；
• 創建：CREATE INDEX idx_rev ON tbl(seq_col) REVERSE TABLESPACE tbs;；
• 查詢時無感知，優化器自動處理反轉。

五、基於函數的索引 (FBI)

• 對表達式/函數結果建索引：CREATE INDEX idx_upper_name ON tbl(UPPER(name));；
• 查詢需完全匹配表達式：WHERE UPPER(name) = 'SMITH'；
• 需 QUERY_REWRITE_ENABLED=TRUE 且收集表統計信息。

六、簇索引

• 針對簇表：CREATE CLUSTER ...; CREATE INDEX idx_clu ON CLUSTER clu_name;；
• 簇鍵值相同的行物理存放在同一塊，適合頻繁按簇鍵連接的主從表。

七、全局/局部分區索引

八、選型決策樹

1. 高基數、等值/範圍查詢、OLTP → B-Tree；
2. 低基數、多列組合過濾、DW/OLAP → Bitmap；
3. 自增主鍵熱點 → Reverse Key；
4. WHERE 子句含函數/表達式 → Function-Based；
5. 分區表 → Local Partitioned (首選) 或 Global。