数据抽取和上载时的SQL优化
WHERE子句中的连接顺序
ORACLE采用自下而上的顺序解析WHERE子句根据这个原理表之间的连接必须写在其它WHERE条件之前那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾
低效SELECT * FROM EMP E WHERE SAL > AND JOB = MANAGER AND < (SELECT COUNT(*) FROM EMP WHERE MGR=EEMPNO);
高效SELECT * FROM EMP E WHERE < (SELECT COUNT(*) FROM EMP WHERE MGR=EEMPNO) AND SAL > AND JOB = MANAGER;
删除全表时用TRUNCATE替代DELETE
当DELETE删除表中的记录时有回滚段(rollback segments ) 用来存放可以被恢复的信息而当运用TRUNCATE时回滚段不再存放任何可被恢复的信息所以执行时间也会很短同时需要注意TRUNCATE只在删除全表时适用因为TRUNCATE是DDL而不是DML
尽量多使用COMMIT
ETL中同一个过程的数据操作步骤很多数据仓库采用的是数据抽取后分析模型重算的原理所以对数据的COMMIT不像业务系统为保证数据的完整和一致性而需要某个操作过程全部完成才能进行只要有可能就在程序中对每个DELETEINSERT和UPDATE操作尽量多使用COMMIT 这样系统性能会因为COMMIT所释放的资源而大大提高
用EXISTS替代IN
在许多基于基础表的查询中为了满足一个条件往往需要对另一个表进行联接例如在ETL过程写数据到模型时经常需要关联个左右的维表在这种情况下使用EXISTS而不用IN将提高查询的效率
用NOT EXISTS替代NOT IN
子查询中NOT IN子句将执行一个内部的排序和合并无论在哪种情况下NOT IN都是最低效的因为它对子查询中的表执行了一个全表遍历用NOT EXISTS替代NOT IN将提高查询的效率
优化GROUP BY
提高GROUP BY 语句的效率可以通过将不需要的记录在GROUP BY 之前过滤掉
低效 SELECT JOB AVG(SAL) FROM EMP GROUP BY JOB HAVING JOB = PRESIDENT OR JOB = MANAGER
高效 SELECT JOB AVG(SAL) FROM EMP WHERE JOB = PRESIDENT OR JOB = MANAGER GROUP BY JOB
有条件的使用UNIONALL 替换UNION
ETL过程针对多表连接操作的情况很多有条件的使用UNIONALL 替换UNION的前提是所连接的各个表中无主关键字相同的记录因为UNION ALL 将重复输出两个结果集合中相同记录
当SQL语句需要UNION两个查询结果集合时这两个结果集合会以UNIONALL的方式被合并然后在输出最终结果前进行排序如果用UNION ALL替代UNION这样排序就不是必要了效率就会因此得到提高倍
分离表和索引
总是将你的表和索引建立在不同的表空间内决不要将不属于ORACLE内部系统的对象存放到SYSTEM表空间里同时确保数据表空间和索引表空间置与不同的硬盘控制卡控制的硬盘上
[] []
![ETL的过程原理和数据仓库建设[2]](/UploadFiles/5.jpg)