标题: sql优化问题 [打印本页] 作者: superliming 时间: 2010-5-10 10:15 标题: sql优化问题 这个语句执行要200多秒，其实只有30多条内容。请问如何优化下
select to_char(trunc(A.org_time,'iw'),'yyyy-mm-dd') as 时间,
A.device_name as device_name,
B.cuid as cuid ,
B.link_id,
B.LINK_DESCRIPTION ,
B.left_equip_id as left_equip ,
B.left_port_name as left_port_name ,
A.ifspeed /1000/1000 as ifspeed ,
max(nvl(A.MAXIFINOCTETS_SPEED,0)) as M3,
avg(nvl(A.AVGIFINOCTETS_SPEED,0)) as M5,
max(nvl(A.MAXIFINOCTETS_SPEED,0)) /(A.ifspeed/1000/1000) *100 as maxinuing ,
avg(nvl(A.AVGIFINOCTETS_SPEED,0)) /(A.ifspeed/1000/1000) *100 as avginuing
from SA_DMP.rpt_network_interface_hour A ,
SA_OMP.设备链路 B
where A.device_name = B.left_equip_id and
( B.cuid like '%BC-SXNET-RT%' or B.cuid like '%BA-IPNET-RT%' ) and B.cuid not like '%CE%'
and B.left_port_name = A.ifname
and trunc(A.org_time,'iw') >= trunc(to_date('2010-04-26','yyyy-mm-dd'),'iw')
and trunc(A.org_time,'iw') < trunc(to_date('2010-05-04','yyyy-mm-dd'),'iw')
group by to_char(trunc(A.org_time,'iw'),'yyyy-mm-dd'), B.cuid ,A.device_name ,B.left_equip_id ,B.left_port_name ,A.ifspeed ,B.link_id ,B.LINK_DESCRIPTION
order by to_char(trunc(A.org_time,'iw'),'yyyy-mm-dd'),B.LINK_DESCRIPTION asc作者: archonwang 时间: 2010-5-11 14:28
。。。
老实说，使用了group by和order by速度必然下降，你试试看不用这两个，能快多少？