Oracle SQL 效能優化系列 (五)：減少資料庫往返、DECODE 聚合技巧

每次 SQL 執行伴隨解析、優化、綁定、塊讀取等內部工作。減少往返次數 = 減少總工作量。

7. 減少資料庫往返次數

-- 方法 1：三次往返 (最差)
SELECT ... FROM emp WHERE emp_no = 342;
SELECT ... FROM emp WHERE emp_no = 291;

-- 方法 2：游標迴圈仍兩次往返 (次差)
DECLARE CURSOR c1(e_no NUMBER) IS SELECT ... FROM emp WHERE emp_no = e_no;
BEGIN OPEN c1(342); FETCH ...; OPEN c1(291); FETCH ...; END;

-- 方法 3：單次往返自連接 (最佳)
SELECT a.emp_name, a.salary, a.grade,
       b.emp_name, b.salary, b.grade
FROM emp a, emp b
WHERE a.emp_no = 342 AND b.emp_no = 291;

補充：在 SQL*Plus/Forms/Pro*C 中設定 ARRAYSIZE 200 可增加每次攜回行數。

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')) AS d20_cnt,
       COUNT(DECODE(dept_no, 30, 'X')) AS d30_cnt,
       SUM(DECODE(dept_no, 20, sal))    AS d20_sal,
       SUM(DECODE(dept_no, 30, sal))    AS d30_sal
FROM emp
WHERE ename LIKE 'SMITH%';

同樣技巧適用於 GROUP BY、ORDER BY 子句。

「一次掃描、多維聚合」是大數據報表查詢的核心優化模式，DECODE/CASE 皆可實現。