求一性能优化方案,请有这方面经验的帮帮我呀,在线等回复!!!!

Question

表结构如下
create table mytable
(
  RE_ID          NUMBER(4) not null,    --地区ID
  TI_ID          NUMBER(6) not null,    --日期ID
  CH_ID          VARCHAR2(1) not null,  --业务ID
  TYPE           VARCHAR2(1) not null,  --业务类型
  USERID         VARCHAR2(1) not null,  --业务品牌
  ACTYPE_ID      NUMBER(3) not null,    --错误ID
  OPER_COUNTS    NUMBER(16)             --次数
)
我现在要实现的就是想把这个表的OPER_COUNTS(次数)计算出来并更新,其更新规则是要求计算出,在同一地区ID,同一日期ID,同一业务ID,同一业务类型,同一业务品牌,同一错误ID,的次数,也就是说这个次数OPER_COUNTS字段,合计的数次要同时满足以上六个条件共存才行.
我做了两个方法,能实现,就是速度慢,因为我这个表至少有1000万条记录:
我的方法一:用游标实现
CURSOR oper_counts_cur IS
   SELECT RE_ID,TI_ID,CH_ID,TYPE,USERID,ACTYPE_ID
     FROM mytable; 
  OPEN oper_counts_cur;
LOOP
   FETCH oper_counts_cur INTO n_RE_ID,n_TI_ID,v_CH_ID,v_TYPE,v_USERID,n_ACTYPE_ID;
   EXIT WHEN oper_counts_cur%NOTFOUND;
   UPDATE mytable SET oper_counts=(
       SELECT COUNT(*) FROM mytable 
     WHERE userid=v_USERID
       AND ch_id=v_CH_ID
       AND type=v_TYPE
       AND ti_id=n_TI_ID
       AND re_id=n_RE_ID
       AND actype_id=n_ACTYPE_ID);
END LOOP;
  CLOSE oper_counts_cur;
  COMMIT;方法二:直接用update语句修改
   UPDATE mytable b SET b.oper_counts=(
       SELECT COUNT(*) FROM mytable a
     WHERE a.userid=b.userid
       AND a.ch_id=b.ch_id
       AND a.type=b.type
       AND a.ti_id=b.ti_id
       AND a.re_id=b.re_id
       AND a.actype_id=b.actype_id);
  COMMIT;
以上两个方法,跑起来都特别慢,几个小时也跑不完呀,哪位高手帮帮我,看看有没有什么好的解决方案呀,小弟,在线等!!!

Answer 1

你用PLSQL是不是全表搜索,如果是,需要建立对应的索引,速度就会快很多,有可能快几百倍

Answer 2

对地区id、日期ID做分区
然后对各个分区表进行处理
避免大批量数据操作

Answer 3

1000万条记录update本身就会很慢。看看你的sql的执行计划，若是开始能用上索引，就没有问题，若是开始就是full table scan，
那还是乖乖的按照你的where子句的条件，建立索引吧

Answer 4

以下过程一条一条记录修改，100000条commit一次，避免所有记录一起修改引起的回滚段不足；并且在过程运行如果遇到什么错误而终止，也已经有一部分数据已经修改成功，再次运行时只需要修改剩下的记录，避免了一起修改时万一出错就前功尽弃的问题。
declare
v_rowcount number;
begin
   loop
     update T_CARCASE_CAR 
        set CREATED_BY = user,CREATED_DATE=sysdate,UPDATED_BY=user,UPDATED_DATE=sysdate
      where CREATED_BY=null or CREATED_DATE=null or UPDATED_BY=null or UPDATED_DATE=null
        and rownum<100001;
      v_rowcount:=sql%rowcount;     
      commit;   
     exit when v_rowcount<100000;    --  当sql返回的记录数<100000时，说明已经是最后一次循环了，符合exit出循环的条件了
   end loop;  
end;
/   
给你个示例，每10w条记录update一次

Answer 5

我觉得tgm78说的挺有道理的,拜托你能根据我的表做个例子吗?谢谢!我现在急用呀,没有太多时间研究细节,帮帮忙

Answer 6

把我的update语句换成你的即可啊。

Answer 7

可是你这个where条件里要判断null,我的那个要修改的值是number型,默认是0呀,怎么办?

Answer 8

哪位大位帮我想想,我这个更新怎么实现呀,看看在sql语法上还有没有什么改进的地方呀

Answer 9

1000W的数据量也不是很大，建议你用你的第一种方法，只是你的表mytable上面要建个索引，可以用RE_ID,TI_ID,CH_ID,TYPE,USERID,ACTYPE_ID作为一个联合索引来查询，我相信如果数据库性能没什么问题的话，2个多小时应该能执行完，还有就是你游标打开会，每次更新一条数据完后就commit一次，不要等关闭游标再一次性commit

Answer 10

declare
v_rowcount number;
begin
   loop
     UPDATE mytable b SET b.oper_counts=(
       SELECT COUNT(*) FROM mytable a
     WHERE a.userid=b.userid
       AND a.ch_id=b.ch_id
       AND a.type=b.type
       AND a.ti_id=b.ti_id
       AND a.re_id=b.re_id
       AND a.actype_id=b.actype_id);
      v_rowcount:=sql%rowcount;     
      commit;   
     exit when v_rowcount<100000;    --  当sql返回的记录数<100000时，说明已经是最后一次循环了，符合exit出循环的条件了
   end loop;  
end;
/   

Answer 11

我测试过，速度还是慢呀，好像连续and就是慢呀，我想问问有没有别的办法可以避免这连续的and判断，又能和它效果一样呢？这连续的and是不是每个and都要进行全表搜索呀，实在有点慢呀，跑四五个小时也跑不完呀

Answer 12

这要看你是想一次性的计算全部的数据，还是随着数据的变化而计算。如果是一次性的计算现有的全部数据，
不管使用哪种方式，都不会在短时间内完成。如果一次性计算全部建议将任务分开执行，比如可以按地区或者是地区
将数据分开，每个程序完成一个地区的计算，这样就可以同时运行多个程序，多个地区同时计算。写一个带有一个 输入参数的过程，参数为地区编号，这样可以将不同地区的计算分开，相当于并行了。如果有10个地区，就可以当作10个job来运行，速度自然会快。
如果是实时更新，就简单了