PK = ? 應置於末尾。| # | 陷阱 | 失效範例 | 修正寫法 | 原理 |
|---|---|---|---|---|
| 1 | 索引列參與運算/函數 | WHERE sal * 12 > 25000 | WHERE sal > 25000/12 | 函數導致無法使用索引範圍掃描 |
| 2 | 隱式型別轉換落在索引列 | WHERE emp_type = 123 (emp_type 為 VARCHAR2) | WHERE emp_type = '123' 或 TO_CHAR(emp_type) = '123' | Oracle 內部轉換 TO_NUMBER(emp_type) 破壞索引 |
| 3 | IS NULL / IS NOT NULL | WHERE dept_code IS NOT NULL | WHERE dept_code >= 0 (數值) 或 WHERE dept_code IS NOT NULL (配合函數索引) | 單列索引不存 NULL，複合索引全 NULL 不存 |
| 4 | 前導萬用字元 LIKE | WHERE name LIKE '%abc%' | 改用 Oracle Text / Context 索引，或將條件前置 LIKE 'abc%' | B-Tree 無法支援前導萬用字元 |
| 5 | NOT / != / <> 否定條件 | WHERE status != 'CLOSED' | 改寫為 WHERE status IN ('OPEN','PENDING') 或全表掃描 | 否定條件選擇率通常極高，優化器傾向全表掃描 |
| 6 | 複合索引未遵循前導列原則 | 索引 (A,B,C) 查 WHERE B = 1 AND C = 2 | 查詢必須包含前導列 A，或建立 (B,C) 索引 | 索引樹按 (A,B,C) 排序，缺 A 無法定位 |
| 7 | 資料傾斜導致優化器誤判 | 狀態碼 99% 為 'A'，查 WHERE status = 'B' 未用索引 | 建立直方圖 METHOD_OPT => 'FOR COLUMNS SIZE SKEWONLY status' | 無統計資訊時優化器假設均勻分布 |
-- 低效
SELECT DISTINCT d.deptno, d.dname FROM dept d, emp e
WHERE d.deptno = e.deptno AND e.job = 'CLERK';
-- 高效
SELECT d.deptno, d.dname FROM dept d
WHERE EXISTS (SELECT 1 FROM emp e WHERE e.deptno = d.deptno AND e.job = 'CLERK');-- 低效
SELECT * FROM loc WHERE loc_id = 10 OR region = 'TOKYO';
-- 高效 (loc_id、region 均有索引)
SELECT * FROM loc WHERE loc_id = 10
UNION ALL
SELECT * FROM loc WHERE region = 'TOKYO';ORDER BY pk_col 可直接利用索引順序，避免排序操作。-- 低效
SELECT job, AVG(sal) FROM emp GROUP BY job HAVING job = 'MANAGER';
-- 高效
SELECT job, AVG(sal) FROM emp WHERE job = 'MANAGER' GROUP BY job;WITH 子查詢因子化（物化）。EXPLAIN PLAN + DBMS_XPLAN.DISPLAY：查看執行計畫、成本、基數估算。SQL_TRACE + TKPROF：生產環境追蹤真實執行統計。DBMS_SQLTUNE / SQL Tuning Advisor：自動建議索引、改寫、統計資訊收集。V$SQL_PLAN、V$SQLSTATS：歷史執行計畫與統計對比。-- 原始：全表掃描、排序、臨時表
SELECT DISTINCT e.ename, d.dname
FROM emp e, dept d
WHERE e.deptno = d.deptno
AND e.sal > (SELECT AVG(sal) FROM emp WHERE deptno = e.deptno)
ORDER BY e.ename;
-- 優化步驟：
-- 1. 子查詢因子化 (WITH)
-- 2. EXISTS 替代 DISTINCT
-- 3. 確保 deptno、sal 有索引
-- 4. 移除不必要的 ORDER BY (應用層排序)
WITH dept_avg AS (
SELECT deptno, AVG(sal) AS avg_sal FROM emp GROUP BY deptno
)
SELECT e.ename, d.dname
FROM emp e
JOIN dept d ON e.deptno = d.deptno
JOIN dept_avg da ON e.deptno = da.deptno
WHERE e.sal > da.avg_sal
AND EXISTS (SELECT 1 FROM emp e2 WHERE e2.deptno = e.deptno); -- 確保部門有員工
-- 執行計畫：NESTED LOOPS + INDEX RANGE SCAN，Cost 從 120 降至 15Oracle SQL 調優核心在於：理解優化器決策邏輯（CBO）、消除索引失效寫法、善用 EXISTS/UNION ALL/因子化改寫、以執行計畫為依據而非直覺。建立「寫 SQL → 看 Plan → 改寫 → 驗證」閉環習慣，並定期收集統計資訊、監控高耗 SQL，即能持續維持資料庫高效能運行。
