Oracle SQL 效能調優實戰指南 (下)：進階技巧與避坑清單

6. 避免 SELECT * — 明確列出欄位

SELECT * 強迫 Oracle 查詢 Data Dictionary 解析所有欄位名稱，增加解析開銷；且網路傳輸不必要資料。永遠明確列出需要的欄位。

7. 減少資料庫往返次數 (Round Trips)

每次 SQL 執行皆涉及：解析 → 優化 → 綁定 → 執行 → 擷取。批次處理、陣列擷取可大幅降低開銷。

範例：一次查詢多筆員工

-- 低效：多次往返
SELECT emp_name, salary FROM emp WHERE emp_no = 342;
SELECT emp_name, salary FROM emp WHERE emp_no = 291;

-- 次低效：游標迴圈 (仍多次 FETCH)
DECLARE CURSOR c1(p_no NUMBER) IS SELECT ... FROM emp WHERE emp_no = p_no;
BEGIN OPEN c1(342); FETCH ...; OPEN c1(291); FETCH ...; END;

-- 高效：單次查詢 (或使用 IN / UNION ALL)
SELECT a.emp_name, a.salary, b.emp_name, b.salary
FROM emp a, emp b
WHERE a.emp_no = 342 AND b.emp_no = 291;

參數調整：ARRAYSIZE (SQL*Plus/Forms/Pro*C) 建議設 200，增加每次擷取筆數。

8. 善用 DECODE / CASE 避免重複掃描

單次掃描完成多條件聚合，替代多次查詢同一張表。

-- 低效：掃描 EMP 兩次
SELECT COUNT(*), SUM(sal) FROM emp WHERE dept_no = 20 AND ename LIKE 'SMITH%';
SELECT COUNT(*), SUM(sal) FROM emp WHERE dept_no = 30 AND ename LIKE 'SMITH%';

-- 高效：單次掃描
SELECT 
  COUNT(DECODE(dept_no, 20, 'X')) dept20_cnt,
  COUNT(DECODE(dept_no, 30, 'X')) dept30_cnt,
  SUM(DECODE(dept_no, 20, sal)) dept20_sal,
  SUM(DECODE(dept_no, 30, sal)) dept30_sal
FROM emp WHERE ename LIKE 'SMITH%';

同理適用於 GROUP BY、ORDER BY。

9. 整合無關聯的簡單查詢 (以 DUAL 為樞紐)

將多個獨立查詢合併為單一查詢，減少 Parse/Execute 開銷 (代價：可讀性下降，需權衡)。

SELECT e.name, d.name, c.name
FROM cat c, dpt d, emp e, dual x
WHERE NVL('X', x.dummy) = NVL('X', e.rowid(+))
  AND NVL('X', x.dummy) = NVL('X', d.rowid(+))
  AND NVL('X', x.dummy) = NVL('X', c.rowid(+))
  AND e.emp_no(+) = 1234
  AND d.dept_no(+) = 10
  AND c.cat_type(+) = 'RD';

10. 高效刪除重複資料 (使用 ROWID)

DELETE FROM emp e
WHERE e.ROWID > (SELECT MIN(x.ROWID) FROM emp x WHERE x.emp_no = e.emp_no);

保留每組 emp_no 的第一筆 ROWID，刪除其餘。

11. TRUNCATE 替代 DELETE (全表清空)

• DELETE：產生 Undo/Redo、觸發 Trigger、不釋放 High Water Mark、可 Rollback。
• TRUNCATE：DDL 隱含 Commit、不產生 Undo/Redo、瞬間釋放空間、重設 HWM、不可 Rollback、不觸發 DML Trigger。
• 適用場景：全表重載、暫存表清理、測試資料重置。

12. 適度頻繁 COMMIT — 釋放資源但謹慎權衡

COMMIT 釋放：

• Undo Segment (Rollback Segment) 空間
• 行鎖 (TX Lock)
• Redo Log Buffer 空間
• 管理上述資源的內部記憶體

警示：過度 Commit 破壞交易原子性 (All-or-Nothing)，導致資料不一致。原則：在業務邏輯允許的最小原子單位 Commit (如：單筆訂單完成、批次處理每 1000 筆)。

綜合避坑清單

結語

SQL 調優無銀彈，唯有「理解執行計畫 → 找出瓶頸 → 針對性改寫/索引/統計 → 驗證效果」的科學迴圈。上述 12 項技巧涵蓋寫法、架構、參數、交易管理，配合持續監控 (AWR/ASH/SQL Trace)，即可構建高效能 Oracle 應用系統。

1. 上一篇文章： 透過 OMS (Oracle Management Server) 實現 Oracle 9i 資料備份管理
2. 下一篇文章： Oracle SQL 效能調優實戰指南 (上)：優化器、存取路徑與 SQL 寫法核心原則

Oracle 資料庫基礎

知識管理系統（KMS）資料庫平台選型指南：關係型與文檔型的抉擇與融合

前言：KMS 的雙重資料挑戰 知識管理系統（KMS）需處理兩

HBase 鬆散資料儲存設計入門：資料模型、架構與寫入流程

什麼是鬆散資料？ 介於 Key-Value 與關聯式資料列之

Oracle 互聯網檔案系統 (iFS) 實踐：OracleFilesOnline 全球 4.5 萬用戶部署案例

專案背景與目標 Oracle 內部面臨檔案伺服器分散、版本混

Oracle SQL*Loader 高效導入百萬級數據實戰指南：控制文件、數據格式與常見錯誤處理

什麼是 SQL*Loader SQL*Loader（簡稱 s