如何从优化sql入手提高数据仓库的etl效率[以oracle为例]

《如何从优化sql入手提高数据仓库的etl效率[以oracle为例]》由会员上传分享，免费在线阅读，更多相关内容在工程资料-天天文库。

1、1引言数据仓库建设中的ETL(Extract,Transform,Load)是数据抽取、转换和装载到模型的过程，整个过程基本是通过控制用SQL语句编写的存储过程和函数的方式来实现对数据的直接操作，SQL语句的效率将直接影响到数据仓库后台的性能。目前，国内的大中型企业基本都具有四年以上计算机信息系统应用经验，积累了大量可分析的业务数据，这些信息系统中的数据需要通过搭建数据仓库平台才能得到科学的分析，这也是近几年数据仓库系统建设成为IT领域热门话题的原因。2优化的思路分析数据仓库ETL过程的主要特点是：面对海量的数据进行抽取；分时段对大批量数据进行删除、更新和插入操作；面对异常的数据进行规则化的

2、清洗；大量的分析模型重算工作；有特定的过程处理时间规律性，一般整个ETL过程需要在每天的零点开始到6点之前完成。所以，针对ETL过程的优化主要是结合数据仓库自身的特点，抓住需要优化的主要方面，针对不同的情况从如何采用高效的SQL入手来进行。3优化的实例分析目前数据仓库建设中的后台数据库大部分采用Oracle，以下的SQL采用Oracle的语法来说明，所有的测试在Oracle9i环境中通过，但其优化的方法和原理同样适合除Oracle之外的其他数据库。3.1索引的正确使用在海量数据表中，基本每个表都有一个或多个的索引来保证高效的查询，在ETL过程中的索引需要遵循以下使用原则：(1)当插入的数据为

3、数据表中的记录数量10%以上时,首先需要删除该表的索引来提高数据的插入效率，当数据全部插入后再建立索引。(2)避免在索引列上使用函数或计算，在WHERE子句中，如果索引列是函数的一部分，优化器将不使用索引而使用全表扫描。举例:低效：SELECT*ROMDEPTWHERESAL*12>25000;高效：SELECT*FROMDEPTWHERESAL>25000/12;低效：select*fromtable1wherename='zhangsan'andtID>10000高效：select*fromtable1wheretID>10000andname='zhangsan'如果tID是一个聚合索

4、引，那么后一句仅仅从表的10000条以后的记录中查找就行了；而前一句则要先从全表中查找看有几个name='zhangsan'的，而后再根据限制条件条件tID>10000来提出查询结果。(3)避免在索引列上使用NOT和”!=”或<>，索引只能告诉什么存在于表中，而不能告诉什么不存在于表中，当数据库遇到NOT和”!=”时，就会停止使用索引转而执行全表扫描。(4)索引列上用>=替代>高效：SELECT*FROMEMPWHEREDEPTNO>=4低效：SELECT*FROMEMPWHEREDEPTNO>3两者的区别在于，前者DBMS将直接跳到第一个DEPT等于4的记录而后者将首先定位到DEPTNO=

5、3的记录并且向前扫描到第一个DEPT大于3的记录。(5)函数的列启用索引方法，如果一定要对使用函数的列启用索引，Oracle9i以上版本新的功能：基于函数的索引(Function-BasedIndex)是一个较好的方案，但该类型索引的缺点是只能针对某个函数来建立和使用该函数。CREATEINDEXEMP_IONEMP(UPPER(ENAME));/*建立基于函数的索引*/SELECT*FROMEMPWHEREUPPER(ENAME)=‘BLACKSNAIL’;/*将使用索引*/3.2游标的正确使用当在海量数据表中进行数据的删除、更新和插入操作时，用游标处理的效率是最慢的方式，但它在ETL过程

6、中的使用又必不可少，而且使用有着及其重要的地位，所以游标的正确使用尤为重要。对数据仓库维表的数据进行维护时，因为需要保证维表ID的一致性，所以采用游标的是数据维护完整性的最好方式。由于它的效率低，如果按照普通的方式将无法处理大数据量的维表数据维护（一般是指10万条记录以上的维表），以下是处理这种情况的有效方式：(1)在数据抽取的源表中使用时间戳，这样每天的维表数据维护只针对更新日期为最新时间的数据来进行，大大减少需要维护的数据记录数。(2)在INSERT和UPDATE维表时都加上一个条件来过滤维表中已经存在的记录，实例为：INSERTINTODIM_CUSTOMERSELECT*FROMOD

7、S_CUSTOMERWHEREODS_CUSTOMER.CODENOTEXISTS(DIM_CUSTOMER.CODE)/*ODS_CUSTOMER为数据源表；DIM_CUSTOMER为维表*/(3)使用显式的游标(CURSORs)，因为使用隐式的游标将会执行两次操作，第一次检索记录，第二次检查TOOMANYROWS这个EXCEPTION，而显式游标不执行第二次操作。3.3数据抽取和上载时的SQL性能优化3.