查询语句很慢，怎么优化呢？？
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
[解决办法]
- SQL code
select to_date('2012-06-10 08:30:00','yyyy-mm-dd hh24:mi:ss')+level/24 mifrom dualconnect by level <= 15 mi------------------------------1 2012/6/10 9:30:002 2012/6/10 10:30:003 2012/6/10 11:30:004 2012/6/10 12:30:005 2012/6/10 13:30:006 2012/6/10 14:30:007 2012/6/10 15:30:008 2012/6/10 16:30:009 2012/6/10 17:30:0010 2012/6/10 18:30:0011 2012/6/10 19:30:0012 2012/6/10 20:30:0013 2012/6/10 21:30:0014 2012/6/10 22:30:0015 2012/6/10 23:30:00
[解决办法]
WHERE a.step_key IN (SELECT step_key
FROM sbrm_step
WHERE step_type = 'Shipping')
这个IN 子句改为join方式看看，
其次是INNER JOIN sbrm_step s这个join应该可以省略。