表lscq_user中有userms(手机号),tnum1(积分),ltime(时间),xh1(排名);要根据积分和时间来对手机号进行排名,并修改xh1,下面是我的程序,在1万数据下,20s可以跑完,但是10万条数据下半个小时都跑不完,如果有1000万条数据,应该怎样进行优化?或者有什么其他的思路
create or replace procedure user_day_tj_Proc is
p_userms varchar2(15);
p_tnum1 NUMBER(10);
p_ltime date;
p_rank int;
Cursor c_cur is
select userms
from lscq_user
where ltime between
to_date('2009-08-11 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and
to_date('2009-08-28 23:59:59', 'yyyy-mm-dd hh24:mi:ss');
begin
open c_cur;
loop
fetch c_cur
into p_userms1;
exit when c_cur%notfound;
update lscq_user a
set a.xh1 = (select dense_rank(p_tnum1,p_ltime) within
group(
order by tnum1,ltime)
from lscq_user
where ltime between
to_date('2009-08-11 00:00:00',
'yyyy-mm-dd hh24:mi:ss') and
to_date('2009-08-28 23:59:59',
'yyyy-mm-dd hh24:mi:ss'))
where a.userms = p_userms;
end loop;
close c_cur;
end;
create or replace procedure user_day_tj_Proc is
p_userms varchar2(15);
p_tnum1 NUMBER(10);
p_ltime date;
p_rank int;
Cursor c_cur is
select userms
from lscq_user
where ltime between
to_date('2009-08-11 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and
to_date('2009-08-28 23:59:59', 'yyyy-mm-dd hh24:mi:ss');
begin
open c_cur;
loop
fetch c_cur
into p_userms1;
exit when c_cur%notfound;
update lscq_user a
set a.xh1 = (select dense_rank(p_tnum1,p_ltime) within
group(
order by tnum1,ltime)
from lscq_user
where ltime between
to_date('2009-08-11 00:00:00',
'yyyy-mm-dd hh24:mi:ss') and
to_date('2009-08-28 23:59:59',
'yyyy-mm-dd hh24:mi:ss'))
where a.userms = p_userms;
end loop;
close c_cur;
end;
using (select userms, dense_rank(p_tnum1,p_ltime) within
group(
order by tnum1,ltime) d_rank
from lscq_user
where ltime between
to_date('2009-08-11 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and
to_date('2009-08-28 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
) b
on (a.userms = b.userms
and a.ltime between to_date('2009-08-11 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
and to_date('2009-08-28 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
when matched then
set update a.xh1 = b.d_rank;
merge into lscq_user a
using (select userms, dense_rank() over(order by tnum1,ltime) d_rank
from lscq_user
where ltime between
to_date('2009-08-11 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and
to_date('2009-08-28 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
) b
on (a.userms = b.userms
and a.ltime between to_date('2009-08-11 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
and to_date('2009-08-28 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
when matched then
update set a.xh1 = b.d_rank;
[Quote=引用 1 楼 shiyiwan 的回复:]
以前我改过一个存储过程,原先是用游标写的,执行一次要4个小时
后来我修改成用MERGE来实现,1分钟内就搞定了