ETL過程原理和數(shù)據(jù)倉庫建設的優(yōu)化
引言
數(shù)據(jù)倉庫建設中的ETL(Extract, Transform, Load)是數(shù)據(jù)抽取、轉換和裝載到模型的過程，整個過程基本是通過控制用SQL語句編寫的存儲過程和函數(shù)的方式來實現(xiàn)對數(shù)據(jù)的直接操作，SQL語句的效率將直接影響到數(shù)據(jù)倉庫后臺的性能。
目前，國內(nèi)的大中型企業(yè)基本都具有四年以上計算機信息系統(tǒng)應用經(jīng)驗，積累了大量可分析的業(yè)務數(shù)據(jù)，這些信息系統(tǒng)中的數(shù)據(jù)需要通過搭建數(shù)據(jù)倉庫平臺才能得到科學的分析，這也是近幾年數(shù)據(jù)倉庫系統(tǒng)建設成為IT領域熱門話題的原因。
優(yōu)化的思路分析
數(shù)據(jù)倉庫ETL過程的主要特點是：面對海量的數(shù)據(jù)進行抽取；分時段對大批量數(shù)據(jù)進行刪除、更新和插入操作；面對異常的數(shù)據(jù)進行規(guī)則化的清洗；大量的分析模型重算工作；有特定的過程處理時間規(guī)律性，一般整個ETL過程需要在每天的零點開始到6點之前完成。所以，針對ETL過程的優(yōu)化主要是結合數(shù)據(jù)倉庫自身的特點，抓住需要優(yōu)化的主要方面，針對不同的情況從如何采用高效的SQL入手來進行。
優(yōu)化的實例分析
目前數(shù)據(jù)倉庫建設中的后臺數(shù)據(jù)庫大部分采用Oracle，以下的SQL采用Oracle的語法來說明，所有的測試在Oracle9i環(huán)境中通過，但其優(yōu)化的方法和原理同樣適合除Oracle之外的其他數(shù)據(jù)庫。
1.索引的正確使用
在海量數(shù)據(jù)表中，基本每個表都有一個或多個的索引來保證高效的查詢，在ETL過程中的索引需要遵循以下使用原則：
a.當插入的數(shù)據(jù)為數(shù)據(jù)表中的記錄數(shù)量10%以上時, 首先需要刪除該表的索引來提高數(shù)據(jù)的插入效率，當數(shù)據(jù)全部插入后再建立索引。
b.避免在索引列上使用函數(shù)或計算，在WHERE子句中，如果索引列是函數(shù)的一部分，優(yōu)化器將不使用索引而使用全表掃描。舉例:
低效： SELECT * ROM DEPT WHERE SAL * 12 > 25000;
高效： SELECT * FROM DEPT WHERE SAL > 25000/12;
c.避免在索引列上使用NOT和”!=” ， 索引只能告訴什么存在于表中，而不能告訴什么不存在于表中，當數(shù)據(jù)庫遇到NOT和”!=”時，就會停止使用索引轉而執(zhí)行全表掃描。
d.索引列上用>=替代>
高效： SELECT * FROM EMP WHERE DEPTNO >=4
低效： SELECT * FROM EMP WHERE DEPTNO >3
兩者的區(qū)別在于，前者DBMS將直接跳到第一個DEPT等于4的記錄而后者將首先定位到DEPTNO=3的記錄并且向前掃描到第一個DEPT大于3的記錄。
e.函數(shù)的列啟用索引方法，如果一定要對使用函數(shù)的列啟用索引，Oracle9i以上版本新的功能：基于函數(shù)的索引(Function-Based Index)是一個較好的方案，但該類型索引的缺點是只能針對某個函數(shù)來建立和使用該函數(shù)。
CREATE INDEX EMP_I ON EMP (UPPER( ENAME));
SELECT * FROM EMP WHERE UPPER(ENAME) = ‘BLACKSNAIL’;
2.游標的正確使用
當在海量數(shù)據(jù)表中進行數(shù)據(jù)的刪除、更新和插入操作時，用游標處理的效率是最慢的方式，但它在ETL過程中的使用又必不可少，而且使用有著及其重要的地位，所以游標的正確使用尤為重要。
對數(shù)據(jù)倉庫維表的數(shù)據(jù)進行維護時，因為需要保證維表ID的一致性，所以采用游標的是數(shù)據(jù)維護完整性的最好方式。由于它的效率低，如果按照普通的方式將無法處理大數(shù)據(jù)量的維表數(shù)據(jù)維護（一般是指10萬條記錄以上的維表），以下是處理這種情況的有效方式：
a.在數(shù)據(jù)抽取的源表中使用時間戳，這樣每天的維表數(shù)據(jù)維護只針對更新日期為最新時間的數(shù)據(jù)來進行，大大減少需要維護的數(shù)據(jù)記錄數(shù)。
b.在INSERT和UPDATE維表時都加上一個條件來過濾維表中已經(jīng)存在的記錄，實例為：
INSERT INTO DIM_CUSTOMER SELECT * FROM ODS_CUSTOMER WHERE ODS_CUSTOMER.CODE NOT EXISTS (DIM_CUSTOMER.CODE)
c.使用顯式的游標(CURSORs) ，因為使用隱式的游標將會執(zhí)行兩次操作，第一次檢索記錄，第二次檢查TOO MANY ROWS 這個EXCEPTION，而顯式游標不執(zhí)行第二次操作。
#p#
3.數(shù)據(jù)抽取和上載時的SQL優(yōu)化
a.WHERE子句中的連接順序
ORACLE采用自下而上的順序解析WHERE子句，根據(jù)這個原理，表之間的連接必須寫在其它WHERE條件之前，那些可以過濾掉最大數(shù)量記錄的條件必須寫在WHERE子句的末尾。
低效：SELECT * FROM EMP E WHERE SAL > 50000 AND JOB = ‘MANAGER’ AND 25 <(SELECT COUNT(*) FROM EMP WHERE MGR=E.EMPNO);
高效：SELECT * FROM EMP E WHERE 25 <(SELECT COUNT(*) FROM EMP WHERE MGR=E.EMPNO) AND SAL > 50000 AND JOB = ‘MANAGER’;
b.刪除全表時用TRUNCATE替代DELETE
當DELETE刪除表中的記錄時，有回滾段(rollback segments ) 用來存放可以被恢復的信息，而當運用TRUNCATE時，回滾段不再存放任何可被恢復的信息，所以執(zhí)行時間也會很短。同時需要注意TRUNCATE只在刪除全表時適用，因為TRUNCATE是DDL而不是DML。
c.盡量多使用COMMIT
ETL中同一個過程的數(shù)據(jù)操作步驟很多，數(shù)據(jù)倉庫采用的是數(shù)據(jù)抽取后分析模型重算的原理，所以對數(shù)據(jù)的COMMIT不像業(yè)務系統(tǒng)為保證數(shù)據(jù)的完整和一致性而需要某個操作過程全部完成才能進行，只要有可能就在程序中對每個DELETE、INSERT和UPDATE操作盡量多使用COMMIT, 這樣系統(tǒng)性能會因為COMMIT所釋放的資源而大大提高。
d.用EXISTS替代IN
在許多基于基礎表的查詢中，為了滿足一個條件往往需要對另一個表進行聯(lián)接，例如在ETL過程寫數(shù)據(jù)到模型時經(jīng)常需要關聯(lián)10個左右的維表，在這種情況下，使用EXISTS而不用IN將提高查詢的效率。
e.用NOT EXISTS替代NOT IN
子查詢中，NOT IN子句將執(zhí)行一個內(nèi)部的排序和合并，無論在哪種情況下，NOT IN都是最低效的，因為它對子查詢中的表執(zhí)行了一個全表遍歷。用NOT EXISTS替代NOT IN將提高查詢的效率。
f.優(yōu)化GROUP BY
提高GROUP BY 語句的效率，可以通過將不需要的記錄在GROUP BY 之前過濾掉。
低效： SELECT JOB , AVG(SAL) FROM EMP GROUP BY JOB HAVING JOB = ‘PRESIDENT’ OR JOB = ‘MANAGER’
高效： SELECT JOB , AVG(SAL) FROM EMP WHERE JOB = ‘PRESIDENT’ OR JOB = ‘MANAGER’ GROUP BY JOB
g.有條件的使用UNION-ALL 替換UNION
ETL過程針對多表連接操作的情況很多，有條件的使用UNION-ALL 替換UNION的前提是：所連接的各個表中無主關鍵字相同的記錄，因為UNION ALL 將重復輸出兩個結果集合中相同記錄。
當SQL語句需要UNION兩個查詢結果集合時，這兩個結果集合會以UNION-ALL的方式被合并，然后在輸出最終結果前進行排序。如果用UNION ALL替代UNION，這樣排序就不是必要了，效率就會因此得到提高3-5倍
h.分離表和索引
總是將你的表和索引建立在不同的表空間內(nèi)，決不要將不屬于ORACLE內(nèi)部系統(tǒng)的對象存放到SYSTEM表空間里。同時確保數(shù)據(jù)表空間和索引表空間置與不同的硬盤控制卡控制的硬盤上
【編輯推薦】
