僅變更來源表格中相符欄位的紀錄：UPDATE VS. MERGE INTO

從 B Table 將資料更新到 A Table的方式，大都會這樣寫：

UPDATE A 
   SET A.CODE = (SELECT DISTINCT (CODE)
                     FROM B
                    WHERE A.CODE_1 = B.CODE_1
                      AND A.CODE_2 = B.B_CODE_2)

但有個缺點是如果B與A若有沒有相符的紀錄，即在A表格中不符合 A.A_CODE = B.A_CODE & A.B_CODE = B.B_CODE 條件的紀錄，就會被更新成NULL；為了避免這種情況，我們可以加上NVL()函式跳過不相符的紀錄被更新：

UPDATE A 

   SET A.CODE = NVL((SELECT DISTINCT (CODE)

                     FROM B

                    WHERE A.CODE_1 = B.CODE_1

                      AND A.CODE_2 = B.CODE_2), A.CODE)

Oracle 在 9i 之後的版本新增一個 MERGE 的功能，在語意以及速度方面上更勝一籌：

  MERGE INTO A

  USING  B

  ON (A.CODE_1 = B.CODE_1 AND A.CODE_2 = B.CODE_2)

  WHEN MATCHED THEN

    UPDATE SET A.CODE = B.CODE;

語意上就更接近口語化，程式碼也容易變得簡潔。且不止於此，既然有 MATCHED，也就會有 NOT MATCHED，多增加了分支判斷的功能。假設我們想把不符條件的資料記錄到另外一個Table中，就可這樣寫：

MERGE INTO A

  USING  B

  ON (A.CODE_1 = B.CODE_1 AND A.CODE_2 = B.CODE_2)

  WHEN MATCHED THEN

    UPDATE SET A.CODE = B.CODE;

  WHEN NOT MATCHED THEN

INSERT INTO FAIL_REC VALUES (A.CODE_1, A,CODE_2);

分享囉~

Oracle複寫到SQL 2005時，卡在DELETE ARTICLE的終極解法

前篇文章提到當利用SQL 2005複寫機制，將Oracle複寫到SQL端時，一段時間後就會卡死在DELTE FROM Articlexxx的SQL Statement，除了速度緩慢之外，也連帶的影響其他工作效能。最後跟同事試了幾種方式，經由同事的實驗確認了以下方式會取得最佳狀態：

• 建立好複寫機制後，替每個Table做好統計值分析；再確認執行計畫是否有使用Index即可(使用Range Scan而非Full Table Access)。這個步驟有點麻煩；我們的問題是當複寫的時候，DELETE FROM Articlexxx 會因為執行計畫不佳而Hang在哪裏。但每個Article都可能會有這樣的問題，所以我們以Oracle端部署時所用的複寫帳號登入後(假設是repadm)，透過以下SQL Statement準備各Article的DELETE Article SQL Statement：
  
   SELECT 'DELETE FROM ' || TABLE_NAME  ||
         ' WHERE EXISTS ' ||
            (SELECT p.POLL_POLLID ' ||
               'FROM HREPL_POLL p ' || 
             'WHERE CHARTOROWID(l.ROWID) = p.Poll_ROWID ' || 
             '       AND p.Poll_PollID = :Pollid)'
     FROM USER_TABLES
   WHERE TABLE_NAME LIKE 'HREPL_ARTICLE%'
  
  之後，我們先塞點假資料給HREPL_POLL以及各個Article以模擬大量資料(剛開始沒資料，執行計畫就會用Full Table Access的方式規劃)，然後分別開始做統計分析；這時候取得統計值就會接近實際狀況。
  
• 準備好統計資訊後，利用 outline 的方式鎖住上述每一個DELETE Article SQL Statement的執行計畫，避免大量DML後Oracle對該DELETE Statement的執行計畫跑掉。
• 以後每建立一個新的發行項目(Article)，就要執行上述步驟(修改一下SQL Statement取得該新發行項目的Delete Articlexxx SQL Statement)以取得並鎖定最佳的執行計畫。

目前跑了一週，已經沒再發生Hang住的問題，這方法提供參考囉。