首页 > 代码库 > ORA-03113 SQL中select语句全表扫描带来的异常
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仍待优化，若哪位仁兄有更好的见解，还望指导迷津。
ORA-03113 SQL中select语句全表扫描带来的异常
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。