Oracle SQL 编写最佳实践

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

---

1. 概述

SQL 编写最佳实践汇总[1]：

详细见：Oracle SQL 查询优化技巧、Oracle SQL 开发规范详解。

---

2. SELECT

2.1 明确列

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

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

2.2 必要列

-- 仅查询需要的列
SELECT id, name FROM employees;

2.3 别名

SELECT e.id AS employee_id,
       e.name AS employee_name,
       d.dept_name AS department
FROM employees e
  JOIN departments d ON e.dept_id = d.id;

---

3. WHERE

3.1 索引友好

-- 好
SELECT * FROM employees WHERE id = 100;
SELECT * FROM employees WHERE name = 'Alice';

-- 差（函数阻止索引）
SELECT * FROM employees WHERE UPPER(name) = 'ALICE';
SELECT * FROM employees WHERE id + 1 = 101;
SELECT * FROM employees WHERE SUBSTR(name, 1, 3) = 'Ali';

-- 修复
CREATE INDEX idx_upper_name ON employees(UPPER(name));
SELECT * FROM employees WHERE UPPER(name) = 'ALICE';

3.2 类型匹配

-- 好
SELECT * FROM employees WHERE id = 100;
SELECT * FROM employees WHERE hire_date = DATE '2025-07-21';

-- 差（隐式转换）
SELECT * FROM employees WHERE id = '100';
SELECT * FROM employees WHERE hire_date = '2025-07-21';

3.3 前缀

-- 好（前缀匹配，可使用索引）
SELECT * FROM employees WHERE name LIKE 'Smi%';

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

3.4 否定

-- 差（可能阻止索引）
SELECT * FROM employees WHERE dept_id != 10;
SELECT * FROM employees WHERE dept_id <> 10;

-- 好
SELECT * FROM employees WHERE dept_id < 10 OR dept_id > 10;

详细见：Oracle SQL 查询优化技巧。

---

4. JOIN

4.1 现代语法

-- 好
SELECT e.name, d.dept_name
FROM employees e
  INNER 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 别名

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

4.3 OUTER JOIN

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

详细见：Oracle JOIN 连接方式。

---

5. OR vs UNION ALL

5.1 OR

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

5.2 UNION ALL

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

5.3 IN

SELECT * FROM employees WHERE dept_id IN (10, 20, 30);

---

6. IN vs EXISTS

6.1 IN

-- 子查询小
SELECT * FROM employees 
WHERE dept_id IN (SELECT id FROM departments WHERE location = 'NY');

6.2 EXISTS

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

6.3 NOT EXISTS vs NOT IN

-- NOT EXISTS（推荐，NULL 安全）
SELECT * FROM employees e 
WHERE NOT EXISTS (
  SELECT 1 FROM departments d WHERE d.id = e.dept_id
);

-- NOT IN（注意 NULL）
SELECT * FROM employees 
WHERE dept_id NOT IN (SELECT id FROM departments WHERE id IS NOT NULL);

详细见：Oracle 子查询与 EXISTS。

---

7. 绑定变量

7.1 PL/SQL

-- 自动
CREATE PROCEDURE get_emp(p_id NUMBER) IS
  v_name employees.name%TYPE;
BEGIN
  SELECT name INTO v_name FROM employees WHERE id = p_id;
END;

7.2 动态 SQL

-- 好
EXECUTE IMMEDIATE 'SELECT * FROM t WHERE id = :id' USING v_id;

-- 差
EXECUTE IMMEDIATE 'SELECT * FROM t WHERE id = ' || v_id;

7.3 应用

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

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

---

8. 分页

8.1 FETCH（12c+）

SELECT * FROM employees ORDER BY id
OFFSET 100 ROWS FETCH NEXT 10 ROWS ONLY;

8.2 键集

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

8.3 ROWNUM

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

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

---

9. 聚合

9.1 先过滤

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

9.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 视图与物化视图详解。

---

10. 子查询

10.1 CTE

WITH dept_stats AS (
  SELECT dept_id, COUNT(*) AS cnt, AVG(salary) AS avg_sal
  FROM employees GROUP BY dept_id
)
SELECT * FROM dept_stats WHERE avg_sal > 5000;

10.2 内联视图

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;

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

---

11. UNION

11.1 UNION ALL

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

11.2 UNION

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

详细见：Oracle SQL 集合操作。

---

12. DISTINCT

12.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
);

---

13. 函数

13.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.2 SELECT 中合理

SELECT id, UPPER(name), TO_CHAR(hire_date, 'YYYY-MM-DD') FROM employees;

---

14. 排序

14.1 避免

-- 不必要时去掉
SELECT * FROM employees;  -- 不加 ORDER BY

14.2 索引排序

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

---

15. NULL

15.1 比较

-- 差
SELECT * FROM employees WHERE dept_id = NULL;

-- 好
SELECT * FROM employees WHERE dept_id IS NULL;

15.2 函数

SELECT id, NVL(email, 'N/A') FROM employees;
SELECT id, COALESCE(phone1, phone2, phone3) FROM employees;

---

16. 执行计划

16.1 查看

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

16.2 关注

- TABLE ACCESS FULL：全表（差）
- INDEX RANGE SCAN：索引（好）
- HASH JOIN：大表
- NESTED LOOPS：小表
- SORT：排序（开销）

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

---

17. 统计信息

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

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

---

18. Hint

-- 谨慎使用
SELECT /*+ INDEX(e idx_emp_name) */ * FROM employees e WHERE name = 'Alice';
SELECT /*+ PARALLEL(t 8) */ COUNT(*) FROM big_table t;

详细见：Oracle SQL Hint 详解。

---

19. MERGE

-- UPSERT
MERGE INTO employees e
USING (SELECT :id AS id, :name AS name FROM dual) s
ON (e.id = s.id)
WHEN MATCHED THEN UPDATE SET e.name = s.name
WHEN NOT MATCHED THEN INSERT (id, name) VALUES (s.id, s.name);

详细见：Oracle MERGE 语句详解。

---

20. RETURNING

INSERT INTO t VALUES (...) RETURNING id INTO v_id;

DELETE FROM t WHERE ... RETURNING id BULK COLLECT INTO v_ids;

---

21. 常见反模式

21.1 避免

- SELECT *
- 隐式转换
- 函数阻止索引
- OR 性能
- 不必要 DISTINCT
- UNION 改 UNION ALL
- 拼接 SQL
- 全表扫描
- 不必要排序
- 过度复杂 SQL

21.2 推荐

- 明确列
- 类型匹配
- 索引友好
- UNION ALL
- 绑定变量
- 简单清晰

---

22. 性能清单

问题	优化
全表扫描	索引
函数阻止索引	函数索引
统计旧	GATHER_STATS
Hard Parse 多	绑定变量
OR 性能	UNION ALL
JOIN 慢	Hint / 索引
排序大	索引排序
分页慢	FETCH / 键集
DISTINCT 慢	GROUP BY
UNION 慢	UNION ALL

---

23. 最佳实践

1. 明确列：性能
2. 绑定变量：减少解析
3. 索引友好：避免函数
4. 类型匹配：避免隐式
5. 现代 JOIN：清晰
6. FETCH 分页：12c+
7. CTE：复杂查询
8. 执行计划：验证
9. 统计信息：更新
10. 测试：性能

---

24. 参考资料

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