无意中找到一篇干货，借着总结一下SQL优化的知识：
首先看下一条SQL在关系型数据库内部是怎么执行的：
我来阐述一遍执行过程：
一条SQL通过解析器，首先判定SQL的语法和语义有没有差错，如果有，返回错误，如果正确，则在内存中share pool中去寻找和当前SQL有相同hash值的sql，如果找到了，则代表此SQL已经解析过了，通过软解析方式直接执行。如果找不到，则使用硬解析的方式执行，硬解析通过优化规则不同，分别可采用CBO或RBO的方式来生成执行计划执行该SQL。
下面介绍几类专有名词：
硬解析（HADR PARSE）：即整个SQL语句的执行需要完完全全的解析，生成执行计划。而硬解析，生成执行计划需要耗用CPU资源，以及SGA资源。在此不得不提的是对库缓存中闩的使用。闩是锁的细化，可以理解为是一种轻量级的串行化设备。当进程申请到闩后，则这些闩用于保护共享内存的数在同一时刻不会被两个以上的进程修改。在硬解析时，需要申请闩的使用，而闩的数量在有限的情况下需要等待。大量的闩的使用由此造成需要使用闩的进程排队越频繁，性能则逾低下。
软解析（SOFT PARSE）：在重复的SQL语句执行情况下，沿用第一次硬解析时生成的解析树，执行计划来执行该条SQL，而跳过了复杂的解析过程。
CBO:基于代价的优化方式，他是看语句的代价，这里的代价主要指CPU和内存。优化器再判断是否用这种方式时，主要参照的是表及索引的统计信息。统计信息给出表的大小，有多少行每行的长度等信息，这些统计信息起初在库内是没有的，是做analyze后才出现的。很多的时候过期的统计信息会令优化器做出一个错误的执行计划。
RBO:优化器在分析sql语句时，所遵循的是oracle内部预定的一些规则。比如我们常见的，当一个where子句中的一列有索引时去走索引。由于RBO太过死板，没有考虑到表数据突增突减的情况，采用相同的执行计划很可能造成性能风险。所以oracle 8i之后，强烈建议使用CBO。
执行计划：执行计划就是指解析器决定以什么样的方式来执行SQL。
常见的优化总结：
1.使用绑定变量
绑定变量要求变量名称，数据类型以及长度是一致，否则无法使用软解析
绑定变量（bind variable）是指在DML语句中使用一个占位符，即使用冒号后面紧跟变量名的形式，如下
select * from emp where empno=7788 --未使用绑定变量
select * from emp where empono=:eno --:eno即为绑定变量
在第二个查询中，变量值在查询执行时被提供。该查询只编译一次，随后会把查询计划存储在一个共享池（库缓存）中，以便以后获取和重用这个查询计划。
2.用>=替代>
高效:
SELECT * FROM EMP WHERE DEPTNO >=4
低效:
SELECT * FROM EMP WHERE DEPTNO >3
两者的区别在于, 前者DBMS将直接跳到第一个DEPT等于4的记录而后者将首先定位到DEPTNO=3的记录并且向前扫描到第一个DEPT大于3的记录.
3.适量使用COMMIT：
只要有可能,在程序中尽量多使用COMMIT, 这样程序的性能得到提高,需求也会因为COMMIT所释放的资源而减少:
COMMIT所释放的资源:
a. 回滚段上用于恢复数据的信息.
b. 被程序语句获得的锁
c. redo log buffer 中的空间
d. ORACLE为管理上述3种资源中的内部花费
4.用EXISTS替代IN、用NOT EXISTS替代NOT IN：
在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下, 使用EXISTS(或NOT EXISTS)通常将提高查询的效率. 在子查询中,NOT IN子句将执行一个内部的排序和合并. 无论在哪种情况下,NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历). 为了避免使用NOT IN ,我们可以把它改写成外连接(Outer Joins)或NOT EXISTS.
例子：
（高效）SELECT * FROM EMP (基础表) WHERE EMPNO > 0 AND EXISTS (SELECT ‘X' FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = ‘MELB')
(低效)SELECT * FROM EMP (基础表) WHERE EMPNO > 0 AND DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE LOC = ‘MELB')
5.用TRUNCATE替代DELETE：
当删除表中的记录时,在通常情况下, 回滚段(rollback segments ) 用来存放可以被恢复的信息. 如果你没有COMMIT事务,ORACLE会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况) 而当运用TRUNCATE时, 回滚段不再存放任何可被恢复的信息.当命令运行后,数据不能被恢复.因此很少的资源被调用,执行时间也会很短. (TRUNCATE只在删除全表适用,TRUNCATE是DDL不是DML)
6.删除重复记录：
最高效的删除重复记录方法 ( 因为使用了ROWID)例子：
DELETE FROM EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID)
FROM EMP X WHERE X.EMP_NO = E.EMP_NO)
SQL优化之索引介绍：
索引特点：
第一，通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
第二，可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
第三，可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
第四，在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
第五，通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。
应该建索引列的特点：
1）在经常需要搜索的列上，可以加快搜索的速度；
2）在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；
3）在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；
4）在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；
5）在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；
6）在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度；
7）数据唯一性高的列上创建索引。
索引使用的误区：
数据列字段很少用来做查询条件
数据列存在频繁的修改
整个表中的数据量较小
数据列重复值很多，导致查询数据量超过30%以上
示例：
1. IS NULL 与 IS NOT NULL
不能用null作索引，任何包含null值的列都将不会被包含在索引中。即使索引有多列这样的情况下，只要这些列中有一列含有null，该列就会从索引中排除。也就是说如果某列存在空值，即使对该列建索引也不会提高性能。
任何在where子句中使用is null或is not null的语句优化器是不允许使用索引的。
2.用UNION替换OR (适用于索引列)
通常情况下, 用UNION替换WHERE子句中的OR将会起到较好的效果. 对索引列使用OR将造成全表扫描. 注意, 以上规则只针对多个索引列有效. 如果有column没有被索引, 查询效率可能会因为你没有选择OR而降低. 在下面的例子中, LOC_ID 和REGION上都建有索引。
低效:
SELECT LOC_ID , LOC_DESC , REGION
FROM LOCATION
WHERE LOC_ID = 10 OR REGION = “MELBOURNE”
高效:
SELECT LOC_ID , LOC_DESC , REGION
FROM LOCATION
WHERE LOC_ID = 10
UNION
SELECT LOC_ID , LOC_DESC , REGION
FROM LOCATION
WHERE REGION = “MELBOURNE”
OR与UNION：
3.避免改变索引列的类型.:
当比较不同数据类型的数据时, ORACLE自动对列进行简单的类型转换.
假设 EMPNO是一个数值类型的索引列.
SELECT … FROM EMP WHERE EMPNO = ‘123'
实际上,经过ORACLE类型转换, 语句转化为:
SELECT … FROM EMP WHERE EMPNO = TO_NUMBER(‘123')
幸运的是,类型转换没有发生在索引列上,索引的用途没有被改变.
现在,假设EMP_TYPE是一个字符类型的索引列.
SELECT … FROM EMP WHERE EMP_TYPE = 123
这个语句被ORACLE转换为:
SELECT … FROM EMP WHERETO_NUMBER(EMP_TYPE)=123
因为内部发生的类型转换, 这个索引将不会被用到! 为了避免ORACLE对你的SQL进行隐式的类型转换, 最好把类型转换用显式表现出来. 注意当字符和数值比较时, ORACLE会优先转换数值类型到字符类型
ORACLE隐式转换
注意：字符串隐式转换为整形不走索引，整形隐式转换为字符串可以走索引
4.避免在索引列上使用计算．
WHERE子句中，如果索引列是函数的一部分．优化器将不使用索引而使用全表扫描．
举例:
低效：
SELECT … FROM DEPT WHERE SAL * 12 > 25000;
高效:
SELECT … FROM DEPT WHERE SAL > 25000/12;
5.带通配符(%)的like语句
怎么让前模糊能走索引？怎么让前后模糊也能走索引？
后模糊用reserve函数，将字符串颠倒
Select * from xxx where reserve(xx) like reserve('%A');
前后都模糊，改用instr
创建函数索引:
Create index idx_instr on table(instr(xx,'A'));
Select * from table where instr(xx,'A') >0
SQL优化的有效手段之减少数据量
1.通过有效的增加where限制条件或指定必要的数据列，减少数据的读取量，提高SQL效率
2.减少表关联以及排序的数据量
3.减少表的读取次数
示例：
1.SELECT子句中避免使用 ‘ * ’：
ORACLE在解析的过程中, 会将'*' 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间
多余的字段数据读取将耗费额外的时间而且在使用*的SQL语句在遇到表结构调整时会有更多的问题
2.优化GROUP BY:
提高GROUP BY 语句的效率, 可以通过将不需要的记录在GROUP BY 之前过滤掉.下面两个查询返回相同结果但第二个明显就快了许多.
低效:
SELECT JOB , AVG(SAL)
FROM EMP
GROUP by JOB
HAVING JOB = 'PRESIDENT'
OR JOB = 'MANAGER'
高效:
SELECT JOB , AVG(SAL)
FROM EMP
WHERE JOB = 'PRESIDENT'
OR JOB = 'MANAGER'
GROUP by JOB
3.优化or:
SQL1:
SELECT LOC_ID , LOC_DESC , REGION
FROM LOCATION
WHERE LOC_ID = 10 OR REGION = “MELBOURNE”
SQL2：
SELECT LOC_ID , LOC_DESC , REGION
FROM LOCATION
WHERE LOC_ID = 10
UNION
SELECT LOC_ID , LOC_DESC , REGION
FROM LOCATION
WHERE REGION = “MELBOURNE”
SQL优化的有效手段之HINT
Hint 是Oracle 提供的一种SQL语法，它允许用户在SQL语句中插入相关的语法，从而影响SQL的执行方式。
因为Hint的特殊作用，所以对于开发人员不应该在代码中使用它，Hint 更像是Oracle提供给DBA用来分析问题的工具 。在SQL代码中使用Hint，可能导致非常严重的后果，因为数据库的数据是变化的，在某一时刻使用这个执行计划是最优的，在另一个时刻，却可能很差，这也是CBO 取代RBO的原因之一，规则是死的，而数据是时刻变化的，为了获得最正确的执行计划，只有知道表中数据的实际情况，通过计算各种执行计划的成本，则其最优，才是最科学的，这也是CBO的工作机制。 在SQL代码中加入Hint，特别是性能相关的Hint是很危险的做法。
在使用Hint时需要注意的一点是，并非任何时刻Hint都起作用。 导致HINT 失效的原因有如下2点：
（1）如果CBO 认为使用Hint 会导致错误的结果时，Hint将被忽略。如索引中的记录因为空值而和表的记录不一致时，结果就是错误的，会忽略hint。
（2）如果表中指定了别名，那么Hint中也必须使用别名，否则Hint也会忽略。
Select *+full(a)*/ * from t a; -- 使用hint
Select *+full(t) */ * from t a; --不使用hint
ALL_ROWS 和FIRST_ROWS（n） -- CBO 模式
对于OLAP系统，这种系统中通常都是运行一些大的查询操作，如统计，报表等任务。 这时优化器模式应该选择ALL_ROWS. 对于一些分页显示的业务，就应该用FIRST_ROWS（n）。 如果是一个系统上运行这两种业务，那么就需要在SQL 用hint指定优化器模式。
如：
SQL> select * + all_rows*/ * from dave;
SQL> select * + first_rows(20)*/ * from dave;
并行执行相关的Hint（PARALLEL HINT ）
指定SQL 执行的并行度，这个值会覆盖表自身设定的并行度，如果这个值为default，CBO使用系统参数值。
示例：
SQL> select *+parallel(t 4) */ * from scott.dept t;
关于表的并行度，我们在创建表的时候可以指定，如：
SQL> CREATE TABLE test
2 (
3 name VARCHAR2 (10)
4 )
5 PARALLEL 2;
表已创建。
APPEND HINT
提示数据库以直接加载的方式（direct load）将数据加载入库。
示例：
Insert /*+append */ into t as select * from all_objects;
这个hint 用的比较多。 尤其在插入大量的数据，一般都会用此hint。为什么快的原因，因为该方式直接往磁盘里入数据，只记少量的redo，undo。
SQL优化的有效手段之相关知识
如何查看执行计划
1）PL/SQL工具，F5
2）explain plan for …
select * from table(dbms_xplan.display);
3）set autotrace on
oracle访问数据的存取方法
1） 全表扫描（Full Table Scans， FTS）
为实现全表扫描，Oracle读取表中所有的行，并检查每一行是否满足语句的WHERE限制条件一个多块读操作可以使一次I/O能读取多块数据块（db_block_multiblock_read_count参数设定），而不是只读取一个数据块，这极大的减 少了I/O总次数，提高了系统的吞吐量，所以利用多块读的方法可以十分高效地实现全表扫描，而且只有在全表扫描的情况下才能使用多块读操作。在这种访问模 式下，每个数据块只被读一次。
使用FTS的前提条件：在较大的表上不建议使用全表扫描，除非取出数据的比较多，超过总量的5% —— 10%，或你想使用并行查询功能时。
2） 通过ROWID的表存取（Table Access by ROWID或rowid lookup）
行的ROWID指出了该行所在的数据文件、数据块以及行在该块中的位置，所以通过ROWID来存取数据可以快速定位到目标数据上，是Oracle存取单行数据的最快方法。
这种存取方法不会用到多块读操作，一次I/O只能读取一个数据块。我们会经常在执行计划中看到该存取方法，如通过索引查询数据。
3） 索引扫描（Index Scan或index lookup）
我们先通过index查找到数据对应的rowid值（对于非唯一索引可能返回多个rowid值），然后根据rowid直接从表中得到具体的数据，这 种查找方式称为索引扫描或索引查找（index lookup）。一个rowid唯一的表示一行数据，该行对应的数据块是通过一次i/o得到的，在此情况下该次i/o只会读取一个数据库块。
在索引中，除了存储每个索引的值外，索引还存储具有此值的行对应的ROWID值。
索引扫描可以由2步组成：
（1） 扫描索引得到对应的rowid值。
（2） 通过找到的rowid从表中读出具体的数据。
每步都是单独的一次I/O，但是对于索引，由于经常使用，绝大多数都已经CACHE到内存中，所以第1步的 I/O经常是逻辑I/O，即数据可以从内存中得到。但是对于第2步来说，如果表比较大，则其数据不可能全在内存中，所以其I/O很有可能是物理I/O，这 是一个机械操作，相对逻辑I/O来说，是极其费时间的。所以如果多大表进行索引扫描，取出的数据如果大于总量的5% —— 10%，使用索引扫描会效率下降很多。
因为索引是已经排序了的，所以将按照索引的顺序查询出符合条件的行，因此避免了进一步排序操作。
