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 ac02WHERE ac02.aac001 = ac01.aac001AND aab001 = str_aab001AND aae140 = '31'
);

❌ 低效写法

Sql

深色版本

SELECT *
FROM ac01
WHERE aac001 IN (SELECT aac001 FROM ac02WHERE aab001 = str_aab001 AND aae140 = '31'
);

---

5. 使用 NOT EXISTS 替代 NOT IN

NOT IN 在子查询含 NULL 时结果为空，且需全表扫描，效率极低。

✅ 推荐写法

Sql

深色版本

SELECT *
FROM ac01
WHERE NOT EXISTS (SELECT 1 FROM ab01WHERE ab01.aab001 = ac01.aab001AND 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 ISSELECT akc260, aka130FROM kc24WHERE akb020 = str_akb020AND aka130 IN ('11', '21');FOR rec IN cur_kc24 LOOPIF 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 ISSELECT ac04.aac001, ac04.akc010, ac01.aac008FROM ac04, ac01WHERE ac04.aac001 = ac01.aac001AND ac04.aab001 = prm_aab001;FOR rec_ac04 IN cur_ac04 LOOPIF rec_ac04.aac008 = '1' THENn_jfje := rec_ac04.akc010 * 0.08;END IF;
END LOOP;

❌ 低效写法

Sql

深色版本

FOR rec_ac04 IN (SELECT aac001, akc010 FROM ac04) LOOPSELECT aac008 INTO str_aac008 FROM ac01 WHERE aac001 = rec_ac04.aac001;-- 循环内查询，100万次 = 数小时
END LOOP;

---

11. 使用批量绑定（BULK COLLECT + FORALL）

减少上下文切换，提升大批量DML性能。

✅ 推荐写法

Sql

深色版本

DECLARETYPE t_ac04 IS TABLE OF ac04%ROWTYPE;l_data t_ac04;
BEGINSELECT * BULK COLLECT INTO l_data FROM ac04 WHERE aab001 = '1001';FORALL i IN 1..l_data.COUNTINSERT INTO ac04_bak VALUES l_data(i);COMMIT;
END;

❌ 低效写法

Sql

深色版本

FOR rec IN (SELECT * FROM ac04) LOOPINSERT INTO ac04_bak VALUES rec; -- 逐行插入，性能差
END LOOP;

---

五、事务控制与辅助技巧

12. 合理控制 COMMIT 频率

平衡资源释放与提交开销。

✅ 推荐策略

Sql

深色版本

n_count := 0;
FOR rec IN cur_data LOOPINSERT 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 bWHERE a.aac002 = b.aac002AND 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对话！