《ORACLESQL语句优化资料共享.ppt》由会员分享，可在线阅读，更多相关《ORACLESQL语句优化资料共享.ppt（42页珍藏版）》请在三一办公上搜索。
1、ORACLE SQL语句优化,资料共享,此内容参考：ORACLE SQL性能优化系列.doc,访问Table的方式,ORACLE 采用两种访问表中记录的方式:a.全表扫描 全表扫描就是顺序地访问表中每条记录.ORACLE采用一次读入多个数据块(database block)的方式优化全表扫描.b.通过ROWID访问表你可以采用基于ROWID的访问方式情况,提高访问表的效率,ROWID包含了表中记录的物理位置信息.ORACLE采用索引(INDEX)实现了数据和存放数据的物理位置(ROWID)之间的联系.通常索引提供了快速访问ROWID的方法,因此那些基于索引列的查询就可以得到性能上的提高.,删除
2、重复记录,最高效的删除重复记录方法(因为使用了ROWID)DELETE FROM EMP EWHERE E.ROWID(SELECT MIN(X.ROWID)FROM EMP X WHERE X.EMP_NO=E.EMP_NO);,基于索引的SQL语句优化,1.IS NULL 与 IS NOT NULL2.联接列3.带通配符(%)的like语句4.Order by语句5.NOT6.IN和EXISTS7.用表连接替换EXISTS 8.用EXISTS替换DISTINCT9.用WHERE替代ORDER BY10.用UNION替换OR(适用于索引列)11.用IN来替换OR,1.IS NULL 与 IS
3、 NOT NULL,不能用null作索引，任何包含null值的列都将不会被包含在索引中。即使索引有多列的情况下，只要这些列中有一列含有null，该列就会从索引中排除。也就是说如果某列存在空值，即使对该列建索引也不会提高性能。任何在where子句中使用is null或is not null的语句优化器是不允许使用索引的。,2.联接列,对于有联接的列，即使最后的联接值为一个静态值，优化器不会使用索引的。select*from employee where first_name|last_name=George Bush;使用索引的语句Select*From employee where first
4、_name=George and last_name=Bush;select*from employee where first_name=SUBSTR(,3.带通配符(%)的like语句,通配符(%)在搜寻词首出现，所以Oracle系统不使用的索引 不使用索引的SQL select*from employee where last_name like%Bush%;使用索引的SQLselect*from employee where last_name like c%;,4.Order by语句,Order by语句决定了Oracle如何将返回的查询结果排序。Order by语句对要排序的列没
5、有什么特别的限制，也可以将函数加入列中(象联接或者附加等)。任何在Order by语句的非索引项或者有计算表达式都将降低查询速度。仔细检查order by语句以找出非索引项或者表达式，它们会降低性能。解决这个问题的办法就是重写order by语句以使用索引，也可以为所使用的列建立另外一个索引，同时应绝对避免在order by子句中使用表达式。,5.逻辑表达式 NOT,不使用索引where not(status=VALID);where status VALID;where status!=VALID;select*from employee wheresalary3000;使用索引select
6、*from employee wheresalary3000;,6.IN和EXISTS,.where column in(select*from.where.);.where exists(select X from.where.);通过使用EXISTS，Oracle系统会首先检查主查询，然后运行子查询直到找到第一个匹配项，这就节省了时间。Oracle系统在执行IN子查询时，首先执行子查询，并将获得的结果列表存放在一个加了索引的临时表中。在执行子查询之前，系统先将主查询挂起，待子查询执行完毕，存放在临时表中以后再执行主查询。这也就是使用EXISTS比使用IN通常查询速度快的原因 同时应尽可能使
7、用NOT EXISTS来代替NOT IN，尽管二者都使用了NOT(不能使用索引而降低速度)，但NOT EXISTS要比NOT IN查询效率更高,7.用表连接替换EXISTS,通常来说,采用表连接的方式比EXISTS更有效率 SELECT ENAME FROM EMP E WHERE EXISTS(SELECT X FROM DEPT WHERE DEPT_NO=E.DEPT_NO AND DEPT_CAT=A);(更高效)SELECT ENAME FROM DEPT D,EMP E WHERE E.DEPT_NO=D.DEPT_NO AND DEPT_CAT=A;,8.用EXISTS替换DIS
8、TINCT,多表信息的查询时,避免在SELECT子句中使用DISTINCT.一般可以考虑用EXIST替换,例如:低效:SELECT DISTINCT DEPT_NO,DEPT_NAME FROM DEPT D,EMP E WHERE D.DEPT_NO=E.DEPT_NO高效:SELECT DEPT_NO,DEPT_NAME FROM DEPT D WHERE EXISTS(SELECT X FROM EMP E WHERE E.DEPT_NO=D.DEPT_NO);EXISTS 使查询更为迅速,因为RDBMS核心模块将在子查询的条件一旦满足后,立刻返回结果.,9.用WHERE替代ORDER
9、BY,ORDER BY 子句只在两种严格的条件下使用索引.ORDER BY中所有的列必须包含在相同的索引中并保持在索引中的排列顺序.ORDER BY中所有的列必须定义为非空.WHERE子句使用的索引和ORDER BY子句中所使用的索引不能并列.例如:表DEPT包含以下列:DEPT_CODE PK NOT NULL DEPT_DESC NOT NULL DEPT_TYPE NULL 非唯一性的索引(DEPT_TYPE),9.用WHERE替代ORDER BY,低效:(索引不被使用)SELECT DEPT_CODE FROM DEPT ORDER BY DEPT_TYPE EXPLAIN PLAN:
10、SORT ORDER BY TABLE ACCESS FULL 高效:(使用索引)SELECT DEPT_CODE FROM DEPT WHERE DEPT_TYPE 0 EXPLAIN PLAN:TABLE ACCESS BY ROWID ON EMP INDEX RANGE SCAN ON DEPT_IDX,10.用UNION替换OR(适用于索引列),通常情况下,用UNION替换WHERE子句中的OR将会起到较好的效果.对索引列使用OR将造成全表扫描.注意,以上规则只针对多个索引列有效.如果有column没有被索引,查询效率可能会因为你没有选择OR而降低.在下面的例子中,LOC_ID 和R
11、EGION上都建有索引.高效:SELECT LOC_ID,LOC_DESC,REGION FROM LOCATION WHERE LOC_ID=10 UNION SELECT LOC_ID,LOC_DESC,REGION FROM LOCATION WHERE REGION=“MELBOURNE”低效:SELECT LOC_ID,LOC_DESC,REGION FROM LOCATION WHERE LOC_ID=10 OR REGION=“MELBOURNE”,11.用IN来替换OR,下面的查询可以被更有效率的语句替换:低效:SELECT.FROM LOCATIONWHERE LOC_ID=
12、10 OR LOC_ID=20 OR LOC_ID=30 高效SELECTFROM LOCATIONWHERE LOC_IN IN(10,20,30);,最有效率的表名顺序,ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,因此FROM子句中写在最后的表(基础表 driving table)将被最先处理 表 TAB1 16,384 条记录表 TAB2 1 条记录选择TAB2作为基础表(最好的方法)select count(*)from tab1,tab2 执行时间0.96秒 选择TAB2作为基础表(不佳的方法)select count(*)from tab2,tab1 执行时间2
13、6.09秒,多表连接，交叉表为基础表,SELECT*FROM LOCATION L,CATEGORY C,EMP E WHERE E.EMP_NO BETWEEN 1000 AND 2000AND E.CAT_NO=C.CAT_NOAND E.LOCN=L.LOCN将比下列SQL更有效率SELECT*FROM EMP E,LOCATION L,CATEGORY CWHERE E.CAT_NO=C.CAT_NOAND E.LOCN=L.LOCNAND E.EMP_NO BETWEEN 1000 AND 2000,WHERE子句中的连接顺序,原理：自下而上的顺序解析WHERE子句表之间的连接必须写
14、在其他WHERE条件之前,可过滤掉最大数量记录的条件必须写在WHERE子句的末尾,WHERE子句中的连接顺序,例如:(低效,执行时间156.3秒)SELECT FROM EMP EWHERE SAL 50000AND JOB=MANAGERAND 25 50000AND JOB=MANAGER;,SELECT子句中避免使用*,ORACLE在解析的过程中,会将*依次转换成所有的列名,这个工作是通过查询数据字典完成的,这意味着将耗费更多的时间子句中写出指定的列名,使用DECODE函数来减少处理时间,使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表.例如:SELECT COUNT(*)
15、，SUM(SAL)FROMEMP WHERE DEPT_NO=0020 AND ENAME LIKESMITH%;SELECT COUNT(*)，SUM(SAL)FROMEMP WHERE DEPT_NO=0030 AND ENAME LIKESMITH%;,使用DECODE函数来减少处理时间,使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表.例如:你可以用DECODE函数高效地得到相同结果SELECT COUNT(DECODE(DEPT_NO,0020,X,NULL)D0020_COUNT,COUNT(DECODE(DEPT_NO,0030,X,NULL)D0030_COUNT
16、,SUM(DECODE(DEPT_NO,0020,SAL,NULL)D0020_SAL,SUM(DECODE(DEPT_NO,0030,SAL,NULL)D0030_SALFROM EMP WHERE ENAME LIKE SMITH%;类似的,DECODE函数也可以运用于GROUP BY 和ORDER BY子句中.,尽量多使用COMMIT,只要有可能,在程序中尽量多使用COMMIT,这样程序的性能得到提高,需求也会因为COMMIT所释放的资源而减少:COMMIT所释放的资源:a.回滚段上用于恢复数据的信息.b.被程序语句获得的锁c.redo log buffer 中的空间d.ORACLE为管
17、理上述3种资源中的内部花费,用Where子句替换HAVING子句,避免使用HAVING子句,HAVING 只会在检索出所有记录之后才对结果集进行过滤.这个处理需要排序,总计等操作.如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销.,用Where子句替换HAVING子句,例如:低效:SELECT REGION，AVG(LOG_SIZE)FROM LOCATION GROUP BY REGION HAVING REGION REGION!=SYDNEY AND REGION!=PERTH 高效 SELECT REGION，AVG(LOG_SIZE)FROM LOCATION WHER
18、E REGION REGION!=SYDNEY AND REGION!=PERTH GROUP BY REGION,用=替代,如果DEPTNO上有一个索引,高效:SELECT*FROM EMP WHERE DEPTNO=4 低效:SELECT*FROM EMP WHERE DEPTNO 3 两者的区别在于,前者DBMS将直接跳到第一个DEPT等于4的记录而后者将首先定位到DEPTNO=3的记录并且向前扫描到第一个DEPT大于3的记录.,使用表的别名(Alias),当在SQL语句中连接多个表时,请使用表的别名并把别名前缀于每个Column上.这样一来,就可以减少解析的时间并减少那些由Column
19、歧义引起的语法错误.(译者注:Column歧义指的是由于SQL中不同的表具有相同的Column名,当SQL语句中出现这个Column时,SQL解析器无法判断这个Column的归属),减少访问数据库的次数,当执行每条SQL语句时,ORACLE在内部执行了许多工作:解析SQL语句,估算索引的利用率,绑定变量,读数据块等等.由此可见,减少访问数据库的次数,就能实际上减少ORACLE的工作量,减少访问数据库的次数,例如,以下有三种方法可以检索出雇员号等于0342或0291的职员.方法1(最低效)SELECT EMP_NAME,SALARY,GRADE FROM EMP WHERE EMP_NO=342
20、;SELECT EMP_NAME,SALARY,GRADE FROM EMP WHERE EMP_NO=291;,减少访问数据库的次数,例如,以下有三种方法可以检索出雇员号等于0342或0291的职员.方法2(次低效)DECLARE CURSOR C1(E_NO NUMBER)IS SELECT EMP_NAME,SALARY,GRADE FROM EMP WHERE EMP_NO=E_NO;BEGIN OPEN C1(342);FETCH C1 INTO,.,.;.OPEN C1(291);FETCH C1 INTO,.,.;CLOSE C1;END;,减少访问数据库的次数,例如,以下有三种
21、方法可以检索出雇员号等于0342或0291的职员.方法3(高效)SELECT A.EMP_NAME,A.SALARY,A.GRADE,B.EMP_NAME,B.SALARY,B.GRADE FROM EMP A,EMP B WHERE A.EMP_NO=342 AND B.EMP_NO=291;,减少对表的查询,在含有子查询的SQL语句中,要特别注意减少对表的查询,减少对表的查询,例如:低效SELECT TAB_NAME FROM TABLESWHERE TAB_NAME=(SELECT TAB_NAME FROM TAB_COLUMNS WHERE VERSION=604)ANDDB_VER
22、=(SELECT DB_VER FROM TAB_COLUMNS WHERE VERSION=604)高效SELECT TAB_NAME FROM TABLESWHERE(TAB_NAME,DB_VER)=(SELECT TAB_NAME,DB_VER FROM TAB_COLUMNS WHERE VERSION=604),减少对表的查询,Update 多个Column 例子:低效:UPDATE EMP SET EMP_CAT=(SELECT MAX(CATEGORY)FROM EMP_CATEGORIES),SAL_RANGE=(SELECT MAX(SAL_RANGE)FROM EMP_C
23、ATEGORIES)WHERE EMP_DEPT=0020;高效:UPDATE EMP SET(EMP_CAT,SAL_RANGE)=(SELECT MAX(CATEGORY),MAX(SAL_RANGE)FROM EMP_CATEGORIES)WHERE EMP_DEPT=0020;,使用提示(Hints),对于表的访问,可以使用两种Hints.FULL 和 ROWID索引FULL hint 告诉ORACLE使用全表扫描的方式访问指定表.索引hint 告诉ORACLE使用基于索引的扫描方式.你不必说明具体的索引名称 在不使用hint的情况下,以上的查询应该也会使用索引,然而,如果该索引的重复
24、值过多而你的优化器是CBO,优化器就可能忽略索引.在这种情况下,你可以用INDEX hint强制ORACLE使用该索引.ORACLE hints 还包括ALL_ROWS,FIRST_ROWS,RULE,USE_NL,USE_MERGE,USE_HASH 等等参考文件sql语句优化.doc,识别低效执行的SQL语句,用下列SQL工具找出低效SQL:,用EXPLAIN PLAN 分析SQL语句,EXPLAIN PLAN 是一个很好的分析SQL语句的工具,它甚至可以在不执行SQL的情况下分析语句.通过分析,我们就可以知道ORACLE是怎么样连接表,使用什么方式扫描表(索引扫描或全表扫描)以及使用到的
25、索引名称.你需要按照从里到外,从上到下的次序解读分析的结果.EXPLAIN PLAN分析的结果是用缩进的格式排列的,最内部的操作将被最先解读,如果两个操作处于同一层中,带有最小操作号的将被首先执行.NESTED LOOP是少数不按照上述规则处理的操作,正确的执行路径是检查对NESTED LOOP提供数据的操作,其中操作号最小的将被最先处理.,用EXPLAIN PLAN 分析SQL语句,通过实践,感到还是用SQLPLUS中的SET TRACE 功能比较方便.举例:SQL list 1 SELECT*2 FROM SCdept,emp 3*WHERE emp.deptno=dept.deptnoS
26、QL set autotrace traceonly/*traceonly 可以不显示执行结果*/SQL/14 rows selected.,用EXPLAIN PLAN 分析SQL语句,Execution Plan-0 SELECT STATEMENT Optimizer=CHOOSE 1 0 NESTED LOOPS 2 1 TABLE ACCESS(FULL)OF EMP 3 1 TABLE ACCESS(BY INDEX ROWID)OF DEPT 4 3 INDEX(UNIQUE SCAN)OF PK_DEPT(UNIQUE),用EXPLAIN PLAN 分析SQL语句,Statist
27、ics-0 recursive calls 2 db block gets 30 consistent gets 0 physical reads 0 redo size 2598 bytes sent via SQL*Net to client 503 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts(memory)0 sorts(disk)14 rows processed,用EXPLAIN PLAN 分析SQL语句,通过以上分析,可以得出实际的执行步骤是:1.TABLE ACCESS(FULL)OF EMP 2.INDEX(UNIQUE SCAN)OF PK_DEPT(UNIQUE)3.TABLE ACCESS(BY INDEX ROWID)OF DEPT4.NESTED LOOPS(JOINING 1 AND 3)注:目前许多第三方的工具如TOAD和ORACLE本身提供的工具如OMS的SQL Analyze都提供了极其方便的EXPLAIN PLAN工具.也许喜欢图形化界面的朋友们可以选用它们.,
