请求改写Oracle SQL查询：优化性能并筛选无Completed状态的ADDRESS_ID

方案1：LEFT JOIN + IS NULL（替代低效的NOT IN） #

原查询用NOT IN会重复扫描全表两次，改用LEFT JOIN可以减少表扫描次数，同时避免NOT IN遇到NULL时的结果为空陷阱。

WITH base_data AS (
    SELECT 
        ADDRESSPOSTCODE || '_' || ADDRESSHOUSENUMBER || '_' || ADDRESSFLOORNUMBER || '_' || ADDRESSUNITNUMBER AS ADDRESS_ID,
        ADDRESSHOUSENUMBER || ' ' || ADDRESSSTREETNAME || ' #' || ADDRESSFLOORNUMBER || '-' || ADDRESSUNITNUMBER || ' Singapore ' || ADDRESSPOSTCODE AS ADDRESS,
        NETWORKCONVERAGE,
        ADDRESSBUILDINGTYPE,
        ADDRESSBUILDINGNAME,
        ADDRESSPOSTCODE,
        SUBSTR(FTPPORT, 1, INSTR(FTPPORT, '/', 1) - 1) AS FTP_NAME,
        ORDER_ID_NEW,
        FORM_STATUS AS ORDER_STATUS,
        TO_DATE(INSTALLATIONDATE, 'YYYYMMDD') AS INSTALLATION_DATE,
        FORM_STATUS
    FROM ARADMIN4.ON_ADVANCEDINTERFACE_PORTALRES
    WHERE SUBSTR(ORDER_ID_NEW, 4, 2) = '01'
      AND SCHEDULE = '01'
)
SELECT 
    bd.ADDRESS_ID,
    bd.ADDRESS,
    bd.NETWORKCONVERAGE,
    bd.ADDRESSBUILDINGTYPE,
    bd.ADDRESSBUILDINGNAME,
    bd.ADDRESSPOSTCODE,
    bd.FTP_NAME,
    bd.ORDER_ID_NEW,
    bd.ORDER_STATUS,
    bd.INSTALLATION_DATE
FROM base_data bd
LEFT JOIN (
    SELECT DISTINCT
        ADDRESSPOSTCODE || '_' || ADDRESSHOUSENUMBER || '_' || ADDRESSFLOORNUMBER || '_' || ADDRESSUNITNUMBER AS ADDRESS_ID
    FROM base_data
    WHERE FORM_STATUS = 'Completed'
) completed_addr ON bd.ADDRESS_ID = completed_addr.ADDRESS_ID
WHERE completed_addr.ADDRESS_ID IS NULL;

说明：

• 用CTEbase_data预先计算所有基础数据，避免重复拼接字符串的开销
• 子查询提取所有存在Completed状态的地址ID，加DISTINCT避免同一地址多次关联导致重复结果
• 通过LEFT JOIN + IS NULL筛选出从未出现Completed状态的地址记录

方案2：窗口函数（单次扫描，性能最优） #

利用窗口函数一次扫描表即可统计每个地址的Completed订单数，直接筛选符合条件的记录，是效率最高的写法。

SELECT 
    ADDRESS_ID,
    ADDRESS,
    NETWORKCONVERAGE,
    ADDRESSBUILDINGTYPE,
    ADDRESSBUILDINGNAME,
    ADDRESSPOSTCODE,
    FTP_NAME,
    ORDER_ID_NEW,
    ORDER_STATUS,
    INSTALLATION_DATE
FROM (
    SELECT 
        ADDRESSPOSTCODE || '_' || ADDRESSHOUSENUMBER || '_' || ADDRESSFLOORNUMBER || '_' || ADDRESSUNITNUMBER AS ADDRESS_ID,
        ADDRESSHOUSENUMBER || ' ' || ADDRESSSTREETNAME || ' #' || ADDRESSFLOORNUMBER || '-' || ADDRESSUNITNUMBER || ' Singapore ' || ADDRESSPOSTCODE AS ADDRESS,
        NETWORKCONVERAGE,
        ADDRESSBUILDINGTYPE,
        ADDRESSBUILDINGNAME,
        ADDRESSPOSTCODE,
        SUBSTR(FTPPORT, 1, INSTR(FTPPORT, '/', 1) - 1) AS FTP_NAME,
        ORDER_ID_NEW,
        FORM_STATUS AS ORDER_STATUS,
        TO_DATE(INSTALLATIONDATE, 'YYYYMMDD') AS INSTALLATION_DATE,
        COUNT(CASE WHEN FORM_STATUS = 'Completed' THEN 1 END) OVER (PARTITION BY ADDRESSPOSTCODE, ADDRESSHOUSENUMBER, ADDRESSFLOORNUMBER, ADDRESSUNITNUMBER) AS completed_count
    FROM ARADMIN4.ON_ADVANCEDINTERFACE_PORTALRES
    WHERE SUBSTR(ORDER_ID_NEW, 4, 2) = '01'
      AND SCHEDULE = '01'
)
WHERE completed_count = 0;

说明：

• 用PARTITION BY按地址的四个核心字段分区，避免拼接字符串的性能损耗
• COUNT(CASE...)统计每个地址的Completed订单数量，数量为0则说明该地址无Completed状态订单
• 仅需扫描一次表，性能远优于原查询的两次全表扫描

原查询问题及JOIN改写踩坑原因 #

• 原NOT IN的问题：两次全表扫描导致性能差，且如果子查询返回的ADDRESS_ID包含NULL，NOT IN会直接返回空结果
• JOIN改写失败的常见原因：未加DISTINCT导致重复记录、误用INNER JOIN而非LEFT JOIN、关联条件中地址拼接逻辑错误

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