Oracle AskTOM SQL 重写案例集

来源：AskTOM (asktom.oracle.com) 适用版本：Oracle Database 全版本 文档版本：v1.0 / 2026-07-22

---

1. 概述

AskTOM 经典 SQL 重写案例，体现 Tom Kyte “能用 SQL 就用 SQL”的理念[1]。

详细见：Oracle SQL 调优最佳实践。

---

2. 案例 1：逐行 vs 集合

2.1 反例

-- slow-by-slow
DECLARE
  CURSOR c IS SELECT id, salary FROM emp;
BEGIN
  FOR r IN c LOOP
    IF r.salary > 5000 THEN
      UPDATE emp SET bonus = r.salary * 0.1 WHERE id = r.id;
    END IF;
  END LOOP;
END;
/

2.2 Tom 重写

UPDATE emp SET bonus = salary * 0.1 WHERE salary > 5000;

2.3 性能

- 反例：100 秒
- 重写：1 秒
- 差距 100 倍

---

3. 案例 2：NOT IN vs NOT EXISTS

3.1 NOT IN

SELECT * FROM emp 
WHERE deptno NOT IN (SELECT deptno FROM dept WHERE loc='NY');

3.2 NOT EXISTS

SELECT * FROM emp e 
WHERE NOT EXISTS (
  SELECT 1 FROM dept d 
  WHERE d.deptno = e.deptno AND d.loc='NY'
);

3.3 Tom 分析

- NOT IN 处理 NULL 复杂
- NOT EXISTS 通常更快
- NULL 处理是关键

---

4. 案例 3：行转列

4.1 需求

- 按部门统计人数
- 列显示

4.2 Tom 方案

-- 11g+ PIVOT
SELECT * FROM (
  SELECT deptno, job FROM emp
)
PIVOT (
  COUNT(*) FOR job IN ('CLERK','SALESMAN','MANAGER')
);

4.3 旧版本

SELECT deptno,
  SUM(CASE WHEN job='CLERK' THEN 1 ELSE 0 END) AS clerks,
  SUM(CASE WHEN job='SALESMAN' THEN 1 ELSE 0 END) AS salesmen
FROM emp GROUP BY deptno;

---

5. 案例 4：Top-N 查询

5.1 错误

-- 不能保证顺序
SELECT * FROM emp WHERE ROWNUM <= 5;

5.2 Tom 正确

SELECT * FROM (
  SELECT * FROM emp ORDER BY salary DESC
) WHERE ROWNUM <= 5;

5.3 12c+

SELECT * FROM emp 
ORDER BY salary DESC 
FETCH FIRST 5 ROWS ONLY;

---

6. 案例 5：分页

6.1 Tom 方案

SELECT * FROM (
  SELECT a.*, ROWNUM rn FROM (
    SELECT * FROM emp ORDER BY salary DESC
  ) a WHERE ROWNUM <= 20
) WHERE rn > 10;

6.2 12c+

SELECT * FROM emp 
ORDER BY salary DESC 
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

---

7. 案例 6：分析函数

7.1 需求

- 找每个部门薪水最高的员工

7.2 反例

SELECT e.* FROM emp e 
WHERE salary = (
  SELECT MAX(salary) FROM emp WHERE deptno = e.deptno
);

7.3 Tom 重写

SELECT * FROM (
  SELECT e.*, 
    ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY salary DESC) rn
  FROM emp e
) WHERE rn = 1;

7.4 性能

- 子查询：N 次扫描
- 分析函数：1 次扫描
- 差距大

---

8. 案例 7：MERGE

8.1 反例

-- 先 SELECT 再 INSERT/UPDATE
IF EXISTS (SELECT 1 FROM emp WHERE id=10) THEN
  UPDATE emp SET salary=5000 WHERE id=10;
ELSE
  INSERT INTO emp VALUES (10, 'Alice', 5000);
END IF;

8.2 Tom 重写

MERGE INTO emp e
USING (SELECT 10 AS id, 'Alice' AS name, 5000 AS salary FROM dual) s
ON (e.id = s.id)
WHEN MATCHED THEN UPDATE SET salary = s.salary
WHEN NOT MATCHED THEN INSERT (id, name, salary) VALUES (s.id, s.name, s.salary);

---

9. 案例 8：CONNECT BY

9.1 需求

- 层次查询：员工-经理

9.2 Tom 方案

SELECT LPAD(' ', LEVEL*2) || ename AS hierarchy
FROM emp
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr;

9.3 11g+

-- 递归 WITH
WITH emp_tree (empno, ename, mgr, lvl) AS (
  SELECT empno, ename, mgr, 1 FROM emp WHERE mgr IS NULL
  UNION ALL
  SELECT e.empno, e.ename, e.mgr, t.lvl+1
  FROM emp e JOIN emp_tree t ON e.mgr = t.empno
)
SELECT * FROM emp_tree;

---

10. 案例 9：LISTAGG

10.1 需求

- 部门员工列表合并

10.2 Tom 方案

SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS names
FROM emp
GROUP BY deptno;

---

11. 案例 10：WITH 子句

11.1 反例

-- 重复子查询
SELECT * FROM emp WHERE deptno IN (
  SELECT deptno FROM dept WHERE loc='NY'
)
UNION
SELECT * FROM emp WHERE deptno IN (
  SELECT deptno FROM dept WHERE loc='NY'
) AND salary > 5000;

11.2 Tom 重写

WITH ny_depts AS (
  SELECT deptno FROM dept WHERE loc='NY'
)
SELECT * FROM emp WHERE deptno IN (SELECT deptno FROM ny_depts)
UNION
SELECT * FROM emp WHERE deptno IN (SELECT deptno FROM ny_depts) AND salary > 5000;

---

12. 重写原则

12.1 Tom 原则

- 集合优于循环
- 分析函数优于自连接
- EXISTS 优于 IN（部分场景）
- MERGE 优于先查后改
- 一条 SQL 优于多步

12.2 优先级

1. 单条 SQL
2. PL/SQL（如必须）
3. Java/C（最后）

---

13. 最佳实践

1. 集合思维：不要逐行
2. 分析函数：优先
3. MERGE：Upsert
4. WITH：复用
5. 绑定变量：必用
6. 执行计划：验证
7. 测试：性能
8. 原理：理解
9. 简化：清晰
10. 文档：注释

---

14. 参考资料

[1] AskTOM, “SQL Rewrite”, https://asktom.oracle.com [2] Tom Kyte, “Expert Oracle Database Architecture”