能解决这个问题的就是神！！

Question

现在有一个违章表(violation)，主键为违章编号wzbh,还有驾驶员的年龄nl（varchar类型），
现在要做一个年报表(根据违章时间wzsj)统计不同年龄段（0-17，18-30，31-40，41-50，51-65，大于65）的驾驶员违章次数，一年数据量有100多万，怎么样才能提高效率？
我现在是写了一个计算年龄段的函数toagefield（nl）用于返回年龄段。
select count(*),toagefield(nl) from violation where wzsj>=to_date('2004-01-01','yyyy-mm-dd') and wzsj<to_date('2005-01-01','yyyy-mm-dd') group by toagefield(nl);
我在violation表上对wzsj和nl做了索引，但是听说在nl上应用了函数的话，索引就失效了，我自己也觉得特别慢，报表出不来，怎么优化？

Answer 1

可以使用sql的指定索引来提高查询效率，如：
select count(*),toagefield(nl) from violation <font color='#ff0000'>with(index(0))</font> where wzsj>=to_date('2004-01-01','yyyy-mm-dd') and wzsj<to_date('2005-01-01','yyyy-mm-dd') group by toagefield(nl)
也可以采用将表结构进行修改如年龄段既然是固定的，那么直接在数据保存时就将所属年龄段计算并保存为一列，这样一来就能节省出计算年龄段的时间。

Answer 2

给你一个样例
usernameSelect = "select  " _
                & "sum(case when paycode= '001' then paymoney end) as food , " _
                & "sum(case when paycode= '002' then paymoney end) as cloth ," _
                & "sum(case when paycode= '003' then paymoney end) as traval ," _
                & "sum(case when paycode= '004' then paymoney end) as book ," _
                & "sum(case when paycode= '005' then paymoney end) as gas ," _
                & "sum(case when paycode= '006' then paymoney end) as mobile ," _
                & "sum(case when paycode= '007' then paymoney end) as medicine ," _
                & "sum(case when paycode= '008' then paymoney end) as unknown ," _
                & "sum(case when paycode= '009' then paymoney end) as home ," _
                & "sum(case when paycode= '010' then paymoney end) as reward ," _
                & "sum(case when paycode= '011' then paymoney end) as wash ," _
                & "sum(case when paycode= '012' then paymoney end) as othersum ," _
                & "sum(case when paycode= '013' then paymoney end) as electronic ," _
                & "sum(case when paycode= '014' then paymoney end) as furnitrue ," _
                & "sum(case when paycode= '099' then paymoney end) as other ," _
                & "sum(paymoney) as allsum    from Payment " _
                & "where (SUBSTRING(ymd, 1, 4) = @date)   "

Answer 3

这个问题我认为，你无论怎么优化数据库，还是建立什么索引，如果采用你现有的方式是一定要慢的了。我建议你改变一下实现方式，你既然建立了年龄（nl）为索引，你可以把要操作的数据查找出来，比如select nl from violation where wzsj>=to_date('2004-01-01','yyyy-mm-dd') and wzsj<to_date('2005-01-01','yyyy-mm-dd') group by toagefield(nl);
然后在把结果放到一个数组中，剩下的事情就是怎么操作他们了。我想如果这样的话，你的速度就会大大提高的。

Answer 4

用CASE就可以,100万条记录不算太多.

Answer 5

建议把年龄改为INT类型进行比较

Answer 6

你现有的记录可以用SElect 插入！

Answer 7

这个问题我认为，你无论怎么优化数据库，还是建立什么索引，如果采用你现有的方式是一定要慢的了。我建议你改变一下实现方式，你既然建立了年龄（nl）为索引，你可以把要操作的数据查找出来，比如select nl from violation where wzsj>=to_date('2004-01-01','yyyy-mm-dd') and wzsj<to_date('2005-01-01','yyyy-mm-dd') group by toagefield(nl);
然后在把结果放到一个数组中，剩下的事情就是怎么操作他们了。我想如果这样的话，你的速度就会大大提高的。
这个方法不好吧！！几十万条，你全拿出来比，多大内存呀？？

Answer 8

用下面的办法试试看：
SELECT  Case When nl>0 AND nl<=17 Then 18 
  When nl>18 AND nl<=30 Then 30 
 End AS NL
,COUNT(wzbh)
FROM violation
GROUP BY 
 Case When nl>0 AND nl<=17 Then 18 
  When nl>18 AND nl<=30 Then 30
 End

Answer 9

报表肯定是可以出来的，我觉得你的解决方法应该是没有什么问题，要做优化，除了调整一下SQL语句的顺序以外，我觉得还可以从硬件方法解决，比如说你现在用的服务器环境不是很好，换一个比较好的服务器，像这种百万数据量的服务器作为常用的统计应该是小型机才行，建议换好的环境，最好是小型机！

Answer 10

以前表结构的设计是有问题，比如年龄（nl）用的就是varchar2型，数据库里面该字段也很不规范，有空值，也可能有非数字的，所以我才写一个toagefield（）函数，里面对年龄段进行计算并对转换成数字作了异常处理。改表结构不太现实。我们的系统已经用了一年多了，该表结构不现实。
我现在优化的思路是这样的：
1.只取必要的字段，节省内存。比如直接取count(*)而不取nl
2.建索引，对wzsj和nl检索引，现在的问题是toagefield（nl）上索引失效。
3.利用存储过程，但是没怎么用过，好像oracle不支持下面这种类型的存储过程。
create or replace procedure proc_stat_agefield
as
begin
select count(*),toagefield(nl) from violation where wzsj>=to_date('2004-01-01','yyyy-mm-dd') and wzsj<to_date('2005-01-01','yyyy-mm-dd') group by toagefield(nl);
end;