如何通过自连接和分组来调整 SQL 查询

Question

我在游标语句中编写了以下查询，以通过批处理过程更新也具有插入过程的多个表。

但这个过程批量需要超过 30 分钟才能完成，

每次可能需要处理大约 400k 条记录。查询中连接的表在名为 G_FOR_TB 的表中有 600 万条记录，其他表有大约 100 万条记录。如何调整以下查询？

SELECT rs_g_read_flg ,
  RS_SYS_ID ,
  RESP.RSH_SYS_ID ,
  INTM.ENTRY_KEY ,
  PY.POLICY_KEY ,
  CLM.CLAIM_KEY ,
  RSH_SYS_ID ,
  RSH_INTM_NO           AS INTIM_NUM ,
  RSH_POLICY_NO         AS POLICY_NUM ,
  RSH_CLAIM_NO          AS CLAIM_NUM ,
  RESP.RSH_FOR_NO       AS FOR_ID ,
  RSH_REPRESEN_CD AS REPRESEN_CDE ,
  RSH_REPRESEN_NM AS REPRESENT_NM ,
  CASE
    WHEN UPPER(RSH_STS) LIKE '%No Required%' AND NVL(UPPER(RSH_GLX_STS) ,'NA')<>'BOOK LEVEL CLOSE' THEN COALESCE(RSH_FOR_NOT_REQ_REASON ,RSH_SFOREX_COMNT)
    WHEN UPPER(RSH_STS) LIKE '%No Required%' AND NVL(UPPER(RSH_GLX_STS) ,'NA')='BOOK LEVEL CLOSE' THEN RSH_SFOR_CLOSE_REMARKS
    ELSE RSH_SFOREX_COMNT
  END                AS EX_COMMENTS ,
  EMP.EMPLOYEE_KEY   AS Booked_BY ,
  EMP.EMP_FIRST_NAME AS Booked_BY_NAME ,
  RSH_Booked_DT  AS Booked_DATE ,
  NULL               AS BOOK_VISITED_DATE ,
  CASE
    WHEN RSH_DOC_REC_YN='Y' THEN 1
    WHEN RSH_DOC_REC_YN='N' THEN 0
    ELSE NULL
  END DOC_REC_FLAG ,
  RSH_DOC_REC_DT         AS REPORT_RECEIVED_DATE ,
  RSH_REBOOKED_YN        AS REBOOKED_FLAG ,
  RSH_FOR_NOT_REQ_REASON AS REASON_NOT_REQUIRED ,
  RSH_REPRESENT_MOB   AS REPRESENTATIVE_MOBILE_NO ,
  RSH_REPRESENT_TEL   AS REPRESENTATIVE_TEL_NO ,
  CASE
    WHEN RSH_STS                                  ='Booked'         THEN 51
    WHEN RSH_STS                                  ='SFOR Auto Skipped' THEN 52
    WHEN RSH_STS                                  ='DELIVERED'          THEN 5
    WHEN RSH_STS                                  = 'No Required' AND NVL(RSH_GLX_STS ,
      'No Required by Executive at ENTRY Level') =
      'No Required by Executive at ENTRY Level' THEN 217
    WHEN RSH_STS = 'No Required' AND RSH_GLX_STS =
      'BOOK LEVEL CLOSE'            THEN 218
    WHEN RSH_STS ='Rebooked' THEN 51
    ELSE NULL
  END AS STATUS_ID ,
  CASE
    WHEN RSH_STS  ='Booked'                          THEN 'ISFOR'
    WHEN SFOR.STATUS_ID = 51 AND rs_sts ='No Required' THEN 'WSFOR'
    WHEN SFOR.STATUS_ID = 49 AND rs_sts ='No Required' THEN 'ISFOR'
    WHEN RSH_STS  ='DELIVERED'                           THEN 'WSFOR'
    WHEN RSH_STS  ='Rebooked Closed'                   THEN
      'REBOOKEDCLOSEDFROMGLX'
    WHEN RSH_STS ='Rebooked' THEN 'ISFOR'
    ELSE NULL
  END AS PRESENT_LIST ,
  CASE
    WHEN RSH_STS ='Booked'        THEN 'ISFORWSFOR'
    WHEN RSH_STS ='No Required'    THEN 'ISFORSMBT'
    WHEN RSH_STS ='DELIVERED'         THEN 'ISFORWSFOR'
    WHEN RSH_STS ='Rebooked Closed' THEN 'REBOOKEDCLOSEDFROMGLX'
    WHEN RSH_STS ='Rebooked'      THEN 'ISFORWSFOR'
    ELSE NULL
  END                AS OUTCOME ,
  RSH_CALL_OPTION  AS CALL_OPTION ,
  RSH_CAL_REMARK AS CALL_REMARKS ,
  RSH_CAL_MADE    AS CALL_MADE ,
  CASE
    WHEN RSH_ID_REP='Level 1' THEN 1901
    WHEN RSH_ID_REP='Level 2' THEN 1902
    WHEN RSH_ID_REP='Level 3' THEN 1903
  END         AS ALLOTED_ID ,
  RSH_CR_DT AS CRT_DATE ,
  RSH_GLX_YN ,
  RSH_SFOR_UPDATE_YN ,
  RSH_G_UPD_YN ,
  RSH_G_UPD_DATE ,
  FOR_CNT.CNT                        AS SFOR_COUNT ,
  UPPER(RESP.RSH_CODE) AS RSH_CODE ,
  SFOR.SFOR_KEY ,
  SFOR.status_iD AS SFOR_STATUS_ID
  
FROM RESPONSE_HIST RESP
LEFT JOIN RESPONSE RES
ON RES.RS_SYS_ID = RESP.RSH_SYS_ID
LEFT JOIN POLICY_TB PY
ON PY.POLICY_NUM=RESP.RSH_POLICY_NO
LEFT JOIN INTIM_TB INTM
ON RESP.RSH_INTM_NO=INTM.INTIM_NUM
LEFT JOIN CLAIM_tb CLM
ON CLM.CLAIM_NUM=RESP.RSH_CLAIM_NO
LEFT JOIN EMPLOY_MAS EMP
ON UPPER(EMP.EMP_ID)=UPPER(RESP.RSH_CODE)
LEFT JOIN
  (SELECT RS_FOR_NO ,
    COUNT(*)CNT
  FROM RESPONSE R
  WHERE (( NVL(RS_G_YN ,'N')  ='N'
  AND R.RS_FOR_NO              IS NOT NULL )
  AND ( rs_sts              IN ('Rebooked' ,'Rebooked Closed')
  OR rs_sts                 IN ('Rebooked' ,'No Required stopped')))
  OR (NVL(RS_G_YN ,'N')       ='Y'
  AND NVL(RS_FOR_UPDATE_YN ,'N')='Y'
  AND NVL(RS_G_UPD_YN ,'N')   ='N'
  AND R.RS_FOR_NO              IS NOT NULL
  AND ( rs_sts              IN ('Rebooked' ,'Rebooked Closed')
  OR rs_sts                 IN ('Rebooked' ,'No Required stopped')) )
  GROUP BY RS_FOR_NO
  ) FOR_CNT
ON RESP.RSH_FOR_NO=FOR_CNT.RS_FOR_NO
LEFT JOIN
  (SELECT RS_FOR_NO ,
    COUNT(*)CNT_NR
  FROM RESPONSE R
  WHERE (R.RS_FOR_NO IS NOT NULL )
  AND ( rs_sts    IN ('No Required' ,'Rebooked Closed')
    )
  GROUP BY R.RS_FOR_NO
  ) FOR_CNT_NR
ON RESP.RSH_FOR_NO=FOR_CNT_NR.RS_FOR_NO
LEFT JOIN G_FOR_TB SFOR
ON RESP.RSH_FOR_NO=SFOR.FOR_ID
WHERE
  ( NVL(rs_g_read_flg ,'N')='N'
AND UPPER(RSH_STS)        <> 'DELIVERED'
AND (SFOR.STATUS_ID             <> 5
OR SFOR.STATUS_ID               IS NULL) )
ORDER BY RESP.RSH_SYS_ID ,
  RESP.RSH_FOR_NO ;

注意：我使用的是oracle 12c。 实现了连接列的索引。

Answer 1

此过程批次需要 30 多分钟才能完成

为什么您认为问题出在该查询中？你是怎么分析的？ SQL 执行时间/计划/统计信息？ PL/SQL 探查器？痕迹？

无论如何，除此之外，我在这里看到了一些奇怪的事情：

1. UPPER(EMP.EMP_ID)=UPPER(RESP.RSH_CODE) - 它们真的是 varchar 列吗？如果是，为什么你允许大写和小写并且不知道它们的字母大小写？你不使用外键吗？

2. 无需通过 RESPONSE 两次（FOR_CNT 和 FOR_CNT_NR）来加入聚合。这部分

LEFT JOIN
  (SELECT RS_FOR_NO ,
    COUNT(*)CNT
  FROM RESPONSE R
  WHERE (( NVL(RS_G_YN ,'N')  ='N'
  AND R.RS_FOR_NO              IS NOT NULL )
  AND ( rs_sts              IN ('Rebooked' ,'Rebooked Closed')
  OR rs_sts                 IN ('Rebooked' ,'No Required stopped')))
  OR (NVL(RS_G_YN ,'N')       ='Y'
  AND NVL(RS_FOR_UPDATE_YN ,'N')='Y'
  AND NVL(RS_G_UPD_YN ,'N')   ='N'
  AND R.RS_FOR_NO              IS NOT NULL
  AND ( rs_sts              IN ('Rebooked' ,'Rebooked Closed')
  OR rs_sts                 IN ('Rebooked' ,'No Required stopped')) )
  GROUP BY RS_FOR_NO
  ) FOR_CNT
ON RESP.RSH_FOR_NO=FOR_CNT.RS_FOR_NO
LEFT JOIN
  (SELECT RS_FOR_NO ,
    COUNT(*)CNT_NR
  FROM RESPONSE R
  WHERE (R.RS_FOR_NO IS NOT NULL )
  AND ( rs_sts    IN ('No Required' ,'Rebooked Closed')
    )
  GROUP BY R.RS_FOR_NO
  ) FOR_CNT_NR

可以重写为

outer apply (
  SELECT 
    COUNT(
        case when 
         rs_sts IN ('Rebooked' ,'Rebooked Closed','No Required stopped')
         and (     NVL(RS_G_YN ,'N')  ='N'
              OR (
                   NVL(RS_G_YN ,'N')        ='Y'
               AND NVL(RS_FOR_UPDATE_YN,'N')='Y'
               AND NVL(RS_G_UPD_YN ,'N')    ='N'
              )
            )
        then 1 end
    ) as CNT,
    COUNT(case when rs_sts IN ('No Required' ,'Rebooked Closed') then 1 end) as FOR_CNT_NR
  FROM RESPONSE R
  WHERE R.RS_FOR_NO = RESP.RSH_FOR_NO
) FOR_CNT