案例背景
- 環境：Oracle 資料庫 (原文為 Oracle，原理同樣適用 SQL Server)。
- 問題 SQL：雙表聯結 (`BILL_GRANT_CHECK` 732 萬列 + `BILL_CLASS` 206 列)，`WHERE` 含 `IN` 列表 80+ 常數值。
- 原執行計畫：`HASH JOIN` + 兩表 `FULL TABLE SCAN`，Cost **151,000**，預估 30 分鐘。
- 關鍵觀察：結果集 732 萬 / 4.53 億 ≈ 1.6%，極適合索引存取；`IN` 列表為常數，可觸發 **INLIST ITERATOR**。
優化步驟
- 建立虛擬索引 (無段索引) 快速驗證效果，不影響生產：
`CREATE INDEX billgrant_class ON bill_grant_check(bill_class) NOSEGMENT;`
`ALTER SESSION SET "_use_nosegment_indexes"=TRUE;`
- 加入 Hint 強制使用索引並觀察新計畫：
`EXPLAIN PLAN FOR SELECT /*+ INDEX(a billgrant_class) */ ...`
- 新計畫結果：
`MERGE JOIN` → `INLIST ITERATOR` → `TABLE ACCESS BY INDEX ROWID` (BILL_GRANT_CHECK) → `INDEX RANGE SCAN` (虛擬索引)
Cost 從 **151,000 降至 207**，提升 **約 730 倍**。
- 提交真實索引建立申請，上線後持續監控統計資料準確性。
核心知識點
| 概念 | 說明 |
|---|
| 虛擬索引 | Oracle 特有 `NOSEGMENT` 索引，僅存在資料字典，無實體段，配合隱藏參數 `_use_nosegment_indexes` 讓優化器「看到」索引但不實際存取，極適合上線前驗證。 |
| INLIST ITERATOR | 針對 `IN (const1, const2, ...)` 將列表展開為多次 `INDEX RANGE SCAN` 並合併結果，避免全表掃描。SQL Server 同樣支援 (顯示為 `Index Seek` 多次或 `Constant Scan` + `Nested Loops`)。 |
| 選擇性 | 結果集佔表總列數比例。通常 < 5-10% 考慮索引；但需結合聚簇/非聚簇、書籤查找成本綜合判斷。 |
| 統計資料準確性 | Cost 僅在統計資料準確時具參考價值。定期 `UPDATE STATISTICS` / `DBMS_STATS.GATHER_TABLE_STATS` 至關重要。 |
SQL Server 對應實作
-- 1. 建立真實索引 (線上建立避免鎖表)
CREATE NONCLUSTERED INDEX IX_BillGrantCheck_BillClass
ON dbo.Bill_Grant_Check (Bill_Class)
WITH (ONLINE = ON, DATA_COMPRESSION = PAGE);
-- 2. 更新統計資料
UPDATE STATISTICS dbo.Bill_Grant_Check IX_BillGrantCheck_BillClass WITH FULLSCAN;
-- 3. 驗證計畫 (清除快取後)
DBCC FREEPROCCACHE; -- 謹慎使用
SET STATISTICS IO, TIME ON;
SELECT ... FROM ... WHERE Bill_Class IN (...); -- 觀察 Index Seek + IN 列表常數掃描
避坑指南
- **過度索引**：寫入密集表每新增一索引增加 INSERT/UPDATE/DELETE 開銷，權衡讀寫比例。
- **參數嗅探**：若 `IN` 列表來自參數化查詢 (`@list`)，優化器可能無法展開為常數，導致計畫不穩。考慮 `OPTION (RECOMPILE)` 或動態 SQL。
- **隱含轉換**：`Bill_Class` 為 `VARCHAR` 而列表傳入數值常數，會導致 `CONVERT_IMPLICIT` 使索引失效。確保資料類型一致。
