oracle sql优化

整理一下网上所看到sql优化方法

1、使用大写字母书写sql，因为oracle解释器会先将sql语句转换成大写后再解释

2    减少访问数据库的次数，多数情况下一条sql可以达到目的的，就不要使用多条sql。

当执行每条SQL语句时，ORACLE在内部执行了许多工作：解析SQL语句，估算索引的利用率，绑定变量，读数据块等等。由此可见，减少访问数据的次数，就能实际上减少ORACLE的工作量。

例如：

以下有三种方法可以检索出雇员号等于0342或0291的职员。

方法1（最低效）

SELECT EMP_NAME,SALARY,GRADE

FROM EMP

WHERE EMP_NO=342;

SELECT EMP_NAME,SALARY,GRADE

FROM EMP

WHERE EMP_NO=29;

方法2（高效）

SELECT A.EMP_NAME,A.SALARY,A.GRADE,

B.EMP_NAME,B.SALARY,B.GRADE

FORM EMP A,EMP B

WHERE A.EMP_NO=342

AND B.EMP_NO=29

 
3    减少对表的查询

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

低效

SELECT TAB_NAME

FROM TABLES

WHERE TAB_NAME=(SELECT TAB_NAME

FROM TAB_COLUMNS

WHERE VERSION=604)

AND DB_VER=(SELECT DB_VER

FROM TAB_COLUMNS

WHERE VERSION=604)

高效

SELECT TAB_NAME

FROM TABlES

WHERE (TAB_NAME,DB_VER)

=(SELECT TAB_NAME,DB_VER)

FROM TAB_COLUMNS

WHERE VERSION= 604)

低效

UPDATE EMP

SET EMP_CAT=(SELECT MAX(CATEGORY) FROM EMP_CATEGORIES),

SAL_RANGE=(SELECT MAX(SAL_RANGE) FROM EMP_CATEGORIES)

WHERE EMP_DEPT=0020;

高效：

UPDATE EMP

SET(EMP_CAT,SAL_RANGE)

=(SELECT MAX(CATEGORY),MAX(SAL_RANGE)

FROM EMP_CATEGORIES)

WHERE EMP_DEPT-0020;

4    SELECT子句中避免使用*
当你想在SELECT子句中列出所有COLUMN时，使用动态SQL列引用‘*’是一个方便的方法，不幸的是，这是一个非常低效的方法。实际上。ORACLE在解析的过程中，会将‘*’依次转换成所有的列名，这个工作是通过查询数据字典完成的，这意味着将耗费更多的时间。

5、 选择最有效率的表名顺序（只在基于规则的优化器中有效ORACLE）

解析器按照从右到左的顺序处理FROM子句中的表名，因此FROM子句中写在最后的表（基础表driving table）将被最先处理。在FROM子句中包含多个表的情况下，你必须选择记录条数最少的表作为基础表。当ORACEL处理多个表时，会运用排序及合并的方式连接它们。首先，扫描第一个表（FROM子句中最后的那个表）并对记录进行派序，然后扫描第二个表（FROM子句中最后第二个表），最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并。

例如：表TAB1 16,384条记录

表TAB2 1条记录

选择TAB2作为基础表（最好的方法）

Select count(*) from tab1,tab2 执行时间0.96秒

选择TAB2作为基础表（不佳方法）

Select count(*) from tab2,tab1 执行时间26.09秒

如果有3个以上的表连接查询，那就需要选择交叉表（intersection table）作为基础表，交叉表是指那个被其他表所引用的表。

例如：EMP表描述了LOCATION表和CATEGORY表的交集

SELECT *

FROM LOCATION L，

CATEGORY C，

EMP E

WHERE E.EPM_NO BETWEEN 1000 AND 2000

AND E.CAT_NO=C.CAT_NO

AND E.LOCN=L.LOCN

将比下列SQL更有效率

SELECT *

FROM EMP E，

LOCATION L，

CATEGORY C

WHERE E.EPM_NO BETWEEN 1000 AND 2000

AND E.CAT_NO=C.CAT_NO

AND E.LOCN=L.LOCN

6    WHERE子句中的连接顺序

ORACLE采用自下而上的顺序解析WHERE子句，根据这个原理，表之间的连接必须写在其他WHERE条件之前，那些可以过滤掉最大数量记录的条件必须写在WHERE子句末尾。

例如：

（低效，执行时间156.3秒）

SELECT …

FROM EMP E

WHERE SAL>50000

AND JOB=’MANAGER’

AND 25<(SELECT COUNT(*) FROM EMP WHERE MGR=E。EMPNO)；

(高效，执行时间10.6秒)

SELECT …

FROM EMP E

WHERE 25<(SELECT COUNT(*) FROM EMP WHERE MGR=E.EMPNO)

AND SAL>50000

AND JOB=’MANAGER’;

7    用EXISTS替代IN

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

低效

SELECT *

FROM EMP（基础表）

WHERE EMPNO>0

AND DEPTNO IN(SELECT DEPTNO

FROM DEPT

WHERE LOC=’MELB’)

高效

SELECT *

FROM EMP（基础表）

WHERE EMPNO>0

AND EXISTS(SELECT ‘X’

FROM DEPT

WHERE DEPT.DEPTNO=EMP.DEPTNO

AND LOC=’MELB’)

【关于exists和in的区别】

exists()后面的子查询被称做相关子查询 他是不返回列表的值的.只是返回一个ture或false的结果(这也是为什么子查询里是"select 1"的原因，换成"select 6"完全一样，当然也可以select字段，但是明显效率低些)

其运行方式是先运行主查询一次 再去子查询里查询与其对应的结果 如果是ture则输出,反之则不输出.再根据主查询中的每一行去子查询里去查询.

in()后面的子查询 是返回结果集的,换句话说执行次序和exists()不一样.子查询先产生结果集,然后主查询再去结果集里去找符合要求的字段列表去.符合要求的输出,反之则不输出.因此，IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。通常情况下采用exists要比in效率高。

【 in与exists执行效率】

in数据量少效率还可以,数据量大就效率低

exists的效率依赖于匹配度。
8  用NOT EXISTS替代NOTIN

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

例如：

SELECT …

FROM EMP

WHERE DEPT_NO NOT IN(SELECT DEPT_NO

FROM DEPT

WHERE DEPT_CAT=’A’);

为了提高效率改写为：

（方法一：高效）

SELECT ….

FROM EMP A,DEPT B

WHERE A.DEPT_NO=B.DEPT(+)

AND B.DEPT_NO IS NULL

AND B.DEPT_CAT(+)=’A’

（方法二：最高效）

SELECT ….

FROM EMP E

WHERE NOT EXISTS(SELECT ‘X’

FROM DEPT D

WHERE D.DEPT_NO =E.DEPT_NO

AND DEPT_CAT=’A’)

9 使用or代替<>，!=操作符

select * from employee where age <> 36;

select * from employee where age > 36 or age < 36;

第一句效率不如第二句，因为第二句sql可以使用索引。

10 避免在order by字句中使用表达式。

order by语句执行效率低，因为要排序。应避免在order by字句中使用表达式。

11 避免混合类型表达式 

字段candidateno 为varchar2类型

select * from candidate where candidateno = 123;

oracle会建立隐含类型转换，隐含的类型转换oracle会使oracle忽略索引，这时应使用显式的类型转换：

select * from candidate where candidateno = to_char(123);

12 尽量避免使用distinct

distinct总是建立一个排序，所以查询速度也慢。

13 通配符‘%’，当通配符出现在搜索词首时，oracle优化器不使用索引

14 使用>=代替>，<=代替<

高效:
SELECT * FROM  EMP  WHERE  DEPTNO >=4
低效:
SELECT * FROM EMP WHERE DEPTNO >3
两者的区别在于, 前者DBMS将直接跳到第一个DEPT等于4的记录而后者将首先定位到DEPTNO=3的记录并且向前扫描到第一个DEPT大于3的记录.

15 用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”
如果你坚持要用OR, 那就需要返回记录最少的索引列写在最前面.

16 用Where子句替换HAVING子句

避免使用HAVING子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤. 这个处理需要排序,总计等操作. 如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销. (非oracle中)on、where、having这三个都可以加条件的子句中，on是最先执行，where次之，having最后，因为on是先把不符合条件的记录过滤后才进行统计，它就可以减少中间运算要处理的数据，按理说应该速度是最快的，where也应该比having快点的，因为它过滤数据后才进行sum，在两个表联接时才用on的，所以在一个表的时候，就剩下where跟having比较了。在这单表查询统计的情况下，如果要过滤的条件没有涉及到要计算字段，那它们的结果是一样的，只是where可以使用rushmore技术，而having就不能，在速度上后者要慢如果要涉及到计算的字段，就表示在没计算之前，这个字段的值是不确定的，根据上篇写的工作流程，where的作用时间是在计算之前就完成的，而having就是在计算后才起作用的，所以在这种情况下，两者的结果会不同。在多表联接查询时，on比where更早起作用。系统首先根据各个表之间的联接条件，把多个表合成一个临时表后，再由where进行过滤，然后再计算，计算完后再由having进行过滤。由此可见，要想过滤条件起到正确的作用，首先要明白这个条件应该在什么时候起作用，然后再决定放在那里

17 优化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

18 在查询频率高的Where子句、Order By、Group By的列上创建索引

注意索引失效的情况：

1、在索引列上使用函数。如SUBSTR,DECODE,INSTR等，对索引列进行运算.需要建立函数索引就可以解决了。

2、新建的表还没来得及生成统计信息，分析一下就好了

3、基于cost的成本分析，访问的表过小，使用全表扫描的消耗小于使用索引。

4、使用<>、not in 、not exist，对于这三种情况大多数情况下认为结果集很大，一般大于5%-15%就不走索引而走FTS。

5、单独的>、<。

6、like "%_" 百分号在前。

7、单独引用复合索引里非第一位置的索引列。

8、字符型字段为数字时在where条件里不添加引号。

9、当变量采用的是times变量，而表的字段采用的是date变量时.或相反情况。

10、索引失效，可以考虑重建索引，rebuild online。

11、B-tree索引 is null不会走,is not null会走,位图索引 is null,is not null  都会走、联合索引 is not null 只要在建立的索引列（不分先后）都会走。

3.1    使用表的别名（Alias）

当在SQL语句中连接多个表时，请使用表的别名并把别名前缀于每个Column上这样一来，就可以减少解析的时间并减少那些由Column歧义引起的语法错误。

2.1    删除重复记录

最高效的删除重复记录方法 ( 因为使用了ROWID)

DELETE FROM EMP E

WHERE E.ROWID >(SELECT MIN(X.ROWID)

FROM EMP X

WHERE X.EMP_NO = E.EMP_NO);

http://www.cnblogs.com/rootq/archive/2008/11/17/1334727.html