《优化方案：ETL的过程原理和数据仓库建设.docx》由会员分享，可在线阅读，更多相关《优化方案：ETL的过程原理和数据仓库建设.docx（4页珍藏版）》请在三一文库上搜索。
1、优化方案： ETL 的过程原理和数据仓库建设这篇论坛文章根据笔者多年的数据仓库实施经验，同时结合 ETL 的过程原理和数据仓库建设方法归纳总结了以下优化的方案，详细内容请读者参考下文：1. 引言数据仓库建设中的 ETL(Extract, Transform, Load) 是数据抽取、转换和装载到模型 的过程，整个过程基本是通过控制用 SQL 语句编写的存储过程和函数的方式来实现对数据 的直接操作， SQL 语句的效率将直接影响到数据仓库后台的性能。目前，国内的大中型企业基本都具有四年以上计算机信息系统应用经验， 积累了大量可 分析的业务数据，这些信息系统中的数据需要通过搭建数据仓库平台才能得到
2、科学的分析， 这也是近几年数据仓库系统建设成为 IT 领域热门话题的原因。2. 优化的思路分析数据仓库 ETL 过程的主要特点是：面对海量的数据进行抽取；分时段对大批量数据进 行删除、更新和插入操作；面对异常的数据进行规则化的清洗；大量的分析模型重算工作； 有特定的过程处理时间规律性， 一般整个 ETL 过程需要在每天的零点开始到 6 点之前完成。 所以，针对 ETL 过程的优化主要是结合数据仓库自身的特点，抓住需要优化的主要方面， 针对不同的情况从如何采用高效的 SQL 入手来进行。3. 优化的实例分析目前数据仓库建设中的后台数据库大部分采用 Oracle ，以下的 SQL 采用 Oracl
3、e 的语 法来说明，所有的测试在 Oracle9i 环境中通过，但其优化的方法和原理同样适合除 Oracle 之外的其他数据库。3.1 索引的正确使用ETL 过程在海量数据表中，基本每个表都有一个或多个的索引来保证高效的查询，在 中的索引需要遵循以下使用原则：(1) 当插入的数据为数据表中的记录数量 10% 以上时 , 首先需要删除该表的索引来提 高数据的插入效率，当数据全部插入后再建立索引。(2) 避免在索引列上使用函数或计算， 在 where 子句中，如果索引列是函数的一部分， 优化器将不使用索引而使用全表扫描。举例 :低效： select * ROM DEPT where SAL * 1
4、2 25000;高效： select * FROM DEPT where SAL 25000/12;(3) 避免在索引列上使用 NOT 和”!=”，索引只能告诉什么存在于表中， 而不能告诉 什么不存在于表中，当数据库遇到 NOT 和”!= ”时，就会停止使用索引转而执行全表扫描。(4) 索引列上用 = 替代 高效： select * FROM EMP where DEPTNO =4低效： select * FROM EMP where DEPTNO 3DEPT 大于 3 的记录。两者的区别在于， 前者 DBMS 将直接跳到第一个 DEPT 等于 4 的记录而后者将首先定 位到 DEPTNO=3
5、 的记录并且向前扫描到第一个Oracle9i 以上版(5) 函数的列启用索引方法，如果一定要对使用函数的列启用索引， 本新的功能：基于函数的索引 (Function-Based Index)是一个较好的方案， 但该类型索引的缺点是只能针对某个函数来建立和使用该函数。create INDEX EMP_I ON EMP (UPPER( ENAME);select * FROM EMP where UPPER(ENAME) = BLACKSNAIL ;3.2 游标的正确使用当在海量数据表中进行数据的删除、 更新和插入操作时， 用游标处理的效率是最慢的方 式，但它在 ETL 过程中的使用又必不可少，而
6、且使用有着及其重要的地位，所以游标的正 确使用尤为重要。对数据仓库维表的数据进行维护时，因为需要保证维表 ID 的一致性，所以采用游标的 是数据维护完整性的最好方式。 由于它的效率低， 如果按照普通的方式将无法处理大数据量 的维表数据维护(一般是指 10 万条记录以上的维表)，以下是处理这种情况的有效方式：(1) 在数据抽取的源表中使用时间戳， 这样每天的维表数据维护只针对更新日期为最新 时间的数据来进行，大大减少需要维护的数据记录数。(2) 在 insert 和 update 维表时都加上一个条件来过滤维表中已经存在的记录，实例 为：insert INTO DIM_CUSTOMER sele
7、ct * FROM ODS_CUSTOMER whereODS_CUSTOMER.CODE NOT exists (DIM_CUSTOMER.CODE)(3) 使用显式的游标 (CURSORs) ，因为使用隐式的游标将会执行两次操作，第一次 检索记录，第二次检查 TOO MANY ROWS 这个 EXCEPTION ，而显式游标不执行第二次 操作。3.3 数据抽取和上载时的 SQL 优化3.3.1 where 子句中的连接顺序ORACLE 采用自下而上的顺序解析 where 子句， 根据这个原理， 表之间的连接必须写 在其它 where 条件之前，那些可以过滤掉最大数量记录的条件必须写在 wh
8、ere 子句的末 尾。低效： select * FROM EMP E where SAL 50000 AND JOB = MANAGERAND25 (select count(*) FROM EMP where MGR=E.EMPNO);MANAGER;高效： select * FROM EMP E where 25 50000 AND JOB =3.3.2 删除全表时用 truncate 替代 delete当 delete 删除表中的记录时，有回滚段 (rollback segments ) 的信息，而当运用 truncate 时，回滚段不再存放任何可被恢复的信息， 很短。 同时需要注意 t
9、runcate 只在删除全表时适用， 因为 truncate用来存放可以被恢复所以执行时间也会是 DDL 而不是 DML 。3.3.3 尽量多使用 COMMITETL 中同一个过程的数据操作步骤很多，数据仓库采用的是数据抽取后分析模型重算 的原理， 所以对数据的 COMMIT 不像业务系统为保证数据的完整和一致性而需要某个操作 过程全部完成才能进行，只要有可能就在程序中对每个 delete 、 insert 和 update 操作尽 量多使用 COMMIT, 这样系统性能会因为 COMMIT 所释放的资源而大大提高。3.3.4 用 exists 替代 IN例如在exists 而在许多基于基础表
10、的查询中， 为了满足一个条件往往需要对另一个表进行联接，ETL 过程写数据到模型时经常需要关联 10 个左右的维表，在这种情况下，使用 不用 IN 将提高查询的效率。3.3.5 用 NOT exists 替代 NOT IN子查询中， NOT IN 子句将执行一个内部的排序和合并，无论在哪种情况下， 都是最低效的，因为它对子查询中的表执行了一个全表遍历。用 将提高查询的效率。NOT existsNOT IN替代 NOT IN3.3.6 优化 GROUP BY提高 GROUP BY 语句的效率，可以通过将不需要的记录在GROUP BY之前过滤掉。低效： select JOB , AVG(SAL)
11、FROM EMP GROUP BY JOB HAVING JOB =PRESIDENT OR JOB = MANAGER高效： select JOB , AVG(SAL) FROM EMP where JOB = PRESIDENT OR JOBMANAGER GROUP BY JOB3.3.7 有条件的使用 union-ALL 替换 unionunion-ALL 替换 union 的前ETL 过程针对多表连接操作的情况很多，有条件的使用 提是：所连接的各个表中无主关键字相同的记录，因为union ALL 将重复输出两个结果集合中相同记录。当 SQL 语句需要 union 两个查询结果集合时，
12、这两个结果集合会以 union-ALL 的方 式被合并，然后在输出最终结果前进行排序。如果用 union ALL 替代 union ，这样排序就 不是必要了，效率就会因此得到提高 3-5 倍3.3.8 分离表和索引总是将你的表和索引建立在不同的表空间内，决不要将不属于ORACLE内部系统的对象存放到SYSTEM表空间里。同时确保数据表空间和索引表空间置与不同的硬盘控制卡控 制的硬盘上5771001803090012095 579036822859633082 5771001803090012386 576137399735760696 5771001803090013594 578077579
13、902515512 5771001803090012387 577164982601818051 5771001803090012138 572131192158918326 5771001803090012359 579036822361076053 5771001803090012356 576135286143791742 5771001803090012355 57508786970469327917088100343355274 101229944325833379 17088100343355275 101866732938832008 17088100343356107 1015
14、81152501500522 17088100343356108 101000180059871732 17088100343354295 101074194142687017 17088100343356184 101878660869628802 17088100343356185 101775831174086674 17088100343356109 101086014373572846 17088100343356110 101152207216014916 17088100343355237 101027041605702709 17088100343355238 101229364861425414 17088100343356169 101862204402635718 17088100343354928 101760654089788804