优化专项方案文档.doc

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

这篇论坛文章（赛迪网技术小区）依据笔者多年数据仓库实施经验，同时结合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*ROMDEPTWHERESAL*1225000;

高效：SELECT*FROMDEPTWHERESAL25000/12;

(3)避免在索引列上使用NOT和”!=”，索引只能告诉什么存在于表中，而不能告诉什么不存在于表中，当数据库碰到NOT和”!=”时，就会停止使用索引转而实施全表扫描。

(4)索引列上用=替换

高效：SELECT*FROMEMPWHEREDEPTNO=4

低效：SELECT*FROMEMPWHEREDEPTNO3

二者区分在于，前者DBMS将直接跳到第一个DEPT等于4统计以后者将首先定位到DEPTNO=3统计而且向前扫描到第一个DEPT大于3统计。

(5)函数列启用索引方法，假如一定要对使用函数列启用索引，Oracle9i以上版本新功效：基于函数索引(Function-BasedIndex)是一个很好方案，但该类型索引缺点是只能针对某个函数来建立和使用该函数。

CREATEINDEXEMP_IONEMP(UPPER(ENAME));

SELECT*FROMEMPWHEREUPPER(ENAME)=‘BLACKSNAIL’;

3.2游标正确使用

当在海量数据表中进行数据删除、更新和插入操作时，用游标处理效率是最慢方法，但它在ETL过程中使用又必不可少，而且使用有着及其关键地位，所以游标正确使用尤为关键。

对数据仓库维表数据进行维护时，因为需要确保维表ID一致性，所以采取游标是数据维护完整性最好方法。因为它效率低，假如根据一般方法将无法处理大数据量维表数据维护（通常是指10万条统计以上维表），以下是处理这种情况有效方法：

(1)在数据抽取源表中使用时间戳，这么天天维表数据维护只针对更新日期为必威体育精装版时间数据来进行，大大降低需要维护数据统计数。

(2)在INSERT和UPDATE维表时全部加上一个条件来过滤维表中已经存在统计，实例为：

INSERTINTODIM_CUSTOMERSELECT*FROMODS_CUSTOMERWHEREODS_CUSTOMER.CODENOTEXISTS(DIM_CUSTOMER.CODE)

(3)使用显式游标(CURSORs)，因为使用隐式游标将会实施两次操作，第一次检索统计，第二次检验TOOMANYROWS这个EXCEPTION，而显式游标不实施第二次操作。

3.3数据抽取和上载时SQL优化

◆3.3.1WHERE子句中连接次序

ORACLE采取自下而上次序解析WHERE子句，依据这个原理，表之间连接必需写在其它WHERE条件之前，那些能够过滤掉最大数量统计条件必需写在WHERE子句末尾。

低效：SELECT*FROMEMPEWHERESAL50000