查询语句很慢，怎么优化呢？？

SELECT   w.prod_series, e.step_key, SUM (e.qty) qty, '0' AS timeflag,
       e.trx_date
    FROM (SELECT a.step_key, a.qty, a.wo_key,
                 (CASE
                     WHEN a.trx_date >=
                            TO_DATE ('2012-06-13 08:30',
                                     'YYYY/MM/DD HH24:MI:SS')
                     AND a.trx_date <
                            TO_DATE ('2012-06-13 09:30',
                                     'YYYY/MM/DD HH24:MI:SS')
                        THEN '08:30 -09:30'
                     WHEN a.trx_date >=
                            TO_DATE ('2012-06-13 09:30',
                                     'YYYY/MM/DD HH24:MI:SS')
                     AND a.trx_date <
                            TO_DATE ('2012-06-13 10:30',
                                     'YYYY/MM/DD HH24:MI:SS')
                        THEN '09:30 -10:30'
                     WHEN a.trx_date >=
                            TO_DATE ('2012-06-13 10:30',
                                     'YYYY/MM/DD HH24:MI:SS')
                     AND a.trx_date <
                            TO_DATE ('2012-06-13 11:30',
                                     'YYYY/MM/DD HH24:MI:SS' )
                        THEN '10:30 -11:30'
                     WHEN a.trx_date >=
                            TO_DATE ('2012-06-13 11:30',
                                     'YYYY/MM/DD HH24:MI:SS')
                     AND a.trx_date <
                            TO_DATE ('2012-06-13 12:30',
                                     'YYYY/MM/DD HH24:MI:SS')
                        THEN '11:30 -12:30'
                     WHEN a.trx_date >=
                            TO_DATE ('2012-06-13 12:30',
                                     'YYYY/MM/DD HH24:MI:SS')
                     AND a.trx_date <
                            TO_DATE ('2012-06-13 13:30',
                                     'YYYY/MM/DD HH24:MI:SS')
                        THEN '12:30 -13:30'
                     WHEN a.trx_date >=
                            TO_DATE ('2012-06-13 13:30',
                                     'YYYY/MM/DD HH24:MI:SS' )
                     AND a.trx_date <
                            TO_DATE ('2012-06-13 14:30',
                                     'YYYY/MM/DD HH24:MI:SS')
                        THEN '13:30 -14:30'
                     WHEN a.trx_date >=
                            TO_DATE ('2012-06-13 14:30',
                                     'YYYY/MM/DD HH24:MI:SS')
                     AND a.trx_date <
                            TO_DATE ('2012-06-13 15:30',
                                     'YYYY/MM/DD HH24:MI:SS')
                        THEN '14:30 -15:30'
                     WHEN a.trx_date >=
                            TO_DATE ('2012-06-13 15:30',
                                     'YYYY/MM/DD HH24:MI:SS')
                     AND a.trx_date <
                            TO_DATE ('2012-06-13 16:30',
                                     'YYYY/MM/DD HH24:MI:SS')
                        THEN '15:30 -16:30'
                     WHEN a.trx_date >=
                            TO_DATE ('2012-06-13 16:30',
                                     'YYYY/MM/DD HH24:MI:SS' )
                     AND a.trx_date <
                            TO_DATE ('2012-06-13 17:30',
                                     'YYYY/MM/DD HH24:MI:SS')
                        THEN '16:30 -17:30'
                     WHEN a.trx_date >=
                            TO_DATE ('2012-06-13 17:30',
                                     'YYYY/MM/DD HH24:MI:SS')
                     AND a.trx_date <
                            TO_DATE ('2012-06-13 18:30',
                                     'YYYY/MM/DD HH24:MI:SS')
                        THEN '17:30 -18:30'
                     WHEN a.trx_date >=
                            TO_DATE ('2012-06-13 18:30',
                                     'YYYY/MM/DD HH24:MI:SS')
                     AND a.trx_date <
                            TO_DATE ('2012-06-13 19:30',
                                     'YYYY/MM/DD HH24:MI:SS')
                        THEN '18:30 -19:30'
                     WHEN a.trx_date >=
                            TO_DATE ('2012-06-13 19:30',
                                     'YYYY/MM/DD HH24:MI:SS' )
                     AND a.trx_date <
                            TO_DATE ('2012-06-13 20:30',
                                     'YYYY/MM/DD HH24:MI:SS')
                        THEN '19:30 -20:30'
                     WHEN a.trx_date >=
                            TO_DATE ('2012-06-13 20:30',
                                     'YYYY/MM/DD HH24:MI:SS')
                     AND a.trx_date <
                            TO_DATE ('2012-06-13 21:30',
                                     'YYYY/MM/DD HH24:MI:SS')
                        THEN '20:30 -21:30'
                     WHEN a.trx_date >=
                            TO_DATE ('2012-06-13 21:30',
                                     'YYYY/MM/DD HH24:MI:SS')
                     AND a.trx_date <
                            TO_DATE ('2012-06-13 22:30',
                                     'YYYY/MM/DD HH24:MI:SS')
                        THEN '21:30 -22:30'
                     WHEN a.trx_date >=
                            TO_DATE ('2012-06-13 22:30',
                                     'YYYY/MM/DD HH24:MI:SS')
                     AND a.trx_date <
                            TO_DATE ('2012-06-13 23:30',
                                     'YYYY/MM/DD HH24:MI:SS')
                        THEN '22:30 -23:30'
                  END
                 ) AS trx_date
            FROM swip_entity_trx a
           WHERE a.step_key IN (SELECT step_key
                                  FROM sbrm_step
                                 WHERE step_type = 'Shipping')
             AND a.trx_type = 'Completed'
             AND a.step_times = 1
             AND a.trx_date >=
                         TO_DATE ('2012-06-13 08:30', 'YYYY/MM/DD HH24:MI:SS')
             AND a.trx_date <
                         TO_DATE ('2012-06-14 08:30', 'YYYY/MM/DD HH24:MI:SS')) e
         INNER JOIN
         ssch_wo_mst w ON e.wo_key = w.wo_key
         INNER JOIN sbrm_factory sf
         ON w.factory_key = sf.factory_key AND sf.factory_id IN ('TPKG-N')
         INNER JOIN sbrm_step s ON e.step_key = s.step_key
   WHERE (s.step_type <> 'Repair' OR s.step_type IS NULL)
     AND w.prod_series IN ('KNT-B') 
     
GROUP BY w.prod_series, e.step_key, e.trx_date

解决方案 »

  1.   


    select to_date('2012-06-10 08:30:00','yyyy-mm-dd hh24:mi:ss')+level/24 mi
    from dual
    connect by level <= 15          mi
    ------------------------------
    1 2012/6/10 9:30:00
    2 2012/6/10 10:30:00
    3 2012/6/10 11:30:00
    4 2012/6/10 12:30:00
    5 2012/6/10 13:30:00
    6 2012/6/10 14:30:00
    7 2012/6/10 15:30:00
    8 2012/6/10 16:30:00
    9 2012/6/10 17:30:00
    10 2012/6/10 18:30:00
    11 2012/6/10 19:30:00
    12 2012/6/10 20:30:00
    13 2012/6/10 21:30:00
    14 2012/6/10 22:30:00
    15 2012/6/10 23:30:00
      

  2.   

    我把AND sf.factory_id IN ('TPKG-N')
    这个条件去掉，，速度就变得很快了，为什么
      

  3.   

    WHERE     a.step_key IN (SELECT step_key
                                          FROM sbrm_step
                                         WHERE step_type = 'Shipping')
    这个IN 子句改为join方式看看，
    其次是INNER JOIN sbrm_step s这个join应该可以省略。
      

  4.   


    with t1 as
    (
         select to_date('2012-06-13 08:55:00','yyyy-mm-dd hh24:mi:ss') c1 from dual
         union all
         select to_date('2012-06-13 10:44:00','yyyy-mm-dd hh24:mi:ss')  from dual
         union all
         select to_date('2012-06-13 11:10:00','yyyy-mm-dd hh24:mi:ss')  from dual
         union all
         select to_date('2012-06-13 15:15:00','yyyy-mm-dd hh24:mi:ss')  from dual
    )
    select c1,to_char(m1,'hh24:mi')||'-'||to_char(m2,'hh24:mi') c2
    from t1 left join (
                           select to_date('2012-06-13 08:30:00','yyyy-mm-dd hh24:mi:ss')+(level-1)/24 m1,
                                  to_date('2012-06-13 09:30:00','yyyy-mm-dd hh24:mi:ss')+(level-1)/24 m2
                           from dual
                           connect by level <= 15
                      ) on t1.c1 between m1 and m2            c1              c2
    -------------------------------------------------
    1 2012/6/13 8:55:00 08:30-09:30
    2 2012/6/13 10:44:00 10:30-11:30
    3 2012/6/13 11:10:00 10:30-11:30
    4 2012/6/13 15:15:00 14:30-15:30
      

  5.   

    改成 AND sf.factory_id ='TPKG-N' 呢？
      

  6.   

    这个 in ('TPKG-N')   括号里面就是一个值？ 相当于sf.factory_id='TPKG-N' 这种？
      

  7.   

    不管是子查询改成关联查询，，还是把 in ('TPKG-N')改成sf.factory_id='TPKG-N' 都没有用
      

  8.   

    上表吧   哪几个表 哪些字段 什么意思要求什么   只上sql 也只能根据表面看看 全部是inner join  很耗时的