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%';

-- 高效：單次掃描，條件聚合
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%';

`DECODE` 亦可用於 `GROUP BY`、`ORDER BY` 實現動態分組排序。注意：`SUM(DECODE(...,NULL))` 全為 NULL 時結果為 NULL，建議 `DECODE(...,0)` 確保數值型回傳 0。

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

利用 `DUAL` 與 `OUTER JOIN` 將多個單表查詢合併：

SELECT E.NAME, D.NAME, C.NAME
FROM EMP E, DEPT D, CAT 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.EMP_NO(+) = 1234
  AND D.DEPT_NO(+) = 10
  AND C.CAT_TYPE(+) = 'RD';

權衡：減少 Parse/Execute 往返，但可讀性大幅下降，維護成本高。建議僅用於極高頻、極簡單的獨立查詢合併。

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

DELETE FROM EMP E
WHERE E.ROWID > (SELECT MIN(X.ROWID)
                 FROM EMP X
                 WHERE X.EMP_NO = E.EMP_NO);

原理：同鍵值群組中保留最小 ROWID (最早插入)，刪除其餘。適用於無主鍵/唯一索引的臨時清理。大表建議 `CTAS` 重建或 `PARTITION` 交換。

11. 以 TRUNCATE 取代 DELETE (全表清空)

限制：`TRUNCATE` 僅適用全表清空，不可帶 `WHERE`；受外鍵參考時需先 `DISABLE CONSTRAINT`。

12. 適度頻繁 COMMIT 釋放資源

每次 `COMMIT` 釋放：

• Undo Segment 中的回復資訊。
• 持有的鎖 (Row/Table Lock)。
• Redo Log Buffer 空間。
• 管理上述資源的內部記憶體/CPU 開銷。

警示：過度 Commit 破壞交易原子性，導致「部分成功」資料不一致。批次處理建議「每 N 筆 Commit」或「邏輯業務單元 Commit」，並設計可重入/冪等機制應對失敗重跑。

補充：DECODE 聚合 NULL 值處理修正

-- 原文潛在問題：SUM(DECODE(DEPT_NO,20,SAL,NULL)) 全 NULL 回傳 NULL
-- 修正：預設值用 0
SUM(DECODE(DEPT_NO,20,SAL,0)) D20_SAL