设为首页 收藏本站
查看: 764|回复: 0

[经验分享] oracle sql数据的优化及

[复制链接]

尚未签到

发表于 2016-7-31 13:02:12 | 显示全部楼层 |阅读模式
  别人总结好的SQL编写提高效率的方法,挺实用,记下来
  
  
  在海量数据表中,基本每个表都有一个或多个的索引来保证高效的查询,在ETL过程中的索引需要遵循以下使用原则:
  (1) 当插入的数据为数据表中的记录数量10%以上时, 首先需要删除该表的索引来提高数据的插入效率,当数据全部插入后再建立索引。
  (2) 避免在索引列上使用函数或计算,在where子句中,如果索引列是函数的一部分,优化器将不使用索引而使用全表扫描。举例:
  低效: select * ROM DEPT where SAL * 12 > 25000;
  高效: select * FROM DEPT where SAL > 25000/12;
  (3) 避免在索引列上使用NOT和”!=” , 索引只能告诉什么存在于表中,而不能告诉什么不存在于表中,当数据库遇到NOT和”!=”时,就会停止使用索引转而执行全表扫描。
  (4) 索引列上用>=替代>
  高效: select * FROM EMP where DEPTNO >=4
  低效: select * FROM EMP where DEPTNO >3
  两者的区别在于,前者DBMS将直接跳到第一个DEPT等于4的记录而后者将首先定位到DEPTNO=3的记录并且向前扫描到第一个DEPT大于3的记录。
  (5) 函数的列启用索引方法,如果一定要对使用函数的列启用索引,Oracle9i以上版本新的功能:基于函数的索引(Function-Based Index)是一个较好的方案,但该类型索引的缺点是只能针对某个函数来建立和使用该函数。
  create INDEX EMP_I ON EMP (UPPER( ENAME));       
  select * FROM EMP where UPPER(ENAME) = ‘BLACKSNAIL’;
  3.2 游标的正确使用
  当在海量数据表中进行数据的删除、更新和插入操作时,用游标处理的效率是最慢的方式,但它在ETL过程中的使用又必不可少,而且使用有着及其重要的地位,所以游标的正确使用尤为重要。
  对数据仓库维表的数据进行维护时,因为需要保证维表ID的一致性,所以采用游标的是数据维护完整性的最好方式。由于它的效率低,如果按照普通的方式将无法处理大数据量的维表数据维护(一般是指10万条记录以上的维表),以下是处理这种情况的有效方式:
  (1) 在数据抽取的源表中使用时间戳,这样每天的维表数据维护只针对更新日期为最新时间的数据来进行,大大减少需要维护的数据记录数。
  (2) 在insert和update维表时都加上一个条件来过滤维表中已经存在的记录,实例为:
  insert INTO DIM_CUSTOMER select * FROM ODS_CUSTOMER where ODS_CUSTOMER.CODE NOT exists (DIM_CUSTOMER.CODE)
  (3) 使用显式的游标(CURSORs) ,因为使用隐式的游标将会执行两次操作,第一次检索记录,第二次检查TOO MANY ROWS 这个EXCEPTION,而显式游标不执行第二次操作。
  3.3数据抽取和上载时的SQL优化
  3.3.1 where子句中的连接顺序
  ORACLE采用自下而上的顺序解析where子句,根据这个原理,表之间的连接必须写在其它where条件之前,那些可以过滤掉最大数量记录的条件必须写在where子句的末尾。
  低效:select * FROM EMP E where SAL > 50000 AND JOB = ‘MANAGER’ AND 25 < (select count(*) FROM EMP where MGR=E.EMPNO);
  高效:select * FROM EMP E where 25 < (select count(*) FROM EMP where MGR=E.EMPNO) AND SAL > 50000 AND JOB = ‘MANAGER’;
  3.3.2 删除全表时用truncate替代delete
  当delete删除表中的记录时,有回滚段(rollback segments ) 用来存放可以被恢复的信息,而当运用truncate时,回滚段不再存放任何可被恢复的信息,所以执行时间也会很短。同时需要注意truncate只在删除全表时适用,因为truncate是DDL而不是DML。
  3.3.3 尽量多使用COMMIT
  ETL中同一个过程的数据操作步骤很多,数据仓库采用的是数据抽取后分析模型重算的原理,所以对数据的COMMIT不像业务系统为保证数据的完整和一致性而需要某个操作过程全部完成才能进行,只要有可能就在程序中对每个delete、insert和update操作尽量多使用COMMIT, 这样系统性能会因为COMMIT所释放的资源而大大提高。
  3.3.4 用exists替代IN
  在许多基于基础表的查询中,为了满足一个条件往往需要对另一个表进行联接,例如在ETL过程写数据到模型时经常需要关联10个左右的维表,在这种情况下,使用exists而不用IN将提高查询的效率。
  3.3.5 用NOT exists替代NOT IN
  子查询中,NOT IN子句将执行一个内部的排序和合并,无论在哪种情况下,NOT IN都是最低效的,因为它对子查询中的表执行了一个全表遍历。用NOT exists替代NOT IN将提高查询的效率。
  3.3.6 优化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
  3.3.7 有条件的使用union-ALL 替换union
  ETL过程针对多表连接操作的情况很多,有条件的使用union-ALL 替换union的前提是:所连接的各个表中无主关键字相同的记录,因为union ALL 将重复输出两个结果集合中相同记录。
  当SQL语句需要union两个查询结果集合时,这两个结果集合会以union-ALL的方式被合并,然后在输出最终结果前进行排序。如果用union ALL替代union,这样排序就不是必要了,效率就会因此得到提高3-5倍
  3.3.8 分离表和索引
  总是将你的表和索引建立在不同的表空间内,决不要将不属于ORACLE内部系统的对象存放到SYSTEM表空间里。同时确保数据表空间和索引表空间置与不同的硬盘控制卡控制的硬盘上

运维网声明 1、欢迎大家加入本站运维交流群:群②:261659950 群⑤:202807635 群⑦870801961 群⑧679858003
2、本站所有主题由该帖子作者发表,该帖子作者与运维网享有帖子相关版权
3、所有作品的著作权均归原作者享有,请您和我们一样尊重他人的著作权等合法权益。如果您对作品感到满意,请购买正版
4、禁止制作、复制、发布和传播具有反动、淫秽、色情、暴力、凶杀等内容的信息,一经发现立即删除。若您因此触犯法律,一切后果自负,我们对此不承担任何责任
5、所有资源均系网友上传或者通过网络收集,我们仅提供一个展示、介绍、观摩学习的平台,我们不对其内容的准确性、可靠性、正当性、安全性、合法性等负责,亦不承担任何法律责任
6、所有作品仅供您个人学习、研究或欣赏,不得用于商业或者其他用途,否则,一切后果均由您自己承担,我们对此不承担任何法律责任
7、如涉及侵犯版权等问题,请您及时通知我们,我们将立即采取措施予以解决
8、联系人Email:admin@iyunv.com 网址:www.yunweiku.com

所有资源均系网友上传或者通过网络收集,我们仅提供一个展示、介绍、观摩学习的平台,我们不对其承担任何法律责任,如涉及侵犯版权等问题,请您及时通知我们,我们将立即处理,联系人Email:kefu@iyunv.com,QQ:1061981298 本贴地址:https://www.yunweiku.com/thread-251496-1-1.html 上篇帖子: [导入]oracle系统表的查询字典 下篇帖子: Oracle SQL 內置函數大全
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

扫码加入运维网微信交流群X

扫码加入运维网微信交流群

扫描二维码加入运维网微信交流群,最新一手资源尽在官方微信交流群!快快加入我们吧...

扫描微信二维码查看详情

客服E-mail:kefu@iyunv.com 客服QQ:1061981298


QQ群⑦:运维网交流群⑦ QQ群⑧:运维网交流群⑧ k8s群:运维网kubernetes交流群


提醒:禁止发布任何违反国家法律、法规的言论与图片等内容;本站内容均来自个人观点与网络等信息,非本站认同之观点.


本站大部分资源是网友从网上搜集分享而来,其版权均归原作者及其网站所有,我们尊重他人的合法权益,如有内容侵犯您的合法权益,请及时与我们联系进行核实删除!



合作伙伴: 青云cloud

快速回复 返回顶部 返回列表