Oracle SQL 優化核心技巧：WHERE 代替 ORDER BY、類型轉換、索引失效規避

1. 用 WHERE 替代 ORDER BY 利用索引

ORDER BY 僅在嚴格條件下使用索引：列含於同一索引且順序一致、列定義 NOT NULL、WHERE 與 ORDER BY 不共用索引。

-- 低效：DEPT_TYPE 為 NULL 且無索引
SELECT DEPT_CODE FROM DEPT ORDER BY DEPT_TYPE;

-- 高效：利用索引範圍掃描
SELECT DEPT_CODE FROM DEPT WHERE DEPT_TYPE > 0;

2. 避免索引列發生隱式類型轉換

• 數值列比較字符串：EMPNO = '123' → Oracle 內部轉為 TO_NUMBER('123')，**索引未失效** (轉換發生在常量側)
• 字符列比較數值：EMP_TYPE = 123 → 轉為 TO_NUMBER(EMP_TYPE)=123，**索引失效** (轉換發生在列側)

原則：顯式轉換常量，保持列原樣：WHERE EMP_TYPE = TO_CHAR(123)。

3. 導致索引失效的 WHERE 寫法

4. 索引選擇性閾值

• 檢索量 > 30% 表行數 → 索引無優勢，全表掃描更快
• 但通常情況索引快「幾倍到幾千倍」，非特殊場景優先信任索引

5. 避免昂貴排序操作

DISTINCT、UNION、MINUS、INTERSECT、ORDER BY 觸發 SORT。DISTINCT 一次排序，其餘至少兩次。可重寫：

-- 低效
SELECT ... UNION SELECT ...

-- 高效 (業務允許重複時)
SELECT ... UNION ALL SELECT ...

6. GROUP BY 提前過濾

-- 低效：全表聚合再過濾
SELECT JOB, AVG(SAL) FROM EMP GROUP BY JOB HAVING JOB IN ('PRESIDENT','MANAGER');

-- 高效：先過濾再聚合
SELECT JOB, AVG(SAL) FROM EMP WHERE JOB IN ('PRESIDENT','MANAGER') GROUP BY JOB;

7. 綜合優化清單

1. SQL 關鍵字大寫 (減少解析開銷)
2. Java 中少用 + 拼接 SQL，用 StringBuilder 或綁定變量
3. 索引列不加 NOT、不計算、不函數
4. >= 替代 > (直接定位首行)
5. 索引列 OR 改 UNION / IN
6. 復合索引必須引用**首列** (Leading Column)
7. 定期 ALTER INDEX ... REBUILD 消除碎片