Oracle 組合索引與回表機制深度解析

Oracle 組合索引與回表機制深度解析

什麼是「回表」？

當查詢使用索引找到符合條件的 ROWID，但 SELECT 清單包含索引未涵蓋的欄位，Oracle 必須再以 ROWID 回資料區塊讀取完整列資料。執行計劃顯示 TABLE ACCESS BY INDEX ROWID 即為回表操作。

實測範例（參考 Tom Kyte "Expert Oracle Database Architecture"）

建立測試表 big_table (300 萬筆)，索引 idx_big_table_created(created)。

案例 1：僅查詢索引欄位 → 無回表

EXPLAIN PLAN FOR
SELECT created
FROM big_table a
WHERE created = TO_DATE('2010/3/30 10:08:30', 'yyyy/mm/dd hh24:mi:ss');

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

計劃僅顯示 INDEX RANGE SCAN，無 TABLE ACCESS。

案例 2：查詢索引欄位 + 主鍵 id → 產生回表

EXPLAIN PLAN FOR
SELECT id, created
FROM big_table a
WHERE created = TO_DATE('2010/3/30 10:08:30', 'yyyy/mm/dd hh24:mi:ss');

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

出現 TABLE ACCESS BY INDEX ROWID，確認回表。

組合索引可避免回表

建立組合索引 idx_comp(created, object_name)，查詢同時包含這兩欄位：

EXPLAIN PLAN FOR
SELECT object_name, COUNT(1)
FROM big_table a
WHERE created >= TO_DATE('2010/3/30 10:07:50','yyyy/mm/dd hh24:mi:ss')
  AND created <= TO_DATE('2010/3/30 10:08:37','yyyy/mm/dd hh24:mi:ss')
GROUP BY object_name;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

計劃僅有 INDEX RANGE SCAN，達到「覆蓋索引」效果，零回表。

組合索引設計常見問答

1. 一張表建幾個索引適當？ 無標準答案，需平衡查詢加速與 DML (INSERT/UPDATE/DELETE) 維護成本。建議從核心 SQL 反推。
2. 聯合索引一定比單欄索引快？ 只有當 WHERE 條件同時包含多個前導欄位，聯合索引才能過濾更少列、減少回表甚至覆蓋索引。若查詢僅用非前導欄位，效率反而不如單欄索引（索引體積較大、掃描成本高）。
3. COMPRESS 壓縮有何用？ 針對前導欄位重複值高的組合索引，COMPRESS n 可大幅縮小索引體積，降低 I/O，但會增加 CPU 解壓開銷。
4. 「前導欄位」與版本有關？ 無關。B*Tree 索引按前導欄位排序，WHERE 條件缺少前導欄位時，優化器只能選擇 INDEX FULL SCAN 或 INDEX SKIP SCAN (10g+)，效率通常較差。
5. REVERSE KEY 索引作用？ 將索引鍵值位元組反轉儲存（如 'ABCD' → 'DCBA'），打散連續插入的熱點區塊，緩解 RAC 環境下的 buffer busy waits / gc buffer busy。不改變前導欄位邏輯，僅改變物理儲存順序。