SELECT a.bill_class AS pol_code, b.bill_name AS pol_name, ... -- 多欄位
FROM shcvms.bill_grant_check a, shcvms.bill_class b
WHERE a.regist_date IS NOT NULL
AND a.bill_class = b.bill_class
AND a.bill_class IN ('1001','1093',...共 80+ 個常數);
執行時間:~40 秒。執行計畫:Hash Join + 兩張表全表掃描(BILL_GRANT_CHECK 732 萬行,453MB)。
BILL_GRANT_CHECK 732 萬行,滿足 IN 列表的約 1.6%(11.7 萬行),屬於高選擇度篩選,極適合索引求精。IN 列表為常數,最佳化工具可轉為 IN-list Iterator 逐一索引查找。EXPLAIN PLAN 結果可信。-- 1. 建立虛擬索引(不佔空間、不維護、僅供最佳化工具參考)
CREATE INDEX billgrant_class ON shcvms.bill_grant_check(bill_class) NOSEGMENT;
-- 2. 啟用虛擬索引(Session 級別)
ALTER SESSION SET "_use_nosegment_indexes" = TRUE;
-- 3. 加 HINT 強制使用並觀察計畫
EXPLAIN PLAN FOR
SELECT /*+ INDEX(a billgrant_class) */ ...
FROM shcvms.bill_grant_check a, shcvms.bill_class b
WHERE a.regist_date IS NOT NULL
AND a.bill_class = b.bill_class
AND a.bill_class IN (...);
Plan hash value: 2116188717
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 117K | 10M | 1234 (2) | 00:00:15 |
| 1 | NESTED LOOPS | | | | | |
| 2 | INLIST ITERATOR | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| BILL_GRANT_CHECK | 117K | 10M | 1234 (2) | 00:00:15 |
|* 4 | INDEX RANGE SCAN | BILLGRANT_CLASS | 117K | | 123 (1) | 00:00:02 |
| 5 | TABLE ACCESS BY INDEX ROWID| BILL_CLASS | 1 | 25 | 1 (0) | 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | BILL_CLASS_PK | 1 | | 0 (0) | 00:00:01 |
CREATE INDEX billgrant_class ON shcvms.bill_grant_check(bill_class);DBMS_STATS.GATHER_TABLE_STATS('SHCVMS','BILL_GRANT_CHECK');_use_nosegment_indexes 僅供測試,生產嚴禁開啟。IN 列表過長(>1000)可能超過解析限制,改用臨時表 JOIN 或 EXISTS。