Oracle优化器的基础知识

发表于 | 分类于 ， |
| 字数统计: 4,413

优化器概念

优化器（Optimizer）是Oracle数据库内置的一个核心子系统。优化器的目的是按照一定的判断原则来得到它认为的目标SQL在当前的情形下的最高效的执行路径，也就是为了得到目标SQL的最佳执行计划。依据所选择执行计划时所用的判断原则，Oracle数据库里的优化器又分为RBO（基于规则的优化器）和CBO（基于成本的优化器，SQL的成本根据统计信息算出）两种。

在8i之前，Oracle使用的是RBO（Rule Based Optimizer，基于规则的优化器），他的执行非常简单，就是在优化器里面嵌入15种规则，执行的SQL语句符合哪种规则，就按照规则定制出相应的SQL执行计划。由于他是一种过时呆板的优化器，在10g以后的版本中已经被彻底废弃。

从8i开始，Oracle引入了CBO（Cost Based Optimizer，基于成本的优化器），他的思路是让Oracle获取所有的执行计划的相关信息，通过这些信息做计算分析，最后得出一个代价最小的执行计划作为最终的执行计划。

优化器的模式

优化器模式用于决定oracle在解析目标SQL时所选择的优化器类型，以及选择使用CBO时计算成本的侧重点。在oracle数据库中，优化器模式由参数OPTIMIZER_MODE的值决定，通常OPTIMIZER_MODE的值为RULE,CHOOSE,FIRST_ROWS_n(N=1、10、100、1000),FIRST_ROWS或ALL_ROWS。OPTIMIZER_MODE的值得各个含义如下：

  1. RULE
    RULE表示优化器使用RBO来解析目标SQL，此时目标SQL所涉及的各个对象的统计信息对于RBO来说将毫无意义。

  2. CHOOSE
    CHOOSE是oracle 9i中OPTIMIZER_MODE的默认值，他表示oracle在解析目标SQL时到底使用CBO还是RBO取决于目标SQL所涉及对象是否有统计信息。具体来说：只要目标SQL对象含有统计信息，即使用CBO，反之，使用RBO来解析目标SQL。

  3. FIRST_ROWS_n(N=1、10、100、1000)
    FIRST_ROWS_n(N=1、10、100、1000)可以是FIRST_ROWS_1、FIRST_ROWS_10、FIRST_ROWS_100、FIRST_ROWS_1000中的任意一个值，他表示oracle在解析目标SQL时，oracle会使用CBO来解析目标SQL，且此时CBO在计算各条执行路径的成本时的侧重点在于以最快响应速度返回前n条数据。

  4. FIRST_ROWS
    FIRST_ROWS是一个在oracle 9i中就过时的一个参数，他表示oracle在解析目标SQL时会联合使用CBO和RBO。在大部分情况下，oracle还是会选用CBO作为解析目标SQL，此时oracle的侧重点是以最快的相应速度返回前n行。在一些特俗情况下，oracle会选用RBO来解析目标SQL而不考虑成本。比如当OPTIMIZER_MODE为FIRST_ROWS时有一个内置的规则，就是oracle如果发现能用相关索引来避免排序，则oracle就会选择该索引所对应的路径而不考虑成本值。

  5. ALL_ROWS
    ALL_ROWS是oracle 10g及以后oracle的版本中OPTIMIZER_MODE的默认值，它表示oracle会使用CBO来解析目标SQL，此时CBO计算目标SQL的各个执行路径的成本的侧重点是最佳吞吐量。当OPTIMIZER_MODE为FIRST_ROWS时，CBO计算成本侧重于最快响应时间；当OPTIMIZER_MODE为ALL_ROWS时，CBO计算成本侧重于最佳吞吐量。

结果集

结果集(Row Source)是指包含指定执行结果的集合。对于优化器而言(无论是RBO还是CBO)，结果集和目标SQL执行计划的执行步骤相对应，一个执行步骤所产生的执行结果就是该执行步骤所对应的输出结果集。

对于目标SQL的执行计划而言，其中某个执行步骤的输出结果就是该执行步骤所对应的输出结果集，同时，该执行步骤所对应的输出结果集可能就是下一个执行步骤的输入结果集。这样一步一步执行下来，伴随的就是结果集在各个执行步骤之间的传递，等目标SQL执行计划的各个执行步骤全部执行完毕后，最后的输出结果集就是该SQL最终的执行结果。

对于RBO而言，在对应的执行计划中看不到相关执行步骤所对应的结果集的描述，虽然结果集的概念对于RBO来说也同样适用。

对于CBO而言，对应执行中的Rows列反映的就是CBO对于相关执行步骤所对应输出结果集的记录数(即Cardinality)的估算值。

访问数据的方法

优化器访问数据的方法有3种，一种是直接访问表；一种是访问索引，直接从索引中取值；另一种是先访问索引，再回表。

1.访问表的方法

访问表的方法2种：全表扫描；rowid扫描。

  1. 全表扫描（Table Access Full）
    全表扫描是指Oracle在访问目标表里的数据时，会从该表所占用的第一个区(EXTENT)的第一个块(BLOCK)开始扫描，直接扫描到该表的高水位线(HWM,High Water Mark)，这段范围内所有的数据块Oracle都必须读到。当然，Oracle会对这期间读到的所有数据施加目标SQL的where条件中指定的过滤条件，最后只返回那些满足过滤条件的数据。

    不是说全表扫描不好，事实上Oracle在做全表扫描操作时会使用多块读，这在目标表的数据不大时执行效率是非常高的，但全表扫描最大的问题就在于走全表扫描的目标SQL执行时间会不稳定、不可控，这个执行时间一定会随着目标表数据量的递增而递增。因为随着目标表数据量的递增，它的高水位线会一直不段往上涨，所以全表扫描时所需要读取的数据块的数据也会不断增加，这意味着全表扫描该表时所需要耗费的I/O资源会随之不断增加，当然完成对该表的全表扫描操作所需要耗费的时间也会随之增加。

    在Oracle中如果对目标表不停地插入数据，当分配给该表的现有空间不足时高水位线就会向上移动，但如果你用DELETE语句从该表删除数据，则高水位线并不会随之往下移动。高水位线这种特性所带来的副作用是，即使使用DELETE删光了目标表中的所有数据，高水位线还是会在原来的位置，这意味着全表扫描该表时Oacle还是需要扫描该表高水位线下所有的数据块，此时对该表的全表扫描操作耗费的时间与之前相比并不会有明显的改观。

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

    既然ROWID代表的就是表的数据行所在的物理存储地址，那么当Oracle知道待访问的数据行所在的ROWID后，自然就可以根据该RWOID去直接访问对应表的相关数据行，这就是ROWID扫描的含义。

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

    对Oracle中的堆表而言，我们可以通过Oracle内置的ROWID伪列得到对应行记录所在的ROWID值，然后还可以通过DBMS_ROWID包中的相关方法将ROWID伪列的值翻译成对应数据行的实际物理存储地址。

2.访问索引的方法（TABLE ACCESS BY INDEX SCAN）

这里提到的索引是指最常用的B-Tree索引

Oracle数据库的的B-Tree索引就好像一棵倒长的树，它包含两种类型的数据块，一种是索引分支块，另一种是索引叶子块。

索引分支块包含指向相应索引分支块/叶子块的指针和索引键值列(这里的指针是指相关分支块/叶子块的块地址RDBA。每个索引分支块都会有两种类型的指针，一种是lmc，另一种是索引分支块的索引行记录所记录的指针。lmc是Left Most Child的缩写，每个索引分支块都只有一个lmc，这个lmc指向的分支块/叶子块中的所有索引键值列中的最大值一定小于该lmc所在索引分支块的所有索引键值列中的最小值；而索引分支块的索引行记录所记录的指针所指向的分支块/叶子块的所有索引键值列中的最小值一定大于或等于该行记录的索引键值列的值)。这个索引列值不一定就是完整的被索引键值，它可能只是被索引键值的前缀，只要Oracle能通过这些前缀区分相应的索引分支块/叶子块就行，这样Oracle就能够既节省分支块的存储空间，又可以快速定位其下层的索引分支块/叶子块。索引分支块最上层的那个块就是所谓的索引根节点。在Oracle里访问B-Tree索引的操作都必须从根节点开始，即都会经历一个从根节点到分支块再到叶子块的过程。

索引叶子块包含被索引键值和用于定位该索引键值所在的数据行在表中实际物理存储位置的ROWID。对于唯一性的B-Tree索引而言，ROWID是存储在索引行的行头，所以此时Oracle并不需要额外礁该ROWID的长度。而对于非唯一性的B-Tree索引而言，ROWID被当作额外的列与被索引的键值列一起存储，所以此时Oracle既要存储ROWID，同时又要存储其长度，这意味着在同等条件下，唯一性B-Tree索引要比非唯一性B-Tree索引节省索引叶子块的存储空间。对于非唯一性索引而言，B-Tree索引的有序性体现在

Oralce会按照被索引键值和相应的ROWID来联合排序。Oralce里的索引叶子块是左右互联的，即相当于有一个双向指针链表把这些索引叶子块互相连接在了一起。

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

  2. 索引范围扫描(INDEX RANGE SCAN)
    索引范围扫描适用于所有类型的B-Tree索引，当扫描的对象是唯一性索引时，此时目标SQL的where条件一定是范围查询(谓词条件为BETWEEN、<、>等)；当扫描的对象是非唯一性索引时，对目标SQL的where条件没有限制(可以是等值查询，也可以是范围查询)。索引范围扫描的结果可能会返回多条记录，其实这就是索引范围扫描中的“范围”二字的本质含义。

    在同等条件下，当目标索引的索引行的数量大于1时，索引扫描范围所耗费的逻辑读至少会比索引唯一性扫描的逻辑读多1。因为扫描结果可能会返回多条记录，又因为目标索引的索引行数量大于1，Oracle为了确定索引范围扫描的扫描终点，就不得不去多次访问相关的叶子块。

  3. 索引全扫描(INDEX FULL SCAN)
    索引全扫描适用于所有类型的B-Tree索引(包括唯一性索引和非唯一性索引)。所谓的“索引全扫描”，就是指要扫描目标索引所有叶子块的所有索引行。这里需要注意的是，索引全扫描需要扫描目标索引的所有叶子块，但并不意味着需要扫描该索引的所有分支块。在默认情况下，Oracle在做索引全扫描时只需通过访问必要的分支块定位到位置该索引最左边的叶子块的第一行索引行，就可以利用该索引叶子块之间的双向指针链表，从左至右依次顺序扫描该索引所有叶子块的所有索引行了。由于索引是有序的，所以索引全扫描的执行结果也是有序的，并且是按照索引的索引键值列来排序，这也意味着走索引全扫描能够既达到排序的效果，又同时避免了对该索引的索引键值列的真正排序操作。

    默认情况下，索引全扫描的扫描结果的有序性就决定了索引全扫描是不能够并行执行的，并且通常情况下索引全扫描使用的是单块读。

    通常情况下，索引全扫描是不需要回表的，所以索引全扫描适用于目标SQL的查询全部是目标索引的索引键值列的情形。我们知道，对于Oracle数据库的B-Tree索引而言，当所有索引键值列全为NULL值时不入索引，这意味着Oracle中能做索引全扫描的前提条件是目标索引至少有一个索引键值列的属性是NOT NULL。这很显然，如果目标索引的所有索引键值列的属性均为允许NULL值，此时如果还走索引全扫描，就会漏掉目标表中那些索引值列均为NULL的记录，即此时走索引全扫描的结果就不准了！Oracle不允许这种事情发生。

  4. 索引快速全扫描(INDEX FAST FULL SCAN)
    索引快速全扫描和索引全扫描极为类似，它也适用于所有类型的B-Tree索引。和索引全扫描一样，索引快速全扫描也需要扫描目标索引所有叶子块的所有索引行。

    索引快速全扫描与索引全扫描相比有如下三点区别：

    • 索引快速全扫描只适用于CBO。
    • 索引快速全扫描可以使用多块读，也可以并行执行。
    • 索引快速全扫描的执行结果不一定是有序的。这是因为索引快速全扫描时Orace是根据索引行在磁盘上的物理存储顺序来扫描，而不是根据索引行的逻辑顺序来扫描的，所以扫描结果才不一定有序(对于单个索引叶子块的索引行而言，其物理存储顺序和逻辑存储顺序一致；但对于物理存储位置相邻的索引叶子块而言，块与块之间索引行的物理存储顺序则不一定在逻辑上有序)。

  1. 索引跳跃式扫描(INDEX SKIP SCAN)
    索引跳跃式扫描适用于所有类型的复合B-Tree索引，它使那些在where条件中没有对目标索引的前导列指定查询条件，但同时又对该索引的非前导列指定了查询条件的目标SQL依然可以使用上该索引，这就像是在扫描该索引时跳过了它的前导列，直接从该索引的非前导到开始扫描一样(实际执行过程并非如此)，这便是索引跳跃式扫描中“跳跃”(SKIP)一词的含义。

    例如：

    假设表emp有gender（性别）、name（雇员名字）两个个字段，并且建立了如 create index idx_emp on emp (gender, name) 的复合索引。gender只有 ‘M’ 和 ‘F’ 两个值。

    当查询 select * from emp where name = ‘zhangsan’ 时，可以理解Oracle的执行了如下操作：

    1
    2
    3
    select * from emp where gender = 'F' and name = 'zhangsan'
    union
    select * from emp where gender = 'M' and name = 'zhangsan'

    这里在没有指定前导列的情况下能使用到这个跳跃索引，是因为Oracle对索引的前导列的所有distinct值做了遍历。
    所谓对目标索引的所有distinct值做遍历，其实际含义相当于对原目标SQL做等价改写（即把要用的目标索引的所有前导列的distinct值都加进来）。

    Oracle中的索引跳跃式扫描仅仅适用于那些目标索引前导导列的distinct值数量较少，后续非前导列的可选择性又非常好的情形，因为索引跳跃式扫描的执行效率一定会随着目标索引前导列的distinct值数量的递增而递减。

本文参考：

  1. 《基于Oracle的优化》
------ 本文完 ------