PL/SQL 性能优化指南
PL/SQL 性能优化指南
本文系统梳理了在SQL编写和PL/SQL程序设计中常见的性能问题，并提供可落地的优化策略，帮助开发者写出更高效、更稳定的数据库代码。
一、表连接与解析顺序优化
1. 选择最优的表名顺序（适用于RBO，现代已不敏感但建议保留习惯）
Oracle在基于规则的优化器（RBO）中按从右到左解析 FROM 子句，最后一个表为"驱动表"先被处理。应将记录最少的表放在最后以减少中间结果集。
⚠️ 注意：当前Oracle普遍使用CBO（成本优化器），此规则不再生效，但仍建议保持良好编程习惯。
✅ 推荐写法（小表驱动）
Sql
深色版本
-- 假设 ab01 只有1条记录，ac01 有16,384条
SELECT COUNT(*) FROM ab01, ac01; -- 高效：ab01 作为驱动表
❌ 低效写法
Sql
深色版本
SELECT COUNT(*) FROM ac01, ab01; -- 低效：ac01 先扫描，执行时间显著增加
2. WHERE子句中的连接顺序（自下而上解析）
Oracle解析 WHERE 子句是自下而上的，因此应将表连接条件写在其他过滤条件之前，以便尽早完成关联，减少中间结果集。
✅ 推荐写法
Sql
深色版本
SELECT ab01.aab001, ab02.aab051
FROM ab01, ab02
WHERE ab01.aab001 = ab02.aab001 -- 先执行连接，减少数据量
AND ab02.aae140 = '31'; -- 再应用过滤条件
❌ 低效写法
Sql
深色版本
SELECT ab01.aab001, ab02.aab051
FROM ab01, ab02
WHERE ab02.aae140 = '31' -- 先过滤可能导致全表扫描
AND ab01.aab001 = ab02.aab001; -- 后连接，效率低
二、子查询与集合操作优化
3. 避免使用 SELECT *
使用 SELECT * 会触发数据字典查询，将 * 展开为所有列名，增加解析时间与I/O开销。
✅ 推荐写法
Sql
深色版本
-- 明确列出所需字段，减少数据传输
SELECT aac001, aac002, aab001
FROM ac01
WHERE aab001 = '1001';
❌ 低效写法
Sql
深色版本
-- 不必要的列加载
SELECT * FROM ac01 WHERE aab001 = '1001';
4. 使用 EXISTS 替代 IN（尤其适用于子查询）
EXISTS 在找到第一个匹配项后即返回，而 IN 需构建完整结果集，效率较低。
✅ 推荐写法
Sql
深色版本
SELECT *
FROM ac01
WHERE EXISTS (
SELECT 1 FROM ac02
WHERE ac02.aac001 = ac01.aac001
AND aab001 = str_aab001
AND aae140 = '31'
);
❌ 低效写法
Sql
深色版本
SELECT *
FROM ac01
WHERE aac001 IN (
SELECT aac001 FROM ac02
WHERE aab001 = str_aab001 AND aae140 = '31'
);
5. 使用 NOT EXISTS 替代 NOT IN
NOT IN 在子查询含 NULL 时结果为空，且需全表扫描，效率极低。
✅ 推荐写法
Sql
深色版本
SELECT *
FROM ac01
WHERE NOT EXISTS (
SELECT 1 FROM ab01
WHERE ab01.aab001 = ac01.aab001
AND aab020 = '100'
);
❌ 低效写法
Sql
深色版本
SELECT * FROM ac01
WHERE aab001 NOT IN (
SELECT aab001 FROM ab01 WHERE aab020 = '100'
);
三、集合操作与条件过滤优化
6. 尽量使用 UNION ALL 替代 UNION
UNION 会去重并排序，消耗大量资源；UNION ALL 直接合并，效率更高。
✅ 推荐写法
Sql
深色版本
SELECT col FROM tab1
UNION ALL
SELECT col FROM tab2;
❌ 低效写法
Sql
深色版本
SELECT col FROM tab1
UNION
SELECT col FROM tab2;
7. 使用 DECODE 函数减少处理时间
用 DECODE 在一次扫描中完成多条件统计，避免重复查询。
✅ 推荐写法
Sql
深色版本
SELECT
COUNT(DECODE(aac008, '1', '1', NULL)) AS zz,
COUNT(DECODE(aac008, '2', '1', NULL)) AS tx
FROM ac01
WHERE aab001 = '100001';
❌ 低效写法
Sql
深色版本
SELECT COUNT(1) FROM ac01 WHERE aab001='100001' AND aac008='1';
SELECT COUNT(1) FROM ac01 WHERE aab001='100001' AND aac008='2';
8. 用 WHERE 替代 HAVING
HAVING 在分组后过滤，WHERE 在分组前过滤，能显著减少数据量。
✅ 推荐写法
Sql
深色版本
SELECT aac008, COUNT(*)
FROM ac01
WHERE aac008 IN ('1', '2') -- 分组前过滤
GROUP BY aac008;
❌ 低效写法
Sql
深色版本
SELECT aac008, COUNT(*)
FROM ac01
GROUP BY aac008
HAVING aac008 IN ('1', '2'); -- 分组后才过滤
四、PL/SQL 程序结构优化
9. 减少对表的重复查询
合并相似查询，一次读取，分逻辑处理，减少I/O。
✅ 推荐写法
Sql
深色版本
CURSOR cur_kc24 IS
SELECT akc260, aka130
FROM kc24
WHERE akb020 = str_akb020
AND aka130 IN ('11', '21');
FOR rec IN cur_kc24 LOOP
IF rec.aka130 = '11' THEN
-- 门诊处理
ELSIF rec.aka130 = '21' THEN
-- 住院处理
END IF;
END LOOP;
❌ 低效写法
Sql
深色版本
-- 执行两次查询
FOR rec_mz IN (SELECT akc260 FROM kc24 WHERE aka130='11') LOOP ... END LOOP;
FOR rec_zy IN (SELECT akc260 FROM kc24 WHERE aka130='21') LOOP ... END LOOP;
10. 避免游标中嵌套查询（N+1问题）
游标中嵌查询会导致N次数据库往返，性能灾难。
✅ 推荐写法
Sql
深色版本
CURSOR cur_ac04 IS
SELECT ac04.aac001, ac04.akc010, ac01.aac008
FROM ac04, ac01
WHERE ac04.aac001 = ac01.aac001
AND ac04.aab001 = prm_aab001;
FOR rec_ac04 IN cur_ac04 LOOP
IF rec_ac04.aac008 = '1' THEN
n_jfje := rec_ac04.akc010 * 0.08;
END IF;
END LOOP;
❌ 低效写法
Sql
深色版本
FOR rec_ac04 IN (SELECT aac001, akc010 FROM ac04) LOOP
SELECT aac008 INTO str_aac008 FROM ac01 WHERE aac001 = rec_ac04.aac001;
-- 循环内查询，100万次 = 数小时
END LOOP;
11. 使用批量绑定（BULK COLLECT + FORALL）
减少上下文切换，提升大批量DML性能。
✅ 推荐写法
Sql
深色版本
DECLARE
TYPE t_ac04 IS TABLE OF ac04%ROWTYPE;
l_data t_ac04;
BEGIN
SELECT * BULK COLLECT INTO l_data FROM ac04 WHERE aab001 = '1001';
FORALL i IN 1..l_data.COUNT
INSERT INTO ac04_bak VALUES l_data(i);
COMMIT;
END;
❌ 低效写法
Sql
深色版本
FOR rec IN (SELECT * FROM ac04) LOOP
INSERT INTO ac04_bak VALUES rec; -- 逐行插入，性能差
END LOOP;
五、事务控制与辅助技巧
12. 合理控制 COMMIT 频率
平衡资源释放与提交开销。
✅ 推荐策略
Sql
深色版本
n_count := 0;
FOR rec IN cur_data LOOP
INSERT INTO target VALUES (...);
n_count := n_count + 1;
IF n_count >= 10000 THEN -- 每1万条提交一次
COMMIT;
n_count := 0;
END IF;
END LOOP;
COMMIT;
❌ 低效做法
每条提交：开销过大
全部完成再提交：回滚段溢出、快照过旧
13. 删除重复记录（基于ROWID）
利用物理地址快速去重。
✅ 推荐写法
Sql
深色版本
DELETE FROM ac01 a
WHERE a.rowid > (
SELECT MIN(b.rowid)
FROM ac01 b
WHERE a.aac002 = b.aac002
AND a.aac003 = b.aac003
);
14. 使用表别名（Alias）
提高可读性，避免歧义，加快解析。
✅ 推荐写法
Sql
深色版本
SELECT a.aac001, b.akc010
FROM ac01 a, ac04 b
WHERE a.aac001 = b.aac001;
❌ 低效写法
Sql
深色版本
SELECT ac01.aac001, ac04.akc010
FROM ac01, ac04
WHERE ac01.aac001 = ac04.aac001; -- 字段多时易混淆
六、索引与排序优化（新增）
15. 避免在索引列使用函数或运算
索引列参与函数或运算会导致索引失效，强制全表扫描。
✅ 推荐写法
Sql
深色版本
-- 确保索引列未被修改
SELECT * FROM ac01 WHERE aac001 = '1001';
❌ 低效写法
Sql
深色版本
-- 索引失效，全表扫描
SELECT * FROM ac01 WHERE UPPER(aac001) = '1001';
16. 使用索引优化排序（ORDER BY）
通过索引避免排序操作，显著提升性能。
✅ 推荐写法
Sql
深色版本
-- 确保索引列与ORDER BY字段一致
CREATE INDEX idx_ac01_aac008 ON ac01(aac008);
SELECT * FROM ac01 ORDER BY aac008;
❌ 低效写法
Sql
深色版本
-- 无索引时强制排序
SELECT * FROM ac01 ORDER BY aac008;
七、锁与连接问题处理（新增）
17. 锁等待排查与处理
频繁的锁等待会导致PL/SQL卡死，需通过监控工具定位并优化。
✅ 推荐策略
Sql
深色版本
-- 使用V$SESSION和V$LOCK视图排查锁等待
SELECT * FROM v$session s, v$lock l
WHERE s.sid = l.sid AND l.block = 1;
八、命名规范（新增）
18. 遵循统一命名规范
良好的命名规范提升代码可读性与维护性，间接优化性能。
✅ 推荐写法
Sql
深色版本
-- 包命名：CUX_PAYROLL_PUBLIC_PKG
-- 函数命名：GET_EMPLOYEE_SALARY
-- 过程命名：UPDATE_EMPLOYEE_DEPT_INFO
-- 参数命名：P_EMPLOYEE_ID
总结：PL/SQL优化 Checklist
优化点推荐说明
SELECT *
❌
明确列名
EXISTS vs IN
✅
子查询优先EXISTS
NOT IN
❌
必须用NOT EXISTS替代
UNION
❌
优先UNION ALL
游标嵌查询
❌
改为JOIN一次性获取
BULK COLLECT
✅
大批量操作必备
COMMIT频率
⚖️
每1万~10万条提交一次
HAVING
⚠️
能用WHERE就不用HAVING
索引列函数
❌
避免索引失效
排序优化
✅
利用索引避免排序
锁等待排查
✅
定期监控锁资源
记住：每一次不必要的查询，都是对数据库的一次"伤害"。从今天开始，用更聪明的方式与Oracle对话！