包含嵌套子查询的SQL无返回且无法结束执行问题咨询

核心问题原因 #

• 冗余子查询重复执行：原SQL的WHERE子句中重复编写了3次SELECT MAX(r.run_id) FROM dbmspcc_runs r WHERE run_comment = 'Example'子查询，以及2次从dbmspcc_units查询name、type的子查询。这些子查询逻辑完全一致，但如果Oracle优化器没有自动识别并做子查询结果物化，就会对主查询扫描的每一行都重新执行一次子查询计算，数据量稍大就会出现执行时间无限拉长的情况，并非真的陷入递归调用。
• 子查询结果未复用：单独执行子查询能快速得到结果，说明子查询本身逻辑无问题，问题出在主查询执行阶段没有复用子查询的固定结果，重复计算消耗了大量数据库资源。

优化方案 #

将固定不变的子查询结果提前提取复用，用WITH子句先计算出需要的常量参数，再带入主查询使用，优化后参考SQL如下：

WITH run_params AS (
    -- 仅计算一次所需参数，全局复用
    SELECT 
        MAX(r.run_id) AS max_run_id,
        MAX(u.name) KEEP (DENSE_RANK LAST ORDER BY u.run_id) AS obj_name,
        MAX(u.type) KEEP (DENSE_RANK LAST ORDER BY u.run_id) AS obj_type
    FROM dbmspcc_runs r
    LEFT JOIN dbmspcc_units u ON r.run_id = u.run_id
    WHERE r.run_comment = 'Example'
)
SELECT 
    LISTAGG(ccb.col, ',') WITHIN GROUP (ORDER BY ccb.col) AS col,
    LISTAGG(ccb.covered, ',') WITHIN GROUP (ORDER BY ccb.col) AS covered,
    s.line,
    s.text
FROM user_source s
JOIN dbmspcc_units ccu 
    ON s.name = ccu.name 
    AND s.type = ccu.type
JOIN run_params p
    ON s.name = p.obj_name
    AND s.type = p.obj_type
    AND ccu.run_id = p.max_run_id
LEFT JOIN dbmspcc_blocks ccb 
    ON ccu.run_id = ccb.run_id 
    AND ccu.object_id = ccb.object_id 
    AND s.line = ccb.line
WHERE ccb.NOT_FEASIBLE = 0
GROUP BY s.line, s.text
ORDER BY s.line;

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