【oracle】SQL语句优化问题

0 0

【oracle】SQL语句优化问题0

大家帮我看下以下这个sql语句能不能优化下数据量大的时候查询速度太慢了几乎卡住

select distinct zch.fluxtime, zch.inavgvec, zch.outavgvec, zch.bandwidth
from zw_circuitflux_hour zch
join zw_devicechname zd on zch.remark = zd.deremark
join zw_circuit_performance zcp on zd.devicename = zcp.equipname
where zcp.equipname =
       (select distinct (zcp.equipname)
          from zw_circuit_performance zcp
          left join zw_devicechname zd on zcp.equipname = zd.devicename
         where zd.devicechname = '省级厂巷核心路由器')
   and zch.fluxtime = trunc(zch.fluxtime, 'dd')
   and zch.fluxtime >= to_date('2011-10-21 16:35', 'yyyy-mm-dd hh24:mi:ss')
   and zch.fluxtime <= to_date('2012-10-21 16:35', 'yyyy-mm-dd hh24:mi:ss')
order by zch.fluxtime asc

谢谢了！！！

2012年10月25日 12:31

八岭书生
-2
0 1 7

7个答案按时间排序按投票排序

0 0

1、尽量不用子查询
2、根据表的数据量，将小表放在后面
3、distinct最后不要放到子查询下面
4、where条件中将比较精确的放在最后比较
5、可能的关联字段索引是否有效
6、最后将大表分区吧

2012年10月28日 10:27

zrz_1989
2
0 0 6

添加评论

0 0

其实sql的优化，通过查看查询计划然后根据具体的业务可以把查询慢的sql拆分出来，然后可以在程序中对数据处理，分组，排序，拼接。

2012年10月28日 08:11

tws502934462
40
0 0 4

添加评论

0 0

select distinct zch.fluxtime, zch.inavgvec, zch.outavgvec, zch.bandwidth
from zw_circuitflux_hour zch
join zw_devicechname zd on zch.remark = zd.deremark
join zw_circuit_performance zcp on zd.devicename = zcp.equipname
where zcp.equipname =
       (select distinct (zcp.equipname)
          from zw_circuit_performance zcp
          left join zw_devicechname zd on zcp.equipname = zd.devicename
         where zd.devicechname = '省级厂巷核心路由器')
   and zch.fluxtime = trunc(zch.fluxtime, 'dd')
   and zch.fluxtime >= to_date('2011-10-21 16:35', 'yyyy-mm-dd hh24:mi:ss')
   and zch.fluxtime <= to_date('2012-10-21 16:35', 'yyyy-mm-dd hh24:mi:ss')
order by zch.fluxtime asc

1、如下子查询有点罗嗦直接在外查询中搞定
zcp.equipname =
       (select distinct (zcp.equipname)
          from zw_circuit_performance zcp
          left join zw_devicechname zd on zcp.equipname = zd.devicename
         where zd.devicechname = '省级厂巷核心路由器')

====》》》
select distinct zch.fluxtime, zch.inavgvec, zch.outavgvec, zch.bandwidth
from zw_circuitflux_hour zch
join zw_devicechname zd on zch.remark = zd.deremark
join zw_circuit_performance zcp on zd.devicename = zcp.equipname
   and zd.devicechname = '省级厂巷核心路由器'
   and zch.fluxtime = trunc(zch.fluxtime, 'dd')
   and zch.fluxtime >= to_date('2011-10-21 16:35', 'yyyy-mm-dd hh24:mi:ss')
   and zch.fluxtime <= to_date('2012-10-21 16:35', 'yyyy-mm-dd hh24:mi:ss')
order by zch.fluxtime asc

2、zch.fluxtime = trunc(zch.fluxtime, 'dd') 干嘛的感觉没用因为
   and zch.fluxtime >= to_date('2011-10-21 16:35', 'yyyy-mm-dd hh24:mi:ss')
   and zch.fluxtime <= to_date('2012-10-21 16:35', 'yyyy-mm-dd hh24:mi:ss')
============》》
select distinct zch.fluxtime, zch.inavgvec, zch.outavgvec, zch.bandwidth
from zw_circuitflux_hour zch
join zw_devicechname zd on zch.remark = zd.deremark
join zw_circuit_performance zcp on zd.devicename = zcp.equipname
where
   zch.fluxtime >= to_date('2011-10-21 16:35', 'yyyy-mm-dd hh24:mi:ss')
   and zch.fluxtime <= to_date('2012-10-21 16:35', 'yyyy-mm-dd hh24:mi:ss')
   and zd.devicechname = '省级厂巷核心路由器'
order by zch.fluxtime asc

3、distinct 没必要要排重的话可以考虑分组/在应用里做
select zch.fluxtime, zch.inavgvec, zch.outavgvec, zch.bandwidth
from zw_circuitflux_hour zch
join zw_devicechname zd on zch.remark = zd.deremark
join zw_circuit_performance zcp on zd.devicename = zcp.equipname
where
   zch.fluxtime >= to_date('2011-10-21 16:35', 'yyyy-mm-dd hh24:mi:ss')
   and zch.fluxtime <= to_date('2012-10-21 16:35', 'yyyy-mm-dd hh24:mi:ss')
   and zd.devicechname = '省级厂巷核心路由器'
order by zch.fluxtime asc

4、因为zd.devicename = zcp.equipname 和 zd.devicechname = '省级厂巷核心路由器'
此处不知道join 是inner还是left？如果是inner可以改成
select distinct zch.fluxtime, zch.inavgvec, zch.outavgvec, zch.bandwidth
from zw_circuitflux_hour zch
join zw_devicechname zd on zch.remark = zd.deremark
where
   zch.fluxtime >= to_date('2011-10-21 16:35', 'yyyy-mm-dd hh24:mi:ss')
   and zch.fluxtime <= to_date('2012-10-21 16:35', 'yyyy-mm-dd hh24:mi:ss')
   and zd.devicechname = '省级厂巷核心路由器'
order by zch.fluxtime asc

此时只有两个表的连接

最后重点：
fluxtime 一定建索引 devicechname 尽量也建

2012年10月25日 23:04

jinnianshilongnian
7280
2 3 591

添加评论

0 0

select distinct zch.fluxtime, zch.inavgvec, zch.outavgvec, zch.bandwidth
  from zw_circuitflux_hour zch
  join zw_devicechname zd
    on zch.remark = zd.deremark
  join zw_circuit_performance zcp
    on zd.devicename = zcp.equipname
 where zd.devicechname = '省级厂巷核心路由器'
   and zch.fluxtime = trunc(zch.fluxtime, 'dd')
   and zch.fluxtime >= to_date('2011-10-21 16:35', 'yyyy-mm-dd hh24:mi:ss')
   and zch.fluxtime <= to_date('2012-10-21 16:35', 'yyyy-mm-dd hh24:mi:ss')
 order by zch.fluxtime asc

2012年10月25日 14:13

liuyinhuan
390
1 1 45

添加评论

0 0

SELECT DISTINCT zch.fluxtime
	,zch.inavgvec
	,zch.outavgvec
	,zch.bandwidth
FROM zw_circuitflux_hour zch
INNER JOIN zw_devicechname zd ON zch.remark = zd.deremark
INNER JOIN zw_circuit_performance zcp ON zd.devicename = zcp.equipname

WHERE 1 = 1
	AND zd.devicechname = '省级厂巷核心路由器'
	AND zch.fluxtime = trunc(zch.fluxtime, 'dd')
	AND zch.fluxtime >= to_date('2011-10-21 16:35', 'yyyy-mm-dd hh24:mi:ss')
	AND zch.fluxtime <= to_date('2012-10-21 16:35', 'yyyy-mm-dd hh24:mi:ss')
ORDER BY zch.fluxtime ASC

2012年10月25日 13:39

heichong
10
0 0 5

添加评论

0 0

为什么还要写子循环呢，直接where里面限制条件不一样吗？

SELECT DISTINCT zch.fluxtime
	,zch.inavgvec
	,zch.outavgvec
	,zch.bandwidth
FROM zw_circuitflux_hour zch
INNER JOIN zw_devicechname zd ON zch.remark = zd.deremark
INNER JOIN zw_circuit_performance zcp ON zd.devicename = zcp.equipname

WHERE 1 = 1
	AND zd.devicechname = 'Ê¡¼¶³§ÏïºËÐÄÂ·ÓÉÆ÷'
	AND zch.fluxtime = trunc(zch.fluxtime, 'dd')
	AND zch.fluxtime >= to_date('2011-10-21 16:35', 'yyyy-mm-dd hh24:mi:ss')
	AND zch.fluxtime <= to_date('2012-10-21 16:35', 'yyyy-mm-dd hh24:mi:ss')
ORDER BY zch.fluxtime ASC

2012年10月25日 13:38

heichong
10
0 0 5