问题分析 #

原查询添加NOT IN条件后性能骤降，核心原因有两点：

1. NOT IN子查询会触发全表扫描，且如果子查询结果包含NULL，会导致整个条件逻辑异常（所有匹配行被过滤）；
2. 拼接字符串TRIM(P.EEP_AGENT_COMPANY) || TRIM(P.EEP_AGENT_NUMBER)无法利用字段索引，进一步拖慢查询速度。

你之前尝试NOT EXISTS时报错ORA-00920，大概率是关联条件的语法错误，比如未正确关联主查询和子查询的字段，或是误用了运算符。

重构后的查询代码 #

SELECT DISTINCT
    I.LNGINDCNT,
    I.STRFIRSTNAME,
    I.STRMIDDLENAME,
    I.STRLASTNAME,
    I.STRTAXIDNUM,
    I.STRNPNUM,
    I.STRSTATUS,
    I.STRHIREDATE,
    I.STRSTCODELIC1,
    CASE WHEN A.LNGAPPTCNT IS NULL THEN 'N' ELSE 'Y' END AS LNGAPPTCNT_FLG,
    CASE WHEN L.LNGLICCNT IS NULL THEN 'N' ELSE 'Y' END AS LNGLICCNT_FLG,
    P.EEP_AGENT_COMPANY,
    P.EEP_AGENT_NUMBER,
    P.EEP_AGENT_TERM_DATE,
    P.EEP_AGENT_RETIRE_DATE
FROM
    STG.STG1_EODS_ELS I
    INNER JOIN EODS_STG.STG1_EODS_AGENT P 
        ON I.STRNPNUM = TRIM(P.EEP_AGENT_NPN)
    LEFT OUTER JOIN EODS_STG.STG1_EODS_APPOINTMENT A 
        ON I.LNGINDCNT = A.LNGINDCNT
    LEFT OUTER JOIN EODS_STG.STG1_EODS_LICENSE L 
        ON I.LNGINDCNT = L.LNGINDCNT
    LEFT OUTER JOIN EODS_STG.STG1_EODS_HRCHY H 
        ON TRIM(P.EEP_AGENT_NUMBER) = H.SALES_AGENT_ID
        AND TRIM(P.EEP_AGENT_COMPANY) = SUBSTR(H.COMPANY_NAME,1,3)
WHERE
    I.LNGINDCNT > 15000000
    AND TRIM(P.EEP_AGENT_COMPANY) IN ('INT','NAT')
    AND I.STRSTCODELIC1 IN ('PA','MT','GU')
    AND UPPER(I.STRSTATUS) = 'ACTIVE'
    AND H.SALES_AGENT_ID IS NULL
    AND P.EEP_AGENT_TERM_DATE = '00000000'
    AND P.EEP_AGENT_RETIRE_DATE IS NULL
    AND NOT EXISTS (
        SELECT 1
        FROM
            BIDS.DIM_EV DP
            JOIN BIDS.FACT_SLS FAAS 
                ON DP.DIM_PRDCR_ID = FAAS.DIM_PRDCR_ID
            JOIN BIDS.DIM_EVS DLE 
                ON FAAS.DIM_LEGAL_ENTITY_ID = DLE.DIM_LEGAL_ENTITY_ID
        WHERE
            DLE.LEGAL_ENTITY_CD = TRIM(P.EEP_AGENT_COMPANY)
            AND DP.AGNT_NO = TRIM(P.EEP_AGENT_NUMBER)
            AND DLE.LEGAL_ENTITY_CD IN ('INT','NAT')
    )

关键优化点说明 #

1. 替换LEFT JOIN为INNER JOIN：原查询WHERE子句过滤了TRIM(P.EEP_AGENT_COMPANY) IN ('INT','NAT')，意味着P表必须有匹配行，改成INNER JOIN可减少不必要的行扫描。
2. 用NOT EXISTS替代NOT IN：
   • NOT EXISTS不会因子查询返回NULL导致逻辑异常；
   • 子查询只需返回1，无需DISTINCT（NOT EXISTS找到匹配就停止扫描，去重完全多余）；
   • 拆分字符串拼接为两个字段的直接匹配，可利用字段索引，大幅提升查询效率。
3. 子查询调整连接类型：原LEFT OUTER JOIN在子查询中无意义，因WHERE过滤了DLE.LEGAL_ENTITY_CD IN ('INT','NAT')，改成JOIN可减少无效行处理。

你之前NOT EXISTS报错的常见原因 #

• 未在子查询WHERE中关联主查询字段（比如漏掉DLE.LEGAL_ENTITY_CD = TRIM(P.EEP_AGENT_COMPANY)这类关联条件）；
• 误用运算符（比如把=写成==，或多写多余符号）；
• 子查询字段引用错误（比如拼写错误，或引用了子查询中未出现的表字段）。

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