談資料倉儲的融合技術(2)Merge教戰手冊

http://www.ithome.com.tw/itadm/article.php?c=38332
談資料倉儲的融合技術(2)Merge教戰手冊

談資料倉儲的融合技術(2)Merge教戰手冊

文/ iThome (記者) 2006-07-25

資料掘轉最後兩個步驟－修改（update）與新增（insert），為了減少Full Table Scan 的次數，而造成效能上的影響，也避免多資料來源的衝突，與資料刪除後的影響，Oracle提出Merge語法以解決這些問題。

在資料倉儲的環境下，為了讓單一資料表可以同時接受多個資料表來源的掘轉（ETL），也同時避免資料來源中的資料記錄（records）被刪除後的影響， 大部份的table scheme結構的規劃者不採用Material View，而選擇資料表做為資料掘轉之後，儲存資料的地方。 但這樣子的做法，在資料掘轉過程中，最後兩個步驟–修改（update）與新增（insert），會因為Full Table Scan的次數增加，而造成效能上的影響。為了減少Full Table Scan 的次數，也避免多資料來源的衝突，與資料刪除後的影響，Oracle提出Merge語法以解決這些問題。 Merge語法簡介 Merge是ORACLE資料庫在版本9i以後所提出來的SQL語法，讓使用者可以利用Merge，在一次執行中，同時對資料庫進行修改與新增。 一方面，方便資料庫管理師PL/SQL的撰寫（過去，如果要達到相同的效果，即要利用cursor宣告與控制），另一面就系統效能考量而言，可以減少 Full Table Scan的次數。 簡單的語法結構如 圖一所 示，其中，「ON」字句後面所帶的「條件」即是我們對兩個資料表的JOIN時所設的條件，可做為Table A 與Table B兩個資料表比對的基準，對於Table A與Table B中可以JOIN的資料，表示這些資料已經同時存在Table A與Table B中，此時資料庫即在「WHEN MATCHED THEN UPDATE SET」字句後，依Table B中該筆記錄的值，修改Table A中的記錄。 相反的，Table B如果無法經由「ON」字句後的條件與Table A產生JOIN的結果時，即代表該筆資料只存在於Table B，並不存在於Table A。此時，即利用「WHEN NOT MATCHED THEN INSERT」字句後所帶出的Insert字句進行新增的動作。 如圖二所 示，ORDER資料表是企業銷售部門的訂單資料，隨著企業營運的成長，該資料表中的產品隨之增加，為了資料分析的需求，且不希望過多歷史的資料影響系統的 正常運作，因此每隔一段時間，我們可以利用Merge語法將ORDER的資料轉入資料倉儲中的ORDER_HISTORY資料表。 其中，有吻合（Matched）的資料（表示該筆資料已經存在），資料庫系統即以藍色「UPDATE」字句的字串修改該筆資料；而沒有吻合（Not Matched）的資料（表示該筆資料尚未存在），資料庫系統則以藍色「INSERT」所帶出來的字句新增該筆資料。 不過，特別要說明的是，對於已經存在的資料，ID欄位是沒有必要改變的，因此在UPDATE字句則不需要修改該欄位；反之，在INSERT字句中，我們就可以看到紅色字的部份，ID欄位是必須被新增的。 Merge語法的基本應用 再擴大上述的範例，如果企業有多個營運據點（Region），再加上早期網路不發達，這些據點擁有各自獨立的系統與資料庫，為了讓總公司可以全面性地分析資料，因此，每個月底各據點完成本身的結帳後，該企業會趁凌晨系統閒置的時間，利用Merge語法進行資料的集中。 由於據點的資料庫是獨立的， ORDER資料表中的ID欄位，理論上會有重複情況產生，為了避免這個問題，可以利用同一個據點中ID欄位的值不會重複的特性，將語法可以改寫成 圖三。我們可以看到利用「AND」字句，「ON」字句後面的條件可以不只有一個。但是在「ON」字句後面出現的條件字句中的欄位，都不可在「UPDATE」字句中被更新。 Merge的進階應用 由於Merge具同時新增與修改的功能，因此除了資料轉換的用途外，它其實也讓SQL語法的使用更為靈活，我們可以更經易地將某個系統運作的商業邏輯套用在Merge語法上實做出決策者的需求。 例如某公司每個月月底會檢視所有業務同仁的業績，對於業績毛利有達到業務本薪三倍的員工，則提撥該員工當月毛利的20%做為獎金。不過，獎金是累計到公司年度結束才發放。為了在年度中，可以隨時檢視對所有業務同仁的累積獎金，我們規劃如表格一的BONUSES資料表。 由於，已經有累計獎金的員工才會被記錄這個資料表中，所以並不是所有的業務員工都會出現在這個資料表上。在程式邏輯上，每月底獎金提撥時，已經有 累計獎金的員工（資料已經存放在這個資料表中），系統即修改（UPDATE）BONUSES資料表的BONUS欄位；而尚末有累計獎金的員工，系統即新增 （INSERT）一筆資料。這樣的邏輯即說明Merge語法中，修改與新增同時發生的情況。 如此，我們就直接利用Merge，將商業邏輯上會造成同時要修改與新增的需求，用一個簡單的SQL語法完成。當然，針對上述的需求，我們可以再配合著ORACLE JOB的撰寫，讓Merge的動作，在每個月底自動執行。 Merge語法是Oracle在資料庫9i版時，為了資料倉儲資料表融合（Merge）的需求所提出來的新功能，在資料表掘轉上可以避免Full Table Scan的次數。在商業邏輯的應用上，提供資料庫管理師及程式設計師更靈活的應用。 作者╱杜奕鋒 艾群科技技術顧問，崑山大學講師。國立中央大學畢業，專精於商業流程設計、資料庫管理、T-SQL 及軟體整合。通過 OCP(8i/9i/10g)、RHCE、SCSA、SCNA、SCJP 等認證。 曾任職於 104 人力銀行，負責 eHRPortal 及 WorkFlow 系統整合；中華民國紡拓會，負責紡織業 ERP 系統導入；亦曾擔任臺灣護理學會系統導入顧問；並曾授課於關渡基督書院、永達技術學院。

Merge語法的結構。


ORDER資料表是企業銷售部門的訂單資料，利用Merge語法可將ORDER的資料轉入資料倉儲中的ORDER_HISTORY資料表。已經存在的資 料，資料庫系統即以藍色「UPDATE」字句的字串修改該筆資料；尚未存在的資料，則以藍色「INSERT」所帶出來的字句新增該筆資料。


若各營業據點的ID欄位有可能重複的問題，可以利用同一營運據點ID欄位的值不會重複的特性，改寫語法利用「AND」字句的連接，「ON」字句後面的條件可以不只有一個。

--
萬歲！您的郵件已被捨棄。一封垃圾郵件也沒有！

--
萬歲！您的郵件已被捨棄。一封垃圾郵件也沒有！