Oracle SQL优化：提前过滤+获取离世受试者最新就诊记录

问题背景 #

我们机构需生成跨科室研究报告，统计近3个月内离世的受试者，并包含其最新就诊日期。SQL在JasperReports（Java Web环境运行）执行，因涉及大数量级视图，当前查询速度极慢甚至无法运行。

涉及视图说明 #

• 权限验证类视图：subj_access、sv_user_pcl_permission、sv_user_pcs_access，用于根据contact_id判断数据访问权限，当前通过usa CTE实现权限过滤，功能正常。
• rv_protocol_subject_basic：每个受试者对应一条记录，含is_expired字段标记是否离世，存在大量存活受试者的无效数据。
• vw_subject_visits：存储所有就诊记录，visit_status标记就诊是否完成，需按sequence_number（受试者标识）获取每个受试者visit_status='Occurred'的最新visit_date，当前存在大量无效数据。

当前SQL存在的问题 #

返回每个受试者多条记录，未提前过滤无效数据，导致查询效率低下。需优化为仅获取每个离世受试者的最新已完成就诊记录，并提前过滤数据提升查询速度。

当前SQL代码：

WITH usa AS (
    SELECT subj_access.protocol_id, subj_access.protocol_subject_id
    FROM sv_user_pcl_permission priv_check
    JOIN sv_user_pcs_access subj_access ON priv_check.protocol_id = subj_access.protocol_id AND priv_check.contact_id = subj_access.contact_id
    WHERE priv_check.function_name = 'CRPT-Subject Visits'
    AND priv_check.contact_id = '1234'
    -- 实际为运行报表时的动态参数，每个用户的contact_id不同
)
SELECT usa.protocol_id, pd.protocol_no, sub_p.subject_no, sub_p.subject_mrn, sub_p.sequence_number, sub_p.subject_status, sub_p.is_expired, sub_p.expired_date, pd.visit_status, pd.visit_date
FROM usa
INNER JOIN RV_Protocol_subject_basic sub_p ON sub_p.protocol_id = usa.protocol_id
INNER JOIN vw_subject_visits pd ON pd.protocol_id = usa.protocol_id and pd.sequence_number = sub_p.sequence_number
WHERE sub_p.is_expired = 'Y', pd.visit_status = 'Occurred'
ORDER BY pd.Protocol_no,sub_p.expired_date

优化方案 #

优化思路 #

1. 提前过滤核心数据：先筛选出近3个月内离世的受试者，减少后续关联的数据量。
2. 预聚合最新就诊记录：在CTE中完成每个受试者最新已完成就诊记录的筛选，避免主查询产生冗余数据。
3. 精简关联逻辑：确保仅关联必要的过滤后数据，降低查询计算量。

优化后SQL代码 #

WITH usa AS (
    SELECT subj_access.protocol_id, subj_access.protocol_subject_id
    FROM sv_user_pcl_permission priv_check
    JOIN sv_user_pcs_access subj_access ON priv_check.protocol_id = subj_access.protocol_id AND priv_check.contact_id = subj_access.contact_id
    WHERE priv_check.function_name = 'CRPT-Subject Visits'
    AND priv_check.contact_id = '1234' -- 动态参数，运行时替换为当前用户ID
),
-- 筛选近3个月内离世的受试者，提前过滤无效数据
expired_subjects AS (
    SELECT 
        sub_p.protocol_id,
        sub_p.subject_no,
        sub_p.subject_mrn,
        sub_p.sequence_number,
        sub_p.subject_status,
        sub_p.is_expired,
        sub_p.expired_date
    FROM usa
    INNER JOIN rv_protocol_subject_basic sub_p 
        ON sub_p.protocol_id = usa.protocol_id 
        AND sub_p.is_expired = 'Y'
        -- 过滤近3个月内离世的受试者，根据实际字段调整日期逻辑
        AND sub_p.expired_date >= ADD_MONTHS(SYSDATE, -3)
),
-- 获取每个离世受试者的最新已完成就诊记录
latest_visits AS (
    SELECT 
        pd.protocol_id,
        pd.sequence_number,
        pd.protocol_no,
        pd.visit_status,
        pd.visit_date
    FROM vw_subject_visits pd
    INNER JOIN expired_subjects es 
        ON pd.protocol_id = es.protocol_id 
        AND pd.sequence_number = es.sequence_number
    WHERE pd.visit_status = 'Occurred'
    -- 窗口函数筛选每个受试者的最新就诊记录（Oracle 12c及以上支持）
    QUALIFY ROW_NUMBER() OVER (PARTITION BY pd.sequence_number ORDER BY pd.visit_date DESC) = 1
)
-- 最终关联获取所需字段
SELECT 
    es.protocol_id,
    lv.protocol_no,
    es.subject_no,
    es.subject_mrn,
    es.sequence_number,
    es.subject_status,
    es.is_expired,
    es.expired_date,
    lv.visit_status,
    lv.visit_date
FROM expired_subjects es
INNER JOIN latest_visits lv 
    ON es.protocol_id = lv.protocol_id 
    AND es.sequence_number = lv.sequence_number
ORDER BY lv.protocol_no, es.expired_date;

优化说明 #

• expired_subjects CTE：提前筛选出近3个月内离世且用户有权限访问的受试者，大幅减少后续关联的数据量。
• latest_visits CTE：使用QUALIFY+窗口函数直接筛选每个受试者的最新已完成就诊记录，避免返回多条重复数据。
• 所有关联均基于已过滤后的数据集，减少了大视图的全表扫描范围，提升查询效率。

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