oracle sql优化学习笔记

1. select 语句中避免使用'*'，这种方法非常低效，在oracle解析过程中会将*转换成所有的列名，这个工作通常要查询数据库对象数据字典来完成，耗费了更多的时间。
2. 减少数据库的访问次数，实际当执行sql时候，oracle内部执行了很多工作，解析、估算索引利用率、绑定变量、读数据块等。例如sql也可以这样写： 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。
3. 使用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子句中.

 

     4.  访问Table的方式有两种，全表扫描（顺序的访问表中每条记录，一次读入多个数据块）和通过ROWID访问表（包含了表中数据存放的物理位置）。oracle采用了索引（index）实现了数据和存放位置的联系。所以通过索引就能快速的查询就可以得到性能上的提高。

     5.  删除重复记录

         最高效的删除方法使用rowid

         例如：DELETE FROM EMP E 
                  WHERE E.ROWID > (SELECT MIN(X.ROWID)
 
                   FROM EMP X
 
                   WHERE X.EMP_NO = E.EMP_NO);

      6.  统计计算记录数

           count(*)>count(1)，当然如果可以通过索引列检索，对索引列的检索依然是最快的，如：count(empno).

      7.  where条件语句的写法

           在下面的例子中，‘！=’将不使用索引。索引只能告诉你什么存在表中，而不能告诉你什么不存在表中。

           不使用索引: SELECT ACCOUNT_NAME FROM TRANSACTION WHERE AMOUNT !=0;

           使用索引: SELECT ACCOUNT_NAME FROM TRANSACTION WHERE AMOUNT >0;

           ‘||’是字符连接函数. 就象其他函数那样, 停用了索引.

           ‘+’是数学函数. 就象其他数学函数那样, 停用了索引.

            如果一定要对使用函数的列启用索引, ORACLE新的功能: 基于函数的索引(Function-Based Index) 也许是一    个较好的方案.  CREATE INDEX EMP_I ON EMP (UPPER(ename)); /*建立基于函数的索引*/

SELECT * FROM emp WHERE UPPER(ename) = ‘BLACKSNAIL’; /*将使用索引*/

     8.  用exist替换distinct。

          避免在sql语句中使用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);