DB / Oracle / SQL 开发

Oracle SQL 查询优化基础

2026.09.06

Oracle SQL 查询优化基础

适用版本：Oracle Database 11g / 12c / 19c / 23ai 文档版本：v1.0 / 2026-07

1. 概述

SQL 查询优化是性能调优的基础[1]：

优化原则：

  • 减少数据扫描
  • 减少数据处理
  • 利用索引
  • 合理执行计划

2. SELECT 优化

2.1 只查需要的列

-- 不好
SELECT * FROM employees WHERE dept_id = 10;

-- 好
SELECT employee_id, last_name, salary 
FROM employees 
WHERE dept_id = 10;

2.2 只查需要的行

-- 加 WHERE 条件
SELECT * FROM sales 
WHERE sale_date >= TRUNC(SYSDATE, 'MM');

2.3 LIMIT 结果

-- Top N
SELECT * FROM (
  SELECT * FROM employees ORDER BY salary DESC
) WHERE ROWNUM <= 10;

-- 12c+
SELECT * FROM employees 
ORDER BY salary DESC 
FETCH FIRST 10 ROWS ONLY;

3. WHERE 优化

3.1 避免函数阻止索引

-- 不好（索引失效）
SELECT * FROM employees 
WHERE UPPER(last_name) = 'SMITH';

-- 好（建函数索引）
CREATE INDEX idx_emp_upper_name ON employees(UPPER(last_name));
SELECT * FROM employees WHERE UPPER(last_name) = 'SMITH';

-- 好（不使用函数）
SELECT * FROM employees 
WHERE last_name = 'Smith' OR last_name = 'SMITH';

3.2 避免隐式转换

-- 不好（字符转数字，索引失效）
SELECT * FROM employees WHERE id = '100';

-- 好
SELECT * FROM employees WHERE id = 100;

3.3 避免 NULL 比较

-- 不好（索引不存 NULL）
SELECT * FROM employees WHERE commission IS NOT NULL;

-- 好
SELECT * FROM employees WHERE commission > 0;

3.4 LIKE 优化

-- 不好（前缀通配符，索引失效）
SELECT * FROM employees WHERE last_name LIKE '%ith';

-- 好（前缀固定）
SELECT * FROM employees WHERE last_name LIKE 'Smit%';

3.5 避免负向条件

-- 不好（不能用索引）
SELECT * FROM employees WHERE dept_id != 10;

-- 好
SELECT * FROM employees WHERE dept_id < 10 OR dept_id > 10;
-- 或使用 IN
SELECT * FROM employees WHERE dept_id IN (20, 30, 40);

4. JOIN 优化

4.1 连接列加索引

CREATE INDEX idx_emp_dept ON employees(dept_id);
CREATE INDEX idx_dept_id ON departments(id);

SELECT * FROM employees e JOIN departments d ON e.dept_id = d.id;

4.2 小表驱动大表

-- 小表在外（驱动表）
SELECT /*+ LEADING(d) USE_NL(e) */ *
FROM departments d, employees e
WHERE e.dept_id = d.id;

4.3 减少 JOIN 表数量

-- 不好
SELECT * FROM a JOIN b ON ... JOIN c ON ... JOIN d ON ... JOIN e ON ...

-- 好（拆分或使用 WITH）
WITH ab AS (SELECT ... FROM a JOIN b ON ...)
SELECT * FROM ab JOIN c ON ...;

4.4 内连接优先

-- 不好（外连接慢）
SELECT * FROM employees e LEFT JOIN departments d ON e.dept_id = d.id;

-- 好（如不需要 NULL）
SELECT * FROM employees e JOIN departments d ON e.dept_id = d.id;

5. 子查询优化

5.1 用 JOIN 替代子查询

-- 不好
SELECT * FROM employees 
WHERE dept_id IN (SELECT id FROM departments WHERE location = 'NY');

-- 好
SELECT e.* 
FROM employees e, departments d
WHERE e.dept_id = d.id AND d.location = 'NY';

5.2 EXISTS 替代 IN

-- 不好（大数据集）
SELECT * FROM employees 
WHERE dept_id IN (SELECT id FROM departments);

-- 好
SELECT * FROM employees e
WHERE EXISTS (SELECT 1 FROM departments d WHERE d.id = e.dept_id);

5.3 NOT EXISTS 替代 NOT IN

-- 不好（NULL 问题）
SELECT * FROM employees 
WHERE dept_id NOT IN (SELECT dept_id FROM departments);

-- 好
SELECT * FROM employees e
WHERE NOT EXISTS (SELECT 1 FROM departments d WHERE d.id = e.dept_id);

6. 聚合优化

6.1 减少聚合数据

-- 不好
SELECT dept_id, AVG(salary) FROM employees GROUP BY dept_id;

-- 好（先过滤）
SELECT dept_id, AVG(salary) 
FROM employees 
WHERE dept_id IN (10, 20, 30)
GROUP BY dept_id;

6.2 HAVING vs WHERE

-- 不好（HAVING 过滤聚合后）
SELECT dept_id, COUNT(*) 
FROM employees 
GROUP BY dept_id 
HAVING dept_id = 10;

-- 好（WHERE 过滤聚合前）
SELECT dept_id, COUNT(*) 
FROM employees 
WHERE dept_id = 10
GROUP BY dept_id;

7. ORDER BY 优化

7.1 利用索引

-- 索引 (dept_id, salary)
SELECT * FROM employees 
WHERE dept_id = 10 
ORDER BY salary;
-- 索引已排序，无需额外排序

7.2 避免 ORDER BY

-- 如不需要排序
SELECT * FROM employees WHERE dept_id = 10;

8. UNION 优化

8.1 UNION ALL 替代 UNION

-- 不好（去重，排序）
SELECT name FROM employees UNION SELECT name FROM contractors;

-- 好（不去重）
SELECT name FROM employees UNION ALL SELECT name FROM contractors;

8.2 分拆查询

-- 不好
SELECT * FROM big_table WHERE ...

-- 好（分批）
SELECT * FROM big_table WHERE ... AND ROWNUM <= 10000;

9. 绑定变量

9.1 使用绑定变量

-- 不好（硬解析）
EXECUTE IMMEDIATE 'SELECT * FROM emp WHERE id = ' || v_id;

-- 好（绑定变量）
EXECUTE IMMEDIATE 'SELECT * FROM emp WHERE id = :1' USING v_id;

9.2 优势

  • 减少硬解析
  • 共享池利用
  • 性能提升

10. HINT 使用

10.1 常用 HINT

-- 索引
SELECT /*+ INDEX(e idx_emp_name) */ * FROM employees e WHERE ...

-- Hash Join
SELECT /*+ USE_HASH(e d) */ * FROM employees e, departments d WHERE ...

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

-- FIRST_ROWS
SELECT /*+ FIRST_ROWS(10) */ * FROM employees WHERE ... FETCH FIRST 10 ROWS ONLY;

10.2 谨慎使用

  • 仅在 CBO 错误时
  • 测试验证
  • 定期复审

11. 执行计划

11.1 查看

EXPLAIN PLAN FOR SELECT ...;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- 或
SET AUTOTRACE ON;
SELECT ...;

11.2 关注点

  • Cost
  • Cardinality
  • Rows
  • 使用的索引
  • JOIN 方法

详细见：Oracle 执行计划详解。

12. 常见坑与排错

12.1 全表扫描

-- 检查执行计划
-- 1. 索引是否存在
-- 2. 统计信息是否最新
-- 3. 是否被函数阻止

12.2 排序溢出

-- 检查 PGA
-- 1. 增大 PGA_AGGREGATE_TARGET
-- 2. 减少排序数据
-- 3. 利用索引排序

12.3 笛卡尔积

-- 检查 JOIN 条件
-- 1. 确保每个 JOIN 有 ON
-- 2. 避免 CROSS JOIN

12.4 子查询性能差

-- 1. 改写为 JOIN
-- 2. 使用 EXISTS
-- 3. 使用 WITH

13. 最佳实践

  1. 只查需要的列和行：减少 I/O
  2. 利用索引：避免函数阻止
  3. JOIN 加索引：连接列
  4. 小表驱动大表：性能
  5. EXISTS 替代 IN：大数据
  6. UNION ALL 替代 UNION：避免排序
  7. WHERE 替代 HAVING：早过滤
  8. 绑定变量：减少硬解析
  9. 定期收集统计信息：CBO
  10. 查看执行计划：验证优化

14. 参考资料

[1] Oracle Database SQL Tuning Guide 19c https://docs.oracle.com/en/database/oracle/oracle-database/19/tgsql/