查询多年多站点统计值:循环查询还是利用ORACLE内部分组函数一次查询?

时间:2020-12-31 00:48:20
各位大侠好!

    兹有我服务器端一个包含近2百万条记录的数据库表,该表有54个字段,主键为站点和日期(yyyy-mm-dd格式)。我现在需要在客户端查询该表中,连续多年的某个时段分站点的某字段资料,如1951-2013年共63年,每一年1月1日至08月31日的某字段平均值资料,需要用到ORACLE的分组函数。

    方法一、如果我循环63次,每次查询某一年该时段的该字段资料,我的某一次SQL语句如下:
select stacode STA,avg(T) AAA from SURF_MUL_DAY  
Where T <> -9999 and ddate between to_date('1982-01-01','yyyy-mm-dd') and to_date('1982-08-31’,'yyyy-mm-dd') 
group by stacode 
    利用该语句得到某一年的资料,再多次循环得到连续多年的统计资料。

    方法二、如果我利用ORACLE分组函数可以实现一次查询得出多年统计资料,SQL语句如下:
select stacode,to_char(ddate,'yyyy') YER,avg(T) AAA from surf_mul_day 
where to_char(ddate,'mm-dd') between '01-01' and '12-31' and to_char(ddate,'yyyy') between '1951' and '2013' 
and T<>-9999 
group by to_char(ddate,'yyyy'),stacode

    现在我的问题是:
    如果我利用第一种方法查询63年短时段内的统计资料,如1月1日至3月31日(共3个月),则每一次循环查询耗时大概是0.06秒,循环63次共耗时也不过4秒;但是如果我需要查询的时段稍长一些,则一次查询耗时就突然从0.06秒骤增至4秒多,63次循环下来耗时就很长了。
    另外也有很奇怪的一个现象,我查询某些年份如2011年全年值耗时0.14秒,但同样的语句查询其他年份如1981年全年值则耗时4.776秒。
    如果我利用第二种方法查询63年的统计资料,无论我需要查询的时段为多长(1年或1天),这一次查询的耗时都是8秒左右。

    我想请教各位大侠,我的SQL语句可以优化以提升查询速度吗?或者我可以通过其他方法思路来提高查询效率吗?
    因为我正在编写一个基于该ORACLE数据库的C/S架构的查询系统,如果查询效率太低的话,这个系统肯定会被抛弃而失去生命力,我花了很多功夫来编写这个系统的,非常希望它可以有个好的结局,所以恳请各位大侠不吝赐教!小生当不尽感激!

8 个解决方案

#1


200W数据 不算多 只看sql的情况下 尽量少考虑对表里面的字段使用函数 很费时 

减少一次后效率大概多少 


select stacode,to_char(ddate,'yyyy') YER,avg(T) AAA 
from surf_mul_day 
where ddate between date'1951-01-01' and date'2013-08-31' 
      and to_char(ddate,'mm') between 1 and 8 and T <> -9999 
group by to_char(ddate,'yyyy'),stacode

#2


感谢您的回复
下面是您建议的SQL语句,查询耗时7.125秒
select stacode,to_char(ddate,'yyyy') YER,avg(T) AAA  from surf_mul_day  
where ddate between date'1951-01-01' and date'2013-12-31' 
and to_char(ddate,'mm') between 1 and 12 and T <> -9999  
group by to_char(ddate,'yyyy'),stacode
下面是我原本的SQL语句,查询耗时7.5秒
select stacode,to_char(ddate,'yyyy') YER,avg(T) AAA from surf_mul_day 
where to_char(ddate,'mm-dd') between '01-01' and '12-31' 
and to_char(ddate,'yyyy') between '1951' and '2013' and T<>-9999 
group by to_char(ddate,'yyyy'),stacode

另外您建议的语句不满足我的需求,我不是取整月的数据,比如我有时会查询1月5日至12月8日的数据,就没法按照您的写法来执行SQL了

#3


并行呢/*+parallel(t,10)*/

#4


谢谢gioh0022的建议,刚刚试过了,并行与否没有任何差异。我的oracle数据库版本应该是11g的,应该是支持并行的,但是没有什么改进,不知道为啥?

#5


11g Oracle自己选择它认为高效的执行计划

你的SQL没必要拆分得那么奇怪,发执行计划看看,不行就把年月日都做成函数索引。

#6


引用 5 楼 forgetsam 的回复:
11g Oracle自己选择它认为高效的执行计划
你的SQL没必要拆分得那么奇怪,发执行计划看看,不行就把年月日都做成函数索引。

谢谢您的答复,我是用PL/SQL软件测试的,用F5调出执行计划吧?我针对两种SQL语句分别截了两张图,您看看这样行吗?
另外您说的我的SQL拆分得奇怪是指的什么?
还有把年月日都做成函数索引是什么意思?
我专业不是做数据库的,所以麻烦您不介意小生愚笨,不吝赐教,感谢!
查询多年多站点统计值:循环查询还是利用ORACLE内部分组函数一次查询?
查询多年多站点统计值:循环查询还是利用ORACLE内部分组函数一次查询?

#7


过了这么多天也没人回复,那我把自己的应急方案写出来做个参考吧,不知道有没有更好的办法。

用PL/SQL测试发现:不用oracle分组函数读取100天以内的 数据速度超快,读取100以上的数据就慢了;用oracle分组函数读取无论多长时段资料速度都是一样的。

因此分情形来进行代码编写:1、当日数<=100日,用非分组函数循环读取每年资料;2、当日数>100日,用分组函数一次读取多年资料。

这样的折中方案,结果尚能接受。只好这样了,感谢各位!

#8


如果对比自己的循环多次访问,与sql函数的话
我觉得应该是sql函数效率更高
不过前提是sql优化与命中索引

#1


200W数据 不算多 只看sql的情况下 尽量少考虑对表里面的字段使用函数 很费时 

减少一次后效率大概多少 


select stacode,to_char(ddate,'yyyy') YER,avg(T) AAA 
from surf_mul_day 
where ddate between date'1951-01-01' and date'2013-08-31' 
      and to_char(ddate,'mm') between 1 and 8 and T <> -9999 
group by to_char(ddate,'yyyy'),stacode

#2


感谢您的回复
下面是您建议的SQL语句,查询耗时7.125秒
select stacode,to_char(ddate,'yyyy') YER,avg(T) AAA  from surf_mul_day  
where ddate between date'1951-01-01' and date'2013-12-31' 
and to_char(ddate,'mm') between 1 and 12 and T <> -9999  
group by to_char(ddate,'yyyy'),stacode
下面是我原本的SQL语句,查询耗时7.5秒
select stacode,to_char(ddate,'yyyy') YER,avg(T) AAA from surf_mul_day 
where to_char(ddate,'mm-dd') between '01-01' and '12-31' 
and to_char(ddate,'yyyy') between '1951' and '2013' and T<>-9999 
group by to_char(ddate,'yyyy'),stacode

另外您建议的语句不满足我的需求,我不是取整月的数据,比如我有时会查询1月5日至12月8日的数据,就没法按照您的写法来执行SQL了

#3


并行呢/*+parallel(t,10)*/

#4


谢谢gioh0022的建议,刚刚试过了,并行与否没有任何差异。我的oracle数据库版本应该是11g的,应该是支持并行的,但是没有什么改进,不知道为啥?

#5


11g Oracle自己选择它认为高效的执行计划

你的SQL没必要拆分得那么奇怪,发执行计划看看,不行就把年月日都做成函数索引。

#6


引用 5 楼 forgetsam 的回复:
11g Oracle自己选择它认为高效的执行计划
你的SQL没必要拆分得那么奇怪,发执行计划看看,不行就把年月日都做成函数索引。

谢谢您的答复,我是用PL/SQL软件测试的,用F5调出执行计划吧?我针对两种SQL语句分别截了两张图,您看看这样行吗?
另外您说的我的SQL拆分得奇怪是指的什么?
还有把年月日都做成函数索引是什么意思?
我专业不是做数据库的,所以麻烦您不介意小生愚笨,不吝赐教,感谢!
查询多年多站点统计值:循环查询还是利用ORACLE内部分组函数一次查询?
查询多年多站点统计值:循环查询还是利用ORACLE内部分组函数一次查询?

#7


过了这么多天也没人回复,那我把自己的应急方案写出来做个参考吧,不知道有没有更好的办法。

用PL/SQL测试发现:不用oracle分组函数读取100天以内的 数据速度超快,读取100以上的数据就慢了;用oracle分组函数读取无论多长时段资料速度都是一样的。

因此分情形来进行代码编写:1、当日数<=100日,用非分组函数循环读取每年资料;2、当日数>100日,用分组函数一次读取多年资料。

这样的折中方案,结果尚能接受。只好这样了,感谢各位!

#8


如果对比自己的循环多次访问,与sql函数的话
我觉得应该是sql函数效率更高
不过前提是sql优化与命中索引