Oracle语句优化的十九种方式

孟飞阳 2012-02-09

一．选择合理的表名顺序

该方法只在基于规则的优化器中有效，基于代价时，ORACLE会根据表的物理大小，索引的状态，然后选用花费最低的执行路径。ORACLE的解析器按照从右到左的顺序处理FROM字句中的表名，因此FROM字句写在最后的表将被最先处理。

在FROM字句中的包含多个表的情况下，必须选择记录条数最少的表作为基础表（放在最后）。

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

例：

EMP表描述了LOCATION表和CATEGORY表的交集。

SELECT * FROM LOCATION L,CATEGORY C,EMP E
WHERE E.EMP_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.CAT_NO = C.CAT_NO
AND E.LOCN = L.LOCN
AND E.EMP_NO BETWEEN 1000 AND 2000

二．Where字句中的连接顺序

ORACLE采用自下而上的顺序解析where字句。

ORACLE总会在能使用索引的时候使用索引（除非表特别小），但如果有多个索引可供使用时，可能会因为顺序的不同导致效率的不同。考虑下面的sql语句：

SELECT ……
FROM EMP_REC
WHERE EMAIL = ‘liu.yuzhou’
And DEPT_NO = ‘010201’

如果在（DEPT_NO,EMAIL）上有联合索引，且DEPT_NO上有索引，则使用联合索引；如果在DEPT_NO和EMAIL上都有索引，但EMAIL上是唯一索引，则使用EMAIL上的索引；如果在DEMP_NO和EMAIL都有索引，且都为非唯一性索引，则先根据EMAIL检索记录，再根据DEPT_NO检索记录，然后取他们的交集。

所以，我们在写sql语句时，就应该考虑索引的使用情况。

如果无法使用索引，ORACLE会执行全表扫描，我们应将限制性强的条件放在后面。如我们知道一个部门大约有500人，而一个EMAIL大约对应5人，则我们应该将EMAIL=’liu.yuzhou’的条件放在后面。这样可以避免部分记录的二次比对。

三．避免使用SELECT *

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

四．删除重复记录

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

DELECT FROM EMP E
WHERE E.ROWID > (SELECT MIN(X.ROWID)
FROM EMP X
WHERE X.EMP_NO = E.EMP_NO);

五．减少对表的查询

例1：

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)

例2：

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_CAEGORIES)
WHERE EMP_DEPT = 0020;

六．使用存储函数提高效率

七．使用表的别名

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

Column歧义指的是由于sql中不同的表具有相同的Column名，当sql语句出现在这个Column时，sql解析器无法判断这个Column的归属。

@H_301_108@

八．用EXISTS替代IN

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

例：

SELECT * FROM EMP（基础表）
WHERE EMP_NO > 0
AND DEPTNO IN(SELECT DEPTNO FROM DEPT
WHERE LOC = ‘MELB’)

可改编为：

SELECT * FROM EMP（基础表）
WHERE EMP_NO > 0
AND EXISTS(SELECT ‘X’ FROM DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
AND LOC = ‘MELB’

九．用NOT EXISTS替代NOT IN

在子查询中，NOT IN字句将执行一个内部的排序和合并。无论在哪种情况下，NOT IN都是最低效的（因为它对子查询中的表执行了一个全表遍历）。为了避免使用NOT IN，我们可以把它改写成外连接（Out 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_NO(+)
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’);

十．用表连接替换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改编为连接，如DEPT_NO不唯一。

十一.用EXISTS替换DISTINCT

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

EXISTS使用查询更为迅速，因为REBMS核心模块将在子查询的条件一旦满足后，立刻返回结果。

例：找出有职员的部门

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);

十二.使用索引提高效率

索引是表的一个概念部门，用来提高检索数据的效率。通常，通过索引查询数据比全表扫描的要快。

同样在连结多个表时使用索引也可以提高效率。另一个使用索引的好处是，它提供了主键（primary Key）的唯一性验证。

使用索引时也必须注意到它的代价。索引需要空间存储，也需要定期维护，每当有记录在表中增减或索引列被修改时，索引本身也会修改。这意味着每条记录的INSERT，DELECT，UPDATE将为此多付出4,5次的磁盘I/O。因为索引需要额外的存储空间和处理，那些不必要的索引反而会使查询反应时间变慢。

ORACLE对索引有两种访问模式：

索引唯一扫描（INDEX UNIQUE SCAN）

索引范围查询（INDEX RANGE SCAN）

适用于两种情况：

1）基于一个范围的检索

2）基于非唯一性索引的检索

在前面已经讲了一些使用索引的情况，下面再补充几种：

当WHERE字句中有多个索引列，且包含非’=’号时，ORACLE会放弃使用非’=’号的索引：

例：

DEPTNO上有一个非唯一性索引，EMP_CAT也有一个非唯一性索引

SELECT ENAME FROM EMP

WHERE DEPTNO > 20 AND EMP_CAT = ‘A’;

这里只有EMP_CAT索引被用到，然后所有的记录将逐条与DEPTNO条件进行比较，执行路径如下：

TABLE ACCESS BY ROWIDON EMP

INDEX RANGESCAN ON CAT_IDX

这是因为DEPTNO > 20的条件可能会检索出大量记录，而EMP_CAT = ‘A’可能只检索出少量记录。合并操作比如上面的方式划算。

当WHERE字句中有多个索引列，且都为非’=’号时，ORACLE将只使用一个索引：

例：

DEPTNO上有一个非唯一性索引，EMP_CAT也有一个非唯一性索引。

SELECT ENAME FROM EMP

WHERE DEPTNO > 20 AND EMP_CAT > ‘A’;

这里，ORACLE只会用到其中一个索引（用哪个视优化模式，统计信息而定），执行路径如下（假使使用DEPTNO上的索引）；

TABLE ACCESS BY ROWID ON EMP

INDEX RANGE SCAN ON DEPT_IDX

十三.强制索引失效

如果两个或以上索引具有相同的等级，而我们只想使用其中的一个（通过它，检索出的记录数量少），我们可以使用下面的方法：

SELECT ENAME
FROM EMP
WHERE EMPNO = 7935
AND DEPTNO = 10
AND EMP_TYPE = ‘A’;

如果我们只想用到EMPNO上的索引（相对另外两个条件记录数量较少，做合并不划算），则可改编为：

SELECT ENAME
FROM EMP
WHERE EMPNO = 7935
AND DEPTNO + 0 = 10   /*DEPTNO上的索引将不会使用*/

同样，如果我们想使用某个列上的索引，则不能对此列做运算，例：

SELECT ……
FROM DEPT
WHERE SAL * 12 > 25000;

不能使用SAL列上的索引，可改编为：

SELECT ……
FROM DEPT
WHERE SAL > 25000/12;

十四.用>=替代>

如果DEPTNO上有一个索引，则：

SELECT * FROM EMP
WHERE DEPTNO >= 4

比下面的语句更有效率：

SELECT * FROM EMP
WHERE DEPTNO > 3;

两者的区别在于，前者将直接跳到第一个DEPT等于4的记录而后者将首先定位到DEPTNO = 3的记录并且想前扫描到第一个DEPT大于3的记录。

十五.用UNION替换OR

通常情况下，用UNION替换WHERE字句中的OR将会起到较好的效果。对索引列使用OR将造成全表扫描。注意，以上规则只针对多个索引列有效，如果有列没有被索引，查询效率可能会因为没有选择OR而降低。

在下面的例子中，LOC_ID和REGION上都建有索引，则：

SELECT LOC_ID,LOC_DESC,REGION
FROM LOCATION
WHERE LOC_ID = 10
UNION
SELECT lOC_ID,REGION
FROM LOCATION
WHERE REGION = “MELBOURNE”;

比下面的语句效率高：

SELECT LOC_ID,REGION
FROM LOCATION
WHERE LOC_ID = 10 OR REGION = “MELBOURNE”;

十六.IN 和OR

下面两个查询等价：

SELECT ……
FROM LOCATION
WHERE LOC_ID = 10
OR LOC_ID = 20;

SELECT …… FROM LOCATION
WHERE LOC_IN IN(10,20);

在9i后，ORACLE可以根据优化模式选择合并方式或直接IN检索方式。

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

对于单列索引，如果列包含空值，索引中将不存在此记录。

对于复合索引，如果单个列都为空，索引中同样不存在此记录。如果至少有一个列不为空。则记录存在于索引中。

因为空值不存在于索引中，所以WHERE字句中对索引列进行空值比较将使ORACLE停用该索引。

下面的例子不使用索引：

SELECT ……
FROM DEPARTMENT
WHERE DEPT_CODE IS NOT NULL;

可以改编如下（如果为字符，可根据情况写为>’a’）;

SELECT ……
FROM DEPARTMENT
WHERE DEPT_CODE >= 0;

十八.用UNION-ALL替换UNION

当sql语句需要UNION两个查询结果集合时，这两个结果集合会以UNION-ALL的方式被合并，然后在输出最终结果前进行排序，并将重复记录过滤掉。

如果用UNION-ALL替代UNION，这样排序就不是必要的了，效率会因此得到提高。

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

十九.其他需要注意的问题

‘!=’将不使用索引。索引只能告诉你什么存在于表中，而不能告诉你什么不存在于表中。

基于成本优化器（CBO,Cost-Based Optimizer）对索引的选择性进行判断来决定索引的使用是否能提高效率。

如果索引有很高的选择性，那就是说对于每个不重复的索引键值，只对应数量很少的记录。

比如，表中共有100条记录而其中有80个不重复的索引键值，这个索引的选择性就是80/100=0.8，选择性越高，通过索引键值检索出的记录就越少。

如果索引的选择性很低，检索数据就需要大量的索引范围查询操作和ROWID访问表的操作，也许会比全表扫描的效率更低。

带有DISTINCT，UNION，MINUS，INTERSECT，ORDER BY的sql语句会执行耗费资源的排序（SORT）功能。DISTINCT需要一次排序操作，而其他的至少需要执行两次排序。

例如。一个UNION查询，其中每个查询都带有GROUP BY字句，GROUP BY会触发嵌入排序（NESTED SORT）；这样，每个查询需要执行一次排序，然后在执行UNION时，有一个唯一排序（SORT UNIQUE）操作被执行而且它只能在前面的嵌入排序结束后才能开始执行。嵌入顺序的深度会大大影响查询的效率。

通常，带有UNION，MINUS，INTERSECT的sql语句都可以用其他方式重写。

原文链接：https://www.f2er.com/oracle/213562.html