Oracle优化器的基础知识

共计 23514 个字符，预计需要花费 59 分钟才能阅读完成。

一、优化器的模式

优化器的模式用于决定在 Oracle 中解析目标 SQL 时所用优化器的类型，以及决定当使用 CBO 时计算成本值的侧重点。这里的“侧重点”是指当使用 CBO 来计算目标 SQL 各条执行路径的成本值时，计算成本值的方法会随着优化模式的不同而不同。

在 Oracle 数据库中，优化器的模式是由参数 OPTIMIZER_MODE 的值决定的，OPTIMIZER_MODE 的值可能是 RULE、CHOOSE、FIRST_ROWS_n(N=1,10,100,1000)、FIRST_ROWS 或 ALL_ROWS。

OPTIMIZER_MODE 的各个可能的值的含义为如下所示：

1.RULE

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

2.CHOOSE

CHOOSE 是 Oracle9i 中 OPTIMIZER_MODE 的默认值，它表示 Oracle 在解析目标 SQL 是到底是使用 RBO 还是使用 CBO 取决于该 SQL 中所涉及的表对象是否有统计信息。具体来说就是：只要该 SQL 中所涉及的表对象中有一个有统计信息，那么 Oracle 在解析该 SQL 时就会使用 CBO；如果该 SQL 中所涉及的所有表对象均没有统计信息，那么此时 Oracle 会使用 RBO。

3.FIRST_ROWS_n(n=1,10,100,1000)

其含义是指当 OPTIMIZER_MODE 的值为 FIRST_ROWS_n(n=1,10,100,1000)时，Oracle 会使用 CBO 来解析目标 SQL，且此时 CBO 在计算该 SQL 的各条执行路径的成本值时的侧重点在于以最快的响应速度返回头 n(n=1,10,100,1000)条记录。Oracle 会把那些最快的响应速度返回头 (n=1,10,100,1000) 条记录所对应的执行步骤的成本修改成一个很小的值 (远小于默认情况下 CBO 对同样执行步骤所计算出的成本值)。这样 Oracle 就既没有违背 CBO 选择执行计划的总原则(成本值最小)，同样又兼顾 了 FIRST_ROWS_n(n=1,10,100,1000) 的含义。

4.FIRST_ROWS

FIRST_ROWS 是一个在 Oracle9i 中就已经过时的参数，它表示 Oracle 在解析目标 SQL 是会联合使用 CBO 和 RBO。这里联合使用 CBO 和 RBO 的含义是指在大多数情况下，FIRST_ROWS 还是会使用 CBO 来解析目标 SQL，且此时 CBO 在计算该 SQL 的各条执行路径的成本值时的侧重点在于以最快的响应速度返回头几条记录(类似于 FIRST_ROWS_n)；但是，当出现一些特定情况时，FIRST_ROWS 转而会使用 RBO 中的一些内置规则来选取执行计划而不再考试成本。比如当 OPTIMIZER_MODE 的值为 FIRST_ROWS 有一个内置的规则，就是如果 Oracle 发现能用相关的索引来避免排序，则 Oracle 就会选择该索引所对应的执行路径而不再考虑成本，这显然是不合理的。与这对应的，在当 OPTIMIZER_MODE 的值为 FIRST_ROWS 的情形下，你会发现索引全扫描出现的概率会比之前有所增加，这是因为走索引全面扫描能够避免排序的缘故。

5.ALL_ROWS

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

实际上，成本的计算方法随着优化器模式的不同而不同，主要体现在 ALL_ROWS 和 FIRST_ROWS_n(n=1,10,100,1000)对成本值计算方法的影响上。当优化器模式为 ALL_ROWS 时，CBO 计算成本的侧重点在于最佳的吞吐量；而当优化器模式为 FIRST_ROWS_n(n=1,10,100,1000)时，CBO 计算成本的侧重点会变为以最快的响应速度返回头 n(n=1,10,100,1000)条记录。这意味着同样的执行步骤，在优化器模式不同时 CBO 分别计算出来的成本会存在巨大的差异，这也就意味着优化器对 CBO 计算成本 (进而对 CBO 选择执行计划) 有着决定性的影响。

二、结果集

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

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

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

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

三、访问数据的方法

对于优化器而言，它在解析目标 SQL、得到其执行计划时至关重要的一点是决定访问数据的方法，即优化器要决定采用什么样的方式和方法去访问目标 SQL 所需要访问的存储在 Oracle 数据库中的数据。

目标 SQL 所需要访问的数据一般存储在表，而 Oracle 访问表中数据的方法有两种：一种是直接访问表；另一种是先访问索引，再回表(当然，如果目标 SQL 所访问的数据只通过访问相关的索引就可以得到，那么此时就不需要再回表了)。

3.1 访问表的方法

Oracle 数据库中直接访问表中数据的方法有两种：一种是全表扫描；另一种是 ROWID 扫描。

3.1.1 全表扫描

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

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

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

3.1.2 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 伪列的值翻译成对应数据行的实际物理存储地址。

3.2 访问索引的方法

这里提到的索引是指最常用的 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 里的索引叶子块是左右互联的，即相当于有一个双向指针链表把这些索引叶子块互相连接在了一起。

3.2.1 索引唯一性扫描

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

3.2.2 索引范围扫描

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

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

3.2.3 索引全扫描

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

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

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

3.2.4 索引快速全扫描

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

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

1)索引快速全扫描只适用于 CBO。

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

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

3.2.5 索引跳跃式扫描

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

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

参考：《基于 Oracle 的 SQL 优化》PDF 下载见 http://www.linuxidc.com/Linux/2017-02/140521.htm

更多 Oracle 相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12

本文永久更新链接地址：http://www.linuxidc.com/Linux/2017-02/140522.htm

一、表连接

      顾名思义，表连接就是指多个表之间用连接条件连接在一起，使用表连接的目标 SQL 的目的就是从多个表获取存储在这些表中的不同维度的数据。体现在 SQL 语句上，含表连接的目标 SQL 的from部分会出现多个表，而这些 SQL 的where条件部分则会定义具体的表连接条件。

      当优化器解析含表连接的目标 SQL 时，它除了会根据目标 SQL 的SQL文本的写法来决定表连接的类型之外，还必须决定如下三件事情才能得到最终的执行计划。

1.表连接顺序

      不管目标 SQL 中有多少个表做表连接，Oracle在实际执行该 SQL 时都只能先两两做表连接，再依次执行这样的两两表连接过程，直到目标 SQL 中所有的表都已连接完毕。从严格意义上来说，这里的表连接顺序包含两层含义：一层含义是当两个表做表连接时，优化器需要决定这两个表中谁是驱动表 (outer table)，谁是被驱动表(inner table)；另外一层含义是当多表( 超过两个以上的表 ) 做表连接时，优化器需要决定这些表中谁和谁先做表连接，然后决定这个表连接结果所在的结果集再和剩余表中的哪一个再做表连接，这个两两表连接的过程会一直持续下去，直到目标 SQL 中所有的表都已经连接完为止。

2.表连接方法

      在 Oracle 数据库中，两个表之间的表连接方法有排序合并 (sort merge join)、嵌套循环连接(nested loops join)、哈希连接(hash join) 和笛卡儿连接 (cross join) 这4种，所以优化器在解析含表连接的目标 SQL 时，都需要从上述四种方法中选择一种，作为每一对表两两做表连接时所需要采用的方法。

3.访问单表的方法

      对于优化器而言，仅决定表连接顺序和表连接方法是不够的，这还不中以得到目标 SQL 的最终执行计划，因为优化器在对目标 SQL 中的各个表两两做表连接时，还必须决定如何去获取存储在这些表里的不同维度的数据，即优化器还要决定访问单表的方法。比如在访问某个单表时，是采用全表扫描还是走索引，如果是走索引，应该采用什么样的索引访问方法等。

1.1 表连接的类型

通常情况下，我们可以认为 Oracle 数据库中的表连接分为内连接和外连接这两种类型，表连接的类型会直接决定表连接的结果，而目标 SQL 的SQL文本的写法又直接决定了表连接的类型。

1.1.1 内连接

内连接 (Inner Join) 是指表连接的结果只包含那些完全满足连接条件的记录。对于包含表连接的目标 SQL 而言，只要其 where 条件中没有写那些标准 SQL 中定义或者 Oracle 中自定义的表示外连接的关键字 ( 比如标准 SQL 中的 left outer join、right outer join、full outer join，或者Oracle 中自定义的用来表示外连接的关键字“(+)”)，则该 SQL 的连接类型就是内连接。

Oracle自定义的内连接写法：

      目标表 1, 目标表 2 where 连接条件

标准 SQL 中内连接是用 JOIN ON 或者JOIN USING。

  JOIN ON的语法：

      目标表 1 join 目标表 2 on ( 连接条件)

  JOIN USING的语法：

      目标表 1 join 目标表 2 using( 连接列集合)。

对于使用 JOIN USING 的目标 SQL 而言，如果有多个连接列，其语法中“(连接列集合 )” 里的各个连接列之间应使用逗号来分隔。需要注意的时，使用 JOIN USING 的连接语法，如果连接列同时又出现在查询列中，则该连接列前不能带上表名或者表名的别名 (alias), 否则 Oracle 会报错(ORA-25154)。

标准 SQL 中还有一种特殊的 JOIN USING，我们称之为NATURAL JOIN，其含义是使用NATURAL JOIN 的表连接的连接列是表连接的两个表所有的同名列。语法：

      目标表 1 natural join 目标表2

这实际相当于目标表 1 join 目标表 2 using( 目标表 1 和目标表 2 的所有同名列集合 )。使用NATURAL JOIN 好外是无须写连接列集合，但其坏处是增加了表连接的执行结果出错的风险，因为两个表之间的同名列不一定在含义上就完全相同，也许只是恰好同名，而即使含义相同，也不一定就需要将它们作为连接列。

1.1.2 外连接

      外连接 (Outer Join) 是对内连接的一种扩展，它是指表连接的连接结果除了包含那些完全满足连接条件的记录之外还会包含驱动表中所有不满足该条件的记录。

      标准 SQL 中的外连接分为左外连接 (Left Outer Join)、右连接(Right Outer Join) 和全连接 (Full Outer Join) 这三种，它们在标准 SQL 中对应的关键字分别为 left outer join、right outer join、full outer join，都可以和JOIN ON 或JOIN USING连用。

左连接的语法：

      目标表 1 left outer join 目标表 2 on( 连接条件 ) 或目标表 1 left outer join 目标表 2 using ( 连接列集合)

      其含义是目标表 1 和目标表 2 按括号中的连接条件来做表连接，位于关键字左边的表 1 作为驱动表 (outer table)，此时的连接结果包含了表1 和表 2 中所有满足该连接条件的记录外，还会包含驱动表 ( 表1)中所有不满足该连接条件的记录，同时，驱动表中所有不满足该连接条件的记录所对应的被驱动表 ( 表2)中的查询列均会以 NULL 值来填充。

右连接的语法：

      目标表 1 right outer join 目标表 2 on( 连接条件 ) 或目标表 1 right outer join 目标表 2 using ( 连接列集合)

      含义与左连接相似，不过，这次位于关键字右表的表 2 为驱动表。

全连接语法：

      目标表 1 full outer join 目标表 2 on( 连接条件 ) 或目标表 1 full outer join 目标表 2 using ( 连接列集合)

      其含义是目标表 1 和目标表 2 按括号中的连接条件来做表连接。此时的连接结果除了包含表 1 和表 2 中所有满足该连接条件的记录外，还会包含目标表 1 和目标表 2 中所有不满足该连接条件的记录，同时，表 1 和表 2 中所有不满足该连接条件的记录所对就的另外一个表中的查询列均会以 NULL 值来填充。

      上面介绍的范例 SQL 中除了带连接条件外，并没有带其他额外的限制条件。如果目标 SQL 中除了表连接条件之外还带了额外的限制条件，则目标 SQL 中表连接的类型和该额外限制条件在目标 SQL 的SQL文本中出现的位置都可能会对最终执行结果产生影响。

      内连接添加其他限制条件实例：

      对内连接而言，除了表连接条件之外的额外限制条件在目标 SQL 的SQL文本中所处的位置不会影响该 SQL 的实际执行结果。

      外连接添加其他限制条件实例：

      对于外连接而言，如果额外限制条件在外连接关键字对应的括号内，这表示该限制条件会在表 t1 和表 t2 做右连接之前就被应用在表 t1 上，而如果额外限制条件在外连接关键字对应的括号外，表示该限制条件在表 t1 和表 t2 做完右连接后，才会被应用在表 t1 和表 t2 的连接结果集上。

      所以，对于外连接而言，除了表连接条件之外的额外限制条件在目标 SQL 的SQL文本中所处的位置确实可能会影响该 SQL 的实际执行结果。

      和标准 SQL 里表示外连接的语法不同，Oracle用自定义的关键字“(+)”来表示外连接。关键字“(+)”的位置在目标 SQL 连接条件中某一个表的连接列后面，其含义是关键字“(+)”出现在哪个表的连接列后面，就表明哪个表会以 NULL 值来填充那不满足连接条件找位置该表中的查询列，此时应该以关键字“(+)”对应的表作为外连接的驱动表，这是的关键是哪个表是驱动表！

      之前提到过：对于外连接而言，表连接条件之外的额外限制条件在目标 SQL 的SQL文本中所处位置的不同可能会影响该 SQL 的实际执行结果。那如果使用 Oracle 自定义的关键字“(+)”来表示外连接的话，那么如何体现呢？很简单，Oracle是通过在额外限制条件的目标列的后面带上同样的关键字“(+)”来体现出上述影响的：

select t1.col1,t1.col2,t2.col3

from t1,t2

where t1.col2(+)=t2.col2

and t1.col1(+)=1;

      前面提到的 NATURAL JOIN 不仅适用于内连接，也同样适用于外连接：

select t1.col1,col2,t2.col3

from t1 natural left outer join t2 ;

1.2 表连接的方法

      之前介绍过，优化器在解析含表连接的目标 SQL 时，当它根据目标 SQL 的SQL文本的写法决定表连接的类型之后，接下来要做的事情之一就是决定表连接的方法。

      在 Oracle 数据库中，两个表之间的表连接方法有排序合并连接、嵌套循环连接、哈希连接和笛卡儿连接这四种。这四种表连接各有优缺点，也各有其适用场景，接下来分别介绍它们

1.2.1 排序合并连接

      排序合并连接 (Sort Merge Join) 是一种两个表在做表连接时用排序操作 (Sort) 和合并操作 (Merge) 来得到连接结果集的表连接方法。

      如果两个表 ( 假如为 T1 和T2)做表连接时使用的是排序合并连接，则 Oracle 会依次顺序执行如下步骤：

1. 首先以目标 SQL 中指定的谓词条件 ( 如果有的话 ) 去访问表 T1，然后对访问结果按照表T1 中的连接来排序，排好序后的结果集我们记为结果集1。

2. 接着以目标 SQL 中指定的谓词条件 ( 如果有的话 ) 去访问表 T2，然后对访问结果按照表T2 中的连接来排序，排好序后的结果集我们记为结果集2。

3. 最后对结果集 1 和结果集 2 执行合并操作，从中取出匹配记录来作为排序合并连接的最终执行结果。

      对于排序合并连接的优缺点及适用场景，总结如下：

• 通常情况下，排序合并连接的执行效率会远不如哈希连接，但前者的使用范围更广，因为哈希连接通常只能用于等值连接，而排序合并连接并不能用于其他条件 ( 例如<、<=、>、>=)。

• 通常情况下，排序合并连接并不短途 OLTP 类型的系统，其本质原因是因为对 OLTP 类型的系统而言，排序是非常昂贵的操作，当然，如果能避免排序操作，那么即使是 OLTP 类型的系统，也还是可以使用排序合并连接的。比如两个表虽然是排序合并连接，但实际上它们并不需要排序，因为这两个表各自的连接列上都存在索引。

• 从严格意义上说，排序合并连接不存在驱动表的概念。

1.2.2 嵌套循环连接

      嵌套循环连接 (Nested Loops Join) 是一种两个表在做表连接时依靠两层嵌套循环 ( 分别为外层循环和内层循环 ) 来得到连接结果集的表连接方法。

      如果两个表 ( 假如为 T1 和T2)在做表连接时使用的是嵌套循环连接，则 Oracle 会依次顺序执行如下步骤：

1. 首先，优化器会按照一定的规则来决定表 T1 和T2中谁是驱动表、谁是被驱动表。驱动表用于外层循环，被驱动表用于内存循环。这是假设驱动表是T1，被驱动表是T2。

2. 接着以目标 SQL 中指定的谓词条件 ( 如果有的话 ) 去访问驱动表 T1，访问驱动表T1 后得到的结果集我们记为驱动结果集1。

3. 然后遍历驱动结果集 1 并同时遍历被驱动表 T2，即先取出驱动结果集1 中的第 1 条记录，接着遍历被驱动表 T2 并按照连接条件去判断 T2 中是否存在匹配的记录，然后再取出驱动结果集 1 中的第 2 条记录，按照同样的连接条件再去遍历被驱动表 T2 并判断 T2 中是否还存在匹配的记录，直到遍历完驱动结果集 1 中所有的记录为止。这里的外层循环是指遍历驱动结果集 1 所对应的循环，内层循环是指遍历被驱动表 T2 所对应的循环。显然，外层循环所对应的驱动结果集 1 有多少条记录，遍历被驱动表 T2 的内层循环就要做多少次，这就是所谓的“嵌套循环”的含义。

      嵌套循环连接的优缺点及适用场景总结如下：

• 从上述嵌套循环连接的具体执行过程可以看出：如果驱动表所对应的驱动结果集的记录数较少，同时被驱动表的连接列上又存在唯一性索引 ( 或者在被驱动表的连接列上存在选择性很的的非唯一性索引)，那么此时使用嵌套循环连接的执行效率就会非常高；但如果驱动表所对应的驱动结果集的记录数很多，即便在被驱动表的连接列上存在索引，此时使用嵌套循环连接的执行效率也不会高。

• 只要驱动结果集的记录数较少，那就具备了做嵌套循环连接的前提条件，而驱动结果集是在对驱动表应用了目标 SQL 中指定的谓词条件 ( 如果有的话 ) 后所得到的结果集，所以大表也可以作为嵌套循环连接的驱动表，关键看目标 SQL 中指定的谓词条件 ( 如果有的话 ) 能否将驱动结果集的数据量降下来。

• 嵌套循环连接有其他连接方法所没有的一个优点：嵌套循环连接可以实现快速响应，即它可以第��时间返回已经连接过具满足连接条件的记录，而不必等待所有的连接操作全部做完才返回连接结果。虽然排序合并连接也可以，但它们并不是第一时间返回，因为排序合并连接要等到排序完后做合并操作时才能开始返回数据，而哈希连接则要等到驱动结果集所对应的 Hash Table 全部建完后才能开始返回数据。

      如果 Oracle 使用的是嵌套循环连接，且在被驱动表的连接列上存在索引，那么 Oracle 在访问索引时通常会使用单块读，这意味着嵌套循环连接的驱动结果集有多少条记录，Oracle就会需要访问该索引多少次。另外，如果目标 SQL 中的查询列并不能全部从驱动表的相关索引中获得，那么 Oracle 在做完嵌套循环连接后还需要对被驱动表执行回表操作。这个回青操作通常也会使用单块读，这意味着做完嵌套循环连接后的连接结果集有多少条记录，Oracle就需要回表多少次。

      为了提高嵌套循环连接的执行效率，在 Oracle 11g 中，Oracle引入了向量 I/O(Vector I/O)。在引入向量I/O 后，Oracle就可以将原先一批单块读所需要耗费的物理 I/O 结合起来，然后用一个向量 I/O 去批处理它们，这样就实现了在单块读的数量不降低的情况下减少这些单块读所需要耗费的物理 I/O 数量，也就提高了嵌套循环连接的执行效率。

1.2.3 哈希连接

      哈希连接 (Hash Join) 是一种两个表在做表连接时主要依靠哈希运算来得到连接结果集的表连接方法。

      在 Oracle7.3 之前，Oracle数据库中常用的表连接方法就只有排序合并连接和嵌套循环连接这两种，但这两种方法都各有其明显缺陷。对于排序合并连接，如果两个表在施加了目标 SQL 中指定的谓词条件 ( 如果有的话 ) 后得到的结果集很大且需要排序，则排序合并连接的执行效率一定不高；而对于嵌套循环连接，如果驱动表所对应的驱动结果集的记录数很大，即便在被驱动表的连接列上存在索引，此时使用嵌套循环连接的执行效率也会同样不高。为了上述情形下效率不高的问题，同时也为了给优化器提供一种新的选择，Oracle在 7.3 中引入了哈希连接。从理论上来说，哈希连接的执行效率会比排序合并连接和嵌套循环连接要高，当然，实际情况并不总是这样。

      在 Oracle 10g 及其以后的 Oracle 数据库版本中，优化器 ( 实际上是 CBO，因为哈希连接仅适用于CBO) 在解析目标 SQL 时是否考虑哈希连接是受限于隐含参数 _HASH_JOIN_ENABLED，而在Oracle10g 以前，CBO在解析目标 SQL 时是否考虑哈希连接是受限于参数 HASH_JOIN_ENABLED。_HASH_JOIN_ENABLED 的默认值是 TRUE，表示允许CBO 在解析目标 SQL 时考虑哈希连接。当然，即使该参数为 FALSE，使用USE_HASH Hint 依然可以让 CBO 在解析目标 SQL 时考虑哈希连接，这说明 USE_HASH Hint 的优先级比参数 _HASH_JOIN_ENABLED 的优先级要高。

  如果两个表 ( 假如为 T1 和T2)在做表连接时使用的是哈希连接，则 Oracle 会依次顺序执行如下步骤：

1. 首先 Oracle 会根据参数 HASH_AREAS_SIZE、DB_BLOCK_SIZE 和_HASH_MULTIBLOCK_IO_COUNT的值来决定 Hash Partition 的数据 (Hash Partition 是一个逻辑上的概念，它实际上是一组 Hash Bucket 的集合。所有 Hash Partition 的集合就被称为 Hash Table，即一个Hash Table 由多个 Hash Partition 所组成，而一个 HashPartition 又由多个 Hash Bucket 所组成的)。

2. 表 T1 和T2在施加了目标 SQL 中指定的谓词条件 ( 如果有的话 ) 后，得到的结果集中数量较少的那个结果集会被 Oracle 选为哈希连接的驱动结果集，这里我们假设 T1 所对应的结果集的数据量相对较少，记为 S；T2 所对应的结果集的数据相对较多，记为 B。显然这里S 是驱动结果集，B是被驱动结果集。

3. 接着 Oracle 会遍历 S，读取S 中的每一条记录，并对每一条记录按照该记录在表 T1 中的连接列做哈希运算。这个哈希运算会使用两个内置哈希函数，这两个哈希函数会同时对该连接列计算哈希值，我们把这两个内置哈希函数分别记为 hash_func_1 和hash_func_2，它们所计算出来的哈希值分别记为 hash_vale_1 和hash_value_2。

4. 然后 Oracle 会按照 hash_value_1 的值把相应的 S 中的对应记录存储在不同的 Hash Partition 的不同 Hash Bucket 里，同时和该记录存储在一起的还有该记录用 hash_func_2 计算出来的 hash_value_2。注意，存储在Hash Bucket 里的记录并不是目标表的完整行记录，只需要存储位置目标 SQL 中与目标表相关的查询列和连接列就足够了。我们把 S 所对应的每一个 Hash Partition 记为Si。

5. 在构建 Si 的同时，Oracle会构建一个位图 (BITMAT)，这个位置用来标记Si 所包含的每一个 Hash Bucket 是否记录 ( 即记录数是否大于0)。

6. 如果 S 的数据量很大，那么在构建 S 所对应的 Hash Table 时，就可能会出现 PGA 的工作区 (WORK AREA) 被填满的情况。这时候 Oracle 会把工作区中包含记录数最多的 Hash Partition 写到磁盘上 (TEMP 表空间 )。接着Oracle 会继续构建 S 所对应的 Hash Table，在继续构建的过程中，如果工作区又满了，则Oracle 会继续重复上述动作，即挑选包含记录数最多的 Hash Partition 并写回到磁盘上。如果要构建的记录所对应的 Hash Partition 已经事先被 Oracle 写回磁盘，则此时 Oracle 会去磁盘上更新 Hash Partition ，即把该条记录和Hash_vale_2 直接加到这个已经位于磁盘上的 Hash Partition 的相应 Hash Bucket 中。注意，极端情况下可能会出现只有某个 Hash Partition 的部分记录不觉 在内存中，该 Hash Partition 的剩余部分和余下的所有 Hash Partition 都已经被写回到磁盘上。

7. 上述构建 S 所对应的 Hash Table 的过程会一直持续下去，直到遍历完 S 中的所有记录为止。

8. 接着，Oracle会对所有的 Si 按照它们所包含的记录数来排序，然后把这些已经诽好序的 Hash Partition 按顺序依次且尽可能全部放到内存中 (PGA 的工作区 )，当然，如果实在放不下，放不下的那部分Hash Partition 还是会位于磁盘上。

9. 至此 Oracle 已经处理完 S，现在可以开始处理B 了。

10. Oracle会遍历 B，读取B 中的每一条记录，并按照该记录在表 T2 中的连接列做哈希运算，这个哈希运算和步骤 3 中的哈希运算是一模一样的，即还是会用步骤 3 中的 hash_func_1 和hash_func_2，并且也会计算出两个哈希值 hash_value_1 和hash_value_2。

    接着 Oracle 会按照该记录所对应的哈希值 hash_value_1 去Si里找匹配的 Hash Bucket 中的每一条记录的连接列，看是否是真的匹配 ( 即这里要校验 S 和B中匹配记录所对应的连接列是否真的相等，因为对于哈希运算而言，不同的值经过哈希运算后的结果可能是相同的 )。如果真的匹配，则上述hash_value_1 所对应 B 中记录的位于目标 SQL 中的查询列和该 Hash Bucket 中的匹配记录便会组合起来，一起作为满足目标 SQL 连接条件的记录返回。如果找不到匹配的 Hash Bucket，则Oracle 就会去访问步骤 5 中构建的位图。

    如果位图显示该 Hash Bucket 在Si中对应的记录数大于 0，则说明该Hash Bucket 虽然不在内存中，但它已经被写回磁盘，此时 Oracle 就会按照 hash_value_1 的值把相应 B 中的对应记录也可以 Hahs Partition 的方式写回到磁盘上，同时和该记录存储在一起的还有该记录用 hash_func_2 计算出来的 hash_value_2 的值。如果位图显示该 Hash Bucket 在Si中对应 的记录数等于 0，则Oralce 就无须把上述 hash_value_1 所对应 B 中的记录写回磁盘了，因为这条记录必须不满足目标 SQL 的连接条件。这个根据位置来决定是否将 hash_value_1 所对就 B 的记录写回到磁盘的动作就是所谓的“位图过滤”(Oralce不一定会启用位图过滤，因为如果所有的 Si 本来就都在内存中，也没发生过将 Si 写回到磁盘的操作，那么这里 Oracle 就不需要启用位图过滤了 )。我们把B 所对应的每一个 Hash Partition 记为Bj。

11. 上述去 Si 中查找匹配 Hash Bucket 和构建 Bj 的过程会一直持续下去，直到遍历完 B 中的所有记录为止。

12. 至此 Oracle 已经处理完所有位于内存中的 Si 和对应的 Bj，现在只剩下位于磁盘上的si 和Bj还未处理。

13. 因为在构建 Si 和Bj时用的是同样的哈希函数 hash_func_1 和hash_func_2，所以 Oracle 在处理位于磁盘上的 Si 和Bj的时候可以放心地配对处理，即只有对应 Hash Partition     Number 值相同的 Si 和Bj才可能会产生满足连接条件的记录。这里我们用 Sn 和Bn来表示位于磁盘上且对应 Hash Partition     Number 值相同的 Si 和Bj。

14. 对于每一对 Sn 和Bn，它们之中记录数较少的会被当作驱动结果集，然后 Oracle 会用这个驱动结果集 Hash Bucket 里的记录的 hash_vale_2 来构建新的 Hash Table，另外一个记录数较多的会被当作被驱动结果集，然后Oracle 会用这个被驱动结果集 Hash Bucket 里记录的 hash_value_2 去上述构建的新的 Hash Table 中找匹配记录。注意，对每一对 Sn 和Bn而言，Oracle始终会选择它们中记录数较少的来作为驱动结果集，所以每一对 Sn 和Bn的驱动结果集都可能会发生变化，这就是所谓的“动态角色互换”。

15. 步骤 14 中如果存在匹配记录，则该匹配记录会作为满足目标 SQL 连接条件的记录返回。

16. 上述处理 Sn 和Bn的过程会一直持续下去，直到遍历完所有的 Sn 和Bn为止。

      哈希连接的优缺点及适用场景总结如下：

• 哈希连接不一定会排序，或者说大多数情况下都不需要排序。

• 哈希连接的驱动表所对应的连接列的可能性应尽可能好，因为这个可选择性会影响对应 Hash Bucket 中的记录数，而 Hash Bucket 中的记录数又会直接影响从该 Hash Bucket 中查找匹配记录的效率。如果一个 Hash Bucket 里所包含的记录数过多，则可能会严重降低所对应哈希连接的执行效率，此时典型的表现就是该哈希连接执行了很长时间都没有结束，数据库所在数据库服务器上的 CPU 占用率很高，但目标 SQL 所消耗的逻辑读却很低，因为此时大部分时间都耗费在了遍历上述 Hash Bucket 里的所有记录上，而遍历 Hash Bucket 里的记录这个动作发生在 PGA 的工作区里，所以不耗费逻辑读。

• 哈希连接只适用于 CBO，它也只能用于等值连接条件( 即使是哈希反连接，Oracle实际上也是将其转换成了等价的等值连接)。

• 哈希连接很适合于小表和大表之间做表连接且连接结果集的记录数较多的情形，特别是在小表的连接列的可选择性非常好的情况下，哈希连接的执行时间就可以近似看作是和全表扫描那个大表所耗费的时间相当。

• 当两个表做哈希连接时，如果在施加了目标 SQL 中指定的谓词条件 ( 如果有的话 ) 后得到的数据量较小的那个结果集所对应的 Hash Table 能够完全被容纳在内存 (PGA 的工作区)，则此时的哈希连接的执行效率会非常高。

1.2.4 笛卡儿连接

      笛卡儿连接 (Cross Join) 又称为笛卡儿乘积(Caresian Product)，这是一种两个表在做表连接时没有任何连接条件的表连接方法。

      如果两个表 ( 假如为 T1 和T2)在做表连接时使用的是笛卡儿连接，则 Oracle 会依次顺序执行如下步骤：

1. 首先以目标 SQL 中指定的谓词条件 ( 如果有的话 ) 访问表 T1，此时得到的结果集我们记为结果集1，这里假设结果集1 的记录数为m。

2. 接着以目标 SQL 中指定的谓词条件 ( 如果有的话 ) 访问表 T2，此时得到的结果集我们记为结果集2，这里假设结果集2 的记录数为n。

3. 最后对结果集 1 和结果集 2 执行合并操作，从中取出匹配记录来作为笛卡儿连接的最终执行结果。这里的特殊之处在于对于笛卡儿连接而言，因为淌有表连接条件，所以在对结果集 1 和结果集 2 执行合并操作时，对于结果集 1 中的任意一条记录，结果集 2 中的所有记录都满足条件，即它们都会是匹配记录，所以上述笛卡儿连接的连接结果的记录数就是 m 和n的乘积 ( 即m×n)。

    语句示例：select t1.col1,t2.col3 from t1,t2;

      标准 SQL 用关键字“CROSS JOIN”来表示笛卡儿连接，如select t1.col1,t2.col3 from t1 cross join t2;

      对于笛卡儿连接的优缺点及适用场景总结如下：

• 笛卡儿连接的出现通常是由于目标 SQL 中漏写了表连接条件，所以笛卡儿连接一般是不好的，除非刻意这样做 ( 比如有些情况下可以利用笛卡儿连接来减少对目标 SQL 中大表的全表扫描次数)。

• 有时候出现笛卡儿连接是因为目标 SQL 中使用了 ORDERED Hint，同时在该SQL 的SQL文本中位置相邻的两个表之间又没有直接的关联条件。

• 有时候出现笛卡儿连接是因为目标 SQL 中相关表的统计信息不准。比如三个表 T1、T2 和T3做表连接，T1和 T2 的连接条件为 T1.ID1=T2.ID1，T2 和T3的连接条件为 T2.ID2=T3.ID2，同时在表T2 的连接列 ID1 和ID2上存在一个包含这两个连接列的组合索引。如果表 T1 和T3的统计信息不准，导致 Oracle 认为表 T1 和T3都只有很少量的记录 ( 比如都只有 1 条记录 )，则此时Oracle 很可能会选择先对表 T1 和T3做笛卡儿连接，然后再和表 T2 做表连接。因为 Oracle 认为表 T1 和T3做笛卡儿连接后连接结果集的 Cardinality 的值是 1，并且连接结果中间会同时包含列ID1 和列 ID2，这意味着此时Oracle 就可以利用表 T2 中的上述组合索引了。这种笛卡儿连接通常是有问题的，如果表 T1 和T3的实际记录数并不都是 1，而全部是1000，那么此时表T1 和表 T3 做笛卡儿连接的结果集的 Cardinality 的值将是 100 万，显然这种情况下如果还是按照笛卡儿连接的方式来执行的话，则该 SQL 的执行效率就会受到严重影响。

参考《基于 Oracle 的 SQL 优化》

一、优化器的模式

优化器的模式用于决定在 Oracle 中解析目标 SQL 时所用优化器的类型，以及决定当使用 CBO 时计算成本值的侧重点。这里的“侧重点”是指当使用 CBO 来计算目标 SQL 各条执行路径的成本值时，计算成本值的方法会随着优化模式的不同而不同。

在 Oracle 数据库中，优化器的模式是由参数 OPTIMIZER_MODE 的值决定的，OPTIMIZER_MODE 的值可能是 RULE、CHOOSE、FIRST_ROWS_n(N=1,10,100,1000)、FIRST_ROWS 或 ALL_ROWS。

OPTIMIZER_MODE 的各个可能的值的含义为如下所示：

1.RULE

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

2.CHOOSE

CHOOSE 是 Oracle9i 中 OPTIMIZER_MODE 的默认值，它表示 Oracle 在解析目标 SQL 是到底是使用 RBO 还是使用 CBO 取决于该 SQL 中所涉及的表对象是否有统计信息。具体来说就是：只要该 SQL 中所涉及的表对象中有一个有统计信息，那么 Oracle 在解析该 SQL 时就会使用 CBO；如果该 SQL 中所涉及的所有表对象均没有统计信息，那么此时 Oracle 会使用 RBO。

3.FIRST_ROWS_n(n=1,10,100,1000)

其含义是指当 OPTIMIZER_MODE 的值为 FIRST_ROWS_n(n=1,10,100,1000)时，Oracle 会使用 CBO 来解析目标 SQL，且此时 CBO 在计算该 SQL 的各条执行路径的成本值时的侧重点在于以最快的响应速度返回头 n(n=1,10,100,1000)条记录。Oracle 会把那些最快的响应速度返回头 (n=1,10,100,1000) 条记录所对应的执行步骤的成本修改成一个很小的值 (远小于默认情况下 CBO 对同样执行步骤所计算出的成本值)。这样 Oracle 就既没有违背 CBO 选择执行计划的总原则(成本值最小)，同样又兼顾 了 FIRST_ROWS_n(n=1,10,100,1000) 的含义。

4.FIRST_ROWS

FIRST_ROWS 是一个在 Oracle9i 中就已经过时的参数，它表示 Oracle 在解析目标 SQL 是会联合使用 CBO 和 RBO。这里联合使用 CBO 和 RBO 的含义是指在大多数情况下，FIRST_ROWS 还是会使用 CBO 来解析目标 SQL，且此时 CBO 在计算该 SQL 的各条执行路径的成本值时的侧重点在于以最快的响应速度返回头几条记录(类似于 FIRST_ROWS_n)；但是，当出现一些特定情况时，FIRST_ROWS 转而会使用 RBO 中的一些内置规则来选取执行计划而不再考试成本。比如当 OPTIMIZER_MODE 的值为 FIRST_ROWS 有一个内置的规则，就是如果 Oracle 发现能用相关的索引来避免排序，则 Oracle 就会选择该索引所对应的执行路径而不再考虑成本，这显然是不合理的。与这对应的，在当 OPTIMIZER_MODE 的值为 FIRST_ROWS 的情形下，你会发现索引全扫描出现的概率会比之前有所增加，这是因为走索引全面扫描能够避免排序的缘故。

5.ALL_ROWS

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

实际上，成本的计算方法随着优化器模式的不同而不同，主要体现在 ALL_ROWS 和 FIRST_ROWS_n(n=1,10,100,1000)对成本值计算方法的影响上。当优化器模式为 ALL_ROWS 时，CBO 计算成本的侧重点在于最佳的吞吐量；而当优化器模式为 FIRST_ROWS_n(n=1,10,100,1000)时，CBO 计算成本的侧重点会变为以最快的响应速度返回头 n(n=1,10,100,1000)条记录。这意味着同样的执行步骤，在优化器模式不同时 CBO 分别计算出来的成本会存在巨大的差异，这也就意味着优化器对 CBO 计算成本 (进而对 CBO 选择执行计划) 有着决定性的影响。

二、结果集

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

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

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

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

三、访问数据的方法

对于优化器而言，它在解析目标 SQL、得到其执行计划时至关重要的一点是决定访问数据的方法，即优化器要决定采用什么样的方式和方法去访问目标 SQL 所需要访问的存储在 Oracle 数据库中的数据。

目标 SQL 所需要访问的数据一般存储在表，而 Oracle 访问表中数据的方法有两种：一种是直接访问表；另一种是先访问索引，再回表(当然，如果目标 SQL 所访问的数据只通过访问相关的索引就可以得到，那么此时就不需要再回表了)。

3.1 访问表的方法

Oracle 数据库中直接访问表中数据的方法有两种：一种是全表扫描；另一种是 ROWID 扫描。

3.1.1 全表扫描

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

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

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

3.1.2 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 伪列的值翻译成对应数据行的实际物理存储地址。

3.2 访问索引的方法

这里提到的索引是指最常用的 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 里的索引叶子块是左右互联的，即相当于有一个双向指针链表把这些索引叶子块互相连接在了一起。

3.2.1 索引唯一性扫描

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

3.2.2 索引范围扫描

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

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

3.2.3 索引全扫描

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

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

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

3.2.4 索引快速全扫描

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

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

1)索引快速全扫描只适用于 CBO。

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

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

3.2.5 索引跳跃式扫描

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

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

参考：《基于 Oracle 的 SQL 优化》PDF 下载见 http://www.linuxidc.com/Linux/2017-02/140521.htm

更多 Oracle 相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12

本文永久更新链接地址：http://www.linuxidc.com/Linux/2017-02/140522.htm