转载来源:IT专家网 作者:Peak Wong
1. 使用表的别名(Alias)
当在SQL语句中连接多个表时, 请使用表的别名并把别名前缀于每个Column上。这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误。
(Column歧义指的是由于SQL中不同的表具有相同的Column名,当SQL语句中出现这个Column时,SQL解析器无法判断这个Column的归属)
2. 用EXISTS替代IN
在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接。在这种情况下, 使用EXISTS(或NOT EXISTS)通常将提高查询的效率。
低效:
SELECT *
FROM EMP (基础表)
WHERE EMPNO > 0
AND DEPTNO IN (SELECT DEPTNO
FROM DEPT
WHERE LOC = ‘MELB’)
高效:
SELECT *
FROM EMP (基础表)
WHERE EMPNO > 0
AND EXISTS (SELECT ‘X’
FROM DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
AND LOC = ‘MELB’)
(相对来说,用NOT EXISTS替换NOT IN 将更显著地提高效率,下面将指出)
3. 用NOT EXISTS替代NOT IN
在子查询中,NOT IN子句将执行一个内部的排序和合并。 无论在哪种情况下,NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历)。 为了避免使用NOT IN ,我们可以把它改写成外连接(Outer 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(+)
AND B.DEPT_NO IS NULL
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’);
4. 用表连接替换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’ ;
(在RBO的情况下,前者的执行路径包括FILTER,后者使用NESTED LOOP)
5. 用EXISTS替换DISTINCT
当提交一个包含一对多表信息(比如部门表和雇员表)的查询时,避免在SELECT子句中使用DISTINCT. 一般可以考虑用EXIST替换
例如:
低效:
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);
EXISTS 使查询更为迅速,因为RDBMS核心模块将在子查询的条件一旦满足后,立刻返回结果。
6. 识别‘低效执行’的SQL语句
用下列SQL工具找出低效SQL:
SELECT EXECUTIONS , DISK_READS, BUFFER_GETS,
ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) Hit_radio,
ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run,
SQL_TEXT
FROM V$SQLAREA
WHERE EXECUTIONS>0
AND BUFFER_GETS > 0
AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8
ORDER BY 4 DESC;
(虽然目前各种关于SQL优化的图形化工具层出不穷,但是写出自己的SQL工具来解决问题始终是一个最好的方法)
7. 使用TKPROF 工具来查询SQL性能状态
SQL trace 工具收集正在执行的SQL的性能状态数据并记录到一个跟踪文件中。 这个跟踪文件提供了许多有用的信息,例如解析次数。执行次数,CPU使用时间等。这些数据将可以用来优化你的系统。
设置SQL TRACE在会话级别:
有效
ALTER SESSION SET SQL_TRACE TRUE
设置SQL TRACE 在整个数据库有效仿, 你必须将SQL_TRACE参数在init.ora中设为TRUE, USER_DUMP_DEST参数说明了生成跟踪文件的目录
(这里并没有提到TKPROF的用法, 对SQL TRACE的用法也不够准确, 设置SQL TRACE首先要在init.ora中设定TIMED_STATISTICS, 这样才能得到那些重要的时间状态。 生成的trace文件是不可读的,所以要用TKPROF工具对其进行转换,TKPROF有许多执行参数。 大家可以参考ORACLE手册来了解具体的配置。 )
分享到:
相关推荐
这篇文档“Oracle语句优化规则汇总.pdf”很可能提供了关于如何提高Oracle数据库查询效率、减少资源消耗的关键信息。以下是一些可能涵盖在文档中的关键知识点: 1. **索引优化**: - 使用合适的索引类型,如B树索引...
### 超酷30个Oracle语句优化规则详解 #### 一、选用适合的Oracle优化器 在Oracle数据库中,优化器的选择对于查询性能至关重要。Oracle提供了三种优化器模式来帮助用户根据实际情况选择最合适的执行计划: 1. **...
Oracle数据库是全球广泛使用的...通过遵循这些Oracle语句优化规则,你可以显著提高数据库的性能,减少资源消耗,同时提高应用程序的响应速度。持续学习和实践,结合具体的业务场景,你会发现更多优化的策略和技巧。
### Oracle语句优化53个规则详解 #### 一、选用适合的Oracle优化器 在Oracle数据库中,优化器的选择对于SQL语句的执行效率至关重要。Oracle提供了三种优化器模式: 1. **基于规则的优化器(RULE)**:这是一种较...
本文将深入探讨"性能优化案例分析与Oracle语句优化53个规则详解"这一主题,涵盖Oracle语句优化、数据库优化以及SQL优化的关键知识点。 首先,Oracle语句优化是提升系统性能的核心手段之一。在"Oracle语句优化53个...
本文将深入解析30个Oracle语句优化规则,聚焦于优化器的选择、表的访问方式以及SQL语句的共享机制。 首先,Oracle提供了三种优化器:基于规则的(RULE)、基于成本的(COST)和选择性(CHOOSE)。基于规则的优化器按照预...
以下是根据标题、描述和部分内容提炼出的一些Oracle语句优化规则: 1. **选择合适的优化器**: ORACLE提供了三种优化器:基于规则(RULE)、基于成本(COST)和选择性(CHOOSE)。基于成本的优化器(CBO)是推荐的...
"Oracle 语句优化" 本文将详细地介绍 Oracle 语句优化的方法和技巧,涵盖多表关联、EXISTS 和 IN 语句的使用、INDEX_JOIN 优化、WHERE 和 HAVING 语句的区别、NOT IN 和 <> 操作符的使用、LIKE 操作符的替代、UNION...
以下是对Oracle语句优化规则的总结:
本文将深入探讨Oracle语句优化的44个规则中的几个关键点,以帮助提升系统的执行性能和内存利用效率。 首先,Oracle提供了三种不同的优化器:基于规则(RULE)、基于成本(COST)和选择性(CHOOSE)。基于规则的优化...