oralce sql 4

来源：互联网发布：智能家居网络拓扑图编辑：程序博客网时间：2024/06/03 16:45

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往往牺牲了执行效率. 能够掌握上面的运用函数解决问题的方法在实际工作中是非常有意义的)

========================================================

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工具来解决问题始终是一个最好的方法)

23. 使用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手册来了解具体的配置. )

=================================================

24. 用EXPLAIN PLAN 分析SQL语句

EXPLAIN PLAN 是一个很好的分析SQL语句的工具,它甚至可以在不执行SQL的情况下分析语句. 通过分析,我们就可以知道ORACLE是怎么样连接表,使用什么方式扫描表(索引扫描或全表扫描)以及使用到的索引名称.

你需要按照从里到外,从上到下的次序解读分析的结果. EXPLAIN PLAN分析的结果是用缩进的格式排列的, 最内部的操作将被最先解读, 如果两个操作处于同一层中,带有最小操作号的将被首先执行.

NESTED LOOP是少数不按照上述规则处理的操作, 正确的执行路径是检查对NESTED LOOP提供数据的操作,其中操作号最小的将被最先处理.

译者按:

通过实践, 感到还是用SQLPLUS中的SET TRACE 功能比较方便.

举例:

SQL> list

1 SELECT *

2 FROM dept, emp

3* WHERE emp.deptno = dept.deptno

SQL> set autotrace traceonly /*traceonly 可以不显示执行结果*/

SQL> /

14 rows selected.

Execution Plan

----------------------------------------------------------

   0 SELECT STATEMENT Optimizer=CHOOSE

   1 0 NESTED LOOPS

   2 1 TABLE ACCESS (FULL) OF 'EMP'

   3 1 TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'

   4 3 INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)

Statistics

----------------------------------------------------------

          0 recursive calls

          2 db block gets

         30 consistent gets

          0 physical reads

          0 redo size

       2598 bytes sent via SQL*Net to client

        503 bytes received via SQL*Net from client

          2 SQL*Net roundtrips to/from client

          0 sorts (memory)

          0 sorts (disk)

         14 rows processed

通过以上分析,可以得出实际的执行步骤是:

1. TABLE ACCESS (FULL) OF 'EMP'

2. INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)

3. TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'

4. NESTED LOOPS (JOINING 1 AND 3)



注: 目前许多第三方的工具如TOAD和ORACLE本身提供的工具如OMS的SQL Analyze都提供了极其方便的EXPLAIN PLAN工具.也许喜欢图形化界面的朋友们可以选用它们.

==================================================

25. 用索引提高效率

索引是表的一个概念部分,用来提高检索数据的效率. 实际上,ORACLE使用了一个复杂的自平衡B-tree结构. 通常,通过索引查询数据比全表扫描要快. 当ORACLE找出执行查询和Update语句的最佳路径时, ORACLE优化器将使用索引. 同样在联结多个表时使用索引也可以提高效率. 另一个使用索引的好处是,它提供了主键(primary key)的唯一性验证.

除了那些LONG或LONG RAW数据类型, 你可以索引几乎所有的列. 通常, 在大型表中使用索引特别有效. 当然,你也会发现, 在扫描小表时,使用索引同样能提高效率.

虽然使用索引能得到查询效率的提高,但是我们也必须注意到它的代价. 索引需要空间来

存储,也需要定期维护, 每当有记录在表中增减或索引列被修改时, 索引本身也会被修改. 这意味着每条记录的INSERT , DELETE , UPDATE将为此多付出4 , 5 次的磁盘I/O . 因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢.

译者按:

定期的重构索引是有必要的.

ALTER INDEX <INDEXNAME> REBUILD <TABLESPACENAME>

26. 索引的操作

ORACLE对索引有两种访问模式.

索引唯一扫描 ( INDEX UNIQUE SCAN)

大多数情况下, 优化器通过WHERE子句访问INDEX.

例如:

表LODGING有两个索引 : 建立在LODGING列上的唯一性索引LODGING_PK和建立在MANAGER列上的非唯一性索引LODGING$MANAGER.

SELECT *

FROM LODGING

WHERE LODGING = ‘ROSE HILL’;

   在内部 , 上述SQL将被分成两步执行, 首先 , LODGING_PK 索引将通过索引唯一扫描的方式被访问 , 获得相对应的ROWID, 通过ROWID访问表的方式执行下一步检索.

   如果被检索返回的列包括在INDEX列中,ORACLE将不执行第二步的处理(通过ROWID访问表). 因为检索数据保存在索引中, 单单访问索引就可以完全满足查询结果.

   下面SQL只需要INDEX UNIQUE SCAN 操作.



        SELECT LODGING

        FROM LODGING

WHERE LODGING = ‘ROSE HILL’;

索引范围查询(INDEX RANGE SCAN)

      适用于两种情况:

1. 基于一个范围的检索

2. 基于非唯一性索引的检索

例1:

      SELECT LODGING

      FROM LODGING

WHERE LODGING LIKE ‘M%’;

WHERE子句条件包括一系列值, ORACLE将通过索引范围查询的方式查询LODGING_PK . 由于索引范围查询将返回一组值, 它的效率就要比索引唯一扫描

低一些.

例2:

      SELECT LODGING

      FROM LODGING

WHERE MANAGER = ‘BILL GATES’;

这个SQL的执行分两步, LODGING$MANAGER的索引范围查询(得到所有符合条件记录的ROWID) 和下一步同过ROWID访问表得到LODGING列的值. 由于LODGING$MANAGER是一个非唯一性的索引,数据库不能对它执行索引唯一扫描.

由于SQL返回LODGING列,而它并不存在于LODGING$MANAGER索引中, 所以在索引范围查询后会执行一个通过ROWID访问表的操作.

WHERE子句中, 如果索引列所对应的值的第一个字符由通配符(WILDCARD)开始, 索引将不被采用.

SELECT LODGING

      FROM LODGING

WHERE MANAGER LIKE ‘％HANMAN’;

在这种情况下，ORACLE将使用全表扫描.