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在解析的过程中，会将’* ‘依次转换成所有的列名，这个工作是通过查询数据字典完成的，这意味着将耗费更多的时间。
四．删除重复记录
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的归属。
八．用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