ORACLE性能优化(五/六)
17.使用表的别名(Alias)
当在SQL语句中连接多个表时,请使用表的别名并把别名前缀于每个Column上.这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误.
(译者注:Column歧义指的是由于SQL中不同的表具有相同的Column名,当SQL语句中出现这个Column时,SQL解析器无法判断这个Column的归属)
18.用EXISTS替代IN
在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下,使用EXISTS(或NOTEXISTS)通常将提高查询的效率.
低效:
SELECT*
FROMEMP(基础表)
WHEREEMPNO>0
ANDDEPTNOIN(SELECTDEPTNO
FROMDEPT
WHERELOC=‘MELB')
高效:
SELECT*
FROMEMP(基础表)
WHEREEMPNO>0
ANDEXISTS(SELECT‘X'
FROMDEPT
WHEREDEPT.DEPTNO=EMP.DEPTNO
ANDLOC=‘MELB')
(译者按:相对来说,用NOTEXISTS替换NOTIN将更显著地提高效率,下一节中将指出)
19.用NOTEXISTS替代NOTIN
在子查询中,NOTIN子句将执行一个内部的排序和合并.无论在哪种情况下,NOTIN都是最低效的(因为它对子查询中的表执行了一个全表遍历).为了避免使用NOTIN,我们可以把它改写成外连接(OuterJoins)或NOTEXISTS.
例如:
SELECT…
FROMEMP
WHEREDEPT_NONOTIN(SELECTDEPT_NO
FROMDEPT
WHEREDEPT_CAT='A');
为了提高效率.改写为:
(方法一:高效)
SELECT….
FROMEMPA,DEPTB
WHEREA.DEPT_NO=B.DEPT(+)
ANDB.DEPT_NOISNULL
ANDB.DEPT_CAT(+)=‘A'
(方法二:最高效)
SELECT….
FROMEMPE
WHERENOTEXISTS(SELECT‘X'
FROMDEPTD
WHERED.DEPT_NO=E.DEPT_NO
ANDDEPT_CAT=‘A');
20.用表连接替换EXISTS
通常来说,采用表连接的方式比EXISTS更有效率
SELECTENAME
FROMEMPE
WHEREEXISTS(SELECT‘X'
FROMDEPT
WHEREDEPT_NO=E.DEPT_NO
ANDDEPT_CAT=‘A');
(更高效)
SELECTENAME
FROMDEPTD,EMPE
WHEREE.DEPT_NO=D.DEPT_NO
ANDDEPT_CAT=‘A';
(译者按:在RBO的情况下,前者的执行路径包括FILTER,后者使用NESTEDLOOP)
21.用EXISTS替换DISTINCT
当提交一个包含一对多表信息(比如部门表和雇员表)的查询时,避免在SELECT子句中使用DISTINCT.一般可以考虑用EXIST替换
例如:
低效:
SELECTDISTINCTDEPT_NO,DEPT_NAME
FROMDEPTD,EMPE
WHERED.DEPT_NO=E.DEPT_NO
高效:
SELECTDEPT_NO,DEPT_NAME
FROMDEPTD
WHEREEXISTS(SELECT‘X'
FROMEMPE
WHEREE.DEPT_NO=D.DEPT_NO);
EXISTS使查询更为迅速,因为RDBMS核心模块将在子查询的条件一旦满足后,立刻返回结果.
22.识别'低效执行'的SQL语句
用下列SQL工具找出低效SQL:
SELECTEXECUTIONS,DISK_READS,BUFFER_GETS,
ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2)Hit_radio,
ROUND(DISK_READS/EXECUTIONS,2)Reads_per_run,
SQL_TEXT
FROMV$SQLAREA
WHEREEXECUTIONS>0
ANDBUFFER_GETS>0
AND(BUFFER_GETS-DISK_READS)/BUFFER_GETS<0.8
ORDERBY4DESC;
(译者按:虽然目前各种关于SQL优化的图形化工具层出不穷,但是写出自己的SQL工具来解决问题始终是一个最好的方法)
23.使用TKPROF工具来查询SQL性能状态
SQLtrace工具收集正在执行的SQL的性能状态数据并记录到一个跟踪文件中.这个跟踪文件提供了许多有用的信息,例如解析次数.执行次数,CPU使用时间等.这些数据将可以用来优化你的系统.
设置SQLTRACE在会话级别:有效
ALTERSESSIONSETSQL_TRACETRUE
设置SQLTRACE在整个数据库有效仿,你必须将SQL_TRACE参数在init.ora中设为TRUE,USER_DUMP_DEST参数说明了生成跟踪文件的目录
(译者按:这一节中,作者并没有提到TKPROF的用法,对SQLTRACE的用法也不够准确,设置SQLTRACE首先要在init.ora中设定TIMED_STATISTICS,这样才能得到那些重要的时间状态.生成的trace文件是不可读的,所以要用TKPROF工具对其进行转换,TKPROF有许多执行参数.大家可以参考ORACLE手册来了解具体的配置.)