SQL优化

SQL优化的实质就是在结果正确的前提下，用优化器可以识别的语句，充份利用索引，执行过程中访问尽量少的数据块，减少表扫描的I/O次数，尽量避免全表扫描和其他额外开销。

oracle数据库常用的两种优化器：RBO（rule-based-optimizer）和CBO(cost-based-optimizer)。目前更多地采用CBO(cost-based-optimizer)基于开销的优化器。在CBO方式下，Oracle会根据表及索引的状态信息来选择计划；在RBO方式下，Oracle会根据自己内部设置的一些规则来决定选择计划，例如oracle会根据以下优先级来选择执行计划（越靠前，rank越低，越快）

一，      尽量少用IN操作符

基本上所有的IN操作符都可以用EXISTS代替，在选择IN或EXIST操作时，要根据主子表数据量大小来具体考虑，虽然说并不是EXISTS总是比IN好，如果主查询数据量比较大，且子查询结果可以将主查询数据通过索引快速过滤
而子查询的数据量不是很大，即便进行排序合并性能也不成问题，那么Exists的效率相对会减弱，IN的效率相对会提升，到一定程度时，IN的效率比EXISTS的效率会高，但是这个是ORACLE内部控制的。

二，      尽量用NOT EXISTS或者外连接替代NOT IN操作符 （因为NOT IN不能应用表的索引 ）

因为NOT IN不能应用表的索引，请尽量不要使用not in(它会调用子查询)，而尽量使用not exists(它会调用关联子查询)

三，      尽量不用“<>”或者“!=”操作符 （不等于操作符是永远不会用到索引的）

不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。比如：a<>0 改为 a>0 or a<0

四，      在设计表时，把索引列设置为NOT NULL

判断字段是否为空一般是不会应用索引的，因为B树索引是不索引空值的。

五，      尽量不用通配符“%”或者“_”作为查询字符串的第一个字符 （当通配符“%”或者“_”作为查询字符串的第一个字符时，索引不会被使用）

当通配符“%”或者“_”作为查询字符串的第一个字符时，索引不会被使用。比如用T表中Column1 LIKE „T00%‟ 这个条件会产生全表扫描，如果改成Column1 ‟X5400%‟ OR Column1 LIKE ‟B5400%‟ 则会利用Column1的索引进行两个范围的查询，性能肯定大大提高。

六，      Where子句中避免在索引列上使用计算

如果索引不是基于函数的，那么当在Where子句中对索引列使用函数时，索引不再起作用。因此Where子句中避免在索引列上使用计算。

比如： substr(no,1,4)=‟5400‟，优化处理：no like „5400%‟

trunc(hiredate)=trunc(sysdate)，优化处理：hiredate >=trunc(sysdate) and hiredate <trunc(sysdate+1)

七，      用“>=”替代“>”

大于或小于操作符一般情况下是不用调整的，因为它有索引就会采用索引查找，但有的情况下可以对它进行优化，如一个表有100万记录，一个数值型字段A， 30万记录的A=0，30万记录的A=1，39万记录的A=2，1万记录的A=3。那么执行A>2与A>=3的效果就有很大的区别了，因为 A>2时ORACLE会先找出为2的记录索引再进行比较，而A>=3时ORACLE则直接找到=3的记录索引

八， 利用SGA共享池，避开parse阶段 （将SQL的字符串及格式写得完全相同则ORACLE只会分析一次）

同一功能同一性能不同写法SQL的影响

如一个SQL在A程序员写的为 Select * from zl_yhjbqk

B程序员写的为 Select * from dlyx.zl_yhjbqk（带表所有者的前缀）

C程序员写的为 Select * from DLYX.ZLYHJBQK（大写表名）

D程序员写的为 Select * from DLYX.ZLYHJBQK（中间多了空格）

以上四个SQL在ORACLE分析整理之后产生的结果及执行的时间是一样的，但是从ORACLE共享内存SGA的原理，可以得出ORACLE对每个SQL 都会对其进行一次分析，并且占用共享内存，如果将SQL的字符串及格式写得完全相同则ORACLE只会分析一次，共享内存也只会留下一次的分析结果，这不仅可以减少分析SQL的时间，而且可以减少共享内存重复的信息，ORACLE也可以准确统计SQL的执行频率。

不同区域出现的相同的Sql语句要保证查询字符完全相同，建议经常使用变量来代替常量，以尽量使用重复sql代码，以利用SGA共享池，避开parse阶段，防止相同的Sql语句被多次分析，提高执行速度。

因此使用存储过程，是一种很有效的提高share pool共享率，跳过parse阶段，提高效率的办法。

九，      WHERE后面的条件顺序要求，表连接语句写在最前，可以过滤掉最大数量记录的条件居后。

WHERE后面的条件，表连接语句写在最前，可以过滤掉最大数量记录的条件居后。

比如：

Select * from zl_yhjbqk where dy_dj = '1KV以下' and xh_bz=1

Select * from zl_yhjbqk where xh_bz=1 and dy_dj = '1KV以下'

以上两个SQL中dy_dj（电压等级）及xh_bz（销户标志）两个字段都没进行索引，所以执行的时候都是全表扫描，第一条SQL的dy_dj = '1KV以下'条件在记录集内比率为99%，而xh_bz=1的比率只为0.5%，在进行第一条SQL的时候99%条记录都进行dy_dj及xh_bz的比较，而在进行第二条SQL的时候0.5%条记录都进行dy_dj及xh_bz的比较，以此可以得出第二条SQL的CPU占用率明显比第一条低。

十，      使用表的别名，并将之作为每列的前缀

当在Sql语句中连接多个表时，使用表的别名，并将之作为每列的前缀。这样可以减少解析时间

十一，          进行了显式或隐式的运算的字段不能进行索引

比如： ss_df+20>50，优化处理：ss_df>30 „X‟||hbs_bh>‟X5400021452‟，优化处理：hbs_bh>‟5400021542‟ sk_rq+5=sysdate，优化处理：sk_rq=sysdate-5 hbs_bh=5401002554，优化处理：hbs_bh=‟ 5401002554‟，注：此条件对hbs_bh 进行隐式的to_number转换，因为hbs_bh字段是字符型。

十二，          用UNION ALL代替UNION

UNION是最常用的集操作，使多个记录集联结成为单个集，对返回的数据行有唯一性要求，所以oracle就需要进行SORT UNIQUE操作（与使用distinct时操作类似），如果结果集又比较大，则操作会比较慢； UNION ALL操作不排除重复记录行，所以会快很多，如果数据本身重复行存在可能性较小时，用union all会比用union效率高很多！

十三，          其他操作

尽量使用packages： Packages在第一次调用时能将整个包load进内存，对提高性能有帮助。

尽量使用cached sequences 来生成primary key ：提高主键生成速度和使用性能。

很好地利用空间：如用VARCHAR2 数据类型代替CHAR等

使用Sql优化工具：sqlexpert；toad；explain-table；PL/SQL；OEM

十四，          通过改变oracle的SGA的大小

SGA：数据库的系统全局区。

SGA主要由三部分构成：共享池、数据缓冲区、日志缓冲区

1、共享池又由两部分构成：共享SQL区和数据字典缓冲区。共享SQL区专门存放用户SQL命令，oracle使用最近最少使用等优先级算法来更新覆盖；数据字典缓冲区（library cache）存放数据库运行的动态信息。数据库运行一段时间后，DBA需要查看这些内存区域的命中率以从数据库角度对数据库性能调优。通过执行下述语句查看：

select (sum(pins - reloads)) / sum(pins) "Lib Cache" from v$librarycache;

--查看共享SQL区的重用率，最好在90％以上，否则需要增加共享池的大小。

select (sum(gets - getmisses - usage - fixED)) / sum(gets) "Row Cache" from v$rowcache;

--查看数据字典缓冲区的命中率，最好在90％以上，否则需要增加共享池的大小。

2、数据缓冲区：存放sql运行结果抓取到的data block；

SELECT name, value FROM v$sysstat WHERE name IN ('db block gets', 'consistent gets','physical reads');

--查看数据库数据缓冲区的使用情况。查询出来的结果可以计算出来数据缓冲区的使用命中率＝1 - ( physical reads / (db block gets + consistent gets) )。命中率应该在90％以上，否则需要增加数据缓冲区的大小。

3、日志缓冲区：存放数据库运行生成的日志。

select name,value from v$sysstat where name in ('redo entries','redo log space requests');

--查看日志缓冲区的使用情况。查询出的结果可以计算出日志缓冲区的申请失败率：申请失败率＝requests/entries，申请失败率应该接近于0，否则说明日志缓冲区开设太小，需要增加ORACLE数据库的日志缓冲区。

最快删除重复数据的方法：

DELETE FROM   EMP E
      WHERE   E.ROWID > (SELECT   MIN (X.ROWID)
                           FROM   EMP X
                          WHERE   X.EMP_NO = E.EMP_NO);