索引效能與維護成本平衡
- B-Tree 索引加速查詢與聯結，提供 PK 唯一性驗證
- 幾乎所有類型欄位可建索引 (含 LONG/LOB 限制)
- 代價：每 INSERT/DELETE/UPDATE 額外 4-5 次磁碟 I/O 維護索引
- 不必要索引反而拖慢查詢 (優化器評估更多路徑)
- 定期重建：
ALTER INDEX idx_name REBUILD TABLESPACE ts_name;
EXISTS 替代 DISTINCT：一對多查詢去重最佳解
-- 低效：DISTINCT 需排序去重
SELECT DISTINCT d.dept_no, d.dept_name
FROM dept d, emp e WHERE d.dept_no = e.dept_no;
-- 高效：EXISTS 滿足即返回，無排序
SELECT d.dept_no, d.dept_name
FROM dept d
WHERE EXISTS (SELECT 1 FROM emp e WHERE e.dept_no = d.dept_no);
- EXISTS 為半聯結，RDBMS 核心模組滿足條件即停止掃描
- 避免大量中間結果集排序，效能顯著優於 DISTINCT
- 語意更清晰：「存在至少一筆符合條件的記錄」
SQL 撰寫規範速查
| 規範 | 理由 | 範例 |
|---|
| 關鍵字大寫 | Oracle 解析時將小寫轉大寫，省轉換開銷 | SELECT * FROM EMP |
| Java 避免 + 串接字串 | StringBuilder 效能優於 + 操作符 | sb.append("SQL") |
| 索引欄避免 NOT | NOT 導致全表掃描，同函數效果 | WHERE status != 'A' → WHERE status IN ('B','C') |
| 索引欄避免計算/函數 | WHERE SAL*12 > 50000 索引失效 | WHERE SAL > 50000/12 |
| 索引欄避免 IS NULL | 單欄索引不存 NULL，無法使用 | 改用 WHERE NVL(col,'X') = 'X' (需函數索引) |
索引失效經典模式
-- 隱含型別轉換
WHERE char_col = 123 -- 實際執行 TO_NUMBER(char_col)=123，索引失效
WHERE num_col = '123' -- Oracle 優先轉數值→字元，索引可能有效
-- 正確寫法：顯式轉換
WHERE char_col = TO_CHAR(123)
WHERE num_col = 123
綜合建議
- 一對多去重首選 EXISTS，非必要不用 DISTINCT
- 定期
ANALYZE INDEX ... VALIDATE STRUCTURE 檢查索引健康度
- 監控
V$SQL_PLAN 確認索引實際被使用
- 建立命名規範：
IDX_表名_欄位、PK_表名、UK_表名_欄位
- 上一篇文章： Oracle HAVING vs WHERE vs ON 深度解析：執行順序、效能差異與改寫技巧
- 下一篇文章： Oracle UNION ALL 實戰指南：替代 OR/IN、IS NULL 陷阱、複合索引首欄原則與排序消除
Copyright ©
程式碼之家 All Rights Reserved
