select distinct a.productspecname, a.productiontype
from ps_lothistory a
where a.oldareaname = 'ASSY'
and a.oldprocessoperationname = 'AM030'
and a.eventname in
('JobOutProducible', 'ReworkProducible', 'ScrapProducible')
and a.eventtime between
to_date('2011-07-14 08:30:00', 'yyyy-mm-dd hh24:mi:ss') and
to_date('2011-07-15 08:30:00', 'yyyy-mm-dd hh24:mi:ss')
and a.site = 'CP1'sql语句如上,这个语句的运行速度很慢,要1分钟,请问怎么才能提高它的运行速度呢
from ps_lothistory a
where a.oldareaname = 'ASSY'
and a.oldprocessoperationname = 'AM030'
and a.eventname in
('JobOutProducible', 'ReworkProducible', 'ScrapProducible')
and a.eventtime between
to_date('2011-07-14 08:30:00', 'yyyy-mm-dd hh24:mi:ss') and
to_date('2011-07-15 08:30:00', 'yyyy-mm-dd hh24:mi:ss')
and a.site = 'CP1'sql语句如上,这个语句的运行速度很慢,要1分钟,请问怎么才能提高它的运行速度呢
--试试
SELECT A.PRODUCTSPECNAME, A.PRODUCTIONTYPE
FROM PS_LOTHISTORY A
WHERE A.OLDAREANAME = 'ASSY'
AND A.OLDPROCESSOPERATIONNAME = 'AM030'
AND A.EVENTNAME IN
('JobOutProducible', 'ReworkProducible', 'ScrapProducible')
AND A.EVENTTIME BETWEEN
TO_DATE('2011-07-14 08:30:00', 'yyyy-mm-dd hh24:mi:ss') AND
TO_DATE('2011-07-15 08:30:00', 'yyyy-mm-dd hh24:mi:ss')
AND A.SITE = 'CP1'
GROUP BY A.PRODUCTSPECNAME, A.PRODUCTIONTYPE;
from ps_lothistory a
where a.oldareaname = 'ASSY'
and a.oldprocessoperationname = 'AM030'
and a.eventname in
('JobOutProducible', 'ReworkProducible', 'ScrapProducible')
and a.eventtime between
to_date('2011-07-14 08:30:00', 'yyyy-mm-dd hh24:mi:ss') and
to_date('2011-07-15 08:30:00', 'yyyy-mm-dd hh24:mi:ss')
and a.site = 'CP1'
and rowid (select max(rowid) from ps_lothistory b where a.productspecname= b.productspecname and a.productiontype = b.a.productiontype)
from ps_lothistory a
where a.oldareaname = 'ASSY'
and a.oldprocessoperationname = 'AM030'
and a.eventname in
('JobOutProducible', 'ReworkProducible', 'ScrapProducible')
and a.eventtime between
to_date('2011-07-14 08:30:00', 'yyyy-mm-dd hh24:mi:ss') and
to_date('2011-07-15 08:30:00', 'yyyy-mm-dd hh24:mi:ss')
and a.site = 'CP1'
and rowid = (select max(rowid) from ps_lothistory b where a.productspecname= b.productspecname and a.productiontype = b.a.productiontype)
where语句中的查询条件,尽量要往索引上靠。如果实在没有索引,最好建一下。
没有索引,就全表扫描了。