2010年12月20日 星期一

[Oracle]Update data from another Table

Web系統不免會有由Excel Upload的資料去更新資料表的需求,當然如果Excel資料一筆一筆的對Table做更新是不符合效益,這將造成對database的transaction過多,而且因為目前需Update的資料表資料量相當大,所以採用的方法如下:
1.Insert data in db temp table from excel data
2.update data from temp table

上述的從Temp table取用資料Update資料表,就切入本篇的主題,可用的方法有三:
1.Update by sub-query
2.Update table view
3.Merge

方法一:
UPDATE bigTable b
   SET (col1,col2,col3) = (
       SELECT t.col1,t.col2,t.col3
         FROM tempTable t
        WHERE b.col = t.col)
where exists(
       SELECT t.col1,t.col2,t.col3
         FROM tempTable t
        WHERE b.col = t.col);
方法二:
UPDATE (
SELECT b.col1 as old_col1,
       b.col2 as old_col2,
       b.col3 as old_col3,
       t.col1 as new_col1,
       t.col2 as new_col2,
       t.col3 as new_col3 
  FROM bigTable b, tempTable t
 WHERE b.col = t.col)
   SET old_col1 = new_col1,
       old_col2 = new_col2,
       old_col3 = new_col3;

方法三:
MERGE INTO bigTable b
 USING (SELECT col1 , col2 , col3 , col
          tempTable t ) t
    ON ( b.col = t.col)
WHEN matched THEN
UPDATE
   SET old_col1 = new_col1,
       old_col2 = new_col2,
       old_col3 = new_col3;

測試結果:
方法一:140 sec
方法二:1 sec
方法三:未測

透過方法二 明顯是效能最佳的解法,但若會被update的bigTable並非符合UK或PK的條件,將會產生"ORA-01779: cannot modify a column which maps to a non-key-preserved table"的錯誤,若你的table不適合建立UK或PK時,可透過hint的方式/*+ BYPASS_UJVC */來忽略UK的檢查。
UPDATE (
SELECT /*+ BYPASS_UJVC */ b.col1 as old_col1,
       b.col2 as old_col2,
       b.col3 as old_col3,
       t.col1 as new_col1,
       t.col2 as new_col2,
       t.col3 as new_col3 
  FROM bigTable b, tempTable t
 WHERE b.col = t.col)
   SET old_col1 = new_col1,
       old_col2 = new_col2,
       old_col3 = new_col3;

參考網址:http://blog.csdn.net/yuhua3272004/archive/2008/08/06/2776121.aspx

1 則留言: