含REGEXP_REPLACE的Oracle查询性能调优求助（耗时约12分钟）

问题背景 #

• EDW_STG.STG2_SRC表：27,243,705条记录
• EDW_STG.TMP_STG2表：9,948,154条记录
• 目标SQL执行耗时约12分钟，核心瓶颈集中在窗口函数（LISTAGG+REGEXP_REPLACE去重）、表关联环节

原始SQL #

SELECT
        P.UNQ_SRC_ID,
        P.UNQ_CNTRCT_ID,
        P.PLCY_CNTRCT_ID,
        P.SOR_CD,
        P.SRC_AGRMNT_INCPTN_DT,
        P.AGRMNT_INCPTN_DT,
        P.AGRMNT_NM,
        P.SRC_AGRMNT_TYP,
        P.AGRMNT_TYP,
        P.SRC_ISS_CMPNY_CD,
        P.ISS_CMPNY_CD,
        P.SRC_SECNDRY_CMPNY_CD,
        P.SECNDRY_CMPNY_CD,
        P.PLCY_PLAN_PROD_NM,
        P.SRC_STAT_CD,
        P.AGRMNT_STAT_TXT,
        P.AGRMNT_STAT_CLASS_TXT,
        P.PLCY_TYP,
        P.INET_SRC_CD,
        P.SRC_BACC_CLIENT_ACCT_ID,
        P.BACC_CLIENT_ACCT_ID,
        P.PLAN_CD,
        P.LOB_CD,
        P.PLCY_AMT,
        P.SRC_PLCY_ISS_DT,
        P.PLCY_ISS_DT,
        P.SRC_EXPIRY_DT,
        P.SRC_CEASE_DT,
        P.SRC_PLCY_TERM_DT,
        P.PLCY_TERM_DT,
        P.PLCY_ADTNL_TERM_AMT,
        P.ANTY_CY_VAL_AMT,
        P.ANTY_INIT_YR_VAL_AMT,
        P.ANTY_OPT_CD,
        P.ANTY_CUR_AMT,
        P.AVLBL_SURR_AMT,
        P.AGRMNT_CMNT_TXT,
        P.AS_OF_DT,
        P.UNQ_CNTRCT_PARTY_ID,
        P.UNQ_PARTY_ID,
        P.SRC_PARTY_TYP,
        P.PARTY_TYP,
        P.PARTY_STAT_TXT,
        P.CLIENT_TYP,
        P.SRC_CLIENT_STAT_TXT,
        MAX(
            P.CLIENT_STAT_TXT
        ) OVER(PARTITION BY
            P.UNQ_CNTRCT_PARTY_ID
        ) AS CLIENT_STAT_TXT,
        P.NM_PARSE_TYP,
        P.SRC_FULL_NM,
        P.FULL_NM,
        P.UNPARSED_NM,
        P.PARSE_STAT_OUTP_TXT,
        P.SRC_PREFIX_TXT,
        P.PREFIX_TXT,
        P.SRC_FIRST_NM,
        P.FIRST_NM,
        P.SRC_MIDL_NM,
        P.MIDL_NM,
        P.SRC_LAST_NM,
        P.LAST_NM,
        P.SRC_SUFFIX_TXT,
        P.SUFFIX_TXT,
        P.PREFRD_NM,
        P.TITLE_TXT,
        P.OCPTN_TXT,
        P.CITIZENSHIP_TXT,
        P.SRC_SSN_CD,
        P.TXPYR_ID_NO,
        MAX(
            P.TXPYR_ID_NO_SOR_CD
        ) OVER(PARTITION BY
            P.UNQ_CNTRCT_PARTY_ID
        ) AS TXPYR_ID_NO_SOR_CD,
        P.SRC_BIRTH_DT,
        P.BIRTH_DT,
        P.SRC_BIRTH_PLACE_NM,
        REGEXP_REPLACE(
            TRIM(
                LISTAGG(
                    P.BIRTH_PLACE_NM,
                    '; '
                ) WITHIN GROUP(ORDER BY P.BIRTH_PLACE_NM) OVER(PARTITION BY
                    P.UNQ_CNTRCT_PARTY_ID
                )
            ),
            '(^|; )([^;]*)(; \2)+',
            '\1\2'
        ) AS BIRTH_PLACE_NM,
        P.SRC_DTH_DT,
        MAX(
            P.DTH_DT
        ) OVER(PARTITION BY
            P.UNQ_CNTRCT_PARTY_ID
        ) AS DTH_DT,
        P.DTH_DT_SRC_TXT,
        P.DTH_CERT_LOC,
        P.SRC_GENDER_CD,
        MIN(
            P.GENDER_CD
        ) OVER(PARTITION BY
            P.UNQ_CNTRCT_PARTY_ID
        ) AS GENDER_CD,
        P.PREFRD_LANG_TXT,
        P.ALT_LANG_TXT,
        P.SRC_ENTITY_NM,
        P.ENTITY_NM,
        P.ENTITY_TYP,
        P.ENTITY_ALT_NM,
        P.ENTITY_ACRONYM_NM,
        P.ENTITY_INDUSTRY_CD,
        P.ENTITY_DESC_TXT,
        P.DUN_AND_BRADSTREET_ID,
        P.SRC_CMNT_TXT,
        REGEXP_REPLACE(
            TRIM(
                LISTAGG(
                    P.CMNT_TXT,
                    '; '
                ) WITHIN GROUP(ORDER BY P.CMNT_TXT) OVER(PARTITION BY
                    P.UNQ_CNTRCT_PARTY_ID
                )
            ),
            '(^|; )([^;]*)(; \2)+',
            '\1\2'
        ) AS CMNT_TXT,
        P.UNQ_CNTRCT_PARTY_ROLE_ID,
        P.ROLE_ID,
        P.SRC_PARTY_ROLE_NM,
        P.PARTY_ROLE_NM,
        P.PARTY_SOR_CD,
        P.BENE_SHARE_RT,
        P.IRREVOCABLE_BENE_FLG,
        P.BENE_RELATIONSHIP_TXT,
        P.BENE_TYPE,
        P.PARTY_ROLE_CMNT_TXT,
        P.UNQ_CNTRCT_PARTY_ADDR_ID,
        P.ADDR_SRC_ID,
        P.ADDR_TYP,
        P.SRC_ATTEN_LN_TXT,
        P.ATTEN_LN_TXT,
        P.SRC_DELVRY_ADDR_TXT,
        P.DELVRY_ADDR_TXT,
        P.SRC_ADDR1_TXT,
        P.ADDR1_TXT,
        P.SRC_ADDR2_TXT,
        P.ADDR2_TXT,
        P.SRC_ADDR3_TXT,
        P.ADDR3_TXT,
        P.SRC_ADDR4_TXT,
        P.ADDR4_TXT,
        P.SRC_CITY_NM,
        P.CITY_NM,
        P.SRC_STATE_CD,
        P.STATE_CD,
        P.SRC_POSTAL_CD,
        P.POSTAL_CD,
        P.SRC_CNTRY_NM,
        P.SRC_CNTRY_LIT,
        P.CNTRY_NM,
        P.ADDR_CMNT_TXT,
        P.UNQ_CNTRCT_PARTY_EMAIL_ID,
        P.EMAIL_ADDR_SRC_ID,
        P.EMAIL_TYP,
        P.SRC_EMAIL_ADDR_TXT,
        P.EMAIL_ADDR_TXT,
        P.EMAIL_CMNT_TXT,
        P.UNQ_CNTRCT_PARTY_PH_ID,
        P.SRC_PH_NO,
        P.PH_NO,
        P.PH_TYP,
        P.PH_NO_SRC_ID,
        P.PH_CNTRY_CD,
        P.PH_AREA_CD,
        P.PH_EXCH_NO,
        P.PH_LN_NO,
        P.PH_EXT_NO,
        P.DO_NOT_CALL_FLG,
        P.CONVENIENT_TM_TXT,
        P.PH_CMNT_TXT,
        P.ISS_STATE_CD,
        S.UNQ_SRC_ID,
        S.SOR_CD,
        S.ETL_REC_STAT_IND,
        S.ETL_CHKSUM1_NO
    FROM
        EDW_STG.STG2_SRC S
        LEFT OUTER JOIN EDW_STG.TMP_STG2 P ON (P.UNQ_SRC_ID = S.UNQ_SRC_ID AND P.SOR_CD = S.SOR_CD)
    WHERE
        S.SOR_CD IN ('UL','TM')

执行计划关键步骤翻译 #

1. 对STG2_SRC表按SOR_CD IN ('UL','TM')过滤，执行索引范围扫描（INDEX RANGE SCAN）
2. 以STG2_SRC为驱动表，与TMP_STG2表执行哈希连接（HASH JOIN），关联键为UNQ_SRC_ID + SOR_CD
3. 对关联结果集执行多组窗口函数计算：包括MAX/MIN分区聚合、LISTAGG拼接后正则去重
4. 最终生成结果集并返回

调优方案 #

一、窗口函数去重逻辑优化（核心瓶颈） #

原SQL用LISTAGG拼接后再通过REGEXP_REPLACE去重，正则匹配对大字符串开销极大。Oracle 12c+支持LISTAGG直接加DISTINCT，可彻底替换正则去重逻辑：

WITH P_PREPROCESSED AS (
    SELECT 
        P.*,
        -- 替换原REGEXP_REPLACE逻辑，直接去重拼接
        TRIM(LISTAGG(DISTINCT P.BIRTH_PLACE_NM, '; ') WITHIN GROUP(ORDER BY P.BIRTH_PLACE_NM) OVER(PARTITION BY P.UNQ_CNTRCT_PARTY_ID)) AS BIRTH_PLACE_NM,
        TRIM(LISTAGG(DISTINCT P.CMNT_TXT, '; ') WITHIN GROUP(ORDER BY P.CMNT_TXT) OVER(PARTITION BY P.UNQ_CNTRCT_PARTY_ID)) AS CMNT_TXT,
        -- 合并同分区的窗口函数计算，减少重复扫描
        MAX(P.CLIENT_STAT_TXT) OVER(PARTITION BY P.UNQ_CNTRCT_PARTY_ID) AS CLIENT_STAT_TXT,
        MAX(P.TXPYR_ID_NO_SOR_CD) OVER(PARTITION BY P.UNQ_CNTRCT_PARTY_ID) AS TXPYR_ID_NO_SOR_CD,
        MAX(P.DTH_DT) OVER(PARTITION BY P.UNQ_CNTRCT_PARTY_ID) AS DTH_DT,
        MIN(P.GENDER_CD) OVER(PARTITION BY P.UNQ_CNTRCT_PARTY_ID) AS GENDER_CD
    FROM EDW_STG.TMP_STG2 P
    WHERE P.SOR_CD IN ('UL','TM') -- 提前过滤无关数据
)
SELECT 
    -- 直接引用预处理后的窗口计算结果
    P.UNQ_SRC_ID,
    P.UNQ_CNTRCT_ID,
    -- ...其他P表字段...
    P.CLIENT_STAT_TXT,
    P.TXPYR_ID_NO_SOR_CD,
    P.BIRTH_PLACE_NM,
    P.DTH_DT,
    P.GENDER_CD,
    P.CMNT_TXT,
    -- S表字段
    S.UNQ_SRC_ID,
    S.SOR_CD,
    S.ETL_REC_STAT_IND,
    S.ETL_CHKSUM1_NO
FROM EDW_STG.STG2_SRC S
LEFT JOIN P_PREPROCESSED P ON P.UNQ_SRC_ID = S.UNQ_SRC_ID AND P.SOR_CD = S.SOR_CD
WHERE S.SOR_CD IN ('UL','TM')

二、表关联与索引优化 #

1. 创建复合索引：

   • 给STG2_SRC表创建过滤+关联索引：

     CREATE INDEX IDX_STG2_SRC_SOR_UNQ ON EDW_STG.STG2_SRC(SOR_CD, UNQ_SRC_ID);
     

     利用WHERE条件过滤，同时覆盖关联键，减少回表开销
   • 给TMP_STG2表创建关联键索引：

     CREATE INDEX IDX_TMP_STG2_UNQ_SOR ON EDW_STG.TMP_STG2(UNQ_SRC_ID, SOR_CD);
     

     如果UNQ_SRC_ID + SOR_CD是唯一键，改为唯一索引性能更优

2. 调整连接方式：
   如果过滤后STG2_SRC记录数较少，可尝试将哈希连接改为嵌套循环连接，需确保TMP_STG2的关联键索引生效

三、临时表预处理优化 #

若窗口函数计算数据量极大，可将预处理结果存入临时表，避免重复计算：

-- 创建临时表存储窗口计算结果
CREATE GLOBAL TEMPORARY TABLE TMP_WINDOW_RES (
    UNQ_SRC_ID VARCHAR2(100),
    UNQ_CNTRCT_ID VARCHAR2(100),
    UNQ_CNTRCT_PARTY_ID VARCHAR2(100),
    -- ...其他需要的字段...
    CLIENT_STAT_TXT VARCHAR2(100),
    BIRTH_PLACE_NM VARCHAR2(1000),
    CMNT_TXT VARCHAR2(2000)
) ON COMMIT PRESERVE ROWS;

-- 插入预处理数据
INSERT INTO TMP_WINDOW_RES
SELECT 
    P.UNQ_SRC_ID,
    P.UNQ_CNTRCT_ID,
    P.UNQ_CNTRCT_PARTY_ID,
    -- ...其他字段...
    MAX(P.CLIENT_STAT_TXT) OVER(PARTITION BY P.UNQ_CNTRCT_PARTY_ID) AS CLIENT_STAT_TXT,
    TRIM(LISTAGG(DISTINCT P.BIRTH_PLACE_NM, '; ') WITHIN GROUP(ORDER BY P.BIRTH_PLACE_NM) OVER(PARTITION BY P.UNQ_CNTRCT_PARTY_ID)) AS BIRTH_PLACE_NM,
    TRIM(LISTAGG(DISTINCT P.CMNT_TXT, '; ') WITHIN GROUP(ORDER BY P.CMNT_TXT) OVER(PARTITION BY P.UNQ_CNTRCT_PARTY_ID)) AS CMNT_TXT
FROM EDW_STG.TMP_STG2 P
WHERE P.SOR_CD IN ('UL','TM');

-- 最终关联查询
SELECT 
    T.*,
    S.UNQ_SRC_ID,
    S.SOR_CD,
    S.ETL_REC_STAT_IND,
    S.ETL_CHKSUM1_NO
FROM EDW_STG.STG2_SRC S
LEFT JOIN TMP_WINDOW_RES T ON T.UNQ_SRC_ID = S.UNQ_SRC_ID AND T.SOR_CD = S.SOR_CD
WHERE S.SOR_CD IN ('UL','TM');

四、其他优化建议 #

• 剔除不必要字段：SELECT中若有不需要的字段，直接删除，减少数据传输与处理开销
• 更新统计信息：确保优化器有准确数据生成最优计划

  DBMS_STATS.GATHER_TABLE_STATS('EDW_STG', 'STG2_SRC');
  DBMS_STATS.GATHER_TABLE_STATS('EDW_STG', 'TMP_STG2');
  

• 调整PGA内存：若使用哈希连接，确保PGA_AGGREGATE_TARGET足够，避免磁盘哈希导致性能下降

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