優化方案：ETL的過程原理和資料倉儲建設（R2）

轉載地址：http://tech.ccidnet.com/art/1105/20080611/1471903_1.html

【賽迪網－IT技術報導】這篇論壇文章（賽迪網技術社群）根據筆者多年的資料倉儲實施經驗，同時結合ETL的過程原理和資料倉儲建設方法歸納總結了以下優化的方案，詳細內容請讀者參考下文：

1.引言

資料倉儲建設中的ETL(Extract, Transform,
Load)是資料抽取、轉換和裝載到模型的過程，整個過程基本是通過控制用SQL語句編寫的儲存過程和函式的方式來實現對資料的直接操作，SQL語句的效率將直接影響到資料倉儲後臺的效能。

目前，國內的大中型企業基本都具有四年以上計算機資訊系統應用經驗，積累了大量可分析的業務資料，這些資訊系統中的資料需要通過搭建資料倉儲平臺才能得到科學的分析，這也是近幾年資料倉儲系統建設成為IT領域熱門話題的原因。

2.優化的思路分析

資料倉儲ETL過程的主要特點是：面對海量的資料進行抽取；分時段對大批量資料進行刪除、更新和插入操作；面對異常的資料進行規則化的清洗；大量的分析模型重算工作；有特定的過程處理時間規律性，一般整個ETL過程需要在每天的零點開始到6點之前完成。所以，針對ETL過程的優化主要是結合資料倉儲自身的特點，抓住需要優化的主要方面，針對不同的情況從如何採用高效的SQL入手來進行。

優化的例項分析

目前資料倉儲建設中的後臺資料庫大部分採用Oracle，以下的SQL採用Oracle的語法來說明，所有的測試在Oracle9i環境中通過，但其優化的方法和原理同樣適合除Oracle之外的其他資料庫。

3.1索引的正確使用

在海量資料表中，基本每個表都有一個或多個的索引來保證高效的查詢，在ETL過程中的索引需要遵循以下使用原則：

(1) 當插入的資料為資料表中的記錄數量10%以上時,
首先需要刪除該表的索引來提高資料的插入效率，當資料全部插入後再建立索引。

(2)
避免在索引列上使用函式或計算，在WHERE子句中，如果索引列是函式的一部分，優化器將不使用索引而使用全表掃描。舉例:

低效： SELECT * ROM DEPT WHERE SAL * 12 > 25000;

高效： SELECT * FROM DEPT WHERE SAL > 25000/12;

(3) 避免在索引列上使用NOT和”!=” ，
索引只能告訴什麼存在於表中，而不能告訴什麼不存在於表中，當資料庫遇到NOT和”!=”時，就會停止使用索引轉而執行全表掃描。

(4) 索引列上用>=替代>

高效： SELECT * FROM EMP WHERE DEPTNO >=4

低效： SELECT * FROM EMP WHERE DEPTNO >3

兩者的區別在於，前者DBMS將直接跳到第一個DEPT等於4的記錄而後者將首先定位到DEPTNO=3的記錄並且向前掃描到第一個DEPT大於3的記錄。

(5)
函式的列啟用索引方法，如果一定要對使用函式的列啟用索引，Oracle9i以上版本新的功能：基於函式的索引(Function-Based
Index)是一個較好的方案，但該型別索引的缺點是隻能針對某個函式來建立和使用該函式。

CREATE INDEX EMP_I ON EMP (UPPER( ENAME)); 　　　　 　

SELECT * FROM EMP WHERE UPPER(ENAME) = ‘BLACKSNAIL’;

3.2 遊標的正確使用

當在海量資料表中進行資料的刪除、更新和插入操作時，用遊標處理的效率是最慢的方式，但它在ETL過程中的使用又必不可少，而且使用有著及其重要的地位，所以遊標的正確使用尤為重要。

對資料倉儲維表的資料進行維護時，因為需要保證維表ID的一致性，所以採用遊標的是資料維護完整性的最好方式。由於它的效率低，如果按照普通的方式將無法處理大資料量的維表資料維護（一般是指10萬條記錄以上的維表），以下是處理這種情況的有效方式：

(1)
在資料抽取的源表中使用時間戳，這樣每天的維表資料維護只針對更新日期為最新時間的資料來進行，大大減少需要維護的資料記錄數。

(2) 在INSERT和UPDATE維表時都加上一個條件來過濾維表中已經存在的記錄，例項為：

INSERT INTO DIM_CUSTOMER SELECT * FROM ODS_CUSTOMER
WHERE ODS_CUSTOMER.CODE NOT EXISTS (DIM_CUSTOMER.CODE)

(3) 使用顯式的遊標(CURSORs)
，因為使用隱式的遊標將會執行兩次操作，第一次檢索記錄，第二次檢查TOO MANY ROWS 這個EXCEPTION，而顯式遊標不執行第二次操作。

3.3資料抽取和上載時的SQL優化

◆3.3.1 WHERE子句中的連線順序

ORACLE採用自下而上的順序解析WHERE子句，根據這個原理，表之間的連線必須寫在其它WHERE條件之前，那些可以過濾掉最大數量記錄的條件必須寫在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’;

◆3.3.2 刪除全表時用TRUNCATE替代DELETE

當DELETE刪除表中的記錄時，有回滾段(rollback segments )
用來存放可以被恢復的資訊，而當運用TRUNCATE時，回滾段不再存放任何可被恢復的資訊，所以執行時間也會很短。同時需要注意TRUNCATE只在刪除全表時適用，因為TRUNCATE是DDL而不是DML。

◆3.3.3 儘量多使用COMMIT

ETL中同一個過程的資料操作步驟很多，資料倉儲採用的是資料抽取後分析模型重算的原理，所以對資料的COMMIT不像業務系統為保證資料的完整和一致性而需要某個操作過程全部完成才能進行，只要有可能就在程式中對每個DELETE、INSERT和UPDATE操作儘量多使用COMMIT,
這樣系統效能會因為COMMIT所釋放的資源而大大提高。

◆3.3.4 用EXISTS替代IN

在許多基於基礎表的查詢中，為了滿足一個條件往往需要對另一個表進行聯接，例如在ETL過程寫資料到模型時經常需要關聯10個左右的維表，在這種情況下，使用EXISTS而不用IN將提高查詢的效率。

◆3.3.5 用NOT EXISTS替代NOT IN

子查詢中，NOT IN子句將執行一個內部的排序和合並，無論在哪種情況下，NOT
IN都是最低效的，因為它對子查詢中的表執行了一個全表遍歷。用NOT EXISTS替代NOT IN將提高查詢的效率。

◆3.3.6 優化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

◆3.3.7 有條件的使用UNION-ALL 替換UNION

ETL過程針對多表連線操作的情況很多，有條件的使用UNION-ALL
替換UNION的前提是：所連線的各個表中無主關鍵字相同的記錄，因為UNION ALL 將重複輸出兩個結果集合中相同記錄。

當SQL語句需要UNION兩個查詢結果集合時，這兩個結果集合會以UNION-ALL的方式被合併，然後在輸出最終結果前進行排序。如果用UNION
ALL替代UNION，這樣排序就不是必要了，效率就會因此得到提高3-5倍

◆ 3.3.8 分離表和索引

總是將你的表和索引建立在不同的表空間內，決不要將不屬於ORACLE內部系統的物件存放到SYSTEM表空間裡。同時確保資料表空間和索引表空間置與不同的硬碟控制卡控制的硬碟上。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26613085/viewspace-1126829/，如需轉載，請註明出處，否則將追究法律責任。