Oracle 索引選擇陷阱：統計資訊缺失導致錯誤索引與三大繞過技巧

當複合索引 IDX_A(a,b,c) 與單欄索引 IDX_B(b) 共存，查詢 WHERE a=? AND b=? AND c=? GROUP BY b 在統計資訊過期/缺失時，優化器可能錯誤選擇 IDX_B 導致全表掃描級效能災難。本文實測展示現象並提供三種繞過方案。

現象重現：統計資訊缺失時的錯誤選擇

-- 建表與索引
CREATE TABLE mytest (a INT, b INT, c INT, d INT);
INSERT INTO mytest SELECT LEVEL, LEVEL, LEVEL, LEVEL FROM dual CONNECT BY LEVEL <= 50000;
CREATE INDEX idx_a ON mytest(a,b,c);
CREATE INDEX idx_b ON mytest(b);
ANALYZE TABLE mytest COMPUTE STATISTICS;

-- 正確統計下：選擇 IDX_A（INDEX RANGE SCAN，COST 2）
SELECT MAX(d) FROM mytest WHERE a=50 AND b=50 AND c=50 GROUP BY b;

-- 刪除索引統計
ANALYZE INDEX idx_a DELETE STATISTICS;
ANALYZE INDEX idx_b DELETE STATISTICS;

-- 錯誤統計下：選擇 IDX_B（FILTER A,C + INDEX RANGE SCAN B，同 COST 但實際慢 10 倍+）
SELECT MAX(d) FROM mytest WHERE a=50 AND b=50 AND c=50 GROUP BY b;

根因：無統計資訊時，優化器預設選擇性預估為 1%，單欄索引 IDX_B 看似成本較低，實則需大量 TABLE ACCESS BY INDEX ROWID 過濾 a,c。

三大繞過技巧

技巧 1：改寫 GROUP BY 表達式（零侵入、無 Hint）

-- 數字型欄位
SELECT MAX(d) FROM mytest WHERE a=50 AND b=50 AND c=50 GROUP BY b+0;

-- 字串型欄位
SELECT MAX(d) FROM mytest WHERE a=50 AND b=50 AND c=50 GROUP BY b||'';

原理：改寫後 GROUP BY 運算式不再單純匹配 IDX_B 的前導欄，優化器傾向選擇涵蓋 a,b,c 的 IDX_A 進行 INDEX RANGE SCAN + SORT GROUP BY NOSORT。

技巧 2：NO_INDEX Hint 明確禁用錯誤索引

SELECT /*+ NO_INDEX(mytest idx_b) */ MAX(d)
FROM   mytest
WHERE  a=50 AND b=50 AND c=50
GROUP BY b;

優點：語意明確、易維護；缺點：硬編碼索引名，索引重建/改名需同步修改 SQL。

技巧 3：補齊統計資訊（根治）

EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>'SCOTT', tabname=>'MYTEST', cascade=>TRUE, method_opt=>'FOR ALL COLUMNS SIZE AUTO');

建議：啟用自動統計收集 AUTOSTATS_TARGET，或排程每日 DBMS_STATS.GATHER_DATABASE_STATS。11g+ 預設開啟自動任務，僅需確認維護窗口未被關閉。

效能對比實測（5 萬筆測試資料）

實務建議

1. 優先補統計：治本，解決所有受影響 SQL。
2. 次用 GROUP BY 改寫：無 Hint 風險、不變更應用邏輯、立即生效。
3. 最後用 Hint：針對無法改寫 SQL（封裝套件）的緊急修復。
4. 監控視圖：V$SQL_PLAN 觀察 OPERATION=INDEX RANGE SCAN 的 OBJECT_NAME 是否為預期索引。

掌握「統計資訊缺失 → 錯誤索引 → 改寫/ Hint/補統計」完整鏈路，能在不重啟、不改參數的情況下，秒級修復生產環境效能劣化事件。