提高数据仓库的ETL效率

1、如何从优化 SQL 入手提高数据仓库的 ETL 效率作者：周四阳 蔡自兴引言数据仓库建设中的 ETL(Extract, Transform, Load)是数据抽取、转换和装载到模型的过程，整个过程基本是通过控制用 SQL 语句编写的存储过程和函数的方式来实现对数据的直接操作，SQL 语句的效率将直接影响到数据仓库后台的性能。IT 领域热门话题的原因。优化的思路分析数据仓库 ETL ETL 6 ETL 过程SQL 入手来进行。优化的实例分析目前数据仓库建设中的后台数据库大部分采用eL采用e的语法来说明，Oracle9i Oracle 之外的其他数据库。索引的正确使用在海量数据表中，基本每个表都有

2、一个或多个的索引来保证高效的查询，在 ETL 要遵循以下使用原则：10%以上时, 首先需要删除该表的索引来提高数据的插入效率，当数据全部插入后再建立索引。避免在索引列上使用函数或计算，在 WHERE 将不使用索引而使用全表扫描。举例:低效： SELECT * ROM DEPT WHERE SAL * 12 25000;高效： SELECT * FROM DEPT WHERE SAL 25000/12;NOT 和”!=” ， 索引只能告诉什么存在于表中， 而不能告诉什么不存NOT和”!=”时，就会停止使用索引转而执行全表扫描。索引列上用=替代高效： SELECT *FROM EMPWHERE D

3、EPTNO =4低效： SELECT *FROM EMPWHERE DEPTNO 3两者的区别在于， 前者 DBMS 将直接跳到第一个 DEPT 等于 4 的记录而后者将首先定位到DEPTNO=3 的记录并且向前扫描到第一个 DEPT 大于 3 的记录。 以上版本新的功能： 基于函数的索引(Function-Based CREATE INDEX EMP_I ON EMP (UPPER( ENAME);/*建立基于函数的索引*/SELECT * FROM EMP WHERE UPPER(ENAME) = BLACKSNAIL; /* 将 使 用 索 引 */游标的正确使用当在海量数据表中进行数据

4、的删除、更新和插入操作时，用游标处理的效率是最慢的方式，但它在ETL 过程中的使用又必不可少，而且使用有着及其重要的地位，所以游标的正确使用尤为重要。对数据仓库维表的数据进行维护时，因为需要保证维表 ID （一10 万条记录以上的维表），以下是处理这种情况的有效方式：来进行，大大减少需要维护的数据记录数。INSERTUPDATE维表时都加上一个条件来过滤维表中已经存在的记录，实例为：INSERT INTO DIM_CUSTOMER SELECT * FROM ODS_CUSTOMER WHERE ODS_CUSTOMER.CODE NOT EXISTS (DIM_CUSTOMER.CODE)/

5、* ODS_CUSTOMER 为数据源表；DIM_CUSTOMER 为维表*/使用显式的游标(CURSORs) ，因为使用隐式的游标将会执行两次操作， 第一次检索记录， TOO MANY ROWS EXCEPTION，而显式游标不执行第二次操作。SQL优化WHERE子句中的连接顺序ORACLE 采用自下而上的顺序解析 WHERE 子句，根据这个原理，表之间的连接必须写在其它 WHERE 条件之前，那些可以过滤掉最大数量记录的条件必须写在 WHERE 子句的末尾。低效：SELECT * FROM EMP E WHERE SAL 50000 AND JOB = MANAGER AND 25 (SE

6、LECT COUNT(*) FROM EMPWHERE MGR=E.EMPNO);高效：SELECT * FROM EMP E WHERE 25 50000 AND JOB = MANAGER;TRUNCATEDELETE当 DELETE 删除表中的记录时，有回滚段(rollback segments ) 用来存放可以被恢复的信息， 而当运用 TRUNCATE 时，回滚段不再存放任何可被恢复的信息，所以执行时间也会很短。 同时需要注意 TRUNCATE 只在删除全表时适用，因为 TRUNCATE 是 DDL 而不是 DML。COMMITETL COMMIT 不像业务系统为保证数据的完整和一致性

7、而需要某个操作过程全部完成才能进DELETEINSERT UPDATE COMMIT, 这COMMIT 所释放的资源而大大提高。EXISTSIN在许多基于基础表的查询中，为了满足一个条件往往需要对另一个表进行联接，例如在 ETL 过程10 EXISTS IN 将提高查询的效率。NOT EXISTSNOT IN子查询中，NOT IN子句将执行一个内部的排序和合并，无论在哪种情况下，NOT IN都是最低效NOT EXISTSNOT IN将提高查询的效率。GROUP BYGROUP BY GROUP BY 之前过滤掉。低效： SELECT JOB , AVG(SAL)FROM EMPGROUP BY JOBHAVING JOB =PRESIDENTOR JOB = MANAGER高效：SELECT JOB , AVG(SAL)FROM EMPWHERE JOB = PRESIDENTOR JOB= MANAGERGROUP BY JOBUNION-ALL UNIONETL UNION-ALL UNION 的前提是：所UNION ALL SQLUNION UNION-ALL 的方式被合并， UNION ALL UNION， 这样排序就不是必要了， 3-5倍分离表和索引总是将你的表和索引建立在不同的表空间内，决不要