在 Oracle 執行計劃中出現 FILTER** 操作往往意味著性能隱患，其行為類似 NESTED LOOPS，會導致重複掃描、邏輯判斷下推失效。本文記錄一次真實優化案例：通過將包含 OR EXISTS 的單條 SQL 拆分為 UNION ALL 兩部分，成功消除 FILTER，將邏輯讀從 10 萬+ 降至 4 千+，耗時從 5 分鐘級壓縮至秒級。
SELECT GGPI.ID, GGPI.PLAN_STATE, ... , GGPI.WORKING
FROM GG_PD_PP_INFO GGPI
WHERE PLAN_STATE IN (50, 60, 70)
AND ((GGPI.PLAN_BEGIN_DATE >= SYSDATE AND GGPI.PLAN_BEGIN_DATE <= SYSDATE)
OR (GGPI.PLAN_END_DATE >= SYSDATE AND GGPI.PLAN_END_DATE <= SYSDATE)
OR (GGPI.PLAN_BEGIN_DATE < SYSDATE AND GGPI.PLAN_END_DATE > SYSDATE))
AND GGPI.BUREAU_CODE = '0306'
AND (GGPI.WORK_MASTER_UID = 'FA3502D1291A4A07A3B9E3E0F9A41904'
OR EXISTS (SELECT 1
FROM GG_PD_PP_WORK_MEMBER PMEM
WHERE PMEM.PROD_PLAN_ID = GGPI.ID
AND PMEM.WORK_MEMBER_UID = 'FA3502D1291A4A07A3B9E3E0F9A41904'
AND PMEM.BUREAU_CODE = '0306'));
關鍵計劃特徵：
GG_PD_PP_INFO 全表掃描（分區表 PARTITION RANGE ALL）GG_PD_PP_WORK_MEMBER 的索引範圍掃描 + 表訪問FILTER ("GGPI"."WORK_MASTER_UID" IS NULL OR EXISTS (...))原 WHERE 條件為 A AND (B OR C)，其中 C 為 EXISTS 子查詢。利用關係代數分配律：
A AND (B OR C) ≡ (A AND B) UNION ALL (A AND C)
拆分後兩部分互斥（通過 WORK_MASTER_UID IS NOT NULL 隱含），避免重複結果，故用 UNION ALL 而非 UNION，省去去重排序。
-- 部分 1：主負責人直接匹配
SELECT GGPI.ID, GGPI.PLAN_STATE, ... , GGPI.WORKING
FROM GG_PD_PP_INFO GGPI
WHERE PLAN_STATE IN (50, 60, 70)
AND ((GGPI.PLAN_BEGIN_DATE >= SYSDATE AND GGPI.PLAN_BEGIN_DATE <= SYSDATE)
OR (GGPI.PLAN_END_DATE >= SYSDATE AND GGPI.PLAN_END_DATE <= SYSDATE)
OR (GGPI.PLAN_BEGIN_DATE < SYSDATE AND GGPI.PLAN_END_DATE > SYSDATE))
AND GGPI.BUREAU_CODE = '0306'
AND GGPI.WORK_MASTER_UID = 'FA3502D1291A4A07A3B9E3E0F9A41904'
UNION ALL
-- 部分 2：成員表關聯匹配
SELECT GGPI.ID, GGPI.PLAN_STATE, ... , GGPI.WORKING
FROM GG_PD_PP_INFO GGPI
WHERE PLAN_STATE IN (50, 60, 70)
AND ((GGPI.PLAN_BEGIN_DATE >= SYSDATE AND GGPI.PLAN_BEGIN_DATE <= SYSDATE)
OR (GGPI.PLAN_END_DATE >= SYSDATE AND GGPI.PLAN_END_DATE <= SYSDATE)
OR (GGPI.PLAN_BEGIN_DATE < SYSDATE AND GGPI.PLAN_END_DATE > SYSDATE))
AND GGPI.BUREAU_CODE = '0306'
AND GGPI.ID IN (SELECT PMEM.PROD_PLAN_ID
FROM GG_PD_PP_WORK_MEMBER PMEM
WHERE PMEM.WORK_MEMBER_UID = 'FA3502D1291A4A07A3B9E3E0F9A41904'
AND PMEM.BUREAU_CODE = '0306');
新計劃亮點：
IND_GGPI_WORK_MASTER_UID 索引範圍掃描 → 表訪問，精準定位主負責人數據；NESTED LOOPS 驅動表為 GG_PD_PP_WORK_MEMBER（走 IND_GGPW_WORK_MEMBER_UID 索引），再回主表走主鍵 PK_GG_PD_PP_INFO 唯一掃描；SORT UNIQUE（Id 6）保證成員表去重，避免重複關聯；| 指標 | 優化前 | 優化後 | 提升倍數 |
|---|---|---|---|
| Consistent Gets | 103,763 | 4,321 | 24x |
| Elapsed Time | 00:05:09 | 00:00:45 | 6.9x |
| Execution Plan | FILTER + 全掃 | UNION-ALL + 索引驅動 | 質的飛躍 |
OR 條件拆分為互斥的 UNION ALL 分支，使優化器能為每分支單獨選擇最優訪問路徑；WORK_MASTER_UID、WORK_MEMBER_UID）有可用索引；IN 子句前加 SORT UNIQUE 或改用 EXISTS；OR EXISTS / OR IN 模式；UNION ALL（結果集互斥或可接受去重）。這次優化再次驗證：SQL 重寫往往比調參數、加 Hint 更本質、更穩健。遇到 FILTER 不妨先嘗試邏輯拆分，往往能收獲意想不到的性能紅利。
