Oracle SQL含connect by的查询执行缓慢，需优化且保留distinct与双connect by

你的查询性能差的核心原因是：先基于全量符合条件的行做Connect By生成海量重复数据，再通过DISTINCT去重，导致中间数据量爆炸，消耗大量CPU和内存。以下是在保留DISTINCT和两个CONNECT BY逻辑的前提下的优化方案：

优化步骤及修改后的代码 #

1. 提前去重源数据 #

先对WHERE条件筛选后的行做DISTINCT，把每个(GRE, CR)组合只保留一行，再基于这个精简后的数据集做Connect By，从根源减少后续生成的行数。

2. 预计算日期参数 #

用CTE预存所有固定日期和计算结果，避免在Connect By循环中重复解析日期、计算月份差，降低CPU开销。

3. 替换低效的Prior条件 #

用prior sys_guid() IS NOT NULL替代prior dbms_random.value IS NOT NULL，sys_guid()生成唯一标识符的开销远低于dbms_random.value，同时能同样避免Connect By产生层次循环。

修改后的完整代码：

WITH date_params AS (
    SELECT 
        TRUNC(SYSDATE, 'mm') AS current_month,
        TO_DATE('15/04/2024', 'dd/mm/yyyy') AS apr_target_date,
        TO_DATE('01/10/2024', 'dd/mm/yyyy') AS oct_target_date
    FROM DUAL
),
apr_source_data AS (
    SELECT DISTINCT
        TRUNC(rep_date, 'mm') AS base_month,
        gre,
        cr
    FROM CREDIT_HISTORY
    WHERE rep_date = (SELECT apr_target_date FROM date_params)
),
oct_source_data AS (
    SELECT DISTINCT
        TRUNC(rep_date, 'mm') AS base_month,
        gre,
        cr
    FROM CREDIT_HISTORY
    WHERE rep_date = (SELECT oct_target_date FROM date_params)
)
SELECT "REP_DATE","GRE","CR" 
FROM CREDIT_HISTORY

UNION ALL

SELECT 
    ADD_MONTHS(asd.base_month, LEVEL - 1),
    asd.gre,
    asd.cr
FROM apr_source_data asd
CROSS JOIN date_params dp
CONNECT BY LEVEL <= MONTHS_BETWEEN(dp.current_month, dp.apr_target_date)
    AND PRIOR SYS_GUID() IS NOT NULL

UNION ALL

SELECT 
    ADD_MONTHS(osd.base_month, LEVEL),
    osd.gre,
    osd.cr
FROM oct_source_data osd
CROSS JOIN date_params dp
CONNECT BY LEVEL <= MONTHS_BETWEEN(dp.current_month, dp.oct_target_date)
    AND PRIOR SYS_GUID() IS NOT NULL;

额外优化建议 #

• 确保CREDIT_HISTORY表在rep_date字段上有索引，能快速筛选出指定日期的行
• 如果(GRE, CR)组合的去重后数据量仍然很大，可以考虑将这部分去重结果临时存储（比如全局临时表），进一步提升Connect By的执行效率

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