DB / Oracle / ACE 精选

Oracle 杨廷琨 SQL 优化案例集

2026.09.06

Oracle 杨廷琨 SQL 优化案例集

来源：杨廷琨 (AskTOM 中文 / 啖汤) 适用版本：Oracle Database 全版本 文档版本：v1.0 / 2026-07-22

1. 关于杨廷琨

杨廷琨，Oracle ACE Director，AskTOM 中文版核心专家[1]。

  • 擅长 SQL 优化、执行计划分析
  • 大量经典 SQL 优化案例
  • 系统化讲解 Oracle 内部机制
  • 文章严谨深入

2. 案例 1：子查询低效

2.1 原始 SQL

SELECT * FROM orders 
WHERE customer_id IN (
  SELECT customer_id FROM customers WHERE region='NY'
)
AND EXISTS (
  SELECT 1 FROM order_items WHERE order_items.order_id = orders.id
);

2.2 问题

- IN + EXISTS 双重子查询
- 多次扫描
- 性能差

2.3 杨廷琨优化

SELECT o.* FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE c.region = 'NY'
AND EXISTS (
  SELECT 1 FROM order_items oi WHERE oi.order_id = o.id
);

2.4 性能

- 优化前：10 秒
- 优化后：0.5 秒
- 提升 20 倍

3. 案例 2：OR 改 UNION

3.1 原始 SQL

SELECT * FROM emp 
WHERE deptno = 10 OR salary > 5000;

3.2 问题

- OR 导致全表扫描
- 索引失效

3.3 杨廷琨优化

SELECT * FROM emp WHERE deptno = 10
UNION
SELECT * FROM emp WHERE salary > 5000 AND deptno != 10;

3.4 性能

- 优化前：全表扫描
- 优化后：索引扫描

4. 案例 3：NOT IN 性能

4.1 原始 SQL

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

4.2 问题

- NOT IN NULL 陷阱
- 性能差

4.3 杨廷琨优化

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

-- 或 外连接
SELECT e.* FROM emp e 
LEFT JOIN dept d ON e.deptno = d.deptno AND d.loc='NY'
WHERE d.deptno IS NULL;

5. 案例 4：LIKE 优化

5.1 原始 SQL

SELECT * FROM emp WHERE ename LIKE '%ALICE%';

5.2 问题

- 前导 %
- 索引失效
- 全表扫描

5.3 杨廷琨方案

- Oracle Text
- 反转索引
- 评估业务需求
-- Oracle Text
CREATE INDEX idx_emp_text ON emp(ename) INDEXTYPE IS CTXSYS.CONTEXT;

SELECT * FROM emp WHERE CONTAINS(ename, 'ALICE') > 0;

6. 案例 5：分页优化

6.1 原始 SQL

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

6.2 问题

- 大偏移性能差
- 排序成本

6.3 杨廷琨优化

-- 12c+
SELECT * FROM emp 
ORDER BY salary DESC 
OFFSET 90 ROWS FETCH NEXT 10 ROWS ONLY;

-- 或 seek method
SELECT * FROM emp 
WHERE salary < :last_salary
ORDER BY salary DESC 
FETCH FIRST 10 ROWS ONLY;

7. 案例 6：DECODE 优化

7.1 原始 SQL

SELECT 
  SUM(CASE WHEN deptno=10 THEN salary ELSE 0 END) AS d10,
  SUM(CASE WHEN deptno=20 THEN salary ELSE 0 END) AS d20
FROM emp;

7.2 杨廷琨观点

- CASE 与 DECODE 性能相近
- 选择可读性
- 注意 NULL 处理

8. 案例 7：DISTINCT 优化

8.1 原始 SQL

SELECT DISTINCT deptno FROM emp;

8.2 杨廷琨分析

- DISTINCT 排序/HASH
- 性能差
- 评估是否必要

8.3 优化

-- GROUP BY
SELECT deptno FROM emp GROUP BY deptno;
-- 性能相近，但 CBO 可能选不同计划

9. 案例 8：连接方式

9.1 Nested Loop

- 适合小数据
- 索引驱动
- OLTP

9.2 Hash Join

- 适合大数据
- 内存
- OLAP

9.3 Merge Join

- 排序
- 数据已排序
- 特殊场景

9.4 杨廷琨分析

- 了解三种连接
- CBO 选择
- HINT 必要时

10. 案例 9：统计信息

10.1 问题

- 执行计划差
- 统计信息陈旧

10.2 杨廷琨方案

EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT','EMP',
  CASCADE=>TRUE, METHOD_OPT=>'FOR ALL COLUMNS SIZE AUTO');

-- 直方图
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT','EMP',
  METHOD_OPT=>'FOR COLUMNS salary SIZE 254');

11. 案例 10：HINT 使用

11.1 谨慎

- 优先优化 SQL
- HINT 最后手段
- 文档化

11.2 常用

-- 索引
SELECT /*+ INDEX(e idx_emp) */ * FROM emp e;

-- 连接
SELECT /*+ LEADING(e,d) USE_NL(d) */ * FROM emp e, dept d WHERE ...;

-- 并行
SELECT /*+ PARALLEL(e 4) */ * FROM emp e;

12. 杨廷琨方法论

12.1 步骤

1. 查看执行计划
2. 找出问题（全表/低效连接）
3. 优化（SQL/索引/统计）
4. 验证
5. 监控

12.2 工具

- EXPLAIN PLAN
- DBMS_XPLAN
- AWR
- ASH

12.3 思维

- 数据驱动
- 集合思维
- 原理理解

13. 最佳实践

  1. 执行计划：必看
  2. 统计信息：收集
  3. 索引：合理
  4. SQL 重写：优先
  5. HINT：谨慎
  6. 绑定变量：必用
  7. 案例：学习
  8. 测试：验证
  9. 监控：持续
  10. 原理：理解

14. 参考资料

[1] 杨廷琨 AskTOM 中文, https://asktom.oracle.com/pls/asktom/f?p=100:1:0 [2] 杨廷琨博客, https://www.modb.co/u/yangtingkun