执行超过1个小时的SQL语句

SELECT MO.MO_ID, 
       MO.ITEM, 
       MO.QTYORDERED, 
       MO.PLANNEDSTARTDATE, 
       BR.MAXLOTSIZE 
  FROM TEMP_MO MO, 


       (SELECT PRODUCED_ITEM_ID ITEM_ID, 
               SITEID, 
               MAX(DECODE(NVL(MAXLOTSIZE, 0), 0, 99999999, MAXLOTSIZE)) MAXLOTSIZE 
          FROM IN_ITEMBOMROUTING, 
               TEMP_MO MO 
         WHERE SUBSTR(PRODUCED_ITEM_ID, 7, 3) IN ('101', '401', '402', '501', '502')    
            OR SUBSTR(PRODUCED_ITEM_ID, 9, 4) IN ('1111', '1121') 
            OR PRODUCED_ITEM_ID IN (SELECT IIS.ITEM_ID FROM STG.IN_ITEM_SITE IIS WHERE SUBSTR(IIS.ITEM_ID, 7, 3) IN ('102')  AND TRIM(IIS.PROC_TYPE) = 'E') 
         GROUP BY PRODUCED_ITEM_ID, SITEID) BR 


 WHERE MO.LOC_ID = BR.SITEID 
   AND BR.ITEM_ID = MO.ITEM

  
CREATE INDEX STG.IDX_TEMP_MO_DBA01 ON STG.TEMP_MO(LOC_ID,ITEM)
LOGGING
TABLESPACE WWFDATA
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

CREATE INDEX STG.IDX_IN_ITEM_SITE_DBA01 ON STG.IN_ITEM_SITE(ITEM_ID,PROC_TYPE)
LOGGING
TABLESPACE WWFDATA
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

CREATE INDEX STG.IDX_IN_ITEMBOMROUTING_DBA01 ON STG.IN_ITEMBOMROUTING(PRODUCED_ITEM_ID,SITEID)
LOGGING
TABLESPACE WWFDATA
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

 加了INDEX后执行语句时间由一个小时变成6秒，加到存储过程后还是比较慢，再继续检查SQL语句，发现

 FROM IN_ITEMBOMROUTING,
               TEMP_MO MO

此处的TEMP_MO没有用到，去掉即可