Oracle SQL的优化规则.docx

1、Oracle SQL的优化规则Oracle SQL的优化规则一、in和existsin 是把外表和内表作hash 连接，而exists是对外表作loop循环，每次loop循环再对内表进行查询。一直以来认为exists比in效率高的说法是不准确的。如果查询的两个表大小相当，那么用in和exists差别不大。如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in：例如：表A（小表），表B（大表）1：select * from A where cc in (select cc from B) 效率低，用到了A表上cc列的索引；select * from A where e

2、xists(select cc from B where cc=A.cc) 效率高，用到了B表上cc列的索引。2：相反的select * from B where cc in (select cc from A) 效率高，用到了B表上cc列的索引；select * from B where exists(select cc from A where cc=B.cc) 效率低，用到了A表上cc列的索引。not in 和not exists如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引；而not extsts 的子查询依然能用到表上的索引。所以无论那个表大，用not exi

3、sts都比not in要快。in 与=的区别:select name from student where name in (zhang,wang,li,zhao); 与 select name from student where name=zhang or name=li or name=wang or name=zhao 的结果是相同的。例子：索引示例图1-in语句(用到了主表tbl_user的id字段的聚集索引)create cluster index idx_user on tbl_user(id)select name from tbl_userwhere id in (selec

4、t user_id from tbl_passport)-exists语句(用到了子表tbl_passport的user_id字段的索引)create index idx_user_id on tbl_passport(user_id)select name from tbl_user awhere exists (select id from tbl_passport where a.id = user_id)-not in语句(不能使用索引，内外表皆为全表扫描)select name from tbl_userwhere id not in (select user_id from tbl

5、_passport)-not exists语句(用到了子表tbl_passport的user_id字段的索引)select name from tbl_user awhere not exists (select id from tbl_passport where a.id = user_id)二、用、替代、!=不用“”或者“!=”操作符。对不等于操作符的处理会造成全表扫描，可以用“”代替 不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。 推荐方案：用其它相同功能的操作运算代替，如： 1）a0 改为 a0 or a0 2）a 改为 a例子：-不会用到tbl_user的id字

6、段上的索引，造成全表扫描select name from tbl_userwhere id9-用和代替或!=，会用到id字段的索引，不会造成全表扫描select name from tbl_userwhere id9 or id0 或a等。 2）不允许字段为空，而用一个缺省值代替空值，如业扩申请中状态字段不允许为空，缺省为申请。 3） 建立位图索引（有分区的表不能建，位图索引比较难控制，如字段值太多索引会使性能下降，多人更新操作会增加数据块锁的现象）。四、Like通配符当通配符“%”或者“_”作为查询字符串的第一个字符时，索引不会被使用。LIKE 操作符可以应用通配符查询，里面的通配符组合可能

7、达到几乎是任意的查询，但是如果用得不好则会产生性能上的问题，如LIKE %5400% 这种查询不会引用索引，而LIKE X5400%则会引用范围索引。一个实际例子：用YW_YHJBQK表中营业编号后面的户标识号可来查询营业编号 YY_BH LIKE %5400% 这个条件会产生全表扫描，如果改成YY_BH LIKE X5400% OR YY_BH LIKE B5400% 则会利用YY_BH的索引进行两个范围的查询，性能肯定大大提高。例子：-第一个通配符为%或_，则不会使用索引，全表扫描select name from tbl_userwhere name like %changxiao%-会使

8、用name字段上的索引select name from tbl_userwhere name like changxiao%五、where字句中的索引字段使用函数或计算如果索引不是基于函数的，那么当在Where子句中对索引列使用函数时，索引不再起作用。Where子句中避免在索引列上使用计算，否则将导致索引失效而进行全表扫描。 对数据类型不同的列进行比较时，会使索引失效。例子：-对where字句中的索引字段pass_name使用函数则不会使用索引，造成全表扫描select pass_name from tbl_passportwhere substr(pass_name,1,9) = chang

9、xiao-替代方案select pass_name from tbl_passportwhere pass_name like changxiao%-对where字句中的索引字段id使用计算则不会使用索引，造成全表扫描select name from tbl_userwhere id+1=2-替代方案select name from tbl_userwhere id = 2-1六、用=替代大于或小于操作符一般情况下是不用调整的，因为它有索引就会采用索引查找，但有的情况下可以对它进行优化，如一个表有100万记录，一个数值型字段A， 30万记录的A=0，30万记录的A=1，39万记录的A=2，1万

10、记录的A=3。那么执行A2与A=3的效果就有很大的区别了，因为 A2时ORACLE会先找出为2的记录索引再进行比较，而A=3时ORACLE则直接找到=3的记录索引。 推荐方案：用“=”替代“”。索引示例图2例子:-造100万条数据存放到临时表create table temp2_tbl_passport as select * from tbl_passportinsert into temp2_tbl_passport select * from temp2_tbl_passport-在a字段上创建索引create index idx_a on temp2_tbl_passport(a)-测

11、试效率SQL desc temp2_tbl_passportSQL clear buffer;SQL set timing on;SQL select id form temp2_tbl_passport where a2; . . . 已用时间: 00: 00: 11.11SQL clear buffer;SQL select id from temp2_tbl_passport where a=3; . . . 已用时间: 00: 00: 10.21七、UNION操作符1、UNION和UNION ALLUNION指令的目的是将两个SQL语句的结果合并起来。从这个角度看，UNION和JOIN

12、有些类似，因为这两个指令都可以由多个表格中获取资料。UNION的一个限制是两个SQL语句所产生的栏位需要是同样的资料种类。另外，当我们用UNION这个指令时，我们只会看到不同的资料值(类似select distinct)。例子：-union(合并且去除重复)select id from tbl_userunionselect user_id from tbl_passportUNION ALL这个指令的目的也是将两个SQL语句的结果合并在一期，但只是简单的合并。UNION ALL和UNION不同之处在于UNION ALL会将每一笔符合条件的资料都列出来，无论资料值有无重复。例子：-union

13、all(简单合并且不去除重复)select id from tbl_userunion allselect user_id from tbl_passport推荐方案：在不需要考虑重复记录合并时候用Union All来代替Union，因为UNION ALL操作只是简单的将两个结果合并后就返回，而UNION在合并后会先取出两个表的结果，再用排序空间进行排序删除重复的记录。2、INTERSECT操作符和UNION指令类似，INTERSECT也是对两个SQL语句所产生的结果做处理的。不同的地方是，UNION基本上是一个OR(如果这个值存在于第一句或是第二句，它就会被选出)，而INTERSECT则比较

14、像AND(这个值要存在于第一句和第二句才会被选出)。UNION是联集(合并)，而INTERSECT是交集。 例子： -intersect(取两个结果集交集)select name from tbl_user awhere exists(select id from tbl_passportwhere a.id = user_idintersectselect pass_id from tbl_pass_app)3、MINUS操作符MINUS指令是运用在两个SQL语句上。它先找到第一个SQL语句所产生的结果，然后看这些结果有没有在第二个SQL语句的结果中。如果有的话，那这一笔资料就被去除，而不会

15、在最后的结果中出现。如果第二个SQL语句所产生的结果并没有存在与第一个SQL语句所产生的结果内，那这笔资料就被抛弃。例子：-minus(取两个结果集补集)select name from tbl_user awhere exists(select id from tbl_passportwhere a.id = user_idminusselect pass_id from tbl_pass_app)八、SQL书写的影响共享SQL语句可以提高操作效率, 同一功能同一性能不同写法SQL的影响。SQL：A程序员写的为 Select * from zl_yhjbqk B程序员写的为 Select *

16、 from dlyx.zl_yhjbqk（带表所有者的前缀） C程序员写的为 Select * from DLYX.ZLYHJBQK（大写表名） D程序员写的为 Select * from DLYX.ZLYHJBQK（中间多了空格）以上四个SQL在ORACLE分析整理之后产生的结果及执行的时间是一样的，但是从ORACLE共享内存SGA的原理，可以得出ORACLE对每个SQL 都会对其进行一次分析，并且占用共享内存，如果将SQL的字符串及格式写得完全相同则ORACLE只会分析一次，共享内存也只会留下一次的分析结果，这不仅可以减少分析SQL的时间，而且可以减少共享内存重复的信息，ORACLE也可以

17、准确统计SQL的执行频率。 推荐方案：不同区域出现的相同的Sql语句，要保证查询字符完全相同，以利用SGA共享池，防止相同的Sql语句被多次分析。九、where后面的条件顺序影响Oracle从右到左处理Where子句中多个查询条件，所以表连接语句应写在其他Where条件前，可以过滤掉最大数量记录的条件必须写在Where子句的末尾。例子:-where后面的条件顺序影响-先执行连接再进行过滤(效率低)select a.id from tbl_user a join temp_tbl_passport bon b.user_id=8 and a.id = b.user_id-10.25s-先过滤再执

18、行连接(效率高)select a.id from tbl_user a join temp_tbl_passport bon a.id = b.user_id and b.user_id=8-10.09s一十、查询表顺序的影响Oracle从右到左处理From子句中的表名，所以在From子句中包含多个表的情况下，将记录最少的表放在最后。（只在采用RBO优化时有效) 在FROM后面的表中的列表顺序会对SQL执行性能影响，在没有索引及ORACLE没有对表进行统计分析的情况下ORACLE会按表出现的顺序进行链接，由此因为表的顺序不对会产生十分耗服务器资源的数据交叉。（注：如果对表进行了统计分析， OR

19、ACLE会自动先进小表的链接，再进行大表的链接）。例子:-查询表顺序的影响-小表在左，大表在右(效率低)select a.id from tbl_user a , temp_tbl_passport bwhere a.id = b.user_id-1.31.51m-大表在左，小表在右(效率高)select a.id from temp_tbl_passport b , tbl_user awhere a.id = b.user_id-46.95s一十一、order by语句中使用非索引列Order By语句中的非索引列会降低性能，可以通过添加索引的方式处理。严格控制在Order By语句中使用

20、表达式。一十二、使用表别名当在Sql语句中连接多个表时，使用表的别名，并将之作为每列的前缀。这样可以减少解析时间。一十三、count(*)、count(1)和count(col)count(*)和count(1)的执行效率是完全一样的, count(*)只是返回表中行数，因此Oracle在处理count(*)的时候只需要找到属于表的数据块块头，然后计算一下行数就行了，而不用去读取里面数据列的数据。而对于 count(col)就不一样了，为了去除col列中包含的NULL行，Oracle必须读取该col的每一行的值，然后确认下是否为NULL，然后在进行计数。因此count(*)应该是比count(

21、col)快的。例子：-count(*)select count(*) from temp2_tbl_passport-0.03s-count(1)select count(1) from temp2_tbl_passport-0.03s-count(col)select count(a) from temp2_tbl_passport-0.03s一十四、尽量避免使用select *避免使用Select *,因为系统需要去帮你将*转换为所有的列名，这个需要额外去查询数据字典。一十五、创建视图视图是基于一个表或多个表或视图的逻辑表，本身不包含数据，通过它可以对表里面的数据进行查询和修改。视图是存储

22、在数据字典里的一条select语句。通过创建视图可以提取数据的逻辑上的集合或组合。视图的优点：1.对数据库的访问，因为视图可以有选择性的选取数据库里的一部分。2.用户通过简单的查询可以从复杂查询中得到结果。3.维护数据的独立性，试图可从多个表检索数据。4.对于相同的数据可产生不同的视图。视图分为简单视图和复杂视图：1、简单视图只从单表里获取数据，复杂视图从多表；2、简单视图不包含函数和数据组，复杂视图包含；3、简单视图可以实现DML操作，复杂视图不可以。视图的创建：CREATE OR REPLACE FORCE|NOFORCE VIEW view_name(alias, alias.)AS s

23、ubqueryWITH CHECK OPTION CONSTRAINT constraintWITH READ ONLY其中：OR REPLACE ：若所创建的试图已经存在，ORACLE自动重建该视图；FORCE：不管基表是否存在ORACLE都会自动创建该视图；NOFORCE：只有基表都存在ORACLE才会创建该视图：alias：为视图产生的列定义的别名；subquery：一条完整的SELECT语句，可以在该语句中定义别名；WITH CHECK OPTION ：插入或修改的数据行必须满足视图定义的约束；WITH READ ONLY：该视图上不能进行任何DML操作。例如： CREATE OR R

24、EPLACE VIEW dept_sum_vw(name,minsal,maxsal,avgsal)AS SELECT d.dname,min(e.sal),max(e.sal),avg(e.sal)FROM emp e,dept dWHERE e.deptno=d.deptnoGROUP BY d.dname;视图的定义原则：1.视图的查询可以使用复杂的SELECT语法，包括连接/分组查询和子查询；2.在没有WITH CHECK OPTION和 READ ONLY 的情况下，查询中不能使用ORDER BY 子句；3.如果没有为CHECK OPTION约束命名，系统会自动为之命名，形式为SYS

25、_Cn；4.OR REPLACE选项可以不删除原视图便可更改其定义并重建，或重新授予对象权限。例子：-创建复杂视图create or replace view view_user_appasselect a.name , b.app_name from tbl_user a , tbl_app bwhere exists (select id from tbl_passport c where c.user_id = a.id and exists(select id from tbl_pass_app d where d.pass_id = c.id and d.app_id = b.id)

26、union allselect a.name , null as app_name from tbl_user awhere exists (select id from tbl_passport b where b.user_id = a.id and not exists(select id from tbl_pass_app c where c.pass_id = b.id)order by name-查询视图select count(1) from view_user_app一十六、附件Oracle存储过程编写经验和优化措施：1、开发人员如果用到其他库的Table或View，务必在当前

27、库中建立View来实现跨库操作，最好不要直接使用“databse.dbo.table_name”，因为sp_depends不能显示出该SP所使用的跨库table或view，不方便校验。 2、开发人员在提交SP前，必须已经使用set showplan on分析过查询计划，做过自身的查询优化检查。 3、高程序运行效率，优化应用程序，在SP编写过程中应该注意以下几点： a) SQL的使用规范： i. 尽量避免大事务操作，慎用holdlock子句，提高系统并发能力。 ii. 尽量避免反复访问同一张或几张表，尤其是数据量较大的表，可以考虑先根据条件提取数据到临时表中，然后再做连接。 iii. 尽量避免使

28、用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该改写；如果使用了游标，就要尽量避免在游标循环中再进行表连接的操作。 iv. 注意where字句写法，必须考虑语句顺序，应该根据索引顺序、范围大小来确定条件子句的前后顺序，尽可能的让字段顺序与索引顺序相一致，范围从大到小。 v. 不要在where子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。 vi. 尽量使用exists代替select count(1)来判断是否存在记录，count函数只有在统计表中所有行数时使用，而且count(1)比count(*)更有效率。 vii. 尽量使用“=”

29、，不要使用“”。viii. 注意一些or子句和union子句之间的替换 。 ix. 注意表之间连接的数据类型，避免不同类型数据之间的连接。 x. 注意存储过程中参数和数据类型的关系。 xi. 注意insert、update操作的数据量，防止与其他应用冲突。如果数据量超过200个数据页面（400k），那么系统将会进行锁升级，页级锁会升级成表级锁。 b) 索引的使用规范： i. 索引的创建要与应用结合考虑，建议大的OLTP表不要超过6个索引。 ii. 尽可能的使用索引字段作为查询条件，尤其是聚簇索引，必要时可以通过index index_name来强制指定索引 iii. 避免对大表查询时进行table scan，必要时考虑新建索引。 iv. 在使用索引字段作为条件时，如果该索引是联合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用。 v. 要注意索引的维护，周期性重建索引，重新编译存储过程。 c) tempdb的使用规范： i. 尽量避免使用distinct、o