Update实时测试的速度方案

Update实时测试的速度方案

建议
标准update语法

单表更新或较简单的语句采用使用此方案更优。
inline view更新法

两表关联且被更新表通过关联表主键关联的，采用此方案更优。
merge更新法

两表关联且被更新表不是通过关联表主键关联的，采用此方案更优。
快速游标更新法

多表关联且逻辑复杂的，采用此方案更优。



实时测试的速度：
--48466条数据
--1.297
update (select a.join_state as join_state_a,b.join_state as join_state_b
from t_join_situation a, t_people_info b where a.people_number=b.people_number
and a.year='2011'and a.city_number='M00000'and a.town_number='M51000'
) set join_state_a=join_state_b

--7.156
update t_join_situation a set a.join_state=(select b.join_state from t_people_info b
where a.people_number=b.people_number
and a.year='2011'and a.city_number='M00000'and a.town_number='M51000')
whereexists (select1from t_people_info b
where a.people_number=b.people_number
and a.year='2011'and a.city_number='M00000'and a.town_number='M51000')

--3.281
begin
for cr in (select a.rowid,b.join_state from t_join_situation a,t_people_info b
where a.people_number=b.people_number
and a.year='2011'and a.city_number='M00000'and a.town_number='M51000') loop
update t_join_situation set join_state=cr.join_state where
rowid = cr.rowid;
endloop;
end;