oracle 优化规则(2)

时间:2016-12-13 15:17 来源:linux.it.net.cn 作者:IT

13. 计算记录条数
　和一般的观点相反, count(*) 比count(1)稍快 , 当然如果可以通过索引检索,对索引列的计数仍旧是最快的。例如 COUNT(EMPNO)
    在CSDN论坛中,曾经对此有过相当热烈的讨论, 作者的观点并不十分准确,通过实际的测试,上述三种方法并没有显著的性能差别
14. 用Where子句替换HAVING子句
　避免使用HAVING子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤。这个处理需要排序,总计等操作。如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销。
    例如：
　低效：
     SELECT REGION, AVG(LOG_SIZE)
       FROM LOCATION
      GROUP BY REGION
     HAVING REGION REGION != 'SYDNEY' AND REGION != 'PERTH'
　高效
     SELECT REGION, AVG(LOG_SIZE)
       FROM LOCATION
      WHERE REGION REGION != 'SYDNEY'
        AND REGION != 'PERTH'
      GROUP BY REGION
    HAVING 中的条件一般用于对一些集合函数的比较,如COUNT() 等等。除此而外,一般的条件应该写在WHERE子句中
15. 减少对表的查询
　在含有子查询的SQL语句中,要特别注意减少对表的查询。
    例如：
　低效
      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)
      Update 多个Column 例子：
　低效：
           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_CATEGORIES)
           WHERE EMP_DEPT = 0020;
16. 通过内部函数提高SQL效率。
SELECT H.EMPNO, E.ENAME, H.HIST_TYPE, T.TYPE_DESC, COUNT(*)
FROM HISTORY_TYPE T, EMP E, EMP_HISTORY H
WHERE H.EMPNO = E.EMPNO
   AND H.HIST_TYPE = T.HIST_TYPE
GROUP BY H.EMPNO, E.ENAME, H.HIST_TYPE, T.TYPE_DESC;
　通过调用下面的函数可以提高效率。
FUNCTION LOOKUP_HIST_TYPE(TYP IN NUMBER) RETURN VARCHAR2 AS
TDESC VARCHAR2(30);
CURSOR C1 IS
    SELECT TYPE_DESC FROM HISTORY_TYPE WHERE HIST_TYPE = TYP;
BEGIN
OPEN C1;
FETCH C1
    INTO TDESC;
CLOSE C1;
RETURN(NVL(TDESC, '?'));
END;
FUNCTION LOOKUP_EMP(EMP IN NUMBER) RETURN VARCHAR2 AS
ENAME VARCHAR2(30);
CURSOR C1 IS
    SELECT ENAME FROM EMP WHERE EMPNO = EMP;
BEGIN
OPEN C1;
FETCH C1
    INTO ENAME;
CLOSE C1;
RETURN(NVL(ENAME, '?'));
END;
SELECT H.EMPNO,
       LOOKUP_EMP(H.EMPNO),
       H.HIST_TYPE,
       LOOKUP_HIST_TYPE(H.HIST_TYPE),
       COUNT(*)
FROM EMP_HISTORY H
GROUP BY H.EMPNO, H.HIST_TYPE;
    经常在论坛中看到如 '能不能用一个SQL写出…。' 的贴子, 殊不知复杂的SQL往往牺牲了执行效率。能够掌握上面的运用函数解决问题的方法在实际工作中是非常有意义的
17. 使用表的别名(Alias)
    当在SQL语句中连接多个表时, 请使用表的别名并把别名前缀于每个Column上。这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误。
    Column歧义指的是由于SQL中不同的表具有相同的Column名,当SQL语句中出现这个Column时,SQL解析器无法判断这个Column的归属
18. 用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 将更显著地提高效率,下一节中将指出
19. 用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');
20. 用表连接替换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
21. 用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核心模块将在子查询的条件一旦满足后,立刻返回结果。
22. 识别'低效执行'的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工具来解决问题始终是一个最好的方法 (责任编辑：IT)