各表:
T_Gather_Project 7641MB Rows:4574,4899
T_Gather_ProUser 7776MB Rows:8396,0838
T_Base_user_buy_INfo 548MB Rows: 569,4998
做了些索引,分了些区。效果不大 update T_BASE_USER_BUY_INFO a
set f_buyType=1 ---1 发起合买
where exists
(select 1 from T_Gather_Project b where a.F_UserName=b.F_UserName and F_LotID=F_LotteryID and F_Wtype=F_Wtype and F_Expect =F_Expect
and F_IsCooperateBuy=1 and F_IsTraceExecpte=0);
update T_BASE_USER_BUY_INFO a
set f_buyType=3 -- 3 合代购
where exists (select 1 from T_Gather_Project b where a.F_UserName=b.F_UserName and F_LotID=F_LotteryID and F_Wtype=F_Wtype and F_Expect =F_Expect
and F_IsCooperateBuy=0 and F_IsTraceExecpte=0);
update T_BASE_USER_BUY_INFO a
set f_buyType=4 -- 4追号
where exists (select 1 from T_Gather_Project b where a.F_UserName=b.F_UserName and F_LotID=F_LotteryID and F_Wtype=F_Wtype and F_Expect =F_Expect
and F_IsTraceExecpte=1);
update t_base_user_buy_info i
set i.f_buytype=2 --2 认购合买
where exists
(
select 1 from
(
select p.f_lotteryid,p.f_wtype,p.f_expect,p.f_username
from T_GATHER_PROUSER b inner join t_gather_project p on b.F_Projectid=p.F_id and b.f_lotteryid=p.f_lotteryid
and b.f_wtype=p.f_wtype and b.f_expect=p.f_expect
where p.f_iscooperatebuy=1 and p.f_istraceexecpte=0
) c where c.f_lotteryid=i.f_lotid and c.f_wtype=i.f_wtype and c.f_expect=i.f_expect and c.f_username=i.f_username
);
T_Gather_Project 7641MB Rows:4574,4899
T_Gather_ProUser 7776MB Rows:8396,0838
T_Base_user_buy_INfo 548MB Rows: 569,4998
做了些索引,分了些区。效果不大 update T_BASE_USER_BUY_INFO a
set f_buyType=1 ---1 发起合买
where exists
(select 1 from T_Gather_Project b where a.F_UserName=b.F_UserName and F_LotID=F_LotteryID and F_Wtype=F_Wtype and F_Expect =F_Expect
and F_IsCooperateBuy=1 and F_IsTraceExecpte=0);
update T_BASE_USER_BUY_INFO a
set f_buyType=3 -- 3 合代购
where exists (select 1 from T_Gather_Project b where a.F_UserName=b.F_UserName and F_LotID=F_LotteryID and F_Wtype=F_Wtype and F_Expect =F_Expect
and F_IsCooperateBuy=0 and F_IsTraceExecpte=0);
update T_BASE_USER_BUY_INFO a
set f_buyType=4 -- 4追号
where exists (select 1 from T_Gather_Project b where a.F_UserName=b.F_UserName and F_LotID=F_LotteryID and F_Wtype=F_Wtype and F_Expect =F_Expect
and F_IsTraceExecpte=1);
update t_base_user_buy_info i
set i.f_buytype=2 --2 认购合买
where exists
(
select 1 from
(
select p.f_lotteryid,p.f_wtype,p.f_expect,p.f_username
from T_GATHER_PROUSER b inner join t_gather_project p on b.F_Projectid=p.F_id and b.f_lotteryid=p.f_lotteryid
and b.f_wtype=p.f_wtype and b.f_expect=p.f_expect
where p.f_iscooperatebuy=1 and p.f_istraceexecpte=0
) c where c.f_lotteryid=i.f_lotid and c.f_wtype=i.f_wtype and c.f_expect=i.f_expect and c.f_username=i.f_username
);
并不是所有时候 exists 都比 in 快
select count(0) from T_Gather_Project b where b.F_IsTraceExecpte in ('1','0') 有多大?还是说是T_Gather_Project全部的记录数?
using (
select * from (
select b.f_userName,b.f_lotteryID,b.f_wtype,f_buytype,
case when F_IsCooperateBuy=1 and F_IsTraceExecpte=0 then 1
when F_IsCooperateBuy=0 and F_IsTraceExecpte=0 then 3
when F_IsTraceExecpte=1 then 4
else 2 end f_buytype1
from T_BASE_USER_BUY_INFO a ,
T_Gather_Project b
where a.F_UserName=b.F_UserName
and a.F_LotID= b.F_LotteryID
and a.F_Wtype=b.F_Wtype
and a.F_Expect =b.F_Expect ) t
where f_buytype <> f_buytype1) T2
on (t1.F_UserName=t2.F_UserName and t1.F_LotID=t2.F_LotteryID
and t1.F_Wtype=t2.F_Wtype and t1.F_Expect = t2.F_Expect )
when matched then
update set t1.f_buytype = t2.fbuytype1
when not matched then
insert (t1.f_buytype)
values (t2.f_buytype)
引用楼主 yjdfff 的帖子:
有一个表A有1000万行数据,有字段(C1,C2),B表有100条数据有字段(C1,C2),现要更新A表中的字段C2为A.C2,条件是A.C1=B.C1,
A表中的C1是有重复的,也就是说更新的行数不等于100行,现在我想每10000行COMMIT一次,如何做呢?不用一个变量做计数的方式,
这样反而更慢,请问在过程中用BULK COLLECT和FORALL语句如何实现呢?
DECLARE
TYPE T_B IS TABLE OF B%ROWTYPE INDEX BY VARCHAR2;
V_B T_B;
TYPE T_ROWID IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
V_ROWID T_ROWID;
CURSOR CUR_A IS SELECT ROWID FROM A;
BEGIN
--把B置入索引表,改善效率
FOR X IN CUR_B LOOP
V_B(X.C1) := X.C2;
END LOOP; OPEN CUR_A;
LOOP
FETCH CUR_A BULK COLLECT INTO V_ROWID LIMIT 10000; --每次一万行的控制
FORALL IN X IN 1..V_ROWID.COUNT
UPDATE A SET A.C2 = V_A(A.C1).C2 WHERE A.ROWID = V_ROWID(X); COMMIT;
EXIT WHEN CUR_A%NOTFOUND;
END LOOP;
CLOSE CUR_A;
END; 上述代码只是一个可以参考的思路,未经过执行验证. 谢谢!