Oracle之sql语句优化

项目中虽然使用了orm映射，但Oracle系统的优化还是很有价值的，这里从sql语句的角度对常用的语句做下总结。1、from字段中的优化：Oracle安照从右到左的顺序加载表数据，应该把可以排除数据最多的表放到后面（基础表）。比如，在关联查询中，把课程表放到后面，成绩表放到前面，因为课程表数据一般比较少，关联的时候可以快速的过滤掉一些成绩数据。2、where中的优化：对可以过滤数据最多的，放到后面，原理也是Oracle执行从下到上（从右到左）的顺序。3、使用列名替代*：省去从字典表中解析的过程：Oracle执行sql语句过程：在共享池中搜索sql语句是否已经存在验证sql是否语法精确执行数据字典验证表和列的定义获取对象的分析锁，以便在语句的分析过程中对象的定义不会改变检查用户是否具有相应的操作权限确定语句最佳执行计划将语句和执行方案保存到共享的sql区。4、使用？参数形式：尽量不要使用拼接字串的方式，因为带有？的参数形式会缓存分析结果，省去上面很多步骤。5、使用decode函数：decode不会重复扫描相同记录或重复连接相同的表，减少表的扫描次数。6、使用truncate替代delete：delete：逐条删除，并保存回滚还原点。DML（数据操作语言insert，update，delete，merge）truncate不会保存回滚点，是DDL（数据定义语言，如drop，alter）在oracle里DML是可以rollback的，而DDL是不可以的如果数据量大，差别很大的。7、尽量多用commit：Oracle只有在commit后才会提交（区别于sqlserver），如果没有提交，会在内存中保存很多数据。commit后释放的资源有：回滚上段用于恢复数据库的记录信息被程序语句获得的锁Redo log buffer中的空间为管理上述3种资源的内部花费8、使用where语句替代having子句：在分组或者配合group的时候会使用having。where会直接过滤掉数据，使用having往往会配合group，检索出数据后，会带着数据进行排序、统计等。9、on/where/having排序使用顺序：on：做的是数据映射，在映射时，把没用的数据直接过滤掉了where：先进行一个全表的搜索，之后再进行数据筛选where又比having要快，having中带的垃圾数据同样也做了运算10、减少对表的查询：在含有子查询的sql语句中，要特别注意减少对表的查询。Select tab_name from tables where （tab_name,db_ver）=（select tab_name,db_ver from tab_columns where version=604）11、使用表别名：减少对sql语句解析的时间并减少由多个表相同的Column名歧义引起的语法错误如果不使用别名，会去字典表中查找，判断是否有列名歧义。12、使用exists替代in not exists替代not in：Oracle采用命中即返回的方式，在多表链接查询时，如果使用in会导致子查询的表全表遍历，并排序、合并，这时候可以使用外链接或not exists 替代。13、识别低效率执行的语句：各种sql优化的图形工具层出不穷，但可以写出自己的sql工具来解决问题Select executions, disk_reads, buffer_gets, round（（buffer_gets-disk_reads）/buffer_gets,2） hit_radio,Round（disk_reads/executions,2） reads_per_run,Sql_text From V$sqlarea Where executions>0 and buffer_gets>0 And （buffer_gets-disk_reads）/buffer_gets<0.8 Order by 4 desc;14、使用索引：使用于查询的表，提供了主键的唯一性验证，long或者long raw数据类型，几乎可以索引所有列定期重建索引，在删除和修改多的表，不适用索引，原因，不解释了。注意，并不是创建了索引后就一定会走索引，使用索引的时候，不能使用索引进行一些计算，否则失效使用索引快于全表扫描，在多表连接使用索引提高效率。避免在索引上使用not：会停止索引（not表示的是：没有什么，而索引表示的是：有什么）用exitsts替代distinct：在提交一个包含一对多表信息（部门和雇员表）的查询时使用。原因：查找即返回原理避免在索引列上使用计算：低效：Select * from dept where sal * 12 >25000高效：Select * from dept where sal> 25000/12避免在索引列上使用is null 和 is not null：因为索引不会对null数据类型进行索引，索引只是记录有什么对于单列索引，不会进行索引对于符合索引，如果所有列多为空，不进行索引，只要有一个列不空，就索引。在Oracle中空不等于空，所以，就会插入若干条相同键值的记录，而他们的值都是空，而空值不进行索引，所以，当进行空值比较时，会使用Oracle，停止使用该索引。低效：Select …. From dep where dep_code is not null;高效：Select …. From dep where dep_code >=0;这里也会过滤掉null列总是使用索引的第一个列：如果索引建立在多个列上，只有咋他的第一个列，被where自居引用时，优化器才会选择使用该索引，当仅使用索引的第二个列是，优化器会忽略索引，使用全表索引。15、>=是直接定位：如果使用 >还需要一个判断的过程。16、使用union替代where字句中的or：对索引列使用or会导致全表扫描，针对多个索引列有效17、在oracle8i下，两者执行路径似乎相同，但能用in的就别用or。18、使用union-all：union-all:不排序，查询所有的，不过滤重复的nuion：排序（优化sort_raea_size这块内存），过滤重复的。19、order by 用where 限定：order by名中索引的条件比较苛刻排序列必须包含在相同的索引中，并保持索引中的排列顺序，Order by中所有列不能定义为空。20、需要当心的where子句：！=将不使用索引||字符连接函数，会停用索引+ - * / 会停用索引相同的索引列不能互相比较，否则会启用全表扫描21、使用大写：Oracle中会把所有的语句转换成大写，有些内部表，如，查询表名时，判断某个表是否存在，如果是大写有效，小写就是无效的22、根据磁盘读写速率调整块的大小：一个Oracle数据库中，表空间、段、区、数据块的概念，可以根据服务器的I/O性能调整块的大小。总结：上面的点分的很细，他们都是根据Oracle的内部原理总结出的常用规律，所以，掌握原理要比记住这些跳跳框架更加重要，常用的规则有：Oracle按照从右到左，从下至上、由外到内的执行顺序在检索数据的时候，往往遵循查到即返回的原则索引，记录的是有哪些数据，所以，不要在索引列上直接使用排除不存在条件的查询，也不要在索引上进行计算Oracle认为空不等于空where命中索引的几率比较高，通常情况下，优先选择使用where。由Oracle索引来理解ArcSDE索引 http://www.linuxidc.com/Linux/2012-10/72184.htmOracle索引技术之如何建立最佳索引 http://www.linuxidc.com/Linux/2012-09/70996.htmOracle索引列NULL值引发执行计划该表的测试示例 http://www.linuxidc.com/Linux/2012-09/69938.htmOracle索引主键影响查询速度 http://www.linuxidc.com/Linux/2011-12/48588.htmOracle索引扫描 http://www.linuxidc.com/Linux/2012-03/56644.htm更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址