引言
数据仓库建设中的ETL(Extract Transform Load)是数据抽取转换和装载到模型的过程整个过程基本是通过控制用SQL语句编写的存储过程和函数的方式来实现对数据的直接操作SQL语句的效率将直接影响到数据仓库后台的性能
目前国内的大中型企业基本都具有四年以上计算机信息系统应用经验积累了大量可分析的业务数据这些信息系统中的数据需要通过搭建数据仓库平台才能得到科学的分析这也是近几年数据仓库系统建设成为IT领域热门话题的原因
优化的思路分析
数据仓库ETL过程的主要特点是面对海量的数据进行抽取分时段对大批量数据进行删除更新和插入操作面对异常的数据进行规则化的清洗大量的分析模型重算工作有特定的过程处理时间规律性一般整个ETL过程需要在每天的零点开始到点之前完成所以针对ETL过程的优化主要是结合数据仓库自身的特点抓住需要优化的主要方面针对不同的情况从如何采用高效的SQL入手来进行
优化的实例分析
目前数据仓库建设中的后台数据库大部分采用Oracle以下的SQL采用Oracle的语法来说明所有的测试在Oraclei环境中通过但其优化的方法和原理同样适合除Oracle之外的其他数据库
索引的正确使用
在海量数据表中基本每个表都有一个或多个的索引来保证高效的查询在ETL过程中的索引需要遵循以下使用原则
() 当插入的数据为数据表中的记录数量%以上时 首先需要删除该表的索引来提高数据的插入效率当数据全部插入后再建立索引
() 避免在索引列上使用函数或计算在WHERE子句中如果索引列是函数的一部分优化器将不使用索引而使用全表扫描举例:
低效 SELECT * ROM DEPT WHERE SAL * > ;
高效 SELECT * FROM DEPT WHERE SAL > /;
() 避免在索引列上使用NOT和!= 索引只能告诉什么存在于表中而不能告诉什么不存在于表中当数据库遇到NOT和!=时就会停止使用索引转而执行全表扫描
() 索引列上用>=替代>
高效 SELECT * FROM EMP WHERE DEPTNO >=
低效 SELECT * FROM EMP WHERE DEPTNO >
两者的区别在于前者DBMS将直接跳到第一个DEPT等于的记录而后者将首先定位到DEPTNO=的记录并且向前扫描到第一个DEPT大于的记录
() 函数的列启用索引方法如果一定要对使用函数的列启用索引Oraclei以上版本新的功能基于函数的索引(FunctionBased Index)是一个较好的方案但该类型索引的缺点是只能针对某个函数来建立和使用该函数
CREATE INDEX EMP_I ON EMP (UPPER( ENAME));
SELECT * FROM EMP WHERE UPPER(ENAME) = BLACKSNAIL;
游标的正确使用
当在海量数据表中进行数据的删除更新和插入操作时用游标处理的效率是最慢的方式但它在ETL过程中的使用又必不可少而且使用有着及其重要的地位所以游标的正确使用尤为重要
对数据仓库维表的数据进行维护时因为需要保证维表ID的一致性所以采用游标的是数据维护完整性的最好方式由于它的效率低如果按照普通的方式将无法处理大数据量的维表数据维护（一般是指万条记录以上的维表）以下是处理这种情况的有效方式
() 在数据抽取的源表中使用时间戳这样每天的维表数据维护只针对更新日期为最新时间的数据来进行大大减少需要维护的数据记录数
() 在INSERT和UPDATE维表时都加上一个条件来过滤维表中已经存在的记录实例为
INSERT INTO DIM_CUSTOMER SELECT * FROM ODS_CUSTOMER WHERE ODS_CUSTOMERCODE NOT EXISTS (DIM_CUSTOMERCODE)
() 使用显式的游标(CURSORs) 因为使用隐式的游标将会执行两次操作第一次检索记录第二次检查TOO MANY ROWS 这个EXCEPTION而显式游标不执行第二次操作
[] []
![ETL的过程原理和数据仓库建设[1]](/UploadFiles/6.jpg)