Oracle SQL 查询优化技巧

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

---

1. 概述

SQL 查询优化技巧[1]：

详细见：Oracle SQL 性能调优案例。

---

2. 索引使用

2.1 避免函数

-- 差
SELECT * FROM employees WHERE UPPER(name) = 'SMITH';

-- 好
SELECT * FROM employees WHERE name = 'Smith';
-- 或函数索引
CREATE INDEX idx_upper_name ON employees(UPPER(name));
SELECT * FROM employees WHERE UPPER(name) = 'SMITH';

2.2 前缀

-- 好（前缀）
SELECT * FROM employees WHERE name LIKE 'Smith%';

-- 差（无前缀）
SELECT * FROM employees WHERE name LIKE '%Smith';

2.3 隐式转换

-- 差（隐式转换）
SELECT * FROM employees WHERE id = '100';  -- id NUMBER

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

2.4 复合索引

-- 复合索引 (dept_id, salary)
CREATE INDEX idx_emp_dept_sal ON employees(dept_id, salary);

-- 好（前缀）
SELECT * FROM employees WHERE dept_id = 10;
SELECT * FROM employees WHERE dept_id = 10 AND salary > 5000;

-- 差（非前缀）
SELECT * FROM employees WHERE salary > 5000;

详细见：Oracle 索引类型与应用。

---

3. WHERE 子句

3.1 避免否定

-- 差
SELECT * FROM employees WHERE dept_id != 10;

-- 好
SELECT * FROM employees WHERE dept_id < 10 OR dept_id > 10;
-- 或
SELECT * FROM employees WHERE dept_id NOT IN (10);

3.2 避免 OR

-- 差（可能阻止索引）
SELECT * FROM employees WHERE id = 1 OR salary > 10000;

-- 好（UNION ALL）
SELECT * FROM employees WHERE id = 1
UNION ALL
SELECT * FROM employees WHERE salary > 10000 AND id != 1;

3.3 IN vs EXISTS

-- IN 适合子查询小
SELECT * FROM employees WHERE dept_id IN (SELECT id FROM departments WHERE ...);

-- EXISTS 适合外查询小
SELECT * FROM employees e WHERE EXISTS (
  SELECT 1 FROM departments d WHERE d.id = e.dept_id AND ...
);

详细见：Oracle 子查询与 EXISTS。

---

4. JOIN

4.1 使用 JOIN 语法

-- 现代
SELECT e.name, d.dept_name
FROM employees e
  JOIN departments d ON e.dept_id = d.id
WHERE e.status = 'ACTIVE';

-- 老式（避免）
SELECT e.name, d.dept_name
FROM employees e, departments d
WHERE e.dept_id = d.id AND e.status = 'ACTIVE';

4.2 OUTER JOIN

-- LEFT OUTER
SELECT e.name, d.dept_name
FROM employees e
  LEFT OUTER JOIN departments d ON e.dept_id = d.id;

-- 老式（避免）
SELECT e.name, d.dept_name
FROM employees e, departments d
WHERE e.dept_id = d.id(+);

4.3 JOIN 顺序

- 小表驱动大表
- 索引利用
- Nested Loop / Hash Join

详细见：Oracle JOIN 连接方式。

---

5. SELECT

5.1 避免 SELECT *

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

-- 好
SELECT id, name, salary FROM employees WHERE id = 100;

5.2 必要列

-- 好
SELECT id, name FROM employees;

---

6. 分页

6.1 ROWNUM

-- 旧方式
SELECT * FROM (
  SELECT ROWNUM rn, t.* FROM (
    SELECT * FROM employees ORDER BY id
  ) t WHERE ROWNUM <= 100010
) WHERE rn > 100000;

6.2 12c+ FETCH

-- 好
SELECT * FROM employees ORDER BY id
OFFSET 100000 ROWS FETCH NEXT 10 ROWS ONLY;

6.3 键集

-- 性能最佳
SELECT * FROM employees 
WHERE id > :last_id 
ORDER BY id 
FETCH FIRST 10 ROWS ONLY;

详细见：Oracle 12c 新 SQL 特性。

---

7. 排序

7.1 避免

-- 差（无必要 ORDER BY）
SELECT * FROM employees ORDER BY salary;

-- 好（如不需要）
SELECT * FROM employees;

7.2 索引排序

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

---

8. 聚合

8.1 减少行

-- 好（先过滤再聚合）
SELECT dept_id, AVG(salary) 
FROM employees 
WHERE status = 'ACTIVE'
GROUP BY dept_id;

8.2 物化视图

-- 频繁聚合
CREATE MATERIALIZED VIEW mv_dept_avg
  REFRESH COMPLETE ON DEMAND
  ENABLE QUERY REWRITE
  AS SELECT dept_id, AVG(salary) FROM employees GROUP BY dept_id;

详细见：Oracle 视图与物化视图详解。

---

9. 子查询

9.1 内联视图

SELECT e.name, d.dept_name
FROM employees e,
  (SELECT id, dept_name FROM departments WHERE location = 'NY') d
WHERE e.dept_id = d.id;

9.2 WITH

WITH dept_ny AS (
  SELECT id, dept_name FROM departments WHERE location = 'NY'
)
SELECT e.name, d.dept_name
FROM employees e, dept_ny d
WHERE e.dept_id = d.id;

详细见：Oracle CTE 与递归查询。

---

10. UNION

10.1 UNION ALL

-- 好（无重复）
SELECT id FROM a UNION ALL SELECT id FROM b;

-- 差（排序去重）
SELECT id FROM a UNION SELECT id FROM b;

详细见：Oracle SQL 集合操作。

---

11. DISTINCT

11.1 避免不必要

-- 差
SELECT DISTINCT dept_id FROM employees;

-- 好
SELECT dept_id FROM employees GROUP BY dept_id;
-- 或
SELECT id FROM departments WHERE EXISTS (
  SELECT 1 FROM employees WHERE dept_id = departments.id
);

---

12. 函数

12.1 避免在 WHERE

-- 差
SELECT * FROM employees WHERE TO_CHAR(hire_date, 'YYYY') = '2025';

-- 好
SELECT * FROM employees 
WHERE hire_date >= DATE '2025-01-01' AND hire_date < DATE '2026-01-01';

---

13. 绑定变量

13.1 PL/SQL

-- 好（自动绑定）
FOR rec IN (SELECT * FROM employees WHERE dept_id = p_dept) LOOP ...

-- 动态 SQL
EXECUTE IMMEDIATE 'SELECT * FROM t WHERE id = :id' USING v_id;

13.2 应用

// 好
PreparedStatement ps = con.prepareStatement("SELECT * FROM emp WHERE id = ?");
ps.setInt(1, 100);

// 差
String sql = "SELECT * FROM emp WHERE id = " + 100;

详细见：Oracle PL/SQL 动态 SQL 详解。

---

14. 执行计划

14.1 查看

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

14.2 关注

- TABLE ACCESS FULL：全表
- INDEX RANGE SCAN：索引
- HASH JOIN：大表
- NESTED LOOPS：小表
- SORT：排序

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

---

15. 统计信息

EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT', 'EMPLOYEES', cascade => TRUE);
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SCOTT');

详细见：Oracle 直方图与统计信息。

---

16. Hint

-- 索引
SELECT /*+ INDEX(e idx_emp_name) */ * FROM employees e WHERE name = 'Alice';

-- 并行
SELECT /*+ PARALLEL(t 8) */ COUNT(*) FROM big_table t;

-- JOIN
SELECT /*+ USE_HASH(a b) */ * FROM a, b WHERE a.id = b.id;

详细见：Oracle SQL Hint 详解。

---

17. 常见优化清单

问题	优化
全表扫描	索引
函数阻止索引	函数索引
OR 性能	UNION ALL
SELECT *	必要列
不必要排序	避免
隐式转换	类型匹配
子查询	IN/EXISTS 选择
分页	FETCH / 键集
UNION	UNION ALL
DISTINCT	GROUP BY

---

18. 最佳实践

1. 索引合理：覆盖
2. 绑定变量：减少解析
3. SELECT 列：必要
4. JOIN 现代：清晰
5. 分页 FETCH：12c+
6. 避免排序：性能
7. 执行计划：验证
8. 统计信息：更新
9. Hint 谨慎：兜底
10. 测试：验证

---

19. 参考资料

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