Oracle SQL 效能優化進階篇：五大核心原則與實戰技巧

原則一：拒絕 SELECT * — 顯式投影節省全鏈路資源

SELECT * 解析階段需查詢數據字典展開所有欄位，增加解析 CPU；網路傳輸、客戶端緩衝區、伺服器 PGA 皆承擔無用欄位開銷。鐵律： 應用程式與視圖定義中**永遠顯式列出所需欄位**。

原則二：減少資料庫往返 — 批次化、集合操作、PL/SQL 封裝

每條 SQL 執行觸發：解析 → 優化 → 執行計畫生成 → 綁定 → 執行 → Fetch。
優化手段：

• 多條獨立 DML 合併為 FORALL 批次繫結。
• 邏輯相關查詢合併為單一 SQL (集合運算/分析函數)。
• 高頻簡單邏輯下沉至 PL/SQL 存儲過程，減少網路 RTT。

原則三：善用 DECODE / CASE 實現單掃描多聚合

場景：需統計部門 20 與 30 的員工數與薪資和。
反模式： 兩條獨立 SELECT COUNT(*), SUM(sal) ... WHERE deptno=20 / deptno=30 — **掃描 EMP 兩次**。
正解：

SELECT COUNT(DECODE(deptno, 20, 'X')) d20_cnt,
       COUNT(DECODE(deptno, 30, 'X')) d30_cnt,
       SUM(DECODE(deptno, 20, sal)) d20_sal,
       SUM(DECODE(deptno, 30, sal)) d30_sal
FROM emp
WHERE ename LIKE 'SMITH%';

**單次全表掃描** 完成四項聚合。同理適用於 GROUP BY、ORDER BY 分組排序邏輯。

原則四：TRUNCATE 替代 DELETE — 清空表的極速之道

適用： 清空整表、重載數據倉儲暫存表。**不可用於需保留部分行或需觸發器場景。**

原則五：頻繁 COMMIT — 釋放資源、縮短鎖持有、降低回滾風險

每次 COMMIT 釋放：

1. Undo Segment 中的回滾資訊 (縮小回滾段壓力)。
2. 行級/表級鎖 (提升併發)。
3. Redo Log Buffer 空間 (降低 LGWR 壓力)。
4. 內部管理結構 (Enqueue、ITL Slot 等)。

策略： 批次處理迴圈中每 N 筆 (如 1,000~10,000) COMMIT 一次；長交易拆分為多個短交易。**注意：業務邏輯允許部分提交時才可用，否則破壞原子性。**