引用：http://tech./art/1105/20080611/1471903_1.html

這篇論壇文章（賽迪網(wǎng)技術(shù)社區(qū)）根據(jù)筆者多年的數(shù)據(jù)倉(cāng)庫(kù)實(shí)施經(jīng)驗(yàn)，同時(shí)結(jié)合ETL的過(guò)程原理和數(shù)據(jù)倉(cāng)庫(kù)建設(shè)方法歸納總結(jié)了以下優(yōu)化的方案，詳細(xì)內(nèi)容請(qǐng)讀者參考下文：

1.引言

數(shù)據(jù)倉(cāng)庫(kù)建設(shè)中的ETL(Extract, Transform, Load)是數(shù)據(jù)抽取、轉(zhuǎn)換和裝載到模型的過(guò)程，整個(gè)過(guò)程基本是通過(guò)控制用SQL語(yǔ)句編寫的存儲(chǔ)過(guò)程和函數(shù)的方式來(lái)實(shí)現(xiàn)對(duì)數(shù)據(jù)的直接操作，SQL語(yǔ)句的效率將直接影響到數(shù)據(jù)倉(cāng)庫(kù)后臺(tái)的性能。

目前，國(guó)內(nèi)的大中型企業(yè)基本都具有四年以上計(jì)算機(jī)信息系統(tǒng)應(yīng)用經(jīng)驗(yàn)，積累了大量可分析的業(yè)務(wù)數(shù)據(jù)，這些信息系統(tǒng)中的數(shù)據(jù)需要通過(guò)搭建數(shù)據(jù)倉(cāng)庫(kù)平臺(tái)才能得到科學(xué)的分析，這也是近幾年數(shù)據(jù)倉(cāng)庫(kù)系統(tǒng)建設(shè)成為IT領(lǐng)域熱門話題的原因。

2.優(yōu)化的思路分析

數(shù)據(jù)倉(cāng)庫(kù)ETL過(guò)程的主要特點(diǎn)是：面對(duì)海量的數(shù)據(jù)進(jìn)行抽??；分時(shí)段對(duì)大批量數(shù)據(jù)進(jìn)行刪除、更新和插入操作；面對(duì)異常的數(shù)據(jù)進(jìn)行規(guī)則化的清洗；大量的分析模型重算工作；有特定的過(guò)程處理時(shí)間規(guī)律性，一般整個(gè)ETL過(guò)程需要在每天的零點(diǎn)開始到6點(diǎn)之前完成。所以，針對(duì)ETL過(guò)程的優(yōu)化主要是結(jié)合數(shù)據(jù)倉(cāng)庫(kù)自身的特點(diǎn)，抓住需要優(yōu)化的主要方面，針對(duì)不同的情況從如何采用高效的SQL入手來(lái)進(jìn)行。

優(yōu)化的實(shí)例分析

目前數(shù)據(jù)倉(cāng)庫(kù)建設(shè)中的后臺(tái)數(shù)據(jù)庫(kù)大部分采用Oracle，以下的SQL采用Oracle的語(yǔ)法來(lái)說(shuō)明，所有的測(cè)試在Oracle9i環(huán)境中通過(guò)，但其優(yōu)化的方法和原理同樣適合除Oracle之外的其他數(shù)據(jù)庫(kù)。

3.1索引的正確使用

在海量數(shù)據(jù)表中，基本每個(gè)表都有一個(gè)或多個(gè)的索引來(lái)保證高效的查詢，在ETL過(guò)程中的索引需要遵循以下使用原則：

(1) 當(dāng)插入的數(shù)據(jù)為數(shù)據(jù)表中的記錄數(shù)量10%以上時(shí), 首先需要?jiǎng)h除該表的索引來(lái)提高數(shù)據(jù)的插入效率，當(dāng)數(shù)據(jù)全部插入后再建立索引。

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

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

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

(3) 避免在索引列上使用NOT和”!=” ， 索引只能告訴什么存在于表中，而不能告訴什么不存在于表中，當(dāng)數(shù)據(jù)庫(kù)遇到NOT和”!=”時(shí)，就會(huì)停止使用索引轉(zhuǎn)而執(zhí)行全表掃描。

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

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

低效： SELECT * FROM EMP WHERE DEPTNO >3

兩者的區(qū)別在于，前者DBMS將直接跳到第一個(gè)DEPT等于4的記錄而后者將首先定位到DEPTNO=3的記錄并且向前掃描到第一個(gè)DEPT大于3的記錄。

(5) 函數(shù)的列啟用索引方法，如果一定要對(duì)使用函數(shù)的列啟用索引，Oracle9i以上版本新的功能：基于函數(shù)的索引(Function-Based Index)是一個(gè)較好的方案，但該類型索引的缺點(diǎn)是只能針對(duì)某個(gè)函數(shù)來(lái)建立和使用該函數(shù)。

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

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

3.2 游標(biāo)的正確使用

當(dāng)在海量數(shù)據(jù)表中進(jìn)行數(shù)據(jù)的刪除、更新和插入操作時(shí)，用游標(biāo)處理的效率是最慢的方式，但它在ETL過(guò)程中的使用又必不可少，而且使用有著及其重要的地位，所以游標(biāo)的正確使用尤為重要。

對(duì)數(shù)據(jù)倉(cāng)庫(kù)維表的數(shù)據(jù)進(jìn)行維護(hù)時(shí)，因?yàn)樾枰ＷC維表ID的一致性，所以采用游標(biāo)的是數(shù)據(jù)維護(hù)完整性的最好方式。由于它的效率低，如果按照普通的方式將無(wú)法處理大數(shù)據(jù)量的維表數(shù)據(jù)維護(hù)（一般是指10萬(wàn)條記錄以上的維表），以下是處理這種情況的有效方式：

(1) 在數(shù)據(jù)抽取的源表中使用時(shí)間戳，這樣每天的維表數(shù)據(jù)維護(hù)只針對(duì)更新日期為最新時(shí)間的數(shù)據(jù)來(lái)進(jìn)行，大大減少需要維護(hù)的數(shù)據(jù)記錄數(shù)。

(2) 在INSERT和UPDATE維表時(shí)都加上一個(gè)條件來(lái)過(guò)濾維表中已經(jīng)存在的記錄，實(shí)例為：

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

(3) 使用顯式的游標(biāo)(CURSORs) ，因?yàn)槭褂秒[式的游標(biāo)將會(huì)執(zhí)行兩次操作，第一次檢索記錄，第二次檢查TOO MANY ROWS 這個(gè)EXCEPTION，而顯式游標(biāo)不執(zhí)行第二次操作。

3.3數(shù)據(jù)抽取和上載時(shí)的SQL優(yōu)化

◆3.3.1 WHERE子句中的連接順序

ORACLE采用自下而上的順序解析WHERE子句，根據(jù)這個(gè)原理，表之間的連接必須寫在其它WHERE條件之前，那些可以過(guò)濾掉最大數(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’;

◆3.3.2 刪除全表時(shí)用TRUNCATE替代DELETE

當(dāng)DELETE刪除表中的記錄時(shí)，有回滾段(rollback segments ) 用來(lái)存放可以被恢復(fù)的信息，而當(dāng)運(yùn)用TRUNCATE時(shí)，回滾段不再存放任何可被恢復(fù)的信息，所以執(zhí)行時(shí)間也會(huì)很短。同時(shí)需要注意TRUNCATE只在刪除全表時(shí)適用，因?yàn)門RUNCATE是DDL而不是DML。

◆3.3.3 盡量多使用COMMIT

ETL中同一個(gè)過(guò)程的數(shù)據(jù)操作步驟很多，數(shù)據(jù)倉(cāng)庫(kù)采用的是數(shù)據(jù)抽取后分析模型重算的原理，所以對(duì)數(shù)據(jù)的COMMIT不像業(yè)務(wù)系統(tǒng)為保證數(shù)據(jù)的完整和一致性而需要某個(gè)操作過(guò)程全部完成才能進(jìn)行，只要有可能就在程序中對(duì)每個(gè)DELETE、INSERT和UPDATE操作盡量多使用COMMIT, 這樣系統(tǒng)性能會(huì)因?yàn)镃OMMIT所釋放的資源而大大提高。

◆3.3.4 用EXISTS替代IN

在許多基于基礎(chǔ)表的查詢中，為了滿足一個(gè)條件往往需要對(duì)另一個(gè)表進(jìn)行聯(lián)接，例如在ETL過(guò)程寫數(shù)據(jù)到模型時(shí)經(jīng)常需要關(guān)聯(lián)10個(gè)左右的維表，在這種情況下，使用EXISTS而不用IN將提高查詢的效率。

◆3.3.5 用NOT EXISTS替代NOT IN

子查詢中，NOT IN子句將執(zhí)行一個(gè)內(nèi)部的排序和合并，無(wú)論在哪種情況下，NOT IN都是最低效的，因?yàn)樗鼘?duì)子查詢中的表執(zhí)行了一個(gè)全表遍歷。用NOT EXISTS替代NOT IN將提高查詢的效率。

◆3.3.6 優(yōu)化GROUP BY

提高GROUP BY 語(yǔ)句的效率，可以通過(guò)將不需要的記錄在GROUP BY 之前過(guò)濾掉。

低效： 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過(guò)程針對(duì)多表連接操作的情況很多，有條件的使用UNION-ALL 替換UNION的前提是：所連接的各個(gè)表中無(wú)主關(guān)鍵字相同的記錄，因?yàn)閁NION ALL 將重復(fù)輸出兩個(gè)結(jié)果集合中相同記錄。

當(dāng)SQL語(yǔ)句需要UNION兩個(gè)查詢結(jié)果集合時(shí)，這兩個(gè)結(jié)果集合會(huì)以UNION-ALL的方式被合并，然后在輸出最終結(jié)果前進(jìn)行排序。如果用UNION ALL替代UNION，這樣排序就不是必要了，效率就會(huì)因此得到提高3-5倍

◆ 3.3.8 分離表和索引

總是將你的表和索引建立在不同的表空間內(nèi)，決不要將不屬于ORACLE內(nèi)部系統(tǒng)的對(duì)象存放到SYSTEM表空間里。同時(shí)確保數(shù)據(jù)表空間和索引表空間置與不同的硬盤控制卡控制的硬盤上。