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

各位大侠好！

    兹有我服务器端一个包含近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内部分组函数一次查询？

#7

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

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

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

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

#8

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

#1