Oracle 优化 ---学习

Oracle 优化

1.SQL语句解析：

       从后面往前(从右到左)的解析顺序:  select  id from t1,t2,.......tn      tn先解析，解析完tn再解析tn-1，合并tn与tn-1数据，再解析tn-2，所以记录最少的表应该放到最后，即基表

2.多表查询，交叉表作为基表

     EMP表描述了LOCATION表和CATEGORY表的交集

    a.低效  

n         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

       b.高效         

        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

   3.Where 后过滤条件           

        ORACLE 采用自下而上的顺序解析 WHERE 子句 , 根据这个原理 , 表之间的连接必须写在其他 WHERE 条件之前 , 那些可以过滤掉最大数量记录的条件必须写在 WHERE 子句的末尾 .
        a.低效
         SELECT * FROM EMP E  WHERE SAL > 50000 AND JOB = ‘MANAGER'
                                                              AND 25 < (SELECT COUNT(*) FROM EMP WHERE MGR=E.EMPNO);
         b.高效
         SELECT * FROM EMP E WHERE 25 <(SELECT COUNT(*) FROM EMP WHERE MGR=E.EMPNO);
                                                             AND SAL>50000 AND JOB = 'MANAGER';
   4 select * from table
          * 效率极低，*要转换成所有的列名，通过数据字典来实现，消耗更多的时间
  5 SQL执行
       ORACLE在内部执行了许多工作: 解析SQL语句, 估算索引的利用率, 绑定变量 , 读数据块等等. 由此可见, 减少访问数据库的次数 , 就能实际上减少ORACLE的工作量.
        a.低效
            SELECT EMP_NAME , SALARY , GRADE FROM EMP WHERE EMP_NO = 342;
           SELECT EMP_NAME , SALARY , GRADE FROM EMP WHERE EMP_NO = 291;
        b.次低效(使用游标)
        c.高效
           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;
   6  HAVING和WHERE   GROUP+HAVING < WHERE +GROUP
      避免使用HAVING子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤. 这个处理需要排序,总计等操作. 如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销
             a.低效
              SELECT REGION，AVG(LOG_SIZE) FROM LOCATION GROUP BY REGION HAVING REGION REGION != ‘SYDNEY' AND REGION != ‘PERTH'
             b.高效
             SELECT REGION，AVG(LOG_SIZE) FROM LOCATION WHERE REGION REGION != ‘SYDNEY' AND REGION != ‘PERTH' GROUP BY REGION
   7 子查询的语句，减少对表的查询
     a.低效     
      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)
      b.高效
        SELECT TAB_NAME FROM TABLES WHERE (TAB_NAME,DB_VER) =
    ( SELECT TAB_NAME,DB_VER) FROM TAB_COLUMNS WHERE VERSION = 604)
     a1.低效
       UPDATE EMP SET EMP_CAT = (SELECT MAX(CATEGORY) FROM EMP_CATEGORIES),  SAL_RANGE = (SELECT MAX(SAL_RANGE) FROM EMP_CATEGORIES) WHERE EMP_DEPT = 0020;
     b1.高效
        UPDATE EMP SET (EMP_CAT, SAL_RANGE) = (SELECT MAX(CATEGORY) , MAX(SAL_RANGE) FROM EMP_CATEGORIES) WHERE EMP_DEPT = 0020;
    8.SQL链接多个表时，尽量用表的别名，减少解析时间及由Column引发的奇异(貌似与2NF冲突)
    9 EXITS(NOT EXITS) 高效于IN(NOT IN)
      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 * FROM DEPT WHERE
                                                                                                                     DEPT.DEPTNO = EMP.DEPTNO AND LOC = ‘MELB')
      10 当提交一个包含一对多表信息(比如部门表和雇员表)的查询时,避免在SELECT子句中使用DISTINCT. 一般可以考虑用EXIST替换
         a.低效
               SELECT DISTINCT DEPT_NO,DEPT_NAME FROM DEPT D,EMP E WHERE D.DEPT_NO = E.DEPT_NO
         b.高效
            SELECT DEPT_NO,DEPT_NAME FROM DEPT D WHERE EXISTS ( SELECT * FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO);
         EXISTS 使查询更为迅速,因为RDBMS核心模块将在子查询的条件一旦满足后,立刻返回结果.