发一段SQL高分优化，现在运行30秒，领导说要搞到10秒

发一段SQL高分请教高手优化，现在运行30秒，领导说要搞到10秒。

SELECT /*+index(T HG_JOB_ALL_OUTPUT_2)*/
 SUBSTR(M.ITEM_M, 3, 8) 料號,
 TO_CHAR(T.CREATE_DATE, 'YYYY-MM-DD') 日期,
 TRIM(M.ITEM_M_HG) 料號1,
 0 材不數,
 0 總提檢數,
 0 單片產量,
 0 總損品數,
 0 CG提檢數,
 0 CG損品數,
 0,
 0 HX提檢數,
 0 HX損品數,
 0,
 ROUND(DECODE(T.ENGINEER,
              '二厂ZX',
              DECODE(SUBSTR(T.ITEM, 2, 1),
                     'C',
                     (T.SP_QTY / NVL(MSI.UNIT_WEIGHT, 1) +
                     T.SP2_QTY / NVL(MSI.UNIT_WEIGHT, 1)),
                     'H',
                     (T.SP_QTY / NVL(MSI.UNIT_WEIGHT, 1) +
                     T.SP2_QTY / NVL(MSI.UNIT_WEIGHT, 1))))) CX提檢數,
 ROUND(DECODE(T.ENGINEER,
              '二厂ZX',
              DECODE(SUBSTR(T.ITEM, 2, 1),
                     'C',
                     (T.SP2_QTY / NVL(MSI.UNIT_WEIGHT, 1) +
                     T.SP_QTY / NVL(MSI.UNIT_WEIGHT, 1)),
                     'H',
                     (T.SP2_QTY / NVL(MSI.UNIT_WEIGHT, 1) +
                     T.SP_QTY / NVL(MSI.UNIT_WEIGHT, 1))))) CX損品數,
 ROUND(DECODE(T.ENGINEER,
              '二厂ZX',
              DECODE(SUBSTR(T.ITEM, 2, 1),
                     'C',
                     (T.SP2_QTY / NVL(MSI.UNIT_WEIGHT, 1) +
                     T.SP_QTY / NVL(MSI.UNIT_WEIGHT, 1)),
                     'H',
                     (T.SP2_QTY / NVL(MSI.UNIT_WEIGHT, 1) +
                     T.SP_QTY / NVL(MSI.UNIT_WEIGHT, 1))))) *
 NVL(T.PER_COST, 0) CX_COST,
 
 ROUND(DECODE(T.ENGINEER,
              '二厂ZX',
              DECODE(SUBSTR(T.ITEM, 2, 1),
                     'C',
                     -1 * (T.SP_QTY / NVL(MSI.UNIT_WEIGHT, 1) +
                     T.SP2_QTY / NVL(MSI.UNIT_WEIGHT, 1)),
                     'H',
                     -1 * (T.SP_QTY / NVL(MSI.UNIT_WEIGHT, 1) +
                     T.SP2_QTY / NVL(MSI.UNIT_WEIGHT, 1))))) ZX提檢數,
 ROUND(DECODE(T.ENGINEER,
              '二厂ZX',
              DECODE(SUBSTR(T.ITEM, 2, 1),
                     'C',
                     -1 * (T.SP2_QTY / NVL(MSI.UNIT_WEIGHT, 1) +
                     T.SP_QTY / NVL(MSI.UNIT_WEIGHT, 1)),
                     'H',
                     -1 * (T.SP2_QTY / NVL(MSI.UNIT_WEIGHT, 1) +
                     T.SP_QTY / NVL(MSI.UNIT_WEIGHT, 1))))) ZX損品數,
 ROUND(DECODE(T.ENGINEER,
              '二厂ZX',
              DECODE(SUBSTR(T.ITEM, 2, 1),
                     'C',
                     -1 * (T.SP2_QTY / NVL(MSI.UNIT_WEIGHT, 1) +
                     T.SP_QTY / NVL(MSI.UNIT_WEIGHT, 1)),
                     'H',
                     -1 * (T.SP2_QTY / NVL(MSI.UNIT_WEIGHT, 1) +
                     T.SP_QTY / NVL(MSI.UNIT_WEIGHT, 1))))) *
 NVL(T.PER_COST, 0) ZX_COST,
 0 JH提檢數,
 0 JH損品數,
 0,
 0 TM提檢數,
 0 TM損品數,
 0,
 0 二厂客退品損品數,
 0,
 0 二厂脫膜組損品數,
 0,
 0 二厂其它不良損品數,
 0,
 0 LSS,
 0 LSS_COST,
 0 損品定額
  FROM APPS.HG_JOB_ALL_OUTPUT T,
       APPS.HG_JOB_BOM        M,
       INV.MTL_SYSTEM_ITEMS_B MSI
 WHERE (T.ITEM = M.ITEM_END OR T.ITEM = M.ITEM_M)
   AND MSI.SEGMENT1 = T.ITEM
   AND SUBSTR(T.ITEM, 2, 1) IN ('C', 'H')
   AND T.ENGINEER = '二厂ZX'
   AND SUBSTR(M.ITEM_M, 3, 6) IN
       (SELECT M.ITEM_M
          FROM APPS.HG_TWO_RCBF M
         WHERE M.MATERIAL = 'SOFT'
           AND (TO_CHAR(T.CREATE_DATE, 'YYYY-MM-DD') <=
               TO_CHAR(M.END_DATE, 'yyyy-mm-dd') OR M.END_DATE IS NULL))
                            AND TO_CHAR(T.CREATE_DATE, 'YYYY-MM-DD') BETWEEN '2015-07-01' AND
       '2015-07-31'
   AND T.CREATE_DATE > SYSDATE - 730
UNION ALL
SELECT substr(T.ITME1, 3, 6),
       T.DATE1,
       '',
       0,
       0,
       0,
       0,
       0,
       0,
       0,
       0,
       0,
       0,
       0,
       0,
       0,
       0,
       0,
       0,
       0,
       0,
       0,
       0,
       0,
       0,
       0,
       0,
       0,
       0,
       0,
       0,
       T.LSS1,
       NVL(T.LSS1, 0) * NVL(T.PER_COST, 0) LSS_COST,
       0
  FROM APPS.HG_RCTEAM_LSS_T T

------解决思路----------------------
APPS.HG_TWO_RCBF 有CREATE_DATE该列索引吗？有的话改成这样子
T.CREATE_DATE  BETWEEN date( '2015-07-01')  AND  date( '2015-07-31')



------解决思路----------------------
把　UNION　ALL　两部分数据，分开跑一下，看看各占用的时间，

应该是第一个的时间比较长。

AND SUBSTR(M.ITEM_M, 3, 6) IN　这个可能会占用比较多的 CPU时间。
------解决思路----------------------
 WHERE (T.ITEM = M.ITEM_END OR T.ITEM = M.ITEM_M)
   AND MSI.SEGMENT1 = T.ITEM
   AND SUBSTR(T.ITEM, 2, 1) IN ('C', 'H')
   AND T.ENGINEER = '二厂ZX'  這裡已經有這個條件了，為什麼還要decode  T.ENGINEER??


   AND SUBSTR(M.ITEM_M, 3, 6) IN
       (SELECT M.ITEM_M
          FROM APPS.HG_TWO_RCBF M
         WHERE M.MATERIAL = 'SOFT'
           AND (TO_CHAR(T.CREATE_DATE, 'YYYY-MM-DD') <=
               TO_CHAR(M.END_DATE, 'yyyy-mm-dd') OR M.END_DATE IS NULL))
   AND TO_CHAR(T.CREATE_DATE, 'YYYY-MM-DD') BETWEEN '2015-07-01' AND
       '2015-07-31'   這裡勁量不要對字段to_char，  
------解决思路----------------------
重点关注T和M两表
WHERE (T.ITEM = M.ITEM_END OR T.ITEM = M.ITEM_M)
   AND MSI.SEGMENT1 = T.ITEM
   AND SUBSTR(T.ITEM, 2, 1) IN ('C', 'H')
   AND T.ENGINEER = '二厂ZX'

首先，T作为驱动表，上面的其中一个条件将T表的数据量限制到1条，是哪个条件呢？可以针对该字段单独建一个索引。目前走的xxx_2看起来是组合索引，效率恐怕并不高

其次，M表进行了全表扫描，但实际上关联条件的过滤效果非常强。在item_end和item_m上分别创建一条普通索引试试

以上两个问题若能解决，速度应该能加快不少
     
另外，你的in子查询里还设了一个别名同样为M的表，最好不要这么做，容易引起语义混乱
------解决思路----------------------
中间一定有个过滤效果相当好的字段，否则就是统计信息有问题。抓住这个字段建个索引

cbo对or采取的执行计划有时并不很聪明
可以试着将这段代码改成
select .....
from ...
where t.item=m.item_end and msi....................
union
select ...
from ...
where t.item=m.item_m and msi.....................
union all
.................

看看效果如何