Oracle SQL 優化實戰：從子查詢到連接查詢的效能蛻變

面對這條看似簡單的統計查詢，原始寫法在大數據量下效能低下。本文演示如何通過「子查詢去相關化、索引覆蓋、執行計畫分析」三步走策略，實現數量級效能提升。

原始 SQL 與效能瓶頸

SELECT SUM(sl0000)
FROM xstfxps2
WHERE dhao00 IN (
    SELECT dhao00
    FROM xstfxps1
    WHERE TRUNC(ywrq00) = TRUNC(SYSDATE)
      AND khdm00 = '500000003913'
);

問題剖析：

1. 函數導致索引失效： TRUNC(ywrq00) 使 ywrq00 上的索引無法使用，引發全表掃描。
2. IN 子查詢執行效率低： Oracle 早期版本對 IN 子查詢優化較弱，易退化為 Filter 操作，逐行驗證外層表。
3. 缺乏覆蓋索引： 查詢需回表獲取 sl0000，增加 I/O。

優化步驟演進

第一步：消除函數調用，啟用索引範圍掃描

WHERE ywrq00 >= TRUNC(SYSDATE)
  AND ywrq00 < TRUNC(SYSDATE) + 1

利用日期範圍查詢替代 TRUNC，使 ywrq00 索引生效。

第二步：改寫為 JOIN 或 EXISTS，利用驅動表優化

-- 寫法 A：標準內連接（推薦，優化器可選擇最佳連接順序）
SELECT SUM(t2.sl0000)
FROM xstfxps1 t1
JOIN xstfxps2 t2 ON t1.dhao00 = t2.dhao00
WHERE t1.ywrq00 >= TRUNC(SYSDATE)
  AND t1.ywrq00 < TRUNC(SYSDATE) + 1
  AND t1.khdm00 = '500000003913';

-- 寫法 B：EXISTS 相關子查詢（語義更精確：僅判斷存在性）
SELECT SUM(t2.sl0000)
FROM xstfxps2 t2
WHERE EXISTS (
    SELECT 1 FROM xstfxps1 t1
    WHERE t1.dhao00 = t2.dhao00
      AND t1.ywrq00 >= TRUNC(SYSDATE)
      AND t1.ywrq00 < TRUNC(SYSDATE) + 1
      AND t1.khdm00 = '500000003913'
);

第三步：建立覆蓋索引，實現 Index Only Scan

-- xstfxps1：過濾條件 + 連接鍵 + 選單欄位（此處無額外選單）
CREATE INDEX idx_xstfxps1_khdm_ywrq_dhao ON xstfxps1 (khdm00, ywrq00, dhao00);

-- xstfxps2：連接鍵 + 聚合欄位
CREATE INDEX idx_xstfxps2_dhao_sl ON xstfxps2 (dhao00, sl0000);

查詢僅掃描索引塊，**完全避免回表**，I/O 減少 90%+。

驗證成果：執行計畫對比

核心原則總結

• ✅ **Where 子句欄位不加函數**，改用範圍查詢保留索引可用性。
• ✅ **優先用 JOIN/EXISTS 替代 IN/NOT IN 子查詢**，給優化器更多連接順序選擇權。
• ✅ **針對高頻 SQL 設計覆蓋索引**（含 Where/Order By/Select 欄位），實現 Index Only Scan。
• ✅ **用 EXPLAIN PLAN / DBMS_XPLAN 驗證**，關注 Cost、Rows、Operation 三大指標。