Oracle SQL 效能調優系列 (三)：DECODE 技巧、查詢合併、刪除重複、TRUNCATE 與 COMMIT 策略

8. 善用 DECODE/CASE 進行條件彙總，避免重複掃描

反模式： 多條 SQL 分別統計不同部門

SELECT COUNT(*), SUM(sal) FROM emp WHERE deptno=20 AND ename LIKE 'SMITH%';
SELECT COUNT(*), SUM(sal) FROM emp WHERE deptno=30 AND ename LIKE 'SMITH%';

優化： 單次掃描搭配條件彙總

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

註：DECODE 回傳 NULL 時 SUM 會忽略，故 ELSE 宜給 0 而非 NULL。

9. 合併無關聯的簡單查詢

利用 DUAL 與 OUTER JOIN 將多個單列查詢合為一：

SELECT e.ename, d.dname, c.cat_name
FROM emp e, dept d, category c, 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.empno(+) = 1234
  AND d.deptno(+) = 10
  AND c.cat_type(+) = 'RD';

權衡： 減少網路往返與解析開銷，但降低可讀性，視情況使用。

10. 高效刪除重複資料

利用 ROWID 保留最小/最大值：

DELETE FROM emp e
WHERE e.rowid > (SELECT MIN(x.rowid) FROM emp x WHERE x.empno = e.empno);

11. TRUNCATE 取代 DELETE 清空大表

適用： 清空整張表、重建測試資料、ETL 階段性清空。

12. 適時 COMMIT 釋放資源

COMMIT 釋放：

• UNDO 區段 (Rollback Segment)
• Row Lock / Table Lock
• Redo Log Buffer 空間
• 內部管理記憶體

注意： 交易完整性優先，不可為效能犧牲資料一致性。批次作業中每 N 筆 COMMIT 一次為宜。

綜合建議

1. 優先用條件彙總 (DECODE/CASE) 替代多重掃描
2. 大量刪除用分批 COMMIT 或 TRUNCATE/PARTITION DROP
3. 合併獨立查詢需評估可讀性成本
4. 監控 V$SESSION_LONGOPS、V$SQL_WORKAREA 確認效果