含左连接与拼接值的Oracle SQL查询运行过慢求优化

我通过关联3个子查询获取最终结果，但该查询运行耗时长达45分钟。由于仅拥有数据库只读权限，无法创建索引或修改表结构，只能执行SELECT语句，急需优化运行速度缩短返回时间。

原查询语句：

SELECT 
    C.TRANSACTION_ID, 
    A.LOAN_NUMBER, A.TRAN_DATE, A.ADVANCE_TYPE, A.ADV_PROCESSOR_ID, 
    A.ADV_TRAN_CODE, A.CORP_PAYEE_ID, A.REASON_CODE, 
    A.CATEGORY_DESCRIPTION, A.TRANSACTION_AMOUNT, 
    C.CLAIM_AMOUNT, A.PAYEE_ID, 
    B.DEPT, B.CAT, B.DESCRIPTION AS SUBCAT, B.LIITEM_NOTE, 
    B.COMPL_DT, B.SVC_FROM_DT, B.SVC_TO_DT, 
    NVL(B.VENDOR_NAME,A.PAYEE_ID) AS VENDOR_NAME, B.INVOICE_NUMBER, 
    C.CLAIM_ID, C.CLAIM_STATUS, C.CLAIM_TYPE, C.LOSS_REASON, C.DETAILED_LOSS_REASON
FROM 
    (SELECT 
         LOAN_NUMBER, 
         NVL(CA_HIST_ORIGINAL_DISB_DATE,CA_HIST_TRANSACTION_DATE) TRAN_DATE, 
         CASE   
             WHEN CA_HIST_RECOVERABLE_CODE IN ('T') THEN 'TPCA'     
             WHEN CA_HIST_RECOVERABLE_CODE IN ('N') THEN 'NRCA'     
             WHEN CA_HIST_RECOVERABLE_CODE IN ('R') THEN 'MRCA'  
         END AS ADVANCE_TYPE, 
         CA_HIST_PROCESSOR_ID AS ADV_PROCESSOR_ID, 
         CA_HIST_TRANSACTION_CODE ADV_TRAN_CODE, 
         CA_HIST_CORPORATE_PAYEE_ID CORP_PAYEE_ID, 
         CA_HIST_REASON_CODE REASON_CODE, 
         CA_HIST_REASON_DESCRIPTION AS CATEGORY_DESCRIPTION, 
         (SELECT PAYEE_ADDRESS_LINE_1 
          FROM CPI.D_PAYEE@OCN_DBL_MICCRPT_CONN 
          WHERE PAYEE_ID = CA_HIST_PAYEE_ID AND ROWNUM = 1) AS PAYEE_ID, 
         (CASE 
              WHEN CA_HIST_TRANSACTION_CODE IN (710, 711, 712, 713, 714, 766) 
                  THEN (CA_HIST_ADVANCE_AMOUNT) * - 1 
              ELSE (CA_HIST_ADVANCE_AMOUNT) 
          END) TRANSACTION_AMOUNT,  
         (ROW_NUMBER() OVER (PARTITION BY NVL(CA_HIST_ORIGINAL_DISB_DATE, CA_HIST_TRANSACTION_DATE), CA_HIST_CORPORATE_PAYEE_ID, CA_HIST_REASON_CODE, (CASE WHEN CA_HIST_TRANSACTION_CODE IN (710,711,712,713,714,766) THEN ABS(CA_HIST_ADVANCE_AMOUNT) * - 1   ELSE (CA_HIST_ADVANCE_AMOUNT) END) ORDER BY NVL(CA_HIST_ORIGINAL_DISB_DATE,CA_HIST_TRANSACTION_DATE))) ROW_CORP FROM BDE.CORPORATE_ADV_HIST@OCN_MICC_GC_RPT_CDW_CONN) A
LEFT JOIN 
    (SELECT 
         (ROW_NUMBER() OVER (PARTITION BY TRUNC(PYMT.CREATD_DT), PYMT.TRANS_CD, PYMT.CORP_ADV_CD, PYMT.RSN_CD, PYMT.DISBD_AMT 
                             ORDER BY TRUNC(PYMT.CREATD_DT))) AS ROW_LPS, 
         LITEM.SVCR_LOAN_NUM AS LOAN_NUMBER, 
         TRUNC(PYMT.CREATD_DT) AS TRANDATE, 
         PYMT.TRANS_CD, PYMT.CORP_ADV_CD, PYMT.RSN_CD, 
         INV.DEPT, LITEM.CAT, LITEM.SUB_CAT AS DESCRIPTION, 
         LITEM.LIITEM_NOTE, PYMT.DISBD_AMT AS AMOUNT, 
         LITEM.LI_ITEM_DT AS COMPL_DT, LITEM.SVC_FROM_DT, 
         LITEM.SVC_TO_DT, INV.VEND_NM AS VENDOR_NAME, 
         LITEM.INVC_NUM AS INVOICE_NUMBER 
     FROM 
         LS_INVC.INVC@OCN_MICC_GC_RPT_cdw_CONN INV, 
         LS_INVC.INVC_LIITEM@OCN_MICC_GC_RPT_cdw_CONN LITEM, 
         LS_INVC.INVC_LIITEM_PAYMT@OCN_MICC_GC_RPT_cdw_CONN PYMT 
     WHERE
         INV.INVC_ID = LITEM.INVC_ID 
         AND LITEM.INVC_LIITEM_ID = PYMT.INVC_LIITEM_ID) B ON TO_NUMBER(A.LOAN_NUMBER) = B.LOAN_NUMBER AND A.TRAN_DATE=B.TRANDATE AND A.CORP_PAYEE_ID||'_'||A.REASON_CODE=B.CORP_ADV_CD||'_'||B.RSN_CD AND A.TRANSACTION_AMOUNT=B.AMOUNT AND A.ROW_CORP=B.ROW_LPS
LEFT JOIN 
    (SELECT 
         ROW_NUMBER() OVER (PARTITION BY C.TRANSACTION_DATE, SUBSTR(C.CATEGORY_CODE, 1, 10), (C.TRANSACTION_AMOUNT) 
                            ORDER BY C.TRANSACTION_DATE) ROW_MICC, 
         TRANSACTION_ID, CATEGORY_CODE, TRANSACTION_AMOUNT, LOAN_NUM, 
         CLAIM_ID, TRANSACTION_DATE, CLAIM_AMOUNT, CLAIM_STATUS, 
         CLAIM_TYPE, LOSS_REASON, DETAILED_LOSS_REASON 
     FROM 
         MICC_RPT.CL_TRAN_LVL_DATA C) C ON B.LOAN_NUMBER = TO_NUMBER(C.LOAN_NUM) 
                                        AND B.TRANDATE = C.TRANSACTION_DATE 
                                        AND B.CORP_ADV_CD||'_'||B.RSN_CD = SUBSTR(C.CATEGORY_CODE, 1, 10) 
                                        AND B.AMOUNT = C.TRANSACTION_AMOUNT 
                                        AND B.ROW_LPS = C.ROW_MICC
WHERE 
    A.LOAN_NUMBER = '1234567890'
ORDER BY 
    A.LOAN_NUMBER, A.TRAN_DATE, A.ADV_TRAN_CODE, A.CORP_PAYEE_ID, 
    A.REASON_CODE, A.TRANSACTION_AMOUNT;

优化建议及修改后的查询 #

核心优化点 #

• 提前过滤数据：将外层的A.LOAN_NUMBER = '1234567890'推到所有子查询中，直接减少每个子查询需要处理的数据量
• 替换标量子查询为JOIN：避免每行执行一次子查询，改用批量LEFT JOIN提升效率
• 移除连接条件中的函数/字符串拼接：改用字段直接匹配，减少计算开销同时避免隐式转换导致的性能损耗
• 简化窗口函数计算：将重复的CASE表达式提前计算为独立字段，减少分区时的重复运算
• 使用显式JOIN语法：替代旧式逗号分隔表的写法，让执行计划逻辑更清晰

修改后的查询语句 #

SELECT 
    C.TRANSACTION_ID, 
    A.LOAN_NUMBER, A.TRAN_DATE, A.ADVANCE_TYPE, A.ADV_PROCESSOR_ID, 
    A.ADV_TRAN_CODE, A.CORP_PAYEE_ID, A.REASON_CODE, 
    A.CATEGORY_DESCRIPTION, A.TRANSACTION_AMOUNT, 
    C.CLAIM_AMOUNT, NVL(P.PAYEE_ADDRESS_LINE_1, 'UNKNOWN') AS PAYEE_ID, 
    B.DEPT, B.CAT, B.DESCRIPTION AS SUBCAT, B.LIITEM_NOTE, 
    B.COMPL_DT, B.SVC_FROM_DT, B.SVC_TO_DT, 
    NVL(B.VENDOR_NAME, NVL(P.PAYEE_ADDRESS_LINE_1, 'UNKNOWN')) AS VENDOR_NAME, B.INVOICE_NUMBER, 
    C.CLAIM_ID, C.CLAIM_STATUS, C.CLAIM_TYPE, C.LOSS_REASON, C.DETAILED_LOSS_REASON
FROM 
    -- 子查询A：提前过滤贷款号，替换标量子查询为LEFT JOIN，简化窗口函数
    (SELECT 
         CH.LOAN_NUMBER, 
         TRUNC(NVL(CH.CA_HIST_ORIGINAL_DISB_DATE, CH.CA_HIST_TRANSACTION_DATE)) TRAN_DATE, 
         CASE   
             WHEN CH.CA_HIST_RECOVERABLE_CODE IN ('T') THEN 'TPCA'     
             WHEN CH.CA_HIST_RECOVERABLE_CODE IN ('N') THEN 'NRCA'     
             WHEN CH.CA_HIST_RECOVERABLE_CODE IN ('R') THEN 'MRCA'  
         END AS ADVANCE_TYPE, 
         CH.CA_HIST_PROCESSOR_ID AS ADV_PROCESSOR_ID, 
         CH.CA_HIST_TRANSACTION_CODE ADV_TRAN_CODE, 
         CH.CA_HIST_CORPORATE_PAYEE_ID CORP_PAYEE_ID, 
         CH.CA_HIST_REASON_CODE REASON_CODE, 
         CH.CA_HIST_REASON_DESCRIPTION AS CATEGORY_DESCRIPTION, 
         CASE 
             WHEN CH.CA_HIST_TRANSACTION_CODE IN (710, 711, 712, 713, 714, 766) 
                 THEN CH.CA_HIST_ADVANCE_AMOUNT * -1 
             ELSE CH.CA_HIST_ADVANCE_AMOUNT 
         END TRANSACTION_AMOUNT,
         -- 提前计算窗口函数需要的金额字段
         CASE 
             WHEN CH.CA_HIST_TRANSACTION_CODE IN (710, 711, 712, 713, 714, 766) 
                 THEN ABS(CH.CA_HIST_ADVANCE_AMOUNT) * -1 
             ELSE CH.CA_HIST_ADVANCE_AMOUNT 
         END PARTITION_AMOUNT,
         ROW_NUMBER() OVER (
             PARTITION BY TRUNC(NVL(CH.CA_HIST_ORIGINAL_DISB_DATE, CH.CA_HIST_TRANSACTION_DATE)), 
                          CH.CA_HIST_CORPORATE_PAYEE_ID, 
                          CH.CA_HIST_REASON_CODE, 
                          PARTITION_AMOUNT
             ORDER BY TRUNC(NVL(CH.CA_HIST_ORIGINAL_DISB_DATE, CH.CA_HIST_TRANSACTION_DATE))
         ) ROW_CORP 
     FROM BDE.CORPORATE_ADV_HIST@OCN_MICC_GC_RPT_CDW_CONN CH
     WHERE CH.LOAN_NUMBER = '1234567890') A -- 提前过滤贷款号
LEFT JOIN CPI.D_PAYEE@OCN_DBL_MICCRPT_CONN P 
    ON P.PAYEE_ID = CH.CA_HIST_PAYEE_ID -- 替换标量子查询为LEFT JOIN
LEFT JOIN 
    -- 子查询B：改用显式INNER JOIN，提前处理贷款号类型
    (SELECT 
         ROW_NUMBER() OVER (
             PARTITION BY TRUNC(PYMT.CREATD_DT), PYMT.TRANS_CD, PYMT.CORP_ADV_CD, PYMT.RSN_CD, PYMT.DISBD_AMT 
             ORDER BY TRUNC(PYMT.CREATD_DT)
         ) AS ROW_LPS, 
         TO_CHAR(LITEM.SVCR_LOAN_NUM) AS LOAN_NUMBER, -- 转换为字符串匹配A的LOAN_NUMBER
         TRUNC(PYMT.CREATD_DT) AS TRANDATE, 
         PYMT.TRANS_CD, PYMT.CORP_ADV_CD, PYMT.RSN_CD, 
         INV.DEPT, LITEM.CAT, LITEM.SUB_CAT AS DESCRIPTION, 
         LITEM.LIITEM_NOTE, PYMT.DISBD_AMT AS AMOUNT, 
         LITEM.LI_ITEM_DT AS COMPL_DT, LITEM.SVC_FROM_DT, 
         LITEM.SVC_TO_DT, INV.VEND_NM AS VENDOR_NAME, 
         LITEM.INVC_NUM AS INVOICE_NUMBER 
     FROM LS_INVC.INVC@OCN_MICC_GC_RPT_cdw_CONN INV
     INNER JOIN LS_INVC.INVC_LIITEM@OCN_MICC_GC_RPT_cdw_CONN LITEM 
         ON INV.INVC_ID = LITEM.INVC_ID 
     INNER JOIN LS_INVC.INVC_LIITEM_PAYMT@OCN_MICC_GC_RPT_cdw_CONN PYMT 
         ON LITEM.INVC_LIITEM_ID = PYMT.INVC_LIITEM_ID
     WHERE TO_CHAR(LITEM.SVCR_LOAN_NUM) = '1234567890') B -- 提前过滤贷款号
ON A.LOAN_NUMBER = B.LOAN_NUMBER 
   AND A.TRAN_DATE = B.TRANDATE 
   AND A.CORP_PAYEE_ID = B.CORP_ADV_CD -- 移除字符串拼接，直接匹配字段
   AND A.REASON_CODE = B.RSN_CD 
   AND A.TRANSACTION_AMOUNT = B.AMOUNT 
   AND A.ROW_CORP = B.ROW_LPS
LEFT JOIN 
    -- 子查询C：提前过滤贷款号，拆分CATEGORY_CODE匹配对应字段
    (SELECT 
         ROW_NUMBER() OVER (
             PARTITION BY TRUNC(C.TRANSACTION_DATE), C.CATEGORY_CODE, C.TRANSACTION_AMOUNT 
             ORDER BY TRUNC(C.TRANSACTION_DATE)
         ) ROW_MICC, 
         TRANSACTION_ID, CATEGORY_CODE, TRANSACTION_AMOUNT, 
         TO_CHAR(C.LOAN_NUM) AS LOAN_NUM, -- 转换为字符串匹配
         CLAIM_ID, TRUNC(C.TRANSACTION_DATE) AS TRANSACTION_DATE, 
         CLAIM_AMOUNT, CLAIM_STATUS, CLAIM_TYPE, LOSS_REASON, DETAILED_LOSS_REASON 
     FROM MICC_RPT.CL_TRAN_LVL_DATA C
     WHERE TO_CHAR(C.LOAN_NUM) = '1234567890') C -- 提前过滤贷款号
ON B.LOAN_NUMBER = C.LOAN_NUM 
   AND B.TRANDATE = C.TRANSACTION_DATE 
   AND B.CORP_ADV_CD = SUBSTR(C.CATEGORY_CODE, 1, INSTR(C.CATEGORY_CODE, '_') - 1) -- 拆分匹配CORP_ADV_CD
   AND B.RSN_CD = SUBSTR(C.CATEGORY_CODE, INSTR(C.CATEGORY_CODE, '_') + 1) -- 拆分匹配RSN_CD
   AND B.AMOUNT = C.TRANSACTION_AMOUNT 
   AND B.ROW_LPS = C.ROW_MICC
ORDER BY 
    A.LOAN_NUMBER, A.TRAN_DATE, A.ADV_TRAN_CODE, A.CORP_PAYEE_ID, 
    A.REASON_CODE, A.TRANSACTION_AMOUNT;

额外优化提示 #

• 执行EXPLAIN PLAN FOR对比原查询和优化后查询的执行计划，重点排查全表扫描、笛卡尔积等低效操作
• 若跨库查询存在网络延迟，可申请将关联的小表数据临时导出到本地（若权限允许），减少跨库数据传输开销
• 确认所有日期字段的类型是否一致，避免隐式转换导致的性能损耗

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