优化方案 文档.docVIP

优化方案:EＴL的过程原理和数据仓库建设　 这篇论坛文章(赛迪网技术社区)根据笔者多年的数据仓库实施经验，同时结合EＴL的过程原理和数据仓库建设方法归纳总结了以下优化的方案,详细内容请读者参考下文： １．引言 数据仓库建设中的EＴL(Eｘtract, Tranｓｆoｒm, Load)是数据抽取、转换和装载到模型的过程，整个过程基本是通过控制用SQＬ语句编写的存储过程和函数的方式来实现对数据的直接操作,SQL语句的效率将直接影响到数据仓库后台的性能。 目前，国内的大中型企业基本都具有四年以上计算机信息系统应用经验，积累了大量可分析的业务数据,这些信息系统中的数据需要通过搭建数据仓库平台才能得到科学的分析,这也是近几年数据仓库系统建设成为IT领域热门话题的原因。 2.优化的思路分析 数据仓库ETL过程的主要特点是：面对海量的数据进行抽取;分时段对大批量数据进行删除、更新和插入操作;面对异常的数据进行规则化的清洗;大量的分析模型重算工作；有特定的过程处理时间规律性，一般整个ETL过程需要在每天的零点开始到6点之前完成。所以，针对ＥＴL过程的优化主要是结合数据仓库自身的特点,抓住需要优化的主要方面,针对不同的情况从如何采用高效的SQL入手来进行。 优化的实例分析 目前数据仓库建设中的后台数据库大部分采用Oｒaclｅ,以下的ＳQL采用Oraｃlｅ的语法来说明，所有的测试在Oracle9i环境中通过，但其优化的方法和原理同样适合除Oｒacｌe之外的其他数据库。 ３．1索引的正确使用 在海量数据表中,基本每个表都有一个或多个的索引来保证高效的查询，在ETL过程中的索引需要遵循以下使用原则： (1) 当插入的数据为数据表中的记录数量10％以上时，　首先需要删除该表的索引来提高数据的插入效率，当数据全部插入后再建立索引。 (２） 避免在索引列上使用函数或计算,在WＨERE子句中，如果索引列是函数的一部分,优化器将不使用索引而使用全表扫描。举例: 低效：　SELECＴ * ROM ＤEPT WHＥＲＥ SAL * 1２ ＞ 2５000； 高效: SEＬECＴ ＊ FROM ＤEPT　ＷHERE ＳAＬ 　25０0０/1２; (3)　避免在索引列上使用NOT和”!=”　, 索引只能告诉什么存在于表中，而不能告诉什么不存在于表中,当数据库遇到NOT和”！＝”时，就会停止使用索引转而执行全表扫描。 （4） 索引列上用=替代 高效: SELECT　* ＦROM ＥＭP WHERE DEPTＮO =４ 低效: SELＥＣＴ *　FROM　EMP　WHＥＲE　DEPTNO　＞3 两者的区别在于，前者DBMS将直接跳到第一个DEPT等于4的记录而后者将首先定位到DEPＴNＯ=３的记录并且向前扫描到第一个DＥPT大于3的记录。 (5） 函数的列启用索引方法，如果一定要对使用函数的列启用索引，Ｏrａclｅ9i以上版本新的功能:基于函数的索引(Fuｎctｉon-Based Ｉｎdｅｘ）是一个较好的方案，但该类型索引的缺点是只能针对某个函数来建立和使用该函数。 CREAＴE IＮDEＸ EＭP_I ON　EＭP (UPPＥR（　ENＡME)); 　 SELECT * ＦROM EＭＰ WHERＥ ＵＰPＥR（ENAMＥ) = ‘BLAＣKSNAIL’; 3．2　游标的正确使用 当在海量数据表中进行数据的删除、更新和插入操作时，用游标处理的效率是最慢的方式,但它在ETL过程中的使用又必不可少,而且使用有着及其重要的地位，所以游标的正确使用尤为重要。 对数据仓库维表的数据进行维护时，因为需要保证维表ID的一致性,所以采用游标的是数据维护完整性的最好方式。由于它的效率低，如果按照普通的方式将无法处理大数据量的维表数据维护（一般是指１0万条记录以上的维表)，以下是处理这种情况的有效方式: （1) 在数据抽取的源表中使用时间戳，这样每天的维表数据维护只针对更新日期为必威体育精装版时间的数据来进行，大大减少需要维护的数据记录数。 （2) 在ＩNSERT和UPDＡTＥ维表时都加上一个条件来过滤维表中已经存在的记录,实例为： IＮＳERT　ＩNＴO DIM_CUSＴOMＥR SELECＴ　* FＲＯM OＤS_ＣUＳＴOＭＥＲ　WHERＥ　ODS_ＣUSＴOMEＲ.CODE NOT EXISTS （DIM_ＣUSTOMEＲ.ＣＯDE) (3) 使用显式的游标(CURSＯＲs)　，因为使用隐式的游标将会执行两次操作,第一次检索记录,第二次检查TＯＯ ＭANY　ROWS 这个EＸCEPTION，而显式游标不执行第二次操作。 3．３数据抽取和上载时的SＱL优化 ◆３．３．１ ＷHERＥ子句中的连接顺序 ORAＣLＥ采用自下而上的顺序解析WHERＥ子句，根据这个原理,表之间的连接必须写在其