ORACLE SQL语句优化，性能优化，效率优化。

SQL语句优化概述

SQL语句优化是提高性能的重要环节

• 开发人员不能只注重功能的实现，不管性能如何
• 开发人员不能把Oracle当成一个黑盒子，必须了解其结构、处理SQL和数据的方法
• 必须遵守既定的开发规范
• 为经过SQL语句优化的模块不要上线

SQL语句优化的一般性原则

• 目标
  1.减少服务器资源消耗（主要是磁盘IO）
• 设计方面
  1.尽量以来Oracle的优化器，并未其提供条件
  2.合适的索引，索引的双重效应，列的选择性
• 编码方面
  1.利用索引，避免大表FULL TABLE SCAN
  2.合理使用临时表
  3.避免写过于复杂的SQL，不一定非要一个SQL解决问题
  4.在不影响业务的前提下减少事务的粒度

常见SQL优化方法

共享SQL语句

  为了不重复解析相同的SQL语句，在第一次解析之后，Oracle将SQL语句存放在内存中。这块位于系统全局区域SGA(systemglobal area)的共享池中的内存可以被所有的数据库用户共享。因此，当你执行一个SQL语句(有时被称为一个游标)时，如果它和之前执行过的语句完全相同，Oracle就能很快获得已经被解析的语句以及最好的执行方案。Oracle的这个功能大大的提高了SQL的执行性能并节省了内存的使用

  Oracle对两者采取的是一种严格匹配策略，要达成共享。SQL语句必须完全相同（包括空格、换行等）。能够使用共享的语句必须满足三个条件：
1.字符集的比较。当前被执行的语句和共享池中的语句必须完全相同。
 例如：SELECT * FROM ATABLE;
    和下面的每一个SQL语句都不同：
    SELECT *from ATABLE;  Select * From Atable;
2.语句所指对象必须完全相同，即两条SQL语句操作的数据库对象必须相同。
3.语句中必须使用相同命名的绑定变量
 如第一组的两个SQL语句是相同的，可以共享；而第二组中两个语句不同，即使在运行时赋予不同的绑定变量与相同的值：

• 第一组 select pin,name from people where pin = :blk1.pin;
    select pin,name from people where pin = :blk1.pin;
• 第二组 select pin,name from people where pin = :blk1.ot_jnd;
    select pin,name from people where pin = :blk1.ov_jnd;

减少访问数据库的次数

  当执行没条SQL语句时，Oracle在内部执行许多工作：解析SQL语句，估算索引的利用率，绑定变量，读数据块等。因此，减少访问数据库的次数，能实际减少Oracle的工作量。
  即一个SQL能完成的事，就不要写多个SQL。

$低效：$
select aname from amp a where a.amp_no=341;
select ename from emp e where e.emp_no-291;

$高效：$
select a.aname,e.ename from amp a,emp e where a.amp_no=341 OR e.emp_no=291;

SELECT语句中避免使用*

$低效：$
select * from rs_dept;
引用*是一个方便的方法，但这是一个非常低效的方法。实际上，Oracle在解析的过程中，会将*依次转换成所有的列名，这个工作是通过查询数据字典完成的，这以为这将耗费更多的时间。

$高效：$
select dept_id,dept_code,dept_name from rs_dept;
只提取你所要是用的列；

WHERE子句中的连接顺序

  Oracle采用自下而上的顺序解析WHERE子句，因此表之间的连接必须下载其它WHERE条件之前，那些可以过滤最大数量记录的条件必须写在WHERE子句的末尾。

$低效：$

select ename from emp e
where sql>50000 and job='MANAGER'
and 25<(select count(*) from emp where mrg=e.empno);

$高效：$

select ename from emp e
where 25<(select count(*) from emp where mrg=e.empno)
and sql>50000 and job='MANAGER';

利用DECODE函数来减少处理时间

  使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表。

$低效：$

select count(*),sum(sql) from rmp where emp_no=20 and ename like '李%';
select count(*),sum(sql) from rmp where emp_no=30 and ename like '李%';

$高效：$

select count(decode(emp_no,20,'*',null)),sum(decode(emp_no,20,sal,null)),
       count(decode(emp_no,30,'*',null)),sum(decode(emp_np,30,sal,null))
from emp where ename like '李%'

删除重复记录

  最高效的删除重复记录方法（因为使用了ROWID）。

$高效：$

delete from emp2 e
where e.rowid>(select min(x.rowid) from emp2 x where x.empno=e.empno)

--empno字段重复的多余记录查询：
select * from emp2 e
where e.rowid>(select min(x.rowid) from emp2 x where x.empno=e.empno)

--统计重复的empno及条数
select empno,count(*) from emp2
group by empno having count(*)>1

用truncate代替delete（当需要删除全表数据时建议使用truncate）

  当删除表中的记录时，在通常情况下，回滚段用来存放可以被恢复的消息，如果你没有COMMIT事务，Oracle会将数据恢复到未删除前的状态（准确地说是恢复到执行删除命令之前的状态）。
  而当运用truncate时，回滚段不再存放任何可被恢复的消息，命令运行后，数据不能恢复。
  truncate只在删除全表时适用，truncate是DDL，而不是DML。

$低效：$

delete from emp1;

$高效：$

truncate from emp1;

用where子句替换having子句

  避免使用HAVING子句，HAVING只会在检索出所有记录之后才对结果集进行过滤，这个处理需要排序总计等操作。如果通过WHERE子句限制记录的数目，那就能减少这方面的开销。
  HAVING中的条件一般用于对一些集合函数的比较，如COUNT()等，除此之外，一般的条件放入WHERE子句中。

$低效：$

select e.deptno,avg(nvl(e.sal,0)) from emp e
group by dtptno
having deptno!=201 and deptno!=203

$高效：$

select e.deptno,avg(nvl(e.sal,0)) from emp e
where deptno!=201 and deptno!=203
group by deptno

减少对表的查询

  在含有子查询的SQL语句中，要特别注意减少对表的查询。

$低效：$

select empno from emp
where ename=(select ename from emp1 where sta=1)
and tag=(select tag from emp1 where sta=1)

$高效：$

select empno from emp
where (ename,tag)=(select ename,tag,from emp1 where sta=1)

使用表的别名

  当在SQL语句中连接多个表时，请使用表的别名并把别名前缀到每个COLUMN上。这样，就可以减少解析的时间并减少那些由COLUMN歧义引起的语法错误。
  COLUMN歧义指的是由于SQL中不同的表具有相同的COLUMN名称，当SQL语句中出现这个COLUMN时，SQL解析器无法判断这个COLUMN的归属。

$低效：$

select ename,dept,deptno,dname from emp,dept
where emp.deptno=dept.deptno

$高效：$

select e.ename,d.deptno,d.dname from emp e,dept d
where e.deptno=d.deptno

用exists代替in

  在许多基于基础表的查询中，为了满足一个条件，往往需要对另一个表进行联接。在这种情况下，使用exists（或not exists）通常将提高查询的效率。

$低效：$

select * from emp where empno>0
and deptno in (select deptno from dept where loc='M')

$高效：$

select * from emp e where e.empno>0
and exists (select 1 from dept d where e.deptno=d.deptno and loc='M')

用not exists代替not in

  用子查询时，not in子句将执行一个内部的排序和合并。无论在哪种情况下，not in都是最低效的（因为它对子查询中的表执行了一个全表遍历）。为了避免使用not in，我们可以把它改写成外连接或not exists。

$低效：$

select * from emp where deptno not in (select deptno from dept hwhere dept_cat='A')

$高效一：$

select * from emp a,dept b where a.deptno=b.deptno(+)
and b.dept_no is null and b.dept_cat(+)='A'

$最高效：$

select * from emp e
where not exists (select 1 from dept d where d.deptno=e.deptno an dept_cat='A')

用表连接代替exists

  通常来说，采用表连接的方式比exists更有效率。

$低效：$

select * from emp e
where exists (select 1 from dept d where d.deptno=e.deptno and dept_cat='A')

$高效：$

select * from emp e,dept d
where d.deptno=e.deptno and dept_cat='A'

记住：$表连接>exists>in$

用exists代替distinct

  当提交一个包含一对多信息（比如部门表和雇员表）的查询时，避免在select子句中使用distinct，一般可以考虑用exists替换。

$低效：$

select distinct deptno,deptname from emp e,dept d where d.deptno=e.deptno

$高效：$

select deptno,deptname from dept d
where exists (select 1 from emp e where e.deptno=d.deptno)

distinct会引发全表扫描，exists使查询更为迅速，因为关系型数据库管理系统核心模块将在子查询的条件一旦满足后，立刻返回结果。

SQL中索引列使用注意问题

避免在索引列上使用计算或函数

  WHERE子句中，如果索引列是函数的一部分，优化器将不适用索引而使用全表扫描。

$低效：$

select * from emp where sal*12>25000

$高效：$

select * from emp where sal>25000/12

避免在索引列上使用NOT、<>、!=

  通常，我们要避免在索引列上使用NOT，NOT会产生和在索引列上使用函数相同的影响。
  当Oracle遇到NOT，它就会停止使用索引转而执行全表扫描。

$低效：$

select * from emp where sal<>3000

$高效：$

select * from emp where sal<3000 or sal>3000

用>=代替>

  如果dept表中的deptno上有一个索引，看如下的SQL例子，两者的区别在于，后者DBMS将直接跳到第一个deptno等于4的记录而前者将首先定位到deptno等于3的记录并且向前扫描到第一个deptno大于3的记录。

$低效：$

select * from emp where deptno>3

$高效：$

select * from emp where deptno>=4

用UNION代替OR

  通常情况下，用UNION代替WHERE子句中的OR将会起到较好的效果。对索引列使用OR将造成全表扫描。注意，以上规则只针对多个索引列有效。如果COLUMN没有被索引，查询效率没有OR好。
在下面的例子中，loc_id和region上都建有索引。

$低效：$

select * from locations where loc_id=10 or region='mm'

$高效：$

select * from locations where loc_id=10
union
select * from locations where region='mm'

$如果你坚持要用OR，那就需要返回记录最少的索引列写在最前面$

避免在索引列上使用 IS NULL和 IS NOT NULL

  避免在索引中使用任何可以为空的列，Oracle将无法使用该索引。对于单列索引，如果列包含空值，索引中将不存在此记录。对于复合索引，如果每个列都为空，索引中同样不存在此记录。如果至少有一个列不为空，则记录存在于索引中。

$低效：$

select * from emp where empcode is not null

$高效：$

select * from emp where empcode>=0

任何where子句中使用 is null和 is not null 的语句优化器是不会使用索引的。
推荐用其它相同功能的操作运算符代替，如
a is not null 改为 a>0 或 a>' ' 等。

带通配符(%)的like语句

  通配符（%）在搜寻首出现时，优化器是不会使用索引的，而是进行全表查询。
  假设emp表的last_name列建立了索引：

$低效：$

select * from emp where last_name like '%Beill%'

$高效：$

select * from emp where last_name like 'Beill%'

用union all 代替 union（如果可能的话）

  union在进行表联接后会筛选掉重复的记录，所以在表联接后会对所产生的结果集进行排序运算删除重复的记录再返回结果。
  如果用union all代替union，这样排序就不是必要了，效率就会因此得到提高。

$低效：$

select ename from emp
union
select deptname from dept

$高效：$

select ename from emp
union all
select deptname from dept

$注意：UNIONALL将重复输出两个结果集合中相同记录。因此还是需要从业务需求分析使用UNIONALL的可行性。$

总是使用索引的第一个列

  如果索引是建立在多个列上，只有在它的第一个列被where子句引用时，优化器才会选择使用该索引。例如：新建索引create index idxEmp on EMP (deptno,job);

$低效：$

select ename from emp where jobn='OTHER';
select ename from emp where deptno=10 or job='OTHER';

$高效：$

select ename from emp where deptno=10;
select ename from emp where deptno=10 and job='OTHER';

用WHERE代替ORDER BY

  order by 子句只在两种严格的条件下使用索引：
  order by中所有的列必须包含在相同的索引中并保持在索引中的排列顺序
  order by中所有的列必须定义为非空
  假设dept表中为upperdeptno建立了非唯一性的索引

$低效：$

select t.dname,t.upperdeptno from dept t order by t.upperdeptno

$高效：$

select t.dname,t.upperdeptno from dept t where t.upperdeptno >=0

避免出现索引列自动转换

  当比较不同数据类型的数据时，Oracle自动对列进行简单的类型转换。
  假设user_no是一个字符类型的索引列。

$原SQL：$

select user_name from user_files where user_no=109

$被Oracle转换为：$

select user_name from user_files where to_number(user_no)=109

因为内部发生的类型转换，这个索引将不会被用到。

$正确写法：$

select user_name from user_files where user_no='109'

PL/SQL优化

通过临时表处理数据，减少对真实表的频繁操作

典型案例：抄表机上装ATABLE表
  这个过程里基本上都是UPDATE语句，频繁更新BTABLE，更新CTABLE , DTABLE，ETABLE这些达标时也要用到TMPTABLE，而且抄表机上装在月初用的非常多，并发量大。
优化方式：
  建立临时表，在过程一开始就把TMPTABLE数据插入到临时表，然后后面所有的update语句都是围绕临时表进行，减少了对真实表的频繁更新操作，既提高了速度，也减轻了对数据库的压力。

临时表使用注意问题

• 避免频繁创建和删除临时表，减少系统表资源的消耗。
• 在新建临时表时，如果一次性插入数据量很大，那么可以使用select into代替create table，提高速度。如果数据量不大，为了缓和系统表的资源，建议先create table，然后insert。

select ename,job into newemp from emp

• 如果临时表的数据量很大，需要建立索引，那么应该将创建临时表和建立索引的过程放在单独一个子存储过程中，这样才能保证系统能够很好的使用到该临时表的索引。
• 慎用大的临时表与其他大表的连接查询和修改，减低系统表的负担，因为这种操作会在一条语句中多次使用临时表的系统表。

使用%Type声明与字段类型相关的变量

  硬编码的变量大小很可能与数据库中实际大小不符，如果字段的类型、大小等发生变化，还需要到PL/SQL中调整变量。

少用游标

  任何情况下，数据批量处理的效率都是最高的。有时候为了实现业务功能，才会采用游标处理，对数据由批量处理改成分批处理，但执行时间肯定会延长。对于用户常用的业务，一般都要求实时性，对执行时间有要求，这种情况下就要少用游标。

尽量避免使用动态SQL

  动态SQL失去了编译期检查能力，将发生问题的可能性推迟到运行期。动态SQL也不利于优化，因为只有在运行期才能得到完整的SQL语句。

在满足业务需求的基础上尽量多使用COMMIT

  只要有可能，在程序中尽量多使用COMMIT，这样程序的性能得到提高，需求也会因为COMMIT所释放的资源而减少，COMMIT所释放的资源：

1. 回滚段上用于恢复的数据
2. 被程序语句获得的锁
3. REDO LOG BUFFER中的空间
4. Oracle为管理上述3中资源中的内部花费

注意：在使COMMIT时必须要注意事务的完整性，现实中效率和事务完整性往往是鱼和熊掌不可兼得。