ORA-03113 SQL中select语句全表扫描带来的异常

  今天在ERP系统的维护过程中，业务人员反馈了一个问题过来，是ERP系统生产单模块的预览打印报表出错，看到后我逐步做了以下的排查：

1、尝试其他单据是否存在相同问题

2、直接打开水晶报表，将参数代入看看是否是报表问题

排查之后逐渐发现，问题出在数据源身上，找到返回数据集的存储过程，进入测试窗口检查是否运行正常，结果发现运行即进入卡死状态，进程无法中断，只好强行退出PL/SQL，这时候我估计到问题出在SQL语句上，因此将SQL语句复制到新的窗口，代入参数，如下：

SELECT WO_NBR,WO_LOT,WO_TYPE,WO_TYPE2 ,WO_PART, WO_QTY_ORD,WO_QTY_COMP,WO_QTY_RJCT,WO_LINE,WO_ORD_DATE,WO_REL_DATE,WO_DUE_DATE,WO_STATUS,WO_CLOSE_BY,WO_CLOSE_DATE,
         WO_SO_NBR,WO_SO_LINE,WO_FGOV_PER,WO_VEND,WO_SEND,WO_SOFT,WO_SOFT_VER,WO_ECA_NBR,WO_ECA_VER,WO_COUNT_OUTPUT,WO_ECN_NBR ,WO_TYPE1, WO_SKDR_NBR, WO_MAN_HAUR, WO_LIST_UPDATED,WO_SCHEDULE_DATE,
         WO_STD_PL,WO_ADD_SPARE, WO_ADD_SCREEN,WO_COMMON_ONLY,WO_RMKS,cast(WO_OTHER_RMKS as varchar2(1024)) as WO_OTHER_RMKS,WO_SITE,WO_PROG_CODE, WO_DOC_CODE,WO_CRT_BY,WO_CRT_DATE,WO_MOD_TIMES,WO_MOD_BY,WO_MOD_DATE,WO_CHAR1,WO_CHAR2,WO_CHAR3,WO_CHAR4,WO_CHAR5,       
         WO_CHAR6, WO_QTY1,WO_QTY2,WO_WF_STATUS WO_PST,WO_PST_BY,WO_PST_DATE,WO_SRC_NBR,WO_SRC_LINE,WO_SRC_LOT,WO_PT_LOT,WO_TRY_NBR,
         OEM_MSTR.*,
         
        OEMDO_LOGO_COUNT,OEMDO_MN_LOGO,OEMDO_DEF_LANG,OEMDO_S_LOGO,
           OEMDC_NBR,OEMDC_VERSION, OEMDC_NAME,OEMDC_EN_COMP,(OEMDC_EN_QTY_PER_M/OEMDC_EN_QTY_PER_D) AS OEMDC_EN_QTY_PER,OEMDC_PT_MSTR.PT_UM AS OEMDC_EN_COMP_UM,
           OEMDC_RMKS,
           
        WO_PT_MSTR.PT_DESC1 AS WO_PT_DESC1, WO_PT_MSTR.PT_DESC2 AS WO_PT_DESC2,
        WO_PT_MSTR.PT_DESC1  AS WO_PT_DESC,
        WO_PT_MSTR.PT_DRAW,WO_PT_MSTR.PT_CLASS AS WO_PT_CLASS,WO_PT_MSTR.PT_GROUP AS WO_PT_GROUP,WO_PT_MSTR.PT_MD AS PT_MD,
       
        SOFT_PT_MSTR.PT_DESC1 AS SOFT_PT_DESC1, SOFT_PT_MSTR.PT_DESC2 AS SOFT_PT_DESC2,
        SOFT_PT_MSTR.PT_DESC1 AS SOFT_PT_DESC,
        
        OEMDC_PT_MSTR.PT_DESC1 AS OEMDC_EN_COMP_DESC1,OEMDC_PT_MSTR.PT_DESC2 AS OEMDC_EN_COMP_DESC2,SOD_CMMT ,SO_CUST,
        SC4_MSTR.*,
        SOD_PART_QTY,
        SOD_SC_QTY,SC5_DESC,OEMDO_SFS_NBR,OEMDO_SFS_VERSION,A.GEND_NAME AS MB_DESC ,B.GEND_NAME AS INTERFACE_DESC,SOD_SFS_NBR,SOD_SFS_VERSION,
        SFS_S_LOGO,SFS_MN_LOGO,SFSD1_MN_LANG,wo_print_times,
        wo_try_nbr,try_task_no,try_content,try_aim

 FROM
        WO_MSTR JOIN PT_MSTR  WO_PT_MSTR ON WO_PART = WO_PT_MSTR.PT_PART
           LEFT JOIN OEM_MSTR ON WO_ECA_NBR = OEM_NBR AND WO_ECA_VER = OEM_VERSION
           LEFT JOIN OEMDC_DET ON OEM_NBR = OEMDC_NBR AND OEM_VERSION = OEMDC_VERSION
           LEFT JOIN OEMDO_DET ON OEM_NBR = OEMDO_NBR AND OEM_VERSION = OEMDO_VERSION
           LEFT JOIN PT_MSTR  OEMDC_PT_MSTR ON TRIM(OEMDC_EN_COMP) = TRIM(OEMDC_PT_MSTR.PT_PART)
           LEFT JOIN PT_MSTR  SOFT_PT_MSTR ON TRIM(WO_SOFT) = SOFT_PT_MSTR.PT_PART
           LEFT JOIN GEND_DET A ON OEM_MB_PROJECT = A.GEND_OPTION AND A.GEND_GEN ='SBGD_SCHEME'
           LEFT JOIN GEND_DET B ON OEM_INTER_REQ = B.GEND_OPTION AND B.GEND_GEN ='INTERFACE_REQ'
           
           LEFT JOIN SOD_DET ON SOD_NBR = WO_SO_NBR AND SOD_LINE = WO_SO_LINE
           LEFT JOIN SO_MSTR ON SO_NBR  = SOD_NBR
           LEFT JOIN  SFS_MSTR ON SFS_NBR = SOD_SFS_NBR AND SFS_VERSION = SOD_SFS_VERSION
           LEFT JOIN SFSD1_DET ON SFSD1_NBR = SFS_NBR AND SFSD1_VERSION = SFS_VERSION AND SFSD1_DEF_LANG = 1
           LEFT JOIN SC5_MSTR ON SC5_CODE  = SOD_SC5 AND SC5_CURR = SO_CURR  AND SOD_REQ_DATE BETWEEN  SC5_START AND SC5_EXPIRE   -- SC5_EXPIRE > TRUNC(SYSDATE, 'DD')
           LEFT JOIN SC4_MSTR ON SC4_CODE  = SOD_SC4_CODE AND SC4_CURR = SO_CURR  AND SOD_REQ_DATE BETWEEN SC4_START AND SC4_EXPIRE
           left join try_mstr on wo_try_nbr=try_nbr
    WHERE (WO_NBR = 'GWH17040102' ) AND
         (WO_LOT = ' ') AND
          (OEMDC_NAME  IN (SELECT GEND_OPTION
                                          FROM GEND_DET
                                          WHERE GEND_GEN ='MT_COMP'
                                               AND (GEND_PROPERTY2 ='Y' OR GEND_PROPERTY2 ='Y'))
                         OR ISEQUAL(OEMDC_NAME , '') = 1 )

执行结果如下：

接着又一次进入了卡死状态，PL/SQL客户端崩溃，只好重新登录。

之后为了减少客户端崩溃的次数，思索问题原因和问题解决方案，度娘查了有一会，可能查的方向有所偏差，没搜到能够解决问题的办法，于是只好自己通过调试来解决问题。首先，对于SQL方面的问题，我一般从两个地方入手：一个连表处，一个是列。

刚开始从连表处进行调试，第一次调试顺利的SQL如下：

SELECT count(1)

 FROM
        WO_MSTR JOIN PT_MSTR  WO_PT_MSTR ON WO_PART = WO_PT_MSTR.PT_PART
           LEFT JOIN OEM_MSTR ON WO_ECA_NBR = OEM_NBR AND WO_ECA_VER = OEM_VERSION
           LEFT JOIN OEMDC_DET ON OEM_NBR = OEMDC_NBR AND OEM_VERSION = OEMDC_VERSION
           LEFT JOIN OEMDO_DET ON OEM_NBR = OEMDO_NBR AND OEM_VERSION = OEMDO_VERSION
           LEFT JOIN PT_MSTR  OEMDC_PT_MSTR ON TRIM(OEMDC_EN_COMP) = TRIM(OEMDC_PT_MSTR.PT_PART)
           LEFT JOIN PT_MSTR  SOFT_PT_MSTR ON TRIM(WO_SOFT) = SOFT_PT_MSTR.PT_PART
           LEFT JOIN GEND_DET A ON OEM_MB_PROJECT = A.GEND_OPTION AND A.GEND_GEN ='SBGD_SCHEME'
           LEFT JOIN GEND_DET B ON OEM_INTER_REQ = B.GEND_OPTION AND B.GEND_GEN ='INTERFACE_REQ'
           
           LEFT JOIN SOD_DET ON SOD_NBR = WO_SO_NBR AND SOD_LINE = WO_SO_LINE
           LEFT JOIN SO_MSTR ON SO_NBR  = SOD_NBR
           LEFT JOIN  SFS_MSTR ON SFS_NBR = SOD_SFS_NBR AND SFS_VERSION = SOD_SFS_VERSION
           LEFT JOIN SFSD1_DET ON SFSD1_NBR = SFS_NBR AND SFSD1_VERSION = SFS_VERSION AND SFSD1_DEF_LANG = 1
           --LEFT JOIN SC5_MSTR ON SC5_CODE  = SOD_SC5 AND SC5_CURR = SO_CURR  AND SOD_REQ_DATE BETWEEN  SC5_START AND SC5_EXPIRE   -- SC5_EXPIRE > TRUNC(SYSDATE, 'DD')
           --LEFT JOIN SC4_MSTR ON SC4_CODE  = SOD_SC4_CODE AND SC4_CURR = SO_CURR  AND SOD_REQ_DATE BETWEEN SC4_START AND SC4_EXPIRE
           left join try_mstr on wo_try_nbr=try_nbr
    WHERE (WO_NBR = 'GWH17040102' ) AND
         (WO_LOT = ' ') AND
          (OEMDC_NAME  IN (SELECT GEND_OPTION
                                          FROM GEND_DET
                                          WHERE GEND_GEN ='MT_COMP'
                                               AND (GEND_PROPERTY2 ='Y' OR GEND_PROPERTY2 ='Y'))
                         OR ISEQUAL(OEMDC_NAME , '') = 1 )

虽然调试成功，但是却牺牲了业务需求，业务数据方面不满足实际，因此需要进行下一步尝试，紧接着打算从属性列方面着手，由于之前已有select后面最好带具体的列名的常识，

因此在调试中，直接将OEM_MSTR.*和SC4_MSTR.*这两个全表扫描进行注释，避免执行这两句SQL

SELECT WO_NBR,WO_LOT,WO_TYPE,WO_TYPE2 ,WO_PART, WO_QTY_ORD,WO_QTY_COMP,WO_QTY_RJCT,WO_LINE,WO_ORD_DATE,WO_REL_DATE,WO_DUE_DATE,WO_STATUS,WO_CLOSE_BY,WO_CLOSE_DATE,
         WO_SO_NBR,WO_SO_LINE,WO_FGOV_PER,WO_VEND,WO_SEND,WO_SOFT,WO_SOFT_VER,WO_ECA_NBR,WO_ECA_VER,WO_COUNT_OUTPUT,WO_ECN_NBR ,WO_TYPE1, WO_SKDR_NBR, WO_MAN_HAUR, WO_LIST_UPDATED,WO_SCHEDULE_DATE,
         WO_STD_PL,WO_ADD_SPARE, WO_ADD_SCREEN,WO_COMMON_ONLY,WO_RMKS,cast(WO_OTHER_RMKS as varchar2(1024)) as WO_OTHER_RMKS,WO_SITE,WO_PROG_CODE, WO_DOC_CODE,WO_CRT_BY,WO_CRT_DATE,WO_MOD_TIMES,WO_MOD_BY,WO_MOD_DATE,WO_CHAR1,WO_CHAR2,WO_CHAR3,WO_CHAR4,WO_CHAR5,       
         WO_CHAR6, WO_QTY1,WO_QTY2,WO_WF_STATUS WO_PST,WO_PST_BY,WO_PST_DATE,WO_SRC_NBR,WO_SRC_LINE,WO_SRC_LOT,WO_PT_LOT,WO_TRY_NBR,
         --OEM_MSTR.*,
         
        OEMDO_LOGO_COUNT,OEMDO_MN_LOGO,OEMDO_DEF_LANG,OEMDO_S_LOGO,
           OEMDC_NBR,OEMDC_VERSION, OEMDC_NAME,OEMDC_EN_COMP,(OEMDC_EN_QTY_PER_M/OEMDC_EN_QTY_PER_D) AS OEMDC_EN_QTY_PER,OEMDC_PT_MSTR.PT_UM AS OEMDC_EN_COMP_UM,
           OEMDC_RMKS,
           
        WO_PT_MSTR.PT_DESC1 AS WO_PT_DESC1, WO_PT_MSTR.PT_DESC2 AS WO_PT_DESC2,
        WO_PT_MSTR.PT_DESC1  AS WO_PT_DESC,
        WO_PT_MSTR.PT_DRAW,WO_PT_MSTR.PT_CLASS AS WO_PT_CLASS,WO_PT_MSTR.PT_GROUP AS WO_PT_GROUP,WO_PT_MSTR.PT_MD AS PT_MD,
       
        SOFT_PT_MSTR.PT_DESC1 AS SOFT_PT_DESC1, SOFT_PT_MSTR.PT_DESC2 AS SOFT_PT_DESC2,
        SOFT_PT_MSTR.PT_DESC1 AS SOFT_PT_DESC,
        
        OEMDC_PT_MSTR.PT_DESC1 AS OEMDC_EN_COMP_DESC1,OEMDC_PT_MSTR.PT_DESC2 AS OEMDC_EN_COMP_DESC2,SOD_CMMT ,SO_CUST,
        --SC4_MSTR.*,
        SOD_PART_QTY,
        SOD_SC_QTY,SC5_DESC,OEMDO_SFS_NBR,OEMDO_SFS_VERSION,A.GEND_NAME AS MB_DESC ,B.GEND_NAME AS INTERFACE_DESC,SOD_SFS_NBR,SOD_SFS_VERSION,
        SFS_S_LOGO,SFS_MN_LOGO,SFSD1_MN_LANG,wo_print_times,
        wo_try_nbr,try_task_no,try_content,try_aim

 FROM
        WO_MSTR JOIN PT_MSTR  WO_PT_MSTR ON WO_PART = WO_PT_MSTR.PT_PART
           LEFT JOIN OEM_MSTR ON WO_ECA_NBR = OEM_NBR AND WO_ECA_VER = OEM_VERSION
           LEFT JOIN OEMDC_DET ON OEM_NBR = OEMDC_NBR AND OEM_VERSION = OEMDC_VERSION
           LEFT JOIN OEMDO_DET ON OEM_NBR = OEMDO_NBR AND OEM_VERSION = OEMDO_VERSION
           LEFT JOIN PT_MSTR  OEMDC_PT_MSTR ON TRIM(OEMDC_EN_COMP) = TRIM(OEMDC_PT_MSTR.PT_PART)
           LEFT JOIN PT_MSTR  SOFT_PT_MSTR ON TRIM(WO_SOFT) = SOFT_PT_MSTR.PT_PART
           LEFT JOIN GEND_DET A ON OEM_MB_PROJECT = A.GEND_OPTION AND A.GEND_GEN ='SBGD_SCHEME'
           LEFT JOIN GEND_DET B ON OEM_INTER_REQ = B.GEND_OPTION AND B.GEND_GEN ='INTERFACE_REQ'
           
           LEFT JOIN SOD_DET ON SOD_NBR = WO_SO_NBR AND SOD_LINE = WO_SO_LINE
           LEFT JOIN SO_MSTR ON SO_NBR  = SOD_NBR
           LEFT JOIN  SFS_MSTR ON SFS_NBR = SOD_SFS_NBR AND SFS_VERSION = SOD_SFS_VERSION
           LEFT JOIN SFSD1_DET ON SFSD1_NBR = SFS_NBR AND SFSD1_VERSION = SFS_VERSION AND SFSD1_DEF_LANG = 1
           LEFT JOIN SC5_MSTR ON SC5_CODE  = SOD_SC5 AND SC5_CURR = SO_CURR  AND SOD_REQ_DATE BETWEEN  SC5_START AND SC5_EXPIRE   -- SC5_EXPIRE > TRUNC(SYSDATE, 'DD')
           LEFT JOIN SC4_MSTR ON SC4_CODE  = SOD_SC4_CODE AND SC4_CURR = SO_CURR  AND SOD_REQ_DATE BETWEEN SC4_START AND SC4_EXPIRE
           left join try_mstr on wo_try_nbr=try_nbr
    WHERE (WO_NBR = 'GWH17040102' ) AND
         (WO_LOT = ' ') AND
          (OEMDC_NAME  IN (SELECT GEND_OPTION
                                          FROM GEND_DET
                                          WHERE GEND_GEN ='MT_COMP'
                                               AND (GEND_PROPERTY2 ='Y' OR GEND_PROPERTY2 ='Y'))
                         OR ISEQUAL(OEMDC_NAME , '') = 1 )

结果，SQL顺利执行，并返回受影响的行集，这时候我就想有可能已经找到原因了，同时，如果真的是这个问题，那么解决方案可以将*号替换为具体需要的列，在完全不影响需求而且SQL可行的情况下解决这个问题，于是开始尝试：

SELECT WO_NBR,WO_LOT,WO_TYPE,WO_TYPE2 ,WO_PART, WO_QTY_ORD,WO_QTY_COMP,WO_QTY_RJCT,WO_LINE,WO_ORD_DATE,WO_REL_DATE,WO_DUE_DATE,WO_STATUS,WO_CLOSE_BY,WO_CLOSE_DATE,
         WO_SO_NBR,WO_SO_LINE,WO_FGOV_PER,WO_VEND,WO_SEND,WO_SOFT,WO_SOFT_VER,WO_ECA_NBR,WO_ECA_VER,WO_COUNT_OUTPUT,WO_ECN_NBR ,WO_TYPE1, WO_SKDR_NBR, WO_MAN_HAUR, WO_LIST_UPDATED,WO_SCHEDULE_DATE,
         WO_STD_PL,WO_ADD_SPARE, WO_ADD_SCREEN,WO_COMMON_ONLY,WO_RMKS,cast(WO_OTHER_RMKS as varchar2(1024)) as WO_OTHER_RMKS,WO_SITE,WO_PROG_CODE, WO_DOC_CODE,WO_CRT_BY,WO_CRT_DATE,WO_MOD_TIMES,WO_MOD_BY,WO_MOD_DATE,WO_CHAR1,WO_CHAR2,WO_CHAR3,WO_CHAR4,WO_CHAR5,       
         WO_CHAR6, WO_QTY1,WO_QTY2,WO_WF_STATUS WO_PST,WO_PST_BY,WO_PST_DATE,WO_SRC_NBR,WO_SRC_LINE,WO_SRC_LOT,WO_PT_LOT,WO_TRY_NBR,
         
         OEM_NBR, OEM_VERSION, OEM_APPLY, OEM_CUST, OEM_BRAND, OEM_STATUS, OEM_STCHG_BY, OEM_STCHG_DATE, OEM_MT, OEM_CUST_TYPE, OEM_CFG_TYPE, 
         OEM_SAME, OEM_MB_PRO, OEM_SCREEN, OEM_LEVEL, OEM_QUALITY, OEM_BC_TYPE, OEM_URGENCY, OEM_RMKS, OEM_TEMPLATE, OEM_NG, OEM_NG_BY, OEM_NG_DATE, 
         OEM_NG_REASON, OEM_MT_BY, OEM_MT_DATE, OEM_TRY, OEM_EMC, OEM_SITE, OEM_WF_STATUS, OEM_PROG_CODE, OEM_CRT_BY, OEM_CRT_DATE, OEM_MOD_TIMES, 
         OEM_MOD_BY, OEM_MOD_DATE, OEM_PST, OEM_PST_BY, OEM_PST_DATE, OEM_APRV, OEM_APRV_BY, OEM_APRV_DATE, OEM_CHAR1, OEM_CHAR2, OEM_CHAR3, 
         OEM_CHAR4, OEM_CHAR5, OEM_CHAR6, OEM_QTY1, OEM_QTY2, OEM_SRC_NBR, OEM_MTP_FLOW, OEM_DISABLED, OEM_MB_REQ, OEM_PW_REQ, OEM_MB_PROJECT, 
         OEM_INTER_REQ, OEM_COUNTRY, OEM_MAC_REQ, OEM_MATERIAL, OEM_CI_INTERFACE, OEM_DATE, OEM_DSGN_NBR, OEM_APP_MSTR, OEM_PACK_TYPE, OEM_BOTTOM_CODE, 
         OEM_SALE_METHOD,
         --OEM_MSTR.*,
         
        OEMDO_LOGO_COUNT,OEMDO_MN_LOGO,OEMDO_DEF_LANG,OEMDO_S_LOGO,
           OEMDC_NBR,OEMDC_VERSION, OEMDC_NAME,OEMDC_EN_COMP,(OEMDC_EN_QTY_PER_M/OEMDC_EN_QTY_PER_D) AS OEMDC_EN_QTY_PER,OEMDC_PT_MSTR.PT_UM AS OEMDC_EN_COMP_UM,
           OEMDC_RMKS,
           
        WO_PT_MSTR.PT_DESC1 AS WO_PT_DESC1, WO_PT_MSTR.PT_DESC2 AS WO_PT_DESC2,
        WO_PT_MSTR.PT_DESC1  AS WO_PT_DESC,
        WO_PT_MSTR.PT_DRAW,WO_PT_MSTR.PT_CLASS AS WO_PT_CLASS,WO_PT_MSTR.PT_GROUP AS WO_PT_GROUP,WO_PT_MSTR.PT_MD AS PT_MD,
       
        SOFT_PT_MSTR.PT_DESC1 AS SOFT_PT_DESC1, SOFT_PT_MSTR.PT_DESC2 AS SOFT_PT_DESC2,
        SOFT_PT_MSTR.PT_DESC1 AS SOFT_PT_DESC,
        
        OEMDC_PT_MSTR.PT_DESC1 AS OEMDC_EN_COMP_DESC1,OEMDC_PT_MSTR.PT_DESC2 AS OEMDC_EN_COMP_DESC2,SOD_CMMT ,SO_CUST,
        --SC4_MSTR.*,
        SC4_CODE, SC4_DESC, SC4_CURR, SC4_START, SC4_EXPIRE, SC4_BUY, SC4_SCREEN_INCL, SC4_SC_GRT, SC4_FREE, SC4_PR_DISC, SC4_PR_DISC_QTY, 
        SC4_PR_ADJ, SC4_PR_ADJ_QTY, SC4_FR_SPARE, SC4_FR_SPARE_PCT, SC4_FR_SUIT, SC4_FR_SUIT_PCT, SC4_SPARE_PCT, SC4_PRJ, SC4_RMKS, SC4_WF_STATUS, 
        SC4_PROG_CODE, SC4_CRT_BY, SC4_CRT_DATE, SC4_MOD_TIMES, SC4_MOD_BY, SC4_MOD_DATE, SC4_PST, SC4_PST_BY, SC4_PST_DATE, SC4_CHAR1, SC4_CHAR2, 
        SC4_CHAR3, SC4_CHAR4, SC4_CHAR5, SC4_CHAR6, SC4_QTY1, SC4_QTY2, SC4_FR_SC, SC4_FR_SC_PCT, SC4_CUST, SC4_MACHINE,
        
        SOD_PART_QTY,
        SOD_SC_QTY,SC5_DESC,OEMDO_SFS_NBR,OEMDO_SFS_VERSION,A.GEND_NAME AS MB_DESC ,B.GEND_NAME AS INTERFACE_DESC,SOD_SFS_NBR,SOD_SFS_VERSION,
        SFS_S_LOGO,SFS_MN_LOGO,SFSD1_MN_LANG,wo_print_times,
        wo_try_nbr,try_task_no,try_content,try_aim

 FROM
        WO_MSTR JOIN PT_MSTR  WO_PT_MSTR ON WO_PART = WO_PT_MSTR.PT_PART
           LEFT JOIN OEM_MSTR ON WO_ECA_NBR = OEM_NBR AND WO_ECA_VER = OEM_VERSION
           LEFT JOIN OEMDC_DET ON OEM_NBR = OEMDC_NBR AND OEM_VERSION = OEMDC_VERSION
           LEFT JOIN OEMDO_DET ON OEM_NBR = OEMDO_NBR AND OEM_VERSION = OEMDO_VERSION
           LEFT JOIN PT_MSTR  OEMDC_PT_MSTR ON TRIM(OEMDC_EN_COMP) = TRIM(OEMDC_PT_MSTR.PT_PART)
           LEFT JOIN PT_MSTR  SOFT_PT_MSTR ON TRIM(WO_SOFT) = SOFT_PT_MSTR.PT_PART
           LEFT JOIN GEND_DET A ON OEM_MB_PROJECT = A.GEND_OPTION AND A.GEND_GEN ='SBGD_SCHEME'
           LEFT JOIN GEND_DET B ON OEM_INTER_REQ = B.GEND_OPTION AND B.GEND_GEN ='INTERFACE_REQ'
           
           LEFT JOIN SOD_DET ON SOD_NBR = WO_SO_NBR AND SOD_LINE = WO_SO_LINE
           LEFT JOIN SO_MSTR ON SO_NBR  = SOD_NBR
           LEFT JOIN  SFS_MSTR ON SFS_NBR = SOD_SFS_NBR AND SFS_VERSION = SOD_SFS_VERSION
           LEFT JOIN SFSD1_DET ON SFSD1_NBR = SFS_NBR AND SFSD1_VERSION = SFS_VERSION AND SFSD1_DEF_LANG = 1
           LEFT JOIN SC5_MSTR ON SC5_CODE  = SOD_SC5 AND SC5_CURR = SO_CURR  AND SOD_REQ_DATE BETWEEN  SC5_START AND SC5_EXPIRE   -- SC5_EXPIRE > TRUNC(SYSDATE, 'DD')
           LEFT JOIN SC4_MSTR ON SC4_CODE  = SOD_SC4_CODE AND SC4_CURR = SO_CURR  AND SOD_REQ_DATE BETWEEN SC4_START AND SC4_EXPIRE
           left join try_mstr on wo_try_nbr=try_nbr
    WHERE (WO_NBR = 'GWH17040102' ) AND
         (WO_LOT = ' ') AND
          (OEMDC_NAME  IN (SELECT GEND_OPTION
                                          FROM GEND_DET
                                          WHERE GEND_GEN ='MT_COMP'
                                               AND (GEND_PROPERTY2 ='Y' OR GEND_PROPERTY2 ='Y'))
                         OR ISEQUAL(OEMDC_NAME , '') = 1 )

测试结果，成功！

 

总结：

1、*在复杂的SQL中还是需要慎重使用，特别是本来不需要那么多列数据，结果带出了很多多余的数据，造成浪费，同时，后来者若不熟悉业务，不加研读该存储过程，实际上很难知道，哪些信息是真正需要的，哪些是不需要的，久而久之，会加重企业后期的维护成本；

2、在调试SQL的过程中可以通过注释来排查异常。

3、该SQL仍待优化，若哪位仁兄有更好的见解，还望指导迷津。