《oracle的update的五种方式》由会员分享,可在线阅读,更多相关《oracle的update的五种方式(4页珍藏版)》请在金锄头文库上搜索。
1、Oracle的update语句优化研究一、 update语句的语法与原理1. 语法单表:UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值如:update t_join_situation set join_state=1 where year=2011更新年度为“2011”的数据的join_state字段为“1”。如果更新的字段加了索引,更新时会重建索引,更新效率会慢。多表关联,并把一个表的字段值更新到另一个表中的字段去:update 表a set a.字段1 = (select b.字段1 from 表b where a.字段2=b.字段2) where exis
2、ts(select 1 from 表b where a.字段2=b.字段2) oracle的更新语句不通MSSQL那么简单易写,就算写出来了,但执行时可能会报这是由于set哪里的子查询查出了多行数据值,oracle规定一对一更新数据,所以提示出错。要解决这样必须保证查出来的值一一对应。2. 原理Update语句的原理是先根据where条件查到数据后,如果set中有子查询,则执行子查询把值查出来赋给更新的字段,执行更新。如:update 表a set a.字段1 = (select b.字段1 from 表b where a.字段2=b.字段2) where exists(select 1 fr
3、om 表b where a.字段2=b.字段2)。查表a的所有数据,循环每条数据,验证该条数据是否符合exists(select 1 from 表b where a.字段2=b.字段2)条件,如果是则执行(select b.字段1 from 表b where a.字段2=b.字段2)查询,查到对应的值更新a.字段1中。关联表更新时一定要有exists(select 1 from 表b where a.字段2=b.字段2)这样的条件,否则将表a的其他数据的字段1更新为null值。二、 提高oracle更新效率的各种解决方案1. 标准update语法当你需要更新的表是单个或者被更新的字段不需要关联
4、其他表带过来,则最后选择标准的update语句,速度最快,稳定性最好,并返回影响条数。如果where条件中的字段加上索引,那么更新效率就更高。但对需要关联表更新字段时,update的效率就非常差。2. inline view更新法inline view更新法就是更新一个临时建立的视图。如:update (select a.join_state as join_state_a,b.join_state as join_state_bfrom t_join_situation a, t_people_info b where a.people_number=b.people_number and
5、a.year=2011 and a.city_number=M00000 and a.town_number=M51000) set join_state_a=join_state_b括号里通过关联两表建立一个视图,set中设置好更新的字段。这个解决方法比写法较直观且执行速度快。但表B的主键一定要在where条件中,并且是以“=”来关联被更新表,否则报一下错误: 3. merge更新法merge是oracle特有的语句,语法如下:MERGEINTOtable_namealias1USING(table|view|sub_query)alias2ON(joincondition)WHENMATC
6、HEDTHENUPDATEtable_nameSETcol1=col_val1,col2=col2_valWHENNOTMATCHEDTHENINSERT(column_list)VALUES(column_values);它的原理是在alias2中Select出来的数据,每一条都跟alias1进行 ON (join condition)的比较,如果匹配,就进行更新的操作(Update),如果不匹配,就进行插入操作(Insert)。执行merge不会返回影响的行数。Merge语句的写法比较繁琐,并且最多只能两个表关联,复杂的语句用merge更新法将力不从心且效率差。4. 快速游标更新法语法如:
7、beginfor cr in (查询语句) loop -循环-更新语句(根据查询出来的结果集合)end loop; -结束循环end;oracle支持快速游标,不需要定义直接把游标写到for循环中,这样就方便了我们批量更新数据。再加上oracle的rowid物理字段(oracle默认给每个表都有rowid这个字段,并且是唯一索引),可以快速定位到要更新的记录上。例子如下:beginfor cr in (select a.rowid,b.join_state from t_join_situation a,t_people_info b where a.people_number=b.peopl
8、e_number and a.year=2011 and a.city_number=M00000 and a.town_number=M51000) loopupdate t_join_situation set join_state=cr.join_state whererowid = cr.rowid;end loop;end;使用快速游标的好处很多,可以支持复杂的查询语句,更新准确,无论数据多大更新效率仍然高,但执行后不返回影响行数。三、 结论方案建议标准update语法单表更新或较简单的语句采用使用此方案更优。inline view更新法两表关联且被更新表通过关联表主键关联的,采用此
9、方案更优。merge更新法两表关联且被更新表不是通过关联表主键关联的,采用此方案更优。快速游标更新法多表关联且逻辑复杂的,采用此方案更优。实时测试的速度:-48466条数据-1.297update (select a.join_state as join_state_a,b.join_state as join_state_bfrom 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_numbe
10、r=M51000) set join_state_a=join_state_b-7.156update 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)where exists (select 1 from t_people_info b where a.people_number=
11、b.people_number and a.year=2011 and a.city_number=M00000 and a.town_number=M51000)-3.281beginfor 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) loopupdate t_join_situation set join_state=cr.join_state whererowid = cr.rowid;end loop;end;-1.641merge into t_join_situation ausing t_people_info bon (a.people_number=b.people_number and a.year=2011 and a.city_number=M00000 and a.town_number=M51000)when matched then update set a.join_state=b.join_state