• 1. ORACLE SQL优化学习张军军 项目研发部
  • 2. 目录索引不要让Oracle做得太多给优化器更明确的命令减少访问次数细节上的影响
  • 3. 索引1、 类似书的目录结构 2、 Oracle 的“索引”对象,与表关联的可选对象,提高SQL查询语句的速度 3、 索引直接指向包含所查询值的行的位置,减少磁盘I/O 4、 与所索引的表是相互独立的物理结构 5、 Oracle 自动使用并维护索引,插入、删除、更新表后,自动更新索引 6、 语法:CREATE INDEX index ON table (column[, column]...);
  • 4. sql 语句的编写原则和优化 随着数据库中数据的增加,系统的响应速度就成为目前系统需要解决的最主要的问题之一。系统优化中一个很重要的方面就是SQL语句的优化。对于大量数据,劣质SQL语句和优质SQL语句之间的速度差别可以达到上百倍,对于一个系统不是简单地能实现其功能就可,而是要写出高质量的SQL语句,提高系统的可用性。 在多数情况下,Oracle使用索引来更快地遍历表,优化器主要根据定义的索引来提高性能。如果在SQL语句的where子句中写的SQL代码不合理,就会造成优化器删去索引而使用全表扫描,一般就这种SQL语句就是所谓的劣质SQL语句。
  • 5. 如何优化sql1.不要让Oracle做得太多; 2.给优化器更明确的命令; 3.减少访问次数; 4.细节上的影响;
  • 6. 1.不要让Oracle做得太多
  • 7. 避免使用 ‘ * ‘当你想在SELECT子句中列出所有的COLUMN时,使用动态 SQL列引用 ‘*’ 是一个方便的方法.不幸的是,这是一个非常低 效的方法. 实际上,ORACLE在解析的过程中, 会将’*’ 依次转 换成所有的列名, 这个工作是通过查询数据字典完成的, 这意 味着将耗费更多的时间; 只提取你所要使用的列; 使用别名能够加快解析速度;
  • 8. 避免使用耗费资源的操作带有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的 SQL语句会启动SQL引擎执行耗费资源的排序(SORT)功能. DISTINCT需要一次排序操作, 而其他的至少需要执行两次 排序. 例如,一个UNION查询,其中每个查询都带有GROUP BY子句 , GROUP BY会触发嵌入排序(NESTED SORT) ; 这样, 每个 查询需要执行一次排序, 然后在执行UNION时, 又一个唯一 排序(SORT UNIQUE)操作被执行而且它只能在前面的嵌入 排序结束后才能开始执行. 嵌入的排序的深度会大大影响查 询的效率. 通常, 带有UNION, MINUS , INTERSECT的SQL语句都可以 用其他方式重写.
  • 9. 例如: 低效: SELECT DISTINCT d.DEPTNO,DNAME FROM DEPT D,EMP E WHERE D.DEPTNO = E.DEPTNO 高效: SELECT DEPTNO,DNAME FROM DEPT D WHERE EXISTS ( SELECT e.deptno FROM EMP E WHERE E.DEPTNO = D.DEPTNO);用EXISTS替换DISTINCT
  • 10. 用UNION-ALL 替换UNION ( if possible)当SQL语句需要UNION两个查询结果集合时,这两个结果集合会以 UNION-ALL的方式被合并, 然后在输出最终结果前进行排序. 举例: 低效: select recid,billdefine,billcode from gams_assetcard union select recid,billdefine,billcode from gams_assetcard 高效: select recid,billdefine,billcode from gams_assetcard union all select recid,billdefine,billcode from gams_assetcard
  • 11. 2. 给优化器更明确的命令
  • 12. 自动选择索引如果表中有两个以上(包括两个)索引,其中有一个唯一性 索引,而其他是非唯一性. 在这种情况下,ORACLE将使用唯一性索引而完全忽略非唯 一性索引. 举例: SELECT ENAME FROM EMP WHERE EMPNO = 7369 AND DEPTNO = 20 ; 这里,只有EMPNO上的索引是唯一性的,所以EMPNO索 引将用来检索记录. TABLE ACCESS BY INDEX ON EMP INDEX UNIQUE SCAN
  • 13. 至少要包含组合索引的第一列如果索引是建立在多个列上, 只有在它的第一个列(leading column)被where子句引用时,优化器才会选择使用该索引. SQL> SELECT * FROM EMP WHERE DEPTNO = 20 Execution Plan ---------------------------------------------------------- 1 0 SELECT STATEMENT, GOAL = ALL_ROWS 2 TABLE ACCESS BY INDEX ROWID 3 INDEX RANGE SCAN SCOTT DEPTNO SQL> SELECT * FROM EMP WHERE ENAME = 'SMITH'; Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT, GOAL = ALL_ROWS 1 TABLE ACCESS FULL SCOTT EMP 很明显, 当仅引用索引的第二个列时,优化器使用了全表扫描而忽略了索引
  • 14. 避免在索引列上使用函数WHERE子句中,如果索引列是函数的一部分.优化器将不 使用索引而使用全表扫描. 举例: 低效: SELECT … FROM EMP WHERE SAL * 12 > 25000; 高效: SELECT … FROM EMP WHERE SAL > 25000/12;
  • 15. 避免使用前置通配符WHERE子句中, 如果索引列所对应的值的第一个字符由通 配符(WILDCARD)开始, 索引将不被采用. SQL>SELECT * FROM EMP WHERE EMPNO = '7369' SQL>SELECT * FROM EMP WHERE EMPNO LIKE ‘%7369' 在第二种情况下,ORACLE将使用全表扫描.
  • 16. 避免在索引列上使用NOT通常,我们要避免在索引列上使用NOT, NOT会产生在和在 索引列上使用函数相同的影响. 当ORACLE”遇到”NOT,他就 会停止使用索引转而执行全表扫描. 举例: 低效: (这里,不使用索引) SELECT * FROM DEPT WHERE EMPNO != ‘7369’; 高效: (这里,使用了索引) SELECT … FROM DEPT WHERE EMPNO > ‘7369’;
  • 17. 避免在索引列上使用 IS NULL和IS NOT NULL避免在索引中使用任何可以为空的列,ORACLE将无法使用该 索引 .对于单列索引,如果列包含空值,索引中将不存在此记 录. 对于复合索引,如果每个列都为空,索引中同样不存在此 记录. 如果至少有一个列不为空,则记录存在于索引中. 如果唯一性索引建立在表的A列和B列上, 并且表中存在一条记 录的A,B值为(123,null) , ORACLE将不接受下一条具有相同 A,B值(123,null)的记录(插入). 然而如果所有的索引列都为 空,ORACLE将认为整个键值为空而空不等于空. 因此你可以 插入1000条具有相同键值的记录,当然它们都是空! 因为空值不存在于索引列中,所以WHERE子句中对索引列进行 空值比较将使ORACLE停用该索引. 任何在where子句中使用is null或is not null的语句优化器是 不允许使用索引的。
  • 18. 避免出现索引列自动转换当比较不同数据类型的数据时, ORACLE自动对列进行简单 的类型转换. 假设EMP_TYPE是一个字符类型的索引列. SELECT * FROM EMP WHERE EMPNO = 7369 这个语句被ORACLE转换为: SELECT * FROM EMP WHERE TO_NUMBER(EMPNO) = 7369 因为内部发生的类型转换, 这个索引将不会被用到!
  • 19. 在查询时尽量少用格式转换如用 WHERE a.order_no = b.order_no 不用 WHERE TO_NUMBER (substr(a.order_no, instr(b.order_no, '.') - 1) = TO_NUMBER (substr(a.order_no, instr(b.order_no, '.') - 1)
  • 20. 3.减少访问次数
  • 21. 减少访问数据库的次数当执行每条SQL语句时, ORACLE在内部执行了许多工作: 解析SQL语句, 估算索引的利用率, 绑定变量 , 读数据块等等. 由此可见, 减少访问数据库的次数 , 就能实际上减少 ORACLE的工作量.
  • 22. 使用DECODE来减少处理时间例如: SELECT COUNT(*),SUM(SAL) FROM EMP WHERE DEPTNO = 20 AND ENAME LIKE ‘SMITH%’; SELECT COUNT(*),SUM(SAL) FROM EMP WHERE DEPTNO = 30 AND ENAME LIKE ‘SMITH%’; 可以用DECODE函数高效地得到相同结果 SELECT COUNT(DECODE(DEPTNO,20,0,NULL)) D0020_COUNT, COUNT(DECODE(DEPTNO,30,1,NULL)) D0030_COUNT, SUM(DECODE(DEPTNO,20,SAL,NULL)) D0020_SAL, SUM(DECODE(DEPTNO,30,SAL,NULL)) D0030_SAL FROM EMP WHERE ENAME LIKE ‘SMITH%’;
  • 23. 减少对表的查询在含有子查询的SQL语句中,要特别注意减少对表的查询. 例如: 低效 SELECT TAB_NAME FROM TABLES WHERE TAB_NAME = ( SELECT TAB_NAME FROM TAB_COLUMNS WHERE VERSION = 604) AND DB_VER= ( SELECT DB_VER FROM TAB_COLUMNS WHERE VERSION = 604) 高效 SELECT TAB_NAME FROM TABLES WHERE (TAB_NAME,DB_VER) = ( SELECT TAB_NAME,DB_VER) FROM TAB_COLUMNS WHERE VERSION = 604)
  • 24. 4. 细节上的影响
  • 25. WHERE子句中的连接顺序ORACLE采用自下而上的顺序解析WHERE子句,根据这个原 理, 当在WHERE子句中有多个表联接时,WHERE子句中排 在最后的表应当是返回行数可能最少的表,有过滤条件的子 句应放在WHERE子句中的最后。 如:设从emp表查到的数据比较少或该表的过滤条件比较确定,能大大缩小查询范围,则将最具有选择性部分放在WHERE子句中的最后: select * from emp e,dept d where d.deptno >10 and e.deptno =30 ; 如果dept表返回的记录数较多的话,上面的查询语句会比下面的查询语句响应快得多。 select * from emp e,dept d where e.deptno =30 and d.deptno >10 ;
  • 26. WHERE子句 ——函数、表达式使用最好不要在WHERE子句中使用函或表达式,如果要使用的话,最好统一使用相同的表达式或函数,这样便于以后使用合理的索引。
  • 27. Order by语句 ORDER BY语句决定了Oracle如何将返回的查询结果排序。Order by语句对要排序的列没有什么特别的限制,也可以将函数加入列中(象联接或者附加等)。任何在Order by语句的非索引项或者有计算表达式都将降低查询速度。 仔细检查order by语句以找出非索引项或者表达式,它们会降低性能。解决这个问题的办法就是重写order by语句以使用索引,也可以为所使用的列建立另外一个索引,同时应绝对避免在order by子句中使用表达式。
  • 28. 联接列 对于有联接的列,即使最后的联接值为一个静态值,优化器 是不会使用索引的。 select * from employss   where   first_name||''||last_name ='Beill Cliton'; 系统优化器对基于last_name创建的索引没有使用。 当采用下面这种SQL语句的编写,Oracle系统就可以采用基 于last_name创建的索引。   select * from employee    where first_name ='Beill' and last_name ='Cliton';
  • 29. 带通配符(%)的like语句 通配符(%)在搜寻词首出现,Oracle系统不使用 last_name的索引。 select * from employee where last_name like '%cliton%'; 在很多情况下可能无法避免这种情况,但是一定要心中有底 ,通配符如此使用会降低查询速度。然而当通配符出现在字 符串其他位置时,优化器就能利用索引。在下面的查询中索 引得到了使用: select * from employee where last_name like 'c%';
  • 30. 用Where子句替换HAVING子句避免使用HAVING子句, HAVING 只会在检索出所有记录之后才对结果 集进行过滤. 这个处理需要排序,总计等操作. 如果能通过WHERE子句限 制记录的数目,那就能减少这方面的开销. 例如: 低效: select avg(sal) from emp group by empno having empno !='7369' and empno!='7349' 高效 select avg(sal) from emp where empno !='7369' and empno!='749' group by empno 顺序 WHERE > GROUP > HAVING
  • 31. 用NOT EXISTS 替代 NOT IN在子查询中,NOT IN子句将执行一个内部的排序和合并. 无论在哪种情况 下,NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历). 使用NOT EXISTS 子句可以有效地利用索引。尽可能使用NOT EXISTS 来代替NOT IN,尽管二者都使用了NOT(不能使用索引而降低速度), NOT EXISTS要比NOT IN查询效率更高。 例如: 语句1 SELECT dname, deptno FROM dept WHERE deptno NOT IN (SELECT deptno FROM emp); 语句2 SELECT dname, deptno FROM dept WHERE NOT EXISTS (SELECT deptno FROM emp WHERE dept.deptno = emp.deptno); 2要比1的执行性能好很多。 因为1中对emp进行了full table scan,这是很浪费时间的操作。而且1中没有用到emp的index, 因为没有where子句。而2中的语句对emp进行的是缩小范围的查询。
  • 32. 用索引提高效率索引是表的一个概念部分,用来提高检索数据的效率,ORACLE使 用了一个复杂的自平衡B-tree结构. 通常,通过索引查询数据比全表 扫描要快. 当ORACLE找出执行查询和Update语句的最佳路径时, ORACLE优化器将使用索引. 同样在联结多个表时使用索引也可以 提高效率. 另一个使用索引的好处是,它提供了主键(primary key) 的唯一性验证。 通常, 在大型表中使用索引特别有效. 当然,你也会发现, 在扫描小 表时,使用索引同样能提高效率. 虽然使用索引能得到查询效率的提 高,但是我们也必须注意到它的代价. 索引需要空间来存储,也需要 定期维护, 每当有记录在表中增减或索引列被修改时, 索引本身也 会被修改. 这意味着每条记录的INSERT , DELETE , UPDATE将为 此多付出4 , 5 次的磁盘I/O . 因为索引需要额外的存储空间和处理, 那些不必要的索引反而会使查询反应时间变慢.。定期的重构索引 是有必要的。
  • 33. 避免在索引列上使用计算WHERE子句中,如果索引列是函数的一部分.优化器将不 使用索引而使用全表扫描. 低效: SELECT … FROM DEPT WHERE SAL * 12 > 25000; 高效: SELECT … FROM DEPT WHERE SAL > 25000/12;
  • 34. 用>= 替代 >如果DEPTNO上有一个索引。 高效: SELECT * FROM EMP WHERE DEPTNO >=4 低效: SELECT * FROM EMP WHERE DEPTNO >3
  • 35. 通过使用>=、<=等,避免使用NOT命令例子: select * from emp where sal <> 3000; 对这个查询,可以改写为不使用NOT: select * from em where sal<3000 or sal>3000; 虽然这两种查询的结果一样,但是第二种查询方案会比第一种查询方案更快些。第二种查询允许Oracle对sal列使用索引,而第一种查询则不能使用索引。
  • 36. 如果有其它办法,不要使用子查询。
  • 37. 外部联接"+"的用法 外部联接"+"按其在"="的左边或右边分左联接和右联接。若不带"+"运算符的表中的一个行不直接匹配于带"+"预算符的表中的任何行,则前者的行与后者中的一个空行相匹配并被返回。利用外部联接"+",可以替代效率十分低下的 not in 运算,大大提高运行速度。例如,下面这条命令执行起来很慢: select a.empno from emp a where a.empno not in (select empno from emp1 where job='SALE'); 利用外部联接,改写命令如下: select a.empno from emp a ,emp1 b where a.empno=b.empno(+) and b.empno is null and b.job='SALE'; 这样运行速度明显提高.
  • 38. 计算记录条数和一般的观点相反, count(*) 比count(1)稍快 , 当然如果可 以通过索引检索,对索引列的计数仍旧是最快的. 例如 COUNT(EMPNO)
  • 39. 比如有的表PHONE_NO字段是CHAR型,而且创建有索引, 但在WHERE条件中忘记了加引号,就不会用到索引。 WHERE PHONE_NO=‘13920202022’ WHERE PHONE_NO=13920202022 字符型字段的引号
  • 40. (本页无文本内容)