oracle的update語句研究

1、Oracle的update語句優(yōu)化研究update 語句的語法與原理1.語法單表：UPDATE 表名稱 SET 列名稱=新值 WHERE 列名稱=某值如：updatet_join_situationsetjoin_state=1whereyear=2011更新年度為“2011”的數(shù)據(jù)的 join_state 字段為“1”。 如果更新的字段加了索引， 更新時會重建索引，更新效率會慢。多表關(guān)聯(lián)，并把一個表的字段值更新到另一個表中的字段去：update 表 aseta.字段 1=（selectb.字段 1from 表 bwherea.字段 2=b.字段 2）whereexists（select1fr

2、om 表 bwherea.字段2=b.字段 2）oracle 的更新語句不通 MSSQL 那么簡單易寫，就算寫出來了，但執(zhí)行時可能會報這是由于 set 哪里的子查詢查出了多行數(shù)據(jù)值，oracle 規(guī)定一對一更新數(shù)據(jù),所以提示出錯。要解決這樣必須保證查出來的值一一對應。2.原理Update 語句的原理是先根據(jù) where 條件查到數(shù)據(jù)后，如果 set 中有子查詢，則執(zhí)行子查詢把值查出來賦給更新的字段，執(zhí)行更新。如：update 表 aseta.字段 1=（selectb.字段 1from 表 bwherea.字段 2=b.字段 2）whereexists（select1from 表 bwhere

3、a.與段 2=b.字段 2） 。 查表 a 的所有數(shù)據(jù)， 循環(huán)每條數(shù)據(jù)， 驗證該條數(shù)據(jù)是否符合 exists （select1from表 bwherea.字段 2=b.字段 2）條件，如果是則執(zhí)行（selectb.字段 1from 表 bwherea.字段 2=b.字段 2）查詢，查到對應的值更新 a.字段 1中。關(guān)聯(lián)表更新時一定要有 exists（select1from 表 bwherea.字段 2=b.字段 2）這樣的條件，否則將表 a 的其他數(shù)據(jù)的字段 1 更新為 null 值。提高 oracle 更新效率的各種解決方案1.標準 update 語法當你需要更新的表是單個或者被更新的字段不

4、需要關(guān)聯(lián)其他表帶過來，則最后選擇標準的 update 語句，速度最快，穩(wěn)定性最好，并返回影響條數(shù)。如果 where 條件中的字段加上索引，那么更新效率就更高。但對需要關(guān)聯(lián)表更新字段時，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括號里通過關(guān)聯(lián)兩表建立一個視圖，set 中設置好更新的字段。這個解決方法比寫法較直觀且執(zhí)行速度快。但表 B 的主鍵一定要在 where 條件中，并且是以“二”來關(guān)聯(lián)被更新表，否則報一下錯誤：Error區(qū)j001779：無法修改與非婕值保存蓑?qū)嵉牧型–ancel師.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 出來的數(shù)據(jù)，每一條都跟 alias1 進行 ON(joincondition)的比較，如果匹配，就進行更新的操作(Update),如果不匹配，就進行插入操作（Insert）。執(zhí)行 merge 不會返回影響的行數(shù)。Merge 語句的寫法比較繁瑣，并且最多只能兩個

7、表關(guān)聯(lián)，復雜的語句用 merge 更新法將力不從心且效率差。4 .快速游標更新法語法如：beginforcrin（查詢語句）loop-循環(huán)-更新語句（根據(jù)查詢出來的結(jié)果集合）endloop;-結(jié)束循環(huán)end;oracle 支持快速游標，不需要定義直接把游標寫到 for 循環(huán)中，這樣就方便了我們批量更新數(shù)據(jù)。再加上 oracle 的 rowid 物理字段（oracle 默認給每個表都有 rowid 這個字段，并且是唯一索引），可以快速定位到要更新的記錄上。例子如下：beginforcrin(selecta.rowid,b.joinstatefromtjoinsituationa,tpeoplei

8、nfobwherea.people_number=b.people_numbera.townnumber=M51000)loopwhererowid=cr.rowid;endloop;nd;使用快速游標的好處很多，可以支持復雜的查詢語句，更新準確，無論數(shù)據(jù)多大更新效率仍然高，但執(zhí)行后不返回影響行數(shù)。三、結(jié)論力果建議標準 update 語法單表更新或較簡單的語句米用使用此方案更優(yōu)。inlineview 更新法兩表關(guān)聯(lián)且被更新表通過關(guān)聯(lián)表主鍵關(guān)聯(lián)的，米用此方案更優(yōu)。merge 更新法兩表關(guān)聯(lián)且被更新表不是通過關(guān)聯(lián)表主鍵關(guān)聯(lián)的，米用此方案更優(yōu)。快速游標更新法多表關(guān)聯(lián)且邏輯復雜的，米用此方案更優(yōu)。an

9、da.year=2011anda.city_number=M00000andupdatetjoinsituationsetjoinstate=cr.joinstate實時測試的速度：-48466條數(shù)據(jù)-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