oracle的update语句研究

1、Oracle的update语句优化研究update 语句的语法与原理1.语法单表：UPDATE 表名称 SET 列名称=新值 WHERE 列名称=某值如：updatet_join_situationsetjoin_state=1whereyear=2011更新年度为“2011”的数据的 join_state 字段为“1”。 如果更新的字段加了索引， 更新时会重建索引，更新效率会慢。多表关联，并把一个表的字段值更新到另一个表中的字段去：update 表 aseta.字段 1=（selectb.字段 1from 表 bwherea.字段 2=b.字段 2）whereexists（select1fr

2、om 表 bwherea.字段2=b.字段 2）oracle 的更新语句不通 MSSQL 那么简单易写，就算写出来了，但执行时可能会报这是由于 set 哪里的子查询查出了多行数据值，oracle 规定一对一更新数据,所以提示出错。要解决这样必须保证查出来的值一一对应。2.原理Update 语句的原理是先根据 where 条件查到数据后，如果 set 中有子查询，则执行子查询把值查出来赋给更新的字段，执行更新。如：update 表 aseta.字段 1=（selectb.字段 1from 表 bwherea.字段 2=b.字段 2）whereexists（select1from 表 bwhere

3、a.与段 2=b.字段 2） 。 查表 a 的所有数据， 循环每条数据， 验证该条数据是否符合 exists （select1from表 bwherea.字段 2=b.字段 2）条件，如果是则执行（selectb.字段 1from 表 bwherea.字段 2=b.字段 2）查询，查到对应的值更新 a.字段 1中。关联表更新时一定要有 exists（select1from 表 bwherea.字段 2=b.字段 2）这样的条件，否则将表 a 的其他数据的字段 1 更新为 null 值。提高 oracle 更新效率的各种解决方案1.标准 update 语法当你需要更新的表是单个或者被更新的字段不

4、需要关联其他表带过来，则最后选择标准的 update 语句，速度最快，稳定性最好，并返回影响条数。如果 where 条件中的字段加上索引，那么更新效率就更高。但对需要关联表更新字段时，update 的效率就非常差。2 .inlineview 更新法inlineview 更新法就是更新一个临时建立的视图。如：update(selecta.join_stateasjoin_state_a,b.join_stateasjoin_state_bfromt_join_situationa,t_people_infobwherea.people_number=b.people_numberanda.yea

5、r=2011anda.city_number=M00000anda.town_number=M51000)setjoin_state_a=join_state_b括号里通过关联两表建立一个视图，set 中设置好更新的字段。这个解决方法比写法较直观且执行速度快。但表 B 的主键一定要在 where 条件中，并且是以“二”来关联被更新表，否则报一下错误：Error区j001779：无法修改与非婕值保存蓑对吨的列通Cancel师.3 .merge 更新法merge 是 oracle 特有的语句，语法如下：MERGEINTOtable_namealias1USING(table|view|sub_qu

6、ery)alias2ON(joincondition)WHENMATCHEDTHENUPDATEtable_nameSETcol1=col_val1,col2=col2_valWHENNOTMATCHEDTHENINSERT(column_list)VALUES(column_values);它的原理是在 alias2 中 Select 出来的数据，每一条都跟 alias1 进行 ON(joincondition)的比较，如果匹配，就进行更新的操作(Update),如果不匹配，就进行插入操作（Insert）。执行 merge 不会返回影响的行数。Merge 语句的写法比较繁琐，并且最多只能两个

7、表关联，复杂的语句用 merge 更新法将力不从心且效率差。4 .快速游标更新法语法如：beginforcrin（查询语句）loop-循环-更新语句（根据查询出来的结果集合）endloop;-结束循环end;oracle 支持快速游标，不需要定义直接把游标写到 for 循环中，这样就方便了我们批量更新数据。再加上 oracle 的 rowid 物理字段（oracle 默认给每个表都有 rowid 这个字段，并且是唯一索引），可以快速定位到要更新的记录上。例子如下：beginforcrin(selecta.rowid,b.joinstatefromtjoinsituationa,tpeoplei

8、nfobwherea.people_number=b.people_numbera.townnumber=M51000)loopwhererowid=cr.rowid;endloop;nd;使用快速游标的好处很多，可以支持复杂的查询语句，更新准确，无论数据多大更新效率仍然高，但执行后不返回影响行数。三、结论力果建议标准 update 语法单表更新或较简单的语句米用使用此方案更优。inlineview 更新法两表关联且被更新表通过关联表主键关联的，米用此方案更优。merge 更新法两表关联且被更新表不是通过关联表主键关联的，米用此方案更优。快速游标更新法多表关联且逻辑复杂的，米用此方案更优。an

9、da.year=2011anda.city_number=M00000andupdatetjoinsituationsetjoinstate=cr.joinstate实时测试的速度：-48466条数据-1.297update(selecta.joinstateasjoinstatea,b.joinstateasjoinstatebfromt_join_situationa,t_people_infobwherea.people_number=b.people_numberanda.year=2011anda.citynumber=M00000anda.townnumber=M51000)set

10、join_state_a=join_state_b-7.156updatet_join_situationaseta.join_state=(selectb.join_statefromtpeopleinfobwherea.people_number=b.people_numberanda.year=2011anda.citynumber=M00000anda.townnumber=M51000)whereexists(select1fromt_people_infobwherea.peoplenumber=b.peoplenumberanda.year=2011anda.city_numbe

11、r=M00000anda.town_number=M51000)-3.281beginforcrin(selecta.rowid,b.join_statefromt_join_situationa,t_people_infobwherea.peoplenumber=b.peoplenumberanda.year=2011anda.city_number=M00000anda.town_number=M51000)loopupdatetjoinsituationsetjoinstate=cr.joinstatewhererowid=cr.rowid;endloop;end;-1.641mergeintot_jo