多条件游标更新 变CASE WHEN更新法提高速度

<p>定义个结构体数组</p>
<p>type ty_parmeter is record<br>
(<br>
v_hezuotype number(1),<br>
v_comefrom varchar2(50),<br>
v_regchannel varchar2(50),<br>
v_newchannel nvarchar2(30)<br>
) ;<br><br>
type ty_parmeter_table is table of ty_parmeter index by binary_integer;<br>
parameter_arry ty_parmeter_table;</p>
<p>BEGIN</p>

<p>parameter_arry(1).F_CHANNEL:=N'财付通'; parameter_arry(1).F_TYPE:=1; parameter_arry(1).F_COME:='cft'; parameter_arry(1).F_REG:='cftwan';<br>
parameter_arry(2).F_CHANNEL:=N'拍拍'; parameter_arry(2).F_TYPE:=1; parameter_arry(2).F_COME:='paipai'; parameter_arry(2).F_REG:='paipai';<br>
parameter_arry(7).F_CHANNEL:=N'支付宝'; parameter_arry(7).F_TYPE:=2; parameter_arry(7).F_COME:='zfb'; parameter_arry(7).F_REG:='zfbwan';<br>
parameter_arry(17).F_CHANNEL:=N'新浪'; parameter_arry(17).F_TYPE:=0; parameter_arry(17).F_COME:='%sina%';parameter_arry(17).F_REG:='NULL';<br>
parameter_arry(18).F_CHANNEL:=N'搜狐'; parameter_arry(18).F_TYPE:=0; parameter_arry(18).F_COME:='%sohu%';parameter_arry(18).F_REG:='NULL';<br>
parameter_arry(22).F_CHANNEL:=N'搜索引擎';parameter_arry(22).F_TYPE:=0; parameter_arry(22).F_COME:='SEO%'; parameter_arry(22).F_REG:='NULL';<br>
parameter_arry(23).F_CHANNEL:=N'主站'; parameter_arry(23).F_TYPE:=0; parameter_arry(23).F_COME:='NULL'; parameter_arry(23).F_REG:='NULL';</p>

<p> for i in 1..14<br>
LOOP<br>
update t_gather_userinfo<br>
set F_REG_CHANNEL=parameter_arry(i).f_channel,</p>
<p> F_UPDATEDATE=sysdate<br>
where F_TYPE = parameter_arry(i).f_type<br>
AND F_COME =parameter_arry(i).f_come<br>
AND F_REG =parameter_arry(i).f_reg<br>
and F_REG_CHANNEL is null;<br>
v_rownum :=sql%rowcount;<br>
COMMIT;<br>
END LOOP;<br><br><br>
for i in 15..22 loop<br>
update t_gather_userinfo<br>
set F_REG_CHANNEL=parameter_arry(i).f_channel,</p>
<p> F_UPDATEDATE=sysdate<br>
where F_TYPE = parameter_arry(i).f_type<br>
AND F_COME like parameter_arry(i).f_come</p>
<p> AND F_REG =parameter_arry(i).f_reg<br>
and F_REG_CHANNEL is null;<br>
v_rownum :=sql%rowcount;<br>
COMMIT;<br>
end loop;<br><br>
update t_gather_userinfo<br>
set F_REG_CHANNEL=parameter_arry(23).f_channel,</p>
<p> F_UPDATEDATE=sysdate<br>
where F_REG_CHANNEL is null;<br>
v_rownum :=sql%rowcount;<br>
COMMIT;</p>


<p>这段代码 在8个CPU 32核 30G内存机器上跑 时间 1分钟，16分钟 26，28，30分钟</p>
<p>要迁到差劲2个CPU 8核的机器上 跑的后果是 分别为 114，116，115，146分钟 update 1万9千条数据</p>

<p>采用CASE WHEN写法 就全表扫描一次</p>
<p> UPDATE t_gather_userinfo<br>
SET f_reg_new_channel =<br>
CASE<br>
WHEN f_type = 1 AND f_come = 'CFT' AND f_reg = 'cftwan'<br>
THEN '财付通'<br>
WHEN f_type = 1 AND f_come = 'PAIPAI' AND f_reg = 'paipai'<br>
THEN '拍拍频道'<br>
WHEN f_type = 2 AND f_come = 'ZFBReg' AND f_reg = 'zfbwan'<br>
THEN '支付宝'<br>
WHEN f_type = 0 AND f_come LIKE '%sina%'<br>
THEN '新浪'<br>
WHEN f_type = 0 AND f_come LIKE '%sohu%'<br>
THEN '搜狐'<br>
ELSE 'MAINWEB'<br>
END,<br>
f_updatedate = SYSDATE<br>
WHERE f_reg_new_channel IS NULL; </p>