Oracle SQL 性能優化進階 26 條鐵律 (基礎表選擇、多索引合併、等值/範圍、強制失效、計算欄位、UNION 替代 OR)

16. 基礎表選擇策略

• CBO 模式：依統計資訊 (表大小、索引狀態) 選擇成本最低路徑。
• RBO 模式：若所有連接列均有索引，**FROM 子句最後一張表**為基礎表 (驅動表)。
• 範例：`FROM WORKER A, LODGING B WHERE A.LODGING = B.LODING`，`LODGING` 有索引、`WORKER` 無 → `WORKER` 成基礎表 (全表掃描)，再回表 `LODGING`。

17. 多個等價索引合併 (AND-EQUAL)

• 多表多索引時，Oracle 可同時掃描多索引並 **合併 ROWID (AND-EQUAL)** 取交集。
• 優先級：唯一索引 > 非唯一索引 (僅限索引列與常量比較)。
• 同表同級索引：WHERE 子句先出現者優先；跨表同級：FROM 最後一張表索引優先。

18. 等值 vs 範圍比較

• 混合等值與範圍時，**僅等值索引生效**，範圍條件退化為事後過濾。
• 範例：`DEPTNO > 20 AND EMP_CAT = 'A'` → 僅 `EMP_CAT` 索引掃描，`DEPTNO` 逐行過濾。

19. 不明確索引等級 → 僅用 WHERE 首列索引

• 無法判斷優劣 (如均為範圍) 時，優化器**只使用 WHERE 子句第一個索引列**。
• 範例：`DEPTNO > 20 AND EMP_CAT > 'A'` → 僅 `DEPTNO` 索引。

20. 強制索引失效技巧 (慎用)

• 在索引列上加無意義運算：
  `DEPTNO + 0 = 10` → `DEPTNO` 索引失效
  `EMP_TYPE || '' = 'A'` → `EMP_TYPE` 索引失效
• 適用場景：某索引極不選擇性 (幾千重複值) 導致合併排序開銷大，欲屏蔽該索引強制走另一高選擇性索引。

21. 避免索引列上計算 (核心鐵律)

**索引列參與任何函數/運算 → 全表掃描**。

22. 自動選擇唯一索引忽略非唯一

• 同表存在唯一 + 非唯一索引且均滿足條件時，**唯一索引絕對優先**，非唯一被忽略。

23. 避免索引列用 NOT / != / <>

• `NOT`、`!=`、`<>` 會導致索引失效 → 全表掃描。
• 改寫：`DEPT_CODE != 0` → `DEPT_CODE > 0` (若業務允許)。
• 優化器會自動轉換：`NOT >` → `<=`，`NOT >=` → `<`，`NOT <` → `>=`，`NOT <=` → `>`。

24. 用 >= 替代 > (微優化)

• `DEPTNO >= 4` 直接定位到 4；`DEPTNO > 3` 先定位 3 再掃描到第一個 >3。

25. UNION 替代 OR (索引列必須)

• 多索引列 `OR` 會導致全表掃描；拆分為 `UNION` 可分別走索引再合併。
• 前提：**所有 OR 條件列均有索引**；若有無索引列，`UNION` 反而可能更慢。
• 測試數據證實：`UNION` 版 `consistent gets` 從 144 降至 10，網路傳輸量顯著下降。

26. OR 條件順序優化 (不得不用 OR 時)

• 返回行數最少的索引列寫在最前：`WHERE KEY1=10 (少) OR KEY2=20 (多)`。
• 內部轉換邏輯：`KEY1=10 AND ((NOT KEY1=10) AND KEY2=20)`。