简单sql优化

一，oracle语句共享：
为了不重复解析相同的SQL语句，在第一次解析之后， ORACLE将SQL语句存放在内存中。这块位于系统全局区域SGA(system global area)的共享池(shared buffer pool)中的内存可以被所有的数据库用户共享。 因此，当执行一个SQL语句时，如果它和之前的执行过的语句完全相同， ORACLE就能很快获得已经被解析的语句以及最好的执行路径。
二，选择最有效率的表名顺序：
ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名，因此FROM子句中写在最后的表(基础表)将被最先处理。在FROM子句中包含多个表的情况下，必须选择记录条数最少的表作为基础表。当ORACLE处理多个表时， 会运用排序及合并的方式连接它们。首先，扫描第一个表(FROM子句中最后的那个表)并对记录进行排序，然后扫描第二个表(FROM子句中最后第二个表)，最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并。
例如：
表 TAB1 16，384 条记录
表 TAB2 1 条记录

选择TAB2作为基础表 (最好的方法)
select count(*) from tab1，tab2
选择TAB2作为基础表 (不佳的方法)
select count(*) from tab2，tab1
三，WHERE子句中的连接顺序 ：
ORACLE采用自下而上的顺序解析WHERE子句，根据这个原理，表之间的连接必须写在其他WHERE条件之前， 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾。
例如：
(低效)
SELECT … FROM EMP E WHERE SAL > 50000
AND JOB = ‘MANAGER’ AND 25 <
(SELECT COUNT(*) FROM EMP WHERE MGR=E.EMPNO);
(高效)
SELECT … FROM EMP E WHERE 25 <
(SELECT COUNT(*) FROM EMP WHERE MGR=E.EMPNO)
AND SAL > 50000 AND JOB = ‘MANAGER’;
四，“exists”和“in”的效率：
通过使用EXISTS，Oracle会首先检查主查询，然后运行子查询直到它找到第一个匹配项，这就节省了时间。Oracle在执行IN子查询时，首先执行子查询，并将获得的结果列表存放在一个加了索引的临时表中。在执行子查询之前，系统先将主查询挂起，待子查询执行完毕，存放在临时表中以后再执行主查询。
1) select * from T1 where exists(select 1 from T2 where T1.a=T2.a) ;
T1数据量小而T2数据量非常大时，T1<<T2 时，1) 的查询效率高。
2) select * from T1 where T1.a in (select T2.a from T2) ;
T1数据量非常大而T2数据量小时，T1>>T2 时，2) 的查询效率高
五，用表连接替换EXISTS ：
通常来说 ， 采用表连接的方式比EXISTS更有效率
SELECT ENAME
FROM EMP E
WHERE EXISTS (SELECT ‘X’
FROM DEPT
WHERE DEPT_NO = E。DEPT_NO
AND DEPT_CAT = ‘A’)；

(更高效)
SELECT ENAME
FROM DEPT D，EMP E
WHERE E。DEPT_NO = D。DEPT_NO
AND DEPT_CAT = ‘A’ ；
六，用EXISTS替换DISTINCT ：
当提交一个包含一对多表信息(比如部门表和雇员表)的查询时，避免在SELECT子句中使用DISTINCT。 一般可以考虑用EXIST替换
例如：
低效：
SELECT DISTINCT DEPT_NO，DEPT_NAME
FROM DEPT D，EMP E
WHERE D.DEPT_NO = E.DEPT_NO
高效：
SELECT DEPT_NO，DEPT_NAME
FROM DEPT D
WHERE EXISTS ( SELECT ‘X’
FROM EMP E
WHERE E.DEPT_NO = D.DEPT_NO)；
EXISTS 使查询更为迅速，因为RDBMS核心模块将在子查询的条件一旦满足后，立刻返回结果。
七,关于索引的问题：
1）平等索引的优先级:
如果不同表中两个相同等级的索引将被引用， FROM子句中表的顺序将决定哪个会被率先使用。 FROM子句中最后的表的索引将有最高的优先级。
如果相同表中两个相同等级的索引将被引用， WHERE子句中最先被引用的索引将有最高的优先级。
举例：
DEPTNO上有一个非唯一性索引，EMP_CAT也有一个非唯一性索引。
SELECT ENAME,FROM EMP
WHERE DEPT_NO = 20 AND EMP_CAT = ‘A’；
这里，DEPT_NO索引将被最先检索，然后同EMP_CAT索引检索出的记录进行合并。
2）等式比较和范围比较 ：
EMP 表DEPTNO字段上有一个非唯一性索引，EMP_CAT也有一个非唯一性索引。
SELECT ENAME
FROM EMP
WHERE DEPTNO > 20
AND EMP_CAT = ‘A’；
这里只有EMP_CAT索引被用到，然后所有的记录将逐条与DEPTNO条件进行比较。 执行路径如下：
TABLE ACCESS BY ROWID ON EMP
INDEX RANGE SCAN ON CAT_IDX
3） 不明确的索引等级 ：
当ORACLE无法判断索引的等级高低差别，优化器将只使用一个索引，它就是在WHERE子句中被列在最前面的。
举例：
DEPTNO上有一个非唯一性索引，EMP_CAT也有一个非唯一性索引。

SELECT ENAME
FROM EMP
WHERE DEPTNO > 20
AND EMP_CAT > ‘A’；

这里， ORACLE只用到了DEPT_NO索引。 执行路径如下：

TABLE ACCESS BY ROWID ON EMP
INDEX RANGE SCAN ON DEPT_IDX
4）强制索引失效 ：
如果两个或以上索引具有相同的等级，你可以强制命令ORACLE优化器使用其中的一个(通过它，检索出的记录数量少) 。
举例：
SELECT ENAME
FROM EMP
WHERE EMPNO = 7935
AND DEPTNO + 0 = 10 /*DEPTNO上的索引将失效*/
AND EMP_TYPE || ‘’ = ‘A’ /*EMP_TYPE上的索引将失效*/
这是一种相当直接的提高查询效率的办法。 但必须谨慎考虑这种策略，一般来说，只有在希望单独优化几个SQL时才能采用它。
5）避免在索引列上使用计算 ：
WHERE子句中，如果索引列是函数的一部分．优化器将不使用索引而使用全表扫描．
举例：
低效：
SELECT …FROM DEPT
WHERE SAL * 12 > 25000;
高效：
SELECT …FROM DEPT
WHERE SAL > 25000/12;
6）自动选择索引 ：
如果表中有两个以上（包括两个）索引，其中有一个唯一性索引，而其他是非唯一性．在这种情况下，ORACLE将使用唯一性索引而完全忽略非唯一性索引．
举例：
SELECT ENAME FROM EMP
WHERE EMPNO = 2326 AND DEPTNO = 20 ;

这里，只有EMPNO上的索引是唯一性的，所以EMPNO索引将用来检索记录．
TABLE ACCESS BY ROWID ON EMP
INDEX UNIQUE SCAN ON EMP_NO_IDX
7）用>=替代> ：
如果DEPTNO上有一个索引，
高效：
SELECT *
FROM EMP
WHERE DEPTNO >=4
低效：
SELECT *
FROM EMP
WHERE DEPTNO >3
8）用UNION替换OR (适用于索引列) ：
通常情况下， 用UNION替换WHERE子句中的OR将会起到较好的效果。 对索引列使用OR将造成全表扫描。 注意， 以上规则只针对多个索引列有效。 如果有column没有被索引， 查询效率可能会因为你没有选择OR而降低。
在下面的例子中， LOC_ID 和REGION上都建有索引。
高效：
SELECT LOC_ID,LOC_DESC,REGION FROM LOCATION
WHERE LOC_ID = 10
UNION
SELECT LOC_ID ， LOC_DESC ， REGION FROM LOCATION
WHERE REGION = “MELBOURNE”

低效：
SELECT LOC_ID,LOC_DESC,REGION FROM LOCATION
WHERE LOC_ID = 10 OR REGION = “MELBOURNE”
9）避免使用IS NULL和IS NOT NULL ：
避免在索引中使用任何可以为空的列，ORACLE将无法使用该索引．对于单列索引，如果列包含空值，索引中将不存在此记录。 对于复合索引，如果每个列都为空，索引中同样不存在此记录。如果至少有一个列不为空，则记录存在于索引中。
如果唯一性索引建立在表的A列和B列上， 并且表中存在一条记录的A，B值为(123，null) ， ORACLE将不接受下一条具有相同A，B值（123，null）的记录(插入)。 然而如果所有的索引列都为空，ORACLE将认为整个键值为空而值不等于空。 因此你可以插入1000条具有相同键值的记录，当然它们都是空!因为空值不存在于索引列中，所以WHERE子句中对索引列进行空值比较将使ORACLE停用该索引。
低效： (索引失效)
SELECT …FROM DEPARTMENT WHERE DEPT_CODE IS NOT NULL;
高效： (索引有效)
SELECT …FROM DEPARTMENT WHERE DEPT_CODE >=0;
10）避免改变索引列的类型 ：
当比较不同数据类型的数据时， 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 WHERE TO_NUMBER(EMP_TYPE)=123

因为内部发生的类型转换，这个索引将不会被用到。为了避免ORACLE对你的SQL进行隐式的类型转换， 最好把类型转换用显式表现出来。 注意当字符和数值比较时， ORACLE会优先转换数值类型到字符类型。
八，避免使用耗费资源的操作 ：
带有DISTINCT，UNION，MINUS，INTERSECT，ORDER BY的SQL语句会启动SQL引擎执行耗费资源的排序(SORT)功能。 DISTINCT需要一次排序操作， 而其他的至少需要执行两次排序。
例如，一个UNION查询，其中每个查询都带有GROUP BY子句， GROUP BY会触发嵌入排序(NESTED SORT) ； 这样， 每个查询需要执行一次排序， 然后在执行UNION时， 又一个唯一排序(SORT UNIQUE)操作被执行而且它只能在前面的嵌入排序结束后才能开始执行。 嵌入的排序的深度会大大影响查询的效率。
通常， 带有UNION， MINUS ， INTERSECT的SQL语句都可以用其他方式重写。
如果数据库的SORT_AREA_SIZE调配得好， 使用UNION ， MINUS， INTERSECT也是可以考虑的， 毕竟它们的可读性很强
九,优化GROUP BY :
提高GROUP BY 语句的效率， 可以通过将不需要的记录在GROUP BY 之前过滤掉。下面两个查询返回相同结果但第二个明显就快了许多。

低效：
SELECT JOB ， AVG(SAL) FROM EMP
GROUP JOB HAVING JOB = ‘PRESIDENT’
OR JOB = ‘MANAGER’

高效：
SELECT JOB ， AVG(SAL) FROM EMP
WHERE JOB = ‘PRESIDENT’
OR JOB = ‘MANAGER’
GROUP JOB