(全）关于Oracle中的SQL优化！想要手到擒来！就看这篇文章！分分钟教你如何学会SQL优化！！
大家好，我们都知道目前Oracle是一个广泛使用的数据库管理系统，它提供了强大的SQL语言支持。SQL（Structured Query Language）是一种用于管理关系型数据库的标准化语言，可以用于查询、插入、更新和删除数据等操作。在Oracle中，SQL语句的编写和优化对于提高数据库性能和效率至关重要。
SQL优化技巧大公开（全）
*一、导致性能问题的内在原因*
导致系统性能出现问题从系统底层分析也就是如下几个原因：
CPU占用率过高，资源争用导致等待
内存使用率过高，内存不足需要磁盘虚拟内存
IO占用率过高，磁盘访问需要等待
*二、SQL 优化的核心思想*
SQL 优化实际上就是避免出现“导致性能问题的内在原因”，实际上编写程序，以及性能 问题跟踪应该本着这个核心思想去考虑和解决问题。
PLSQL 程序占用 CPU 的情况
系统解析 SQL 语句执行，会消耗 CPU 的使用
运算（计算）会消耗 CPU 的使用
PLSQL 程序占用内存的情况
读写数据都需要访问内存
内存不足时，也会使用磁盘
PLSQL 程序增大 IO 的情况
读写数据都需要访问磁盘 IO
读取的数据越多，IO就越大
大家都知道 CPU 现在都很高，计算速度非常快；访问内存的速度也很快；但磁盘的访问 相对前两个相比速度就差的非常大了，因此 PLSQL 性能优化的重点也就是减少 IO 的瓶颈， 换句话说就是尽量减少 IO 的访问。
性能的优先级 CPU->内存->IO，影响性能的因素依次递增。根据上面的分析，PLSQL 优 化的核心思想为：
避免过多复杂的SQL脚本，减少系统的解析过程
避免过多的无用的计算，例如：死循环
避免浪费内存空间没有必要的SQL脚本，导致内存不足
内存中计算和访问速度很快
尽可能的减少磁盘的访问的数据量，该原则是PLSQL优化中重要思想
尽可能的减少磁盘的访问的次数，该原则是PLSQL优化中重要思想
*三、如何进行SQL优化*
主要说明了在 SQL 编写上和 PLSQL 程序编写上可以优化的地方。
1. 选择最有效率的表名顺序
只在基于规则的优化器 rule 中有效，目前我们 oracle 选择的优化器基本都不选择 rule，因此该问题基本不会出现，但为了安全和规范起见，建议编程习惯采用该规则。
ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,因此FROM子句中写在最后的表(基础表 driving table)被最先处理. 在 FROM 子句中包含多个表的情况下,你****必须选择记录条数最少的表作为基础表.当 ORACLE 处理多个表时, 会运用排序及合并的方式连接它们.首先,扫描第一个表(FROM子句中最后的那个表)并对记录进行排序,然后扫描第二个表(FROM 子句中最后第二个表),最后将所有从第二个表中 检索出的记录与第一个表中合适记录进行合并。
例如：员工表 emp 有 16384 条记录 而部门表dept有1条记录 选择 dept 作为基础表
好的方法 ,执行时间 0.96 秒 选择 dept 作为基础表
select count(*) from emp,dept
不好的方法 执行时间 26.09 秒
select count(*) from dept,emp
2. WHERE 子句中的连接顺序
ORACLE 采用自下而上的顺序解析 WHERE 子句,根据这个原理,表之间的连接必须写在其他 WHERE 条件之前。
低效
SELECT dept.deptno,emp.job
FROM emp,dept
WHERE emp.job='MANAGER' AND emp.deptno=dept.deptno;
高效
SELECT dept.deptno,emp.job
FROM emp,dept
WHERE emp.deptno=dept.deptno AND emp.job='MANAGER';
3.SELECT 子句中避免使用 ' * '
当你想在SELECT子句中列出所有的column时,使用动态SQL列引用''是一个方便的方法.不幸的是,这是一个非常低效的方法.实际上,ORACLE在解析的过程中, 会将''依次转换成所有的列名,这个工作是通过查询数据字典完成的,这意味着将耗费更多的时间。因此，在咱们SELECT 子句中尽量避免使用 '*'。
4. 用 EXISTS 替代 IN
实际情况看，使用 exists 替换 in 效果不是很明显，基本一样。在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下, 使用 EXISTS(或 NOT EXISTS)通常将提高查询的效率。
低效
SELECT *
FROM table_name1
Where column1 in
(select column1 from table_name2
where column2=str_column2
and column3=’xxxx’);
或
SELECT *
FROM table_name1
Where column1 in
(select distinct column1 from table_name2
where column2=str_column2
and column3=’xxxx’);
注意使用 distinct 也会影响速度
高效
SELECT *
FROM table_name1
Where exists
(select 1 from table_name2
where column1=table_name2.column1
and column2=str_column2
and column3=’xxxx’);
in 的常量列表是优化的(例如：column3 in (‘20’,’30’))，不用 exists 替换；in 列表相当于 or
5. 用 NOT EXISTS 替代 NOT IN
Oracle在10g之前版本not in都是最低效的语句，虽然在10g上not in做到了一些改进，但仍然还是存在一些问题，因此我们一定要使用not exists来替代not in的写法。
在子查询中,NOT IN 子句将执行一个内部的排序和合并. 无论在哪种情况下,NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历). 为了避免使用NOT IN,我们可以把它改写成NOT EXISTS.
SELECT *
FROM table_name1
WHERE column1 NOT IN
(SELECT column1 from table_name2
where column3=’xxxx’);
为了提高效率.改写为:
SELECT *
FROM table_name1
WHERE not exists
(SELECT 1 from table_name2
where column1 =table_name2.column1
and column3=’xxxx’);
6. 用表连接替换 EXISTS
在子查询的表和主表查询是多对一的情况，一般采用表连接的方式比 EXISTS 更有效率。
低效
SELECT table_name1.*
FROM table_name1
Where exists (
select 1 from table_name2
where column1 =table_name1.column1
and column2='xxxx'
and column3='xxxxxx');
高效
SELECT table_name1.*
FROM table_name1,table_name2 Where
table_name1.column1=table_name2.column1
and table_name2.column2='xxxx'
and column3='xxxx';
到底 exists 和表关联哪种效率高，其实是根据两个表之间的数据量差别大小是有关的，如果差别不大实际上速度基本差不多。
7. 用 EXISTS 替换 DISTINCT
当提交一个包含一对多表信息(比如个人基本信息表和个人参保信息表)的查询时,避免在SELECT子句中使用 DISTINCT. 一般可以考虑用 EXISTS 替换
低效
select distinct table_name1.column1
from table_name2,table_name1 where
table_name2.column1 = table_name1.column1
and table_name2.column2='xxxx'
and table_name2.column3='xxxx';
高效.
select table_name1.column1
from table_name1
where exists
(select 1 from table_name2 where
column1 = table_name1.column1
and table_name2.column2='xxxx'
and table_name2.column3='xxxx');
EXISTS 使查询更为迅速,因为 RDBMS 核心模块将在子查询的条件一旦满足后,立刻返回结果。因此如果不是特别研究和追求速度的话（例如：数据转换），查询一个表的数据需要关联其他表的这种情况查询,建议采用 EXISTS 的方式。
8. 减少对表的查询
该问题是我们编程中出现过的问题，请大家一定注意，并且该类问题优化可以带来较大性能的提升。
低效
cursor cur_table1_lj1 is
Select column1
from table1
where column1 =str_column1 and column2='1111';
cursor cur_table1_lj2 is
Select column1
from table1
where column1 =str_column1 and column2='2222';
for rec_lj1 in cur_table1_lj1 loop
业务逻辑1处理…..
end loop;
for rec_lj2 in cur_table1_lj2 loop
业务逻辑2处理…..
end loop;
高效
cursor cur_table1_lj1 is
Select column1,column2
from table1
where column1 =str_column1 and column2 in ('11111','22222');
for rec_lj1 in cur_table1_lj1 loop
if rec_lj1.column2='11111' then
业务逻辑1处理…..
end if;
if rec_lj1.column2='22222' then
业务逻辑2处理…..
end if;
end loop;
高效的做法使用同样的条件（或者说是索引）只访问一次磁盘，低效的做法访问了 2 次磁盘，这样速度差 别将近 2 倍。
9. 避免循环（游标）里面嵌查询
游标里面不能嵌入查询(或者再嵌游标)，其实也不能有 update delete 等语句，只能有 insert 语句。但在实际的编程情况下是不可能完全避免的，但我们一定要尽量避免。该类问题也是我们程序中出现过的问题，该类问题也可以大大提升程序效率，请大家一定注意。
优化的方法是尽量把游标循环中的查询语句放到游标查询中一起查询出来，这样相当于只访问了 1 次磁盘 读到内存；如果放到游标中的话，假如游标有 100 万数据量，那么程序需要 100 万次磁盘，可以想象浪费 了多少 IO 的访问。
如果在程序编写上没有办法避免游标中有查询语句的话（一般情况是可以避免的），那么也要保证游标中 的查询使用的索引（即查询速度非常快），例如：游标 100 万数据量，游标中的查询语句执行需要 0.02 秒， 从这个速度上来说是很快的，但总体上看 100 万*0.02 秒=2 万秒=5 小时 33 分钟，如果写一个不够优化的 语句需要 1 秒，那么需要几天能执行完呢？
10. 尽量用 union all 替换 union
Union 会去掉重复的记录，会有排序的动作，会浪费时间。因此在没有重复记录的情况下或可以允许有重 复记录的话，要尽量采用 union all 来关联。
11. 使用 DECODE 函数来减少处理时间
使用 DECODE 函数可以避免重复扫描相同记录或重复连接相同的表.
低效
select count(1) from table1
where column1='1111' and column2='1';
select count(1) from table1
where column1='1111' and column2='2';
或者
Select count(1),column2
From table1
Where column1='1111' and column2 in ('1','2')
group by column2;
高效
select count(decode(column1,'1',1,0)) zz,
count(decode(column1,'2',1,0)) tx
from table1
where column1='1111';
特别说明：group by 和 order by 都会影响性能，编程时尽量避免没有必要的分组和排序，或者通过其他的有效的编程 办法去替换，比如上面的处理办法。
12. group by 优化
Group by 需要查询后分组，速度慢影响性能，如果查询数据量大，并且分组复杂，这样的查询语句在性能 上是有问题的。尽量避免使用分组或者采用上面的一节的办法去代替。采用 group by 的也一定要进行优化。
低效
select table1.column1,table2.column2,
table2.column3,sum(column5),table1.column4
from table1,table2
where table1.column1=table2.column1
and table1.column4='xxxxxx'
group by table1.column1,table2.column2,
table2.column3,table2.column4;
高效
select table1.column1,table2.column2,
table2.column3,gzze,table1.column4
from
(select column1,sum(column5) gzze
from table1 group by column1) table1,table2
where table1.column1=table2.column1
and column4='xxxxxx' ;
13. 尽量避免用 order by
Order by 需要查询后排序，速度慢影响性能，如果查询数据量大，排序的时间就很长。但我们也不能避免 不使用，这样大家一定注意一点的是如果使用 order by 那么排序的列表必须符合索引，这样在速度上会得 到很大的提升。
14. 用 Where 子句替换 HAVING 子句
避免使用 HAVING 子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤. 这个处理需要排序, 总计等操作. 如果能通过 WHERE 子句限制记录的数目,那就能减少这方面的开销.
低效
SELECT column1,count(1) FROM table1
GROUP BY column1
HAVING column1 in ('1','2');
高效
SELECT column1,count(1) FROM table1
Where column1 in ('1','2')
GROUP BY column1 ;
HAVING 中的条件一般用于对一些集合函数的比较,如 COUNT() 等等. 除此而外,一般的条件应该写在 WHERE 子句中
15. 使用表的别名(Alias)
当在SQL语句中连接多个表时, 请使用表的别名并把别名前缀于每个Column上.这样一来,就可以减少解析 的时间并减少那些由 Column 歧义引起的语法错误.
16. 删除重复记录
一般数据转换的程序经常会使用到该方法。
最高效的删除重复记录方法 ( 因为使用了 ROWID)
DELETE FROM table1 a
WHERE a.rowid > (SELECT MIN(b.rowid) FROM table1 b
WHERE a.column1=b.column1 and a.column2=b.column2);
17.COMMIT 使用
数据转换的程序需要关注这一点。
Commit 执行也是有时间的，不过时间特别短，但提交频率特别大，必然也会浪费时间。
commit 可以释放资源，在大量数据更新时，必须及时提交。
Cur_table1 有 5000 万数据
n_count :=0;
For arec in cur_table1 loop
Insert into table1 ……
n_count := n_count + 1;
If n_count = = 100000 then --10 万一提交
commit;
n_count := 0;
End if;
End loop;
Commit;
如果 1 条一提交，需要提交 5000 万必然浪费时间；如果整体提交，资源不能释放，性能必须下降。在实际编程时，应注意提交的次数和提交的数据量的平衡关系。
18. 减少多表关联
表关联的越多，查询速度就越慢，尽量减少多个表的关联，建议表关联不要超过 3 个（子查询也属于表关 联）。数据转换上会存在大数据量表的关联，关联多了会影响索引的效率，可以采用建立临时表的办法，有时更 能提高速度。
*四、索引使用优化*
在实际的应用系统中索引问题导致性能问题可能能占到 80%，在程序优化上索引问题是需要我们特别关注 的。下面主要描述什么情况索引会不生效。
1. 避免在索引列上使用函数或运算
这个问题是在我们实际编程中出现过的，请大家一定注意。在索引列上使用函数或运算，查询条件都不会 使用索引。
不使用索引
Select * from table1
where column1='xxxxxxx'
and to_char(column2,'yyyymm')='200801';
或者
Select * from table1
where column1='xxxxxxx'
and column2+1=sysdate;
使用索引
Select * from table1
where column1='xxxxxxx'
and column2=to_date('200801','yyyymm');
或者
Select * from table1
where column1='xxxxxxx'
and column2=sysdate -1;
2. 避免改变索引列的类型
索引列的条件如果类型不匹配，则不能使用索引。
3. 避免在索引列上使用 NOT
避免在索引列上使用 NOT, NOT 不会使查询条件使用索引。对于!=这样的判断也是不能使用索引的，索引只能告诉你什么存在于表中, 而不能告诉你什么不存在于表中。
低效: (这里,不使用索引)
select * From table1 Where not column1='10';
高效: (这里,使用了索引)
select * From table1 Where column1 in ('20','30');
4. 用>=替代>
虽然效果不是特别明显，但建议采用这种方式
低效
SELECT * FROM table1 WHERE column1 > '10'
高效
SELECT * FROM table1 WHERE column1 >='20
特别说明：两者的区别在于, 前者 DBMS 首先定位到 column1=10 的记录并且向前扫描到第一个 column1 大于 10 的记录, 而后者 DBMS 将直接跳到第一个 column1 等于 20 的记录
5. 避免在索引列上使用 IS NULL 和 IS NOT NULL
对于索引列使用 is null 或 is not null 不会使用上索引．因为空值不存在于索引列中,所以 WHERE 子句中对 索引列进行空值比较将使 ORACLE 停用该索引.。
低效(索引失效)
select * from table1 where column1 is not null;
高效(索引有效)
select * from table1 where column1 in ('10','20','30');
在实际开发中，对于这类的问题很难避免，如果不是特别影响速度或者要求速度的，可以忽略。
6. 带通配符（%）的 like 语句
%在常量前面索引就不会使用。
不使用索引
Select * from table1 where column1 like '%210104';
Select * from table1 where column1 like '%210104%';
使用索引
Select * from table1 where column1 like '210104%';
7. 总是使用索引的第一个列
如果索引是建立在多个列上, 只有在它的第一个列被 where 子句引用时,优化器才会选择使用该索引。
如：table1 的复合索引(column1 、column2、column3)
低效(不会使用索引)
Select * from table1 where column2='110' and column3='200801';
高效(会使用索引)
Select * from table1 where column1 ='10001000';
如果不使用索引第一列基本上不会使用索引，使用索引要按照索引的顺序使用，另外使用复合索引的列越 多，查询的速度就越快
8. 关于索引建立
索引的使用是肯定会大大提高查询的速度，但索引其实也是一种数据，它也是存放的用户类型的表空间下的，索引建立的越多越大，占用的空间也越大，从用户的环境来说这也不是问题，但如果一个表有过多过大的查询，必然会影响 insert、delete 和 update 索引列的速度，因为这些操作改变了整个表的索引顺序，oracle 需要进行调整，这样性能就下降了。因此我们一定要合理的建立好有效的索引，编程也要符合索引的规则， 而不能是索引符合编程的规则。
案例：某项目数据转换，采用游标循环 insert 的方式，总共 2000 万的数据，总共用了 4 个小时，原因就是目标表 里面有很多索引。解决方法是先删除索引再执行转换脚本，结果不用 1 小时就完成了，建立全部的索引不到半个小时。