基于Oracle的SQL优化--学习（二）

优化器的基础知识

      1、优化器的模式

        Oracle中，优化器的模式是由参数OPTIMIZER_MODE的值来决定的。

        1、RULE：表示Oracle将使用RBO来解析目标SQL，此时SQL中涉及的各个对象的统计信息对于RBO没有任何作用。

        2、CHOOSE：Oracle 9i的默认值，表示在解析目标SQL时使用RBO还是CBO取决于SQL涉及的表对象是否有统计信息。

   3、FIRST_ROWS_n(n=1,10,100,1000)：此时CBO计算SQL的各条执行路径的成本值时的侧重点在于以最快的响应速度返回头n(n=1,10,100,1000)条记录。当选择这条记录的时候Oracle会修改执行步骤的成本值，修改为一个很小的值，从而达到不违背CBO原则的目的。

   4、FIRST_ROWS：Oracle 9i中就已经过时的参数，当一些特殊情况下的时候，会使用RBO中的一些内置的规则来选取执行计划不再考虑成本。

   5、ALL_ROWS：Oracle 10g及以后版本中OPTIMIZER_MODE的默认值，表示使用CBO解析目标SQL，此时CBO计算SQL的各条执行路径的成本值时的侧重点在于最佳的吞吐量(即最小的系统I/O和CPU资源的消耗量)。

  2、结果集

    指包含指定执行结果的集合。对RBO来说，对应的执行计划中没有对相关执行步骤对应的结果集的描述，虽然结果集的概念对RBO也是适用的。

  3、访问数据的方法

    访问数据的方法分为两种：直接访问表；先访问索引，再回表。

    直接访问表：

    1、全表扫描：指Oracle访问目标表里的数据时，会从该表所占用的第一个区(Extent)的第一个块(Block)开始扫描，一直扫描到该表的高水位线(HWM)，这段范围内所有的数据块都必须读到。

          2、ROEID扫描：指Oracle访问目标表里的数据时，直接通过数据所在的ROWID定位并访问这些数据。ROWID表示Oracle中的数据行记录所在的物理存储地址，也就是说ROWID实际上和Oracle中数据块里的行记录一一对应的。

ROWID扫描有两层含义：一种是根据用户在SQL语句中输入的ROWID的值直接访问对应的数据行记录；另外一种是先访问相关的索引，然后根据访问索引后得到的ROWID再回表访问对应的数据行记录。

对Oracle堆表而言，通过Oracle内置的ROWID伪列得到对应航记录所在的ROWID的值(注意：ROWID只是一个伪列，在实际的表块中并不存在该列)，然后还可以根据DBMS_ROWID包中的相关方法(dbms_rowid.rowid_relative_fno、dbms_rowid.rowid_block_number和dbms_rowid.rowid_row_number)将上述ROWID伪列的值翻译成对应数据行的实际物理存储地址。

   访问索引的方法：

    1、索引唯一性扫描：INDEX UNIQUE SCAN，仅适用于where条件中是等值查询的目标SQL。因为扫描的对象是唯一性索引，所以索引唯一性扫描的结果至多只会返回一条记录。

         2、索引范围扫描：INDEX RANGE SCAN，当扫描的对象是唯一性索引时，目标SQL的where条件一定是范围查询(谓词条件为BETWEEN、<、>等)；当扫描的对象是非唯一性索引时，对目标SQL的where条件没有限制(可以是等值查询，也可以是范围查询)。在同等条件下，当目标索引的索引行的数量大于1时，索引范围扫描所耗费的逻辑读至少会比相应的索引唯一性扫描多1。

         3、索引全扫描：指要扫描目标索引所有叶子块的所有索引行。但并不意味着需要扫描该索引的所有分支块。默认情况下，Oracle在做索引全扫描时只需要通过访问必要的分支块定位到位于该索引最左边的叶子块的第一行索引行，就可以利用该索引叶子块之间的双向指针链表，从左至右依次顺序扫描该索引所有叶子块的所有索引行了。按照索引键值顺序排序，即可达到排序的效果。避免真正的排序。默认情况下，索引全扫描的有序性就决定了所以全扫描不能并行执行，通常使用单块读。做索引全扫描的前提条件是目标索引至少有一个索引键值列的属性是NOT NULL。

        4、索引快速全扫描：INDEX FAST FULL SCAN，需要扫描目标索引所有叶子块的所有索引行。与索引全扫描的区别：

        （1）、索引快速全扫描只适用于CBO。

        （2）、索引快速全扫描可以使用多块读，也可以并行执行。

        （3）、索引快速全扫描结果不一定是有序的。因为索引快速全扫描时Oracle是根据索引行在磁盘上的物理存储顺序来扫描，而不是根据索引行的逻辑顺序来扫描的。所以扫描结果才不一定有序（对于单个索引叶子块中的索引行而言，其物理存储顺序和逻辑存储顺序一致，但对于物理存储位置相邻的索引叶子块而言，块与块之间索引行的物理存储顺序则不一定在逻辑上有序。

        5、索引跳跃式扫描：INDEX SKIP SCAN，它使那些在where条件中没有对目标索引的前导列指定查询条件但同时又对该索引的非前导列指定了查询条件的目标SQL依然可以用上该索引，这就像在扫描该索引时跳过了它的前导列。这是因为Oracle帮你对该索引的前导列的所有distinct值做了遍历。Oracle中的索引跳跃式扫描仅适用于那些目标索引前导列的distinct值数量较少，后续非前导列的可选择性又非常好的情形，因为索引跳跃式扫描的执行效率一定会随着目标索引前导列的distinct值数量的递增而递减。