Oracle 多表查詢深度優化：從執行計畫到實戰技巧全解析

核心優化原則：WHERE 子句解析順序

Oracle 採用自下而上的關聯順序解析 WHERE 子句。因此：

1. 表連接條件必須寫在其他 WHERE 條件之前
2. 過濾掉最大數量記錄的條件寫在 WHERE 子句末尾（最後執行，先過濾大量資料）

實戰案例對比

低效寫法（156.3 秒）

SELECT ...
FROM EMP E
WHERE SAL > 50000
  AND JOB = 'MANAGER'
  AND 25 < (SELECT COUNT(*) FROM EMP WHERE MGR = E.EMPNO);

高效寫法（10.6 秒，提升 14 倍）

SELECT ...
FROM EMP E
WHERE 25 < (SELECT COUNT(*) FROM EMP WHERE MGR = E.EMPNO)
  AND SAL > 50000
  AND JOB = 'MANAGER';

原理：子查詢先過濾掉大部分非管理者記錄，再套用薪資與職稱條件，大幅減少掃描量。

關鍵優化技巧

1. 避免 SELECT *

動態 SQL 列引用 * 會在解析階段查詢資料字典轉換所有欄位名，增加 CPU 與 I/O 開銷。永遠明確列出所需欄位。

2. 減少資料庫存取次數

每條 SQL 執行伴隨：解析、索引利用率估算、綁定變數、讀取資料塊等內部工作。

最低效（多次往返）

SELECT EMP_NAME, SALARY, GRADE FROM EMP WHERE EMP_NO = 342;
SELECT EMP_NAME, SALARY, GRADE FROM EMP WHERE EMP_NO = 291;

次低效（游標迴圈）

DECLARE
  CURSOR C1(E_NO NUMBER) IS SELECT EMP_NAME,SALARY,GRADE FROM EMP WHERE EMP_NO = E_NO;
BEGIN
  OPEN C1(342); FETCH C1 INTO ...;
  OPEN C1(291); FETCH C1 INTO ...;
  CLOSE C1;
END;

最高效（單次查詢 + IN 列表）

SELECT EMP_NAME, SALARY, GRADE FROM EMP WHERE EMP_NO IN (342, 291);

單次網路往返、單次解析、單次執行計畫生成，效能最優。

綜合建議

• 使用 EXPLAIN PLAN 分析執行計畫，關注 CARDINALITY 與 COST
• 善用綁定變數減少硬解析
• 適當建立複合索引支援多欄位查詢
• 大表關聯時考慮分區、物化視圖或暫存表中間結果