优化方案：etl的过程原理和数据仓库建设

《优化方案：etl的过程原理和数据仓库建设》由会员上传分享，免费在线阅读，更多相关内容在行业资料-天天文库。

1、优化方案：ETL的过程原理和数据仓库建设这篇论坛文章（赛迪网技术社区）根据笔者多年的数据仓库实施经验，同时结合ETL的过程原理和数据仓库建设方法归纳总结了以下优化的方案，详细内容请读者参考下文：1.引言数据仓库建设中的ETL(Extract,Transform,Load)是数据抽取、转换和装载到模型的过程，整个过程基本是通过控制用SQL语句编写的存储过程和函数的方式来实现对数据的直接操作，SQL语句的效率将直接影响到数据仓库后台的性能。目前，国内的大中型企业基本都具有四年以上计算机信息系统应用经验，积

2、累了大量可分析的业务数据，这些信息系统中的数据需要通过搭建数据仓库平台才能得到科学的分析，这也是近几年数据仓库系统建设成为IT领域热门话题的原因。2.优化的思路分析数据仓库ETL过程的主要特点是：面对海量的数据进行抽取；分时段对大批量数据进行删除、更新和插入操作；面对异常的数据进行规则化的清洗；大量的分析模型重算工作；有特定的过程处理时间规律性，一般整个ETL过程需要在每天的零点开始到6点之前完成。所以，针对ETL过程的优化主要是结合数据仓库自身的特点，抓住需要优化的主要方面，针对不同的情况从如何采用

3、高效的SQL入手来进行。优化的实例分析目前数据仓库建设中的后台数据库大部分采用Oracle，以下的SQL采用Oracle的语法来说明，所有的测试在Oracle9i环境中通过，但其优化的方法和原理同样适合除Oracle之外的其他数据库。3.1索引的正确使用在海量数据表中，基本每个表都有一个或多个的索引来保证高效的查询，在ETL过程中的索引需要遵循以下使用原则：(1)当插入的数据为数据表中的记录数量10%以上时,首先需要删除该表的索引来提高数据的插入效率，当数据全部插入后再建立索引。(2)避免在索引列上使

4、用函数或计算，在WHERE子句中，如果索引列是函数的一部分，优化器将不使用索引而使用全表扫描。举例:低效：SELECT*ROMDEPTWHERESAL*12>25000;高效：SELECT*FROMDEPTWHERESAL>25000/12;(3)避免在索引列上使用NOT和”!=”，索引只能告诉什么存在于表中，而不能告诉什么不存在于表中，当数据库遇到NOT和”!=”时，就会停止使用索引转而执行全表扫描。(4)索引列上用>=替代>高效：SELECT*FROMEMPWHEREDEPTNO>=4低效：SEL

5、ECT*FROMEMPWHEREDEPTNO>3两者的区别在于，前者DBMS将直接跳到第一个DEPT等于4的记录而后者将首先定位到DEPTNO=3的记录并且向前扫描到第一个DEPT大于3的记录。(5)函数的列启用索引方法，如果一定要对使用函数的列启用索引，Oracle9i以上版本新的功能：基于函数的索引(Function-BasedIndex)是一个较好的方案，但该类型索引的缺点是只能针对某个函数来建立和使用该函数。CREATEINDEXEMP_IONEMP(UPPER(ENAME));　　　　　SE

6、LECT*FROMEMPWHEREUPPER(ENAME)=‘BLACKSNAIL’;3.2游标的正确使用当在海量数据表中进行数据的删除、更新和插入操作时，用游标处理的效率是最慢的方式，但它在ETL过程中的使用又必不可少，而且使用有着及其重要的地位，所以游标的正确使用尤为重要。对数据仓库维表的数据进行维护时，因为需要保证维表ID的一致性，所以采用游标的是数据维护完整性的最好方式。由于它的效率低，如果按照普通的方式将无法处理大数据量的维表数据维护（一般是指10万条记录以上的维表），以下是处理这种情况的有

7、效方式：(1)在数据抽取的源表中使用时间戳，这样每天的维表数据维护只针对更新日期为最新时间的数据来进行，大大减少需要维护的数据记录数。(2)在INSERT和UPDATE维表时都加上一个条件来过滤维表中已经存在的记录，实例为：INSERTINTODIM_CUSTOMERSELECT*FROMODS_CUSTOMERWHEREODS_CUSTOMER.CODENOTEXISTS(DIM_CUSTOMER.CODE)(3)使用显式的游标(CURSORs)，因为使用隐式的游标将会执行两次操作，第一次检索记录，

8、第二次检查TOOMANYROWS这个EXCEPTION，而显式游标不执行第二次操作。3.3数据抽取和上载时的SQL优化◆3.3.1WHERE子句中的连接顺序ORACLE采用自下而上的顺序解析WHERE子句，根据这个原理，表之间的连接必须写在其它WHERE条件之前，那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾。低效：SELECT*FROMEMPEWHERESAL>50000ANDJOB=‘MANAGER’AND25<(SELECTCOUNT