Oracle语句优化的十九种方式
Oracle语句优化的十九种方式
孟飞阳 发表于1年前
Oracle语句优化的十九种方式
  • 发表于 1年前
  • 阅读 24
  • 收藏 2
  • 点赞 1
  • 评论 0

腾讯云 十分钟定制你的第一个小程序>>>   

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的归属。

八.用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,LOC_DESC,REGION
FROM LOCATION
WHERE REGION = “MELBOURNE”;

比下面的语句效率高:

SELECT LOC_ID,LOC_DESC,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语句都可以用其他方式重写。

标签: Oracle sql
共有 人打赏支持
粉丝 183
博文 799
码字总数 507685
作品 5
×
孟飞阳
如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!
* 金额(元)
¥1 ¥5 ¥10 ¥20 其他金额
打赏人
留言
* 支付类型
微信扫码支付
打赏金额:
已支付成功
打赏金额: