Oracle ORDER BY 性能優化：子查詢轉 LEFT JOIN + 分組聚合

原始慢 SQL（10 分鐘）

SELECT A.aId,
       (SELECT COUNT(1) FROM B WHERE B.aId = A.aId) c
FROM A
ORDER BY c;

表 A 30 萬行，表 B 18 萬行。去掉 ORDER BY 僅 2 秒。

問題分析

• 相關子查詢在 SELECT 列表中，對 A 每行執行一次掃描 B（或索引範圍掃描），產生 30 萬次子查詢開銷。
• ORDER BY c 強制對所有行排序，需先物化子查詢結果，放大開銷。

優化重寫（10 秒內）

SELECT A.aId, NVL(B.cnt, 0) AS c
FROM A
LEFT JOIN (
    SELECT aId, COUNT(1) AS cnt
    FROM B
    GROUP BY aId
) B ON A.aId = B.aId
ORDER BY B.cnt;

優化原理

1. 分組聚合預計算：子查詢 SELECT aId, COUNT(1) FROM B GROUP BY aId 僅掃描 B 一次，產生 aId → cnt 映射（約幾萬行）。
2. 哈希連接/排序合併連接：A 與聚合結果連接，線性複雜度 O(N+M)。
3. 排序較小結果集：連接後行數 = A 行數（30 萬），但已獲得 cnt 列，排序無需再執行子查詢。

索引建議

• CREATE INDEX idx_b_aid ON B(aId); 加速分組聚合（可選，全表掃描 18 萬行通常已足快）。
• CREATE INDEX idx_a_aid ON A(aId); 如 A 非主鍵。

驗證效果

EXPLAIN PLAN FOR ...;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

觀察 HASH JOIN / SORT GROUP BY / SORT ORDER BY，無 FILTER / NESTED LOOPS 子查詢特徵。