30个Oracle语句优化规则详解(二)

日期: 2009-01-11 作者:gjm008 来源:TechTarget中国 英文

5. 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’; 

6.SELECT子句中避免使用‘ * ’

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

7.减少访问数据库的次数

当执行每条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 = 291; 

方法2(次低效)


DECLARE
CURSOR C1 (E_NO NUMBER) IS
SELECT EMP_NAME,SALARY,GRADE
FROM EMP
WHERE EMP_NO = E_NO;
BEGIN
OPEN C1(342);
FETCH C1 INTO …,..,.. ;
OPEN C1(291);
FETCH C1 INTO …,..,.. ;
CLOSE C1; END;

方法3(高效)


SELECT A.EMP_NAME,A.SALARY,A.GRADE,
B.EMP_NAME, B.SALARY, B.GRADE
FROM EMP A,EMP B
WHERE A.EMP_NO = 342
AND B.EMP_NO = 291; 

注意:

在SQL*Plus,SQL*Forms和Pro*C中重新设置ARRAYSIZE参数,可以增加每次数据库访问的检索数据量,建议值为200。

8.使用DECODE函数来减少处理时间

使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表。

例如:


SELECT COUNT(*),SUM(SAL)
FROM EMP
WHERE DEPT_NO = 0020
AND ENAME LIKE‘SMITH%’;
SELECT COUNT(*),SUM(SAL)
FROM EMP
WHERE DEPT_NO = 0030
AND ENAME LIKE‘SMITH%’; 

你可以用DECODE函数高效地得到相同结果

SELECT COUNT(DECODE(DEPT_NO,0020,’X’,NULL)) D0020_COUNT,
COUNT(DECODE(DEPT_NO,0030,’X’,NULL)) D0030_COUNT,
SUM(DECODE(DEPT_NO,0020,SAL,NULL)) D0020_SAL,
SUM(DECODE(DEPT_NO,0030,SAL,NULL)) D0030_SAL
FROM EMP WHERE ENAME LIKE‘SMITH%’; 

类似的,DECODE函数也可以运用于GROUP BY 和ORDER BY子句中。

9.整合简单,无关联的数据库访问

如果你有几个简单的数据库查询语句,你可以把它们整合到一个查询中(即使它们之间没有关系)

例如:


SELECT NAME
FROM EMP
WHERE EMP_NO = 1234;
SELECT NAME
FROM DPT
WHERE DPT_NO = 10 ;
SELECT NAME
FROM CAT
WHERE CAT_TYPE =‘RD’; 

上面的3个查询可以被合并成一个:

SELECT E.NAME , D.NAME , C.NAME
FROM CAT C , DPT D , EMP E,DUAL X
WHERE NVL(‘X’,X.DUMMY) = NVL(‘X’,E.ROWID(+))
AND NVL(‘X’,X.DUMMY) = NVL(‘X’,D.ROWID(+))
AND NVL(‘X’,X.DUMMY) = NVL(‘X’,C.ROWID(+))
AND E.EMP_NO(+) = 1234
AND D.DEPT_NO(+) = 10
AND C.CAT_TYPE(+) = ‘RD’; 

(译者按:虽然采取这种方法,效率得到提高,但是程序的可读性大大降低,所以读者还是要权衡之间的利弊)

 

我们一直都在努力坚持原创.......请不要一声不吭,就悄悄拿走。

我原创,你原创,我们的内容世界才会更加精彩!

【所有原创内容版权均属TechTarget,欢迎大家转发分享。但未经授权,严禁任何媒体(平面媒体、网络媒体、自媒体等)以及微信公众号复制、转载、摘编或以其他方式进行使用。】

微信公众号

TechTarget微信公众号二维码

TechTarget

官方微博

TechTarget中国官方微博二维码

TechTarget中国

电子邮件地址不会被公开。 必填项已用*标注

敬请读者发表评论,本站保留删除与本文无关和不雅评论的权力。

作者

gjm008
gjm008

相关推荐

  • Oracle依旧领跑关系型数据库市场

    Gartner研究机构在上个月发布了关系型数据库市场的相关数据,其中Oracle在过去的两年中依旧以较大的优势领跑该市场。

  • SQL语句优化的原则与方法

    优化就是WHERE子句利用了索引,不可优化即发生了表扫描或额外开销。经验证,SQL Server性能的最大改进得益于逻辑的数据库设计、索引设计和查询设计方面。

  • 30个Oracle语句优化规则详解(六)

    如何用用索引提高效率?索引是表的一个概念部分,用来提高检索数据的效率。实际上,Oracle使用了一个复杂的自平衡B-tree结构。通常,通过索引查询数据比全表扫描要快……

  • 30个Oracle语句优化规则详解(五)

    通常来说,采用表连接的方式比EXISTS更有效率,究竟该怎样用表连接替换EXISTS?我们采用什么方法识别‘低效执行’的SQL语句?本文对以上问题作出了详细解答。