Oracle SQL 語句優化實戰：從索引原理到 11 條黃金法則

索引核心認知

• Oracle 採用 **自平衡 B-Tree** 結構，索引掃描通常快於全表掃描
• 優化器在查詢/更新路徑選擇、多表聯結時自動評估索引使用
• 索引提供主鍵唯一性驗證；除 LONG/LOB 外幾乎所有列可建索引
• 代價：佔用空間、DML 維護開銷 (每增刪改約 4-5 次額外 I/O)、無用索引拖慢優化器
• 定期重建：ALTER INDEX REBUILD

11 條黃金優化法則

1. EXISTS 替代 DISTINCT (一對多查詢)

   -- 低效
   SELECT DISTINCT D.DEPT_NO, D.DEPT_NAME
   FROM DEPT D, EMP E WHERE D.DEPT_NO = E.DEPT_NO;
   
   -- 高效：滿足即返回，避免排序去重
   SELECT D.DEPT_NO, D.DEPT_NAME
   FROM DEPT D
   WHERE EXISTS (SELECT 'X' FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO);

2. SQL 關鍵字大寫：減少解析階段大小寫轉換開銷。
3. Java 中慎用 "+" 拼接 SQL：用 StringBuilder 或綁定變量。
4. 索引列禁用 NOT：NOT 導致全表掃描，改用 <> 或範圍查詢。
5. 索引列禁用計算/函數

   -- 低效
   SELECT * FROM DEPT WHERE SAL * 12 > 25000;
   -- 高效：常量側計算
   SELECT * FROM DEPT WHERE SAL > 25000/12;

6. >= 替代 >

   -- 高效：直接定位 DEPTNO=4 首行
   SELECT * FROM EMP WHERE DEPTNO >= 4;
   -- 低效：先定位 3 再掃描
   SELECT * FROM EMP WHERE DEPTNO > 3;

7. 索引列 OR 改 UNION (雙列均有索引時)

   -- 高效
   SELECT * FROM LOC WHERE LOC_ID = 10
   UNION
   SELECT * FROM LOC WHERE REGION = 'MELBOURNE';
   
   -- 低效：全表掃描
   SELECT * FROM LOC WHERE LOC_ID = 10 OR REGION = 'MELBOURNE';

8. IN 替換多個 OR

   SELECT * FROM LOC WHERE LOC_ID IN (10,20,30);

   Oracle 8i+ 內部常自動轉換，但寫 IN 可讀性更好。

9. 索引列禁用 IS NULL/IS NOT NULL

   -- 低效：索引失效
   SELECT * FROM DEPT WHERE DEPT_CODE IS NOT NULL;
   -- 高效：業務主鍵通常 >= 0
   SELECT * FROM DEPT WHERE DEPT_CODE >= 0;

   補充：唯一索引允許多行全 NULL，但任一列非 NULL 則記錄入索引。

10. 復合索引必須引用首列：僅引用第二列時優化器忽略索引，全表掃描。
11. UNION ALL 替代 UNION (允許重複時)

    -- 高效：免排序
    SELECT ... FROM ... WHERE TRAN_DATE = '31-DEC-95'
    UNION ALL
    SELECT ... FROM ... WHERE TRAN_DATE = '31-DEC-95';

    UNION 需排序佔用 SORT_AREA_SIZE，UNION ALL 直接合併結果集。

排序內存優化輔助查詢

-- 查看排序內存消耗
SELECT * FROM V$SORT_USAGE;
SELECT * FROM V$PARAMETER WHERE NAME = 'SORT_AREA_SIZE';