反直覺的 Oracle 優化案例：DISTINCT 導致執行計劃變更反而提升效能

現象：加上 DISTINCT，耗時從 10 分鐘降至 4 分鐘

開發人員發現：在一條複雜查詢的最外層加上 DISTINCT 後，執行時間大幅縮短。這違背「DISTINCT 增加排序成本」的常規認知。

模擬環境與執行計劃對比

建立三張表 T1、T2、T3 並收集統計資訊，對比兩條語句：

-- 原始查詢
SELECT T1.OBJECT_NAME, T1.OBJECT_TYPE, T2.TABLESPACE_NAME
FROM T1, T2
WHERE T1.OBJECT_NAME = T2.SEGMENT_NAME
  AND T1.OBJECT_NAME IN (SELECT INDEX_NAME FROM T3 
                         WHERE T3.TABLESPACE_NAME = T2.TABLESPACE_NAME);

-- 加上 DISTINCT
SELECT DISTINCT T1.OBJECT_NAME, T1.OBJECT_TYPE, T2.TABLESPACE_NAME
FROM T1, T2
WHERE T1.OBJECT_NAME = T2.SEGMENT_NAME
  AND T1.OBJECT_NAME IN (SELECT INDEX_NAME FROM T3 
                         WHERE T3.TABLESPACE_NAME = T2.TABLESPACE_NAME);

執行計劃關鍵差異

根因深度解析

SEMI JOIN 的代價

IN 子查詢觸發 HASH JOIN SEMI：為每個外部行在內部構建雜湊表並探測，若找到匹配即停止。但當外部結果集極大、內部表較小時，重複探測成本高於直接雜湊連接。

DISTINCT 改變優化器決策

加上 DISTINCT 後，優化器預知最終需排序去重，傾向選擇標準 HASH JOIN 產生完整連接結果再排序。此時：

• 連接順序調整：T3 → T2 → T1（較小表優先構建雜湊表）
• 避開 SEMI JOIN 的重複探測開銷
• 雖新增 SORT UNIQUE，但結果集較小（311 行），排序成本極低

適用場景與警示

適用條件：

• 連接表資料量大，但最終結果集較小
• SELECT 欄位數較少
• 排序成本 < SEMI JOIN 探測成本

嚴重警示：此現象源於特定資料分布、約束條件與表結構的組合，**不可作為通用優化手法**。不同環境下兩語句結果可能完全不同（DISTINCT 會去除真實重複行）。優化無銀彈，需以執行計劃與實測為準。