Oracle SQL性能优化
原则一:注意WHERE子句中的连接顺序: ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前,那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾.尤其是“主键ID=?”这样的条件。
原则二: SELECT子句中避免使用 ‘ * ‘: ORACLE在解析的过程中,会将'*' 依次转换成所有的列名,这个工作是通过查询数据字典完成的,这意味着将耗费更多的时间 。简单地讲,语句执行的时间越短越好(尤其对于系统的终端用户来说)。而对于查询语句,由于全表扫描读取的数据多,尤其是对于大型表不仅查询速度慢,而且对磁盘IO造成大的压力,通常都要避免,而避免的方式通常是使用索引Index。 使用索引的优势与代价。 代价:虽然使用索引能得到查询效率的提高,但是我们也必须注意到它的代价. 索引需要空间来存储,也需要定期维护,每当有记录在表中增减或索引列被修改时,索引本身也会被修改. 这意味着每条记录的INSERT,DELETE,UPDATE将为此多付出4,5 次的磁盘I/O . 因为索引需要额外的存储空间和处理,那些不必要的索引反而会 使查询反应时间变慢.。而且表越大,影响越严重。 一、使用索引需要注意的地方: 1、避免在索引列上使用NOT 2、避免在索引列上使用计算.
低效:SELECT … FROM DEPT WHERE SAL * 12 > 25000; 高效:SELECT … FROM DEPT WHERE SAL > 25000/12; 3、避免在索引列上使用IS NULL和IS NOT NULL
低效:(索引失效) SELECT … FROM DEPARTMENT WHERE DEPT_CODE IS NOT NULL; 高效:(索引有效) SELECT … FROM DEPARTMENT WHERE DEPT_CODE >=0; 4、注意通配符%的影响 使用通配符的情况下Oracle可能会停用该索引。如 : SELECT…FROM DEPARTMENT WHERE DEPT_CODE like ‘%123456%'(无效)。 SELECT…FROM DEPARTMENT WHERE DEPT_CODE = ‘123456'(有效) 5、避免改变索引列的类型 6、索引的一些“脾气”a. 如果检索数据量超过30%的表中记录数.使用索引将没有显著的效率提高. 二、除了使用索引,我们还有其他能减少资源消耗的方法: 1、用EXISTS替换DISTINCT: (低效): SELECT DISTINCT DEPT_NO,DEPT_NAME FROM DEPT D,EMP E WHERE D.DEPT_NO = E.DEPT_NO And E.sex =man (高效): SELECT DEPT_NO,DEPT_NAME FROM DEPT D WHERE EXISTS ( SELECT ‘X' FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO And E.sex =man ); 2、用(UNION)UNION ALL替换OR (适用于索引列) 通常情况下,用UNION替换WHERE子句中的OR将会起到较好的效果. 对索引列使用OR将造成全表扫描. 注意,以上规则只针对多个索引列有效. 如果有column没有被索引,查询效率可能会因为你没有选择OR而降低. 在下面的例子中,LOC_ID 和REGION上都建有索引. 如果你坚持要用OR,那就需要返回记录最少的索引列写在最前面. 高效: SELECT LOC_ID,LOC_DESC,REGION FROM LOCATION WHERE LOC_ID = 10 UNION ALL SELECT LOC_ID,REGION FROM LOCATION WHERE REGION = “MELBOURNE” 低效: SELECT LOC_ID,REGION FROM LOCATION WHERE LOC_ID = 10 OR REGION = “MELBOURNE” 3、用UNION-ALL 替换UNION ( 如果有可能的话): 当 SQL语句需要UNION两个查询结果集合时,这两个结果集合会以UNION-ALL的方式被合并,然后在输出最终结果前进行排序. 如果用UNION ALL替代UNION,这样排序就不是必要了. 效率就会因此得到提高. 需要注意的是,UNION ALL 将重复输出两个结果集合中相同记录. 因此各位还是要从业务需求分析使用UNION ALL的可行性. UNION 将对结果集合排序,这个操作会使用到SORT_AREA_SIZE这块内存. 对于这块内存的优化也是相当重要的. 4、Order By语句加在索引列,最好是主键PK上。 SELECT DEPT_CODE FROM DEPT ORDER BY DEPT_TYPE(低效) SELECT DEPT_CODE FROM DEPT ORDER BY DEPT_CODE (高效) 5、避免使用耗费资源的操作: 带 有DISTINCT,UNION,MINUS,INTERSECT的SQL语句会启动SQL引擎 执行耗费资源的排序(SORT)功能. DISTINCT需要一次排序操作,而其他的至少需要执行两次排序. 通常,带有UNION,INTERSECT的SQL语句都可以用其他方式重写. 如果你的数据库的SORT_AREA_SIZE调配得好,使用UNION,INTERSECT也是可以考虑的,毕竟它们的可读性很强6、使用Where替代Having(如果可以的话) 低效: SELECT JOB,AVG(SAL) FROM EMP GROUP JOB HAVING JOB = ‘PRESIDENT'AND AVG(SAL)>XXX 高效: SELECT JOB,AVG(SAL) FROM EMP WHERE JOB = ‘PRESIDENT' OR JOB = ‘MANAGER' GROUP JOB Having AND AVG(SAL)>XXX 7、通常来说,如果语句能够避免子查询的 使用,就尽量不用子查询。因为子查询的开销是相当昂贵的。 (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |