Oracle存储过程中重复内部子查询的优化方法咨询

嘿，这个重复子查询的问题我之前写存储过程时也碰到过，确实能通过几种方式优化，减少不必要的重复查询，提升性能。下面给你分享几个实用的方案：

方案一：用PL/SQL集合缓存查询结果 #

这是我最常用的方案，适合数据量中等的场景。核心思路是一次性把需要的key值查出来存到内存集合里，之后三个游标直接用这个集合过滤，避免多次访问tbl_keys表。

代码示例：

-- 首先定义和key字段类型匹配的集合（可以在存储过程内部定义，也可以全局定义）
TYPE t_key_field_list IS TABLE OF tbl_keys.key_field%TYPE;
l_target_keys t_key_field_list;

-- 一次性查询出type=1的所有key，批量存入集合
SELECT key_field
BULK COLLECT INTO l_target_keys
FROM tbl_keys
WHERE type = 1;

-- 后续游标直接使用集合过滤
OPEN curr1 FOR
SELECT * FROM table1 
WHERE key_field MEMBER OF l_target_keys;

OPEN curr2 FOR
SELECT * FROM table2 
WHERE key_field MEMBER OF l_target_keys;

OPEN curr3 FOR
SELECT * FROM table3 
WHERE key_field MEMBER OF l_target_keys;

优势： #

• 只执行一次tbl_keys的查询，大幅减少对该表的访问次数，尤其当tbl_keys数据量大或查询成本高时，性能提升明显。
• 集合存在内存中，访问速度快，无需额外的磁盘IO。

方案二：用临时表缓存结果 #

如果需要过滤的key数量特别大（比如上万条甚至更多），内存集合可能会占用过多内存导致性能下降，这时候可以用会话级临时表来缓存结果：

代码示例：

-- 先创建会话级临时表（只需要创建一次，后续存储过程可直接复用）
CREATE GLOBAL TEMPORARY TABLE tmp_filter_keys (
    key_field tbl_keys.key_field%TYPE PRIMARY KEY
) ON COMMIT PRESERVE ROWS;

-- 在存储过程中先清空临时表，再插入需要的key值
TRUNCATE TABLE tmp_filter_keys;
INSERT INTO tmp_filter_keys
SELECT key_field FROM tbl_keys WHERE type = 1;

-- 游标通过JOIN临时表来过滤数据
OPEN curr1 FOR
SELECT t1.* 
FROM table1 t1
JOIN tmp_filter_keys tk ON t1.key_field = tk.key_field;

OPEN curr2 FOR
SELECT t2.* 
FROM table2 t2
JOIN tmp_filter_keys tk ON t2.key_field = tk.key_field;

OPEN curr3 FOR
SELECT t3.* 
FROM table3 t3
JOIN tmp_filter_keys tk ON t3.key_field = tk.key_field;

优势： #

• 适合大数据量场景，避免内存溢出问题。
• 可以给临时表的key_field加主键/索引，进一步提升关联查询的速度。
• 临时表的数据是会话私有的，不同会话之间不会互相干扰，安全性高。

额外注意点 #

• 如果tbl_keys的type=1数据可能为空，要注意处理游标无数据的情况，避免后续逻辑出错。
• 使用集合时，BULK COLLECT默认没有行限制，但如果数据量极大，可以搭配LIMIT子句分批加载，避免内存压力。

内容的提问来源于stack exchange，提问作者flap13