反直覺的優化：外層加上 DISTINCT 反而大幅降低邏輯讀的原理解析

現象：DISTINCT 並非總是拖累效能

一般認知中，DISTINCT 意味著排序去重，會增加 CPU 與臨時表空間開銷。但實戰案例發現：某複雜查詢加上 DISTINCT 後，執行時間從 10+ 分鐘降至 4 分鐘。經驗證，DISTINCT 位於最外層，非子查詢。

模擬實驗環境

CREATE TABLE t1 AS SELECT * FROM dba_objects WHERE owner='SYS' AND object_type NOT LIKE '%BODY' AND object_type NOT LIKE 'Java%';
CREATE TABLE t2 AS SELECT * FROM dba_segments WHERE owner='SYS';
CREATE TABLE t3 AS SELECT * FROM dba_indexes WHERE owner='SYS';
ALTER TABLE t1 ADD CONSTRAINT pk_t1 PRIMARY KEY (object_name);
CREATE INDEX ind_t2_segname ON t2(segment_name);
CREATE INDEX ind_t3_tabname ON t3(table_name);
-- 統計資訊收集含直方圖

兩種查詢對比

Query A：無 DISTINCT

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);

• 執行計畫：HASH JOIN (SEMI) → HASH JOIN → TABLE ACCESS FULL T2/T1/T3
• Consistent Gets: 93
• Rows Returned: 311

Query B：外層加 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);

• 執行計畫：SORT (UNIQUE) → HASH JOIN → HASH JOIN → TABLE ACCESS FULL T3/T2/T1
• Consistent Gets: 72 (降低 22.6%)
• 新增 1 次 Memory Sort
• Rows Returned: 311 (結果集相同)

核心機制：優化器連接方法選擇的差異

優化器預見最終需 SORT UNIQUE，傾向選擇標準 HASH JOIN 產生已部分有序的中間結果，減少後續排序成本；同時避開 SEMI JOIN 的重複探測開銷。

適用條件與風險提示

• 適用： 連接表數據量大、最終結果集小、SELECT 欄位少 → 排序成本 < SEMI JOIN 節省成本。
• 風險： 若結果集大、欄位多，排序可能溢寫磁碟 (Disk Sort) 導致災難性效能。
• 語意差異： 嚴格說來，加不加 DISTINCT 是兩個不同 SQL。本例因主鍵約束、資料特性恰好結果相等，換環境可能結果不同。

啟示

優化沒有絕對真理，「死規則」會誤事。理解優化器成本模型與連接機制，結合具體數據分佈、約束條件、業務語意，才能做出正確抉擇。此案例旨在拋磚引玉，非提供通用調優手法。