[轉]Oracle的update語句最佳化研究

標籤：font   select   選擇   唯一索引   方法   最好   表名   結果   語句   

原文地址:http://blog.csdn.net/u011721927/article/details/39228001

 

一、         update語句的文法與原理

1.     文法單表：UPDATE 表名稱 SET 列名稱 = 新值 WHERE 列名稱 = 某值如：update t_join_situation set join_state=‘1‘whereyear=‘2011‘更新年度為“2011”的資料的join_state欄位為“1”。如果更新的欄位加了索引，更新時會重建索引，更新效率會慢。   多表關聯，並把一個表的欄位值更新到另一個表中的欄位去：update 表a set a.欄位1 = (select b.欄位1 from 表b where a.欄位2=b.欄位2) where exists(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 from 表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文法當你需要更新的表是單個或者被更新的欄位不需要關聯其他錶帶過來，則最後選擇標準的update語句，速度最快，穩定性最好，並返回影響條數。如果where條件中的欄位加上索引，那麼更新效率就更高。但對需要關聯表更新欄位時，update的效率就非常差。2.     inline view更新法inline view更新法就是更新一個臨時建立的視圖。如：update (select a.join_state asjoin_state_a,b.join_state as join_state_bfrom t_join_situation a, t_people_info b where a.people_number=b.people_numberand a.year=‘2011‘and a.city_number=‘M00000‘and a.town_number=‘M51000‘) setjoin_state_a=join_state_b括弧裡通過關聯兩表建立一個視圖，set中設定好更新的欄位。這個解決方案比寫法較直觀且執行速度快。但表B的主鍵一定要在where條件中，並且是以“=”來關聯被更新表，否則報一下錯誤： 3.merge更新法merge是oracle特有的語句，文法如下：MERGE INTO table_name alias1 
USING (table|view|sub_query) alias2
ON (join condition) 
WHEN MATCHED THEN 
    UPDATE table_name 
    SET col1 = col_val1, 
        col2     = col2_val 
WHEN NOT MATCHED THEN 
    INSERT (column_list) VALUES (column_values); 它的原理是在alias2中Select出來的資料，每一條都跟alias1進行 ON (join condition)的比較，如果匹配，就進行更新的操作(Update),如果不匹配，就進行插入操作(Insert)。執行merge不會返回影響的行數。Merge語句的寫法比較繁瑣，並且最多隻能兩個表關聯，複雜的語句用merge更新法將力不從心且效率差。4.快速遊標更新法文法如：beginfor cr in (查詢語句) loop –-迴圈   --更新語句（根據查詢出來的結果集合）endloop; --結束迴圈end;oracle支援快速遊標，不需要定義直接把遊標寫到for迴圈中，這樣就方便了我們批次更新資料。再加上oracle的rowid物理欄位（oracle預設給每個表都有rowid這個欄位，並且是唯一索引），可以快速定位到要更新的記錄上。例子如下：beginfor cr in (select a.rowid,b.join_state from t_join_situation a,t_people_info bwhere a.people_number=b.people_numberand 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更新法	兩表關聯且被更新表通過關聯表主鍵關聯的，採用此方案更優。
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_numberand a.year=‘2011‘and a.city_number=‘M00000‘and a.town_number=‘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 bwhere a.people_number=b.people_numberand a.year=‘2011‘and a.city_number=‘M00000‘and a.town_number=‘M51000‘)whereexists (select1from t_people_info bwhere a.people_number=b.people_numberand 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 bwhere a.people_number=b.people_numberand 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.641mergeinto t_join_situation ausing t_people_info bon (a.people_number=b.people_numberand a.year=‘2011‘and a.city_number=‘M00000‘and a.town_number=‘M51000‘)whenmatchedthenupdateset a.join_state=b.join_state

 

[轉]Oracle的update語句最佳化研究