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

8. DECODE 函數減少重複掃描

-- 兩次掃描
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%';

-- 單次掃描用 DECODE
SELECT 
  COUNT(DECODE(dept_no,20,'X')) d20_cnt,
  COUNT(DECODE(dept_no,30,'X')) d30_cnt,
  SUM(DECODE(dept_no,20,sal)) d20_sal,
  SUM(DECODE(dept_no,30,sal)) d30_sal
FROM emp WHERE ename LIKE 'SMITH%';

同理適用於 GROUP BY、ORDER BY。

9. 整合無關聯簡單查詢

-- 三個獨立查詢
SELECT name FROM emp WHERE emp_no=1234;
SELECT name FROM dept WHERE dept_no=10;
SELECT name FROM cat WHERE cat_type='RD';

-- 合併為一（利用 DUAL 外部連接）
SELECT e.name, d.name, c.name
FROM cat c, dept 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. 最高效刪除重複記錄

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

利用 ROWID 物理定位，避免全表掃描排序。

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

• DELETE：產生 UNDO、可回滾、觸發 Trigger、不重置 HWM
• TRUNCATE：DDL、隱性提交、無 UNDO、極快、重置 HWM、不觸發 Trigger
• 選項：DROP STORAGE (預設釋放空間) / REUSE STORAGE (保留 Extent)

12. 適時 COMMIT 釋放資源

COMMIT 釋放：UNDO 區段、鎖、Redo Log Buffer 空間、內部管理記憶體。

警示： 必須在交易完整性允許範圍內使用，效率與一致性往往需取捨。

讀者補充修正

DECODE 配合 SUM 時，NULL 值不參與加總。建議改用 SUM(DECODE(dept_no,20,sal,0)) 確保數值型回傳 0 而非 NULL。