Oracle优化之表访问调优

注意： 文章中的各种颜色： 深红色代表需要特别强调的而且是定律性质的东东；深蓝色代表需要强调的数据库的关键字；淡红色代表SQL语句中的注释；

概述:   优化器并不总能选择最优的访问计划,所以我们要能够评估优化器的选择,有时候需要我们促成使用一个不同的访问路径.而且为了给优化器提供可能的最优访问路径,需要我们创建适当的索引\聚簇,或者可能是为表分区.

将讨论一下内容:

单值查询 ；

避免"意外的"表扫描;

多列查询;

查找范围;

使用 LIKE 运算符;

多值单列查找;

优化必要的全表扫描。

1.  单值查询

     先看一个例子:

            SELECT   *  

               FROM    staff   s  

              WHERE    s.birthDay = :birth

            解决这样的查询主要有两种方法：读取表中的所有记录查找匹配的值，或者利用索引或者聚簇的形式更加直接的找出匹配的记录。最有效       的 方法将取决于WHERE子句条件的选择性。

           高选择性的列或者条件将返回所有记录中低比列的记录.主键和唯一键的列选择性最高,相比而言,只有很少唯一值的列(比如性别)选择性较        低.

            我们要知道,表扫描有时候比同等作用的索引查询消耗更少的资源: 通常在选择条件涵盖表的大部分数据的时候都是这样的.表扫描也能更好     的利用并行处理能力.

            索引检索需要读取索引块和(通常也要读取)表块。而且索引扫描期间，oracle可能需要轮流读取索引块和表块，而且还有可能多次读取某       个单独的索引块。如果表的大部分数据被访问，使用索引的开销就可能大于扫描全表的开销。

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

                             一般来说，在WHERE子句的条件选择性不是很高的时候，全表扫描是最适合 的检索路径。                                                                               而在条件的选择性很高的时候，索引或者聚簇方法将更适合。                                                       

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

1.1  在表和索引扫描间选择

        以下因素会影响到索引检索:

        a .  缓冲区高速缓存命中率      索引检索有助于在Oracle的缓冲区高速缓存中获得更高的命中率,相比而言全表扫描获得的命中率通常较低.这

              有助于提升性能.

        b.   记录大小       就IO而言,无论记录多大,每个索引访问的开销几乎都是相同的.然而,记录越长，全表扫描就必须读取的数据块就越多。

        c.    数据分布      如果表中的记录大体按照索引列的顺序存储（记录是按照主键的顺序插入的，就可能发生这样的情况），那么索引可能需                  要访问更少的块并大大减少逻辑IO的开销。

       

        下图比较了各种检索方式性能,横轴为访问的数据占全表的=记录的比例,纵轴为消耗的时间

          

 

以下是一些不容置疑的建议  :

A .   如果需要访问表的大部分记录，那么全表扫描是做这件事的最快方法。

B .   如果大表中检索一条记录，那么基于该列的索引将提供更快捷的检索路径。

C .   在这两个极端之间，他要预见哪种访问路径更快是很困难的。

在很多情况下，我们要么从表中选取少量的记录以满足事务型查询（OLTP） ，要么查询大量的记录以满足决策支持性查询（OLAP）。在这 两种情况下，我们的决策将很简单：对OLTP查询使用索引或者散列，对OLAP查询使用全表扫描。  

 

优化器如何在索引扫描和全表扫描之间选择?——影响优化器决策的因素如下：

A .  进行全表扫描需要读取的数据块数目。

B .  进行索引查询需要读取的数据块数目。这主要基于对WHERE子句谓词返回的记录数的估计。

C .  进行全表扫描时多块读的相关开销，以及为满足索引查询进行的单块读的开销。

D .  内存中对缓存中的索引块和数据块的假设 。 最关键的因素还是优化器对查询将要返回的记录数的评估。

——确保为数据分布不均的列收集直方图，以保证优化器在索引扫描和全表扫描之间做出最佳的选择。

      

帮助优化器

A .  优化器目标被设置为 ALL_ROWS ,但需求实际上是相应时间. 全表扫描可以检索所有记录时带来最快的响应时间 ， 而索引扫描通常在检索开        始的第一条记录或者前几条记录时速度更快。 这种情况下，一个很好的建议是 将 OPTIMIZER_MODE 参数设置为 FIRST_ROWS或者

       FIRST_ROWS_N （这里的N为1、10、100或者1000）。

B .   全表扫描倾向于在单个操作中一次性从磁盘读取多个块，相比而言索引查询一次只读取一个块。

C .   基于索引的执行计划更能够从缓冲区高速缓存只能怪的缓存块上获益。参数OPTIMIZER_INDEX_CACHING 可用来改变优化器估算缓存相关

        行为。其值设置得高可以导致优化器降低评估中的索引读的IO的开销。

——通过维护精确的对象统计信息（利用直方图），并收集系统统计信息，这些都可以帮助优化器在索引扫描和全表扫描之间做出选择。特别是  OPTIMIZER_MODE 和  OPTIMIZER_INDEX_CACHING 这样的惨呼声对优化器是否使用索引有很强烈的影响。

          可以使用提示来引导优化器.比如使用 FULL或者INDEX提示.例如,为了获得基于全表扫描的查询计划,我们可以使用FULL:

                   SELECT    /*+     FULL(s)   */   AVG(staffAge)

                       FROM   staff   s  

                     WHERE    s.staffName = :sname ;

           同样也可以获得基于索引的查询计划，如下

                   SELECT    /*+     INDEX(s)   */   AVG(staffAge)

                       FROM   staff   s  

                     WHERE    s.staffName = :sname ;

         

位图索引和单值查询

A .    多数实践表明:即使列上存在数以千计的唯一值时,位图索引仍然能白哦先出很好的性能。然而，仅当列的唯一值相对较少时，位图索引的性           能才会远优于B*tree索引。

散列聚簇和单值查询,如下:

          

                          从上图可以看出,随着匹配记录数的增加散列表的检索性能出现下降

2.  避免 “意外的” 表扫描

  

2.1  不等条件

       如果使用了不等操作符（<> 或者 != ），那么Oracle一般不会使用索引。不过想想这也是合理的啊，因为在检索除了匹配一个唯一值的记录外的其他记录时，全表扫描通常是获得数据的最快的方法。

2.2  空值查询

        当索引列中的数据都为空值时，B*tree是引用中条目将不会被创建。因此，不能使用某个列上的B*tree索引来查找空值。如果要查询索引列          上的空值，就必须进行全表扫描。也可以通过给空值指定默认值后，使用索引扫描。如下：

        ALTER    TABLE  表名称    MODIFY    列名称    DEFAULT   ' 默认值 ' ;

2.3    在可为空的列上创建索引，但这取决于需求。如果符合下列条件中的全部或者部分，在可以为空的列上创建索引也是值得的。
          A .  列几乎一直为空。

          B .   我们从不想去查找该列为空的记录。

          C .   我们想查找那些该列为非空的记录。

          D .   我们想最小化索引需要占用的空间。

2.4  无意中使用函数而禁用了索引

        如果通过函数或者表达式来操纵列，那么优化器将无法使用该列上的索引。 举例如下：

          我们在staff的birthDay上创建了索引.两个查询如下.为了便于表达,我把SQL写在了画图软件中.

               

 

      

 

2.5   函数索引

        如果要在索引列上使用函数,则可以在该列上创建函数索引 。 如下的语句：

               SELECT   staffID , phoneNumber

                   FROM    staff  s

                  WHERE   UPPER (s.staffname)  =  UPPER (:sname)

                        AND  UPPER(s.loginame)  = UPPER(:logname)

         创建函数索引：

               CREATE   INDEX   staffname_function_index   ON   staff (UPPER(staffname) , UPPER(loginname) )

          注意: 定义函数索引的表达式可以使用自定义的函数或内建函数.如果是用户自定义函数,那么这些函数创建时必须包含DETERMINISTIC                           这个关键字.这个关键字的声明表明了,无论何时只要对其应用相同的输入,函数就必须返回相同的输出 。 举个列。定义了一个函数                         chkTimeStr,它返回一个时间变化的字符串。如，它返回  SYSDATE - birthday  ，那么这个表达式就是随着时间的变化而变化。所以                    用这个函数来创建函数索引是无效的。关键字DETERMINISTIC使用如下:                     

                     
 

3.  多列查询

      概述：当WHERE子句中有多个条件时，我们可以用如下办法完成这个查询

                  A . 对选择性最高的列使用单列索引。

                  B . 对WHERE子句中引用的两列或多列使用组合索引。

                  C . 使用多个索引合并结果。

                  D . 使用全表扫描。

3.1 使用组合索引

      如果我们查询一个表中的多列值，对所有这些值的组合索引通常是最高效的检索方式。

3.2 索引合并

      Oracle可能使用多个索引处理对多个列的查询。在进行索引合并时，Oracle可能将索引条目转化为位图，然后使用位图操作来合并结果。我         们可以使用 INDEX_COMBINE 提示促使Oracle进行索引合并，例如：

              SQL> EXPLAIN   PLAN  FOR

                                      SELECT  /*+      INDEX_COMBINE(s ,  INDEX_1,INDEX_2,INDEX_3)          */         staffid

                                          FROM   staff   s

                                          WHERE   s.staffname='zyj'  -----此列上有INDEX_1

                                          AND         s.loginnane='zyj'  -----此列上有INDEX_2

                                          AND         s.pwd='123456'  -----此列上有INDEX_3

               在O在这个执行计划里面，你会看到一个叫BITMAP  CONVERSION  FROM  ROWIDS 这么个操作。就应正了上面的说法。

       ——总结，索引合并通常没有组合索引高效，而如果相关的列选择性不高（唯一值数目很少）则性能也可能低于全表扫描。而对于选择性不                  高的列来说，位图索引合并会很高效。但切记，位图索引会带来显著的锁开销（这点，至少理论这样说）。

3.3   唯一性与覆盖索引

        举例说明。在我的staff表中，有 Mobilephone字段来表示用户的手机号。如果我们确定不可能有两个人拥有相同的收集号码，那么我可以通         过指定唯一索引来稍加优化。这是为什么呢？——当使用了唯一索引，Oracle可以确定找到的第一条匹配记录就是唯一的匹配记录，这样就         节省了IO，因为一旦发现了匹配的记录，Oracle就可以停止扫描索引。

3.4   范围扫描

         范围扫描可以利用直方图、提示已经常值变量来促使Oracle做出更好的选择。

3.5  使用  LIKE  运算符

 

        仅当查询的字符串不以（% ，_）开头的时，oracle才能够使用B*Tree索引高效的处理包含LIKE运算符的查询。

3.6  优化必要的全表扫描

      

        A .  通过让表变小来减少扫描所需的逻辑块读取的数量；

        B .  通过提高数据库IO的效率来降低扫描所需的物理块读取的数量；

        C .  通过缓存或者抽样降低扫描的开销；

        D .  通过使用并行查询选项为扫描分配更多的资源；

        E .  执行快速全索引扫描来替代全表扫描；

        F .  对表进行分区，只读所选择的分区。

        G .  降低全表扫描的所需读的数据块的数量：通过重建表来降低高水位线、修改PCTFREE或者PCTUSED参数、减少记录长度（可能是               将大的且并不经常访问的列迁移到独立的表中）、压缩表中的数据。

3.6.1   降低高水位线

         实话说，这玩意貌似，我也是第一次听说。但，这没关系，什么东西都是从不到是。呵呵。还是举例说明更加形象。如下：

         当需要进行全表扫描时，Oracle会读取从第一个被分配的块到曾经包含数据的最高的那个块之间的每一个块，这个  “最高”块就是高             水位。假如我们往往staff表中插入很多数据，导致100个块被使用，那么接下来的全表扫描将执行大约100个数据块读。即使我们       删             除了表中的每一条记录，高水位线仍然在第100个数据块处，并且表扫描将仍然需要读取大约100个数据块。

                  因此，可以很明确的说，如果一张表上经常发生大量的删除操作，高水位线将比它实际需要的位置更高。平均每个块里面保存的记录           数就会减少，减少记录的IO开销就会增加。

                  必要的时候我可以重建表。语法为：  ALTER  TABLE  表名称   MOVE  。还可以使用下面两条命令来压缩表：

           ALTER   TABLE   表名称    ENABLE   ROW   MOVEMENT  ; -----必须先执行这行命令，用于激活行迁移表选项。

           ALTER    TABLE   表名称    SHRINK  SPACE ;

          ——总结，从表中删除数据并不会降低全表扫描的IO量，除非对表做重建或者空间收缩。注意： 只有当一张表包含的记录远远少于它曾经                  拥有的记录时，才需要重建或者收缩空间（SHRINK SPACE）以重置高水位线。这将减少执行全表扫描时需要的读取的数据块数目。

         

3.6.2   优化 PCTFREE  和 PCTUSED 

          PCTFREE控制着每个块中为了使记录变长的变更而保留的空间比例。

          PCTUSED 控制当块中的数据删除掉多大比例时这个块可被用来插入，由于现在Oracle普片采用自动断存储管理（ASSM），所以            PCTUSED几乎没意义了。降低PCTFREE的值或者提高PCTUSED的值可以增加块中的记录数，它可以用来降低所需的数据块的数目，从             而降低全表扫描的额外开销。语法如下：

                    ALTER  TABLE  表名称   PCTFREE  值大小  ;

             例如，我可以在创建表空间的时候，指定存储段管理的方式：

                 CREATE   TABLESPACE   asm_ts  

                               .......   -----其他部分省略

                             SEGMENT  SPACE  MANAGEMENT  AUTO ;

                 CREATE   TABLE  zyjTable (......)  TABLESPACE   asm_ts ;

           ——总结，如果一张表上经常进行表扫描，确保PCTFREE不高于它所需要的值，尤其当表未被更新时。如果表空间没有使用ASSM，还需                   考虑提高PCTUSED的值。

3.6.3   减少记录长度

          如果表上精心全表扫描，而且包含大的不经常访问的列，可以通过将这些列迁移到其他表中来减少需要扫描的块。对于BLOB和CLOB可以           使用 DISABLE  STORAGE   IN  ROW 设置。如：

                    CREATE    TABLE  test_lob_table

                       (

                    id    number    NOT    NULL   PRIMARY   KEY ,

                    lobdata    blob

                       )  LOB( lobdata )  STORE   AS  ( DISABLE   STORAGE   IN  ROW  )  ;   ------如果DISABLE这个属性,那么lob数据会在行外存储,         行内只存储该lob值得指针,而且这个属性在表创建后只能在MOVE表时才可以被改变  。

          ——总结，对那些全表扫描性能至关重要的表，考虑将长的不经常访问的列放到单独的表中，。对LOB和CLOB类型的字段考虑使用

                  DISABLE  STORAGE   IN  ROW 子句。

3.6.4   分区

           使用索引检索记录的效率会随着检索的记录比例的增加而下降。当被检索记录的比例太高导致不能高效执行索引查找，但检索表的所有记            录性能有更低时，使用分区表或许可以带来更好的性能。下图比较集中扫描方式：

                  

                  ——总结，当需要处理的记录比例，对于基于索引的访问路径来说比例太高，而对于做全表扫描来讲比例有太低时，分区模式和分区                          消除可能可以比全表扫描或者索引扫描性能更好。

  - - - - - - - - - - - - - - - - - - - - 累啊， 凌晨2:13了 。睡觉。- - - - - - - - - - - - - - - - - - -