1      序

执行计划是Oracle优化器在执行SQL时选择的执行方案。对于优化器来说，同一个SQL可能有很多种执行计划，执行效率也可能相差很多。虽然优化器已经越来越智能，可以通过收集统计信息选择出最优的执行计划，看懂执行计划和调整执行计划仍然是数据库调优的必备技能。

本文讲解了常见的执行计划，重点介绍多表连接的执行计划。虽然SQL语句中存在三个甚至更多的表连接，但是实际上优化器每次只能做两个表的连接，然后使用连接结果再和第三个表连接，所以只要掌握了两个表的连接自然也就掌握了多表的连接。

表的连接方式有很多种，最常用的是嵌套循环和哈希连接，需要重点掌握。如果你还没有掌握单表的执行计划，请先阅读轻松掌握Oracle索引，它是阅读本文的基础。看完本文，相信你可以看懂常见的执行计划，知道什么样的执行计划好，以及如何使用提示固定简单的执行计划。

2      基础知识

    在讲解执行计划前，首先介绍一些基础知识：优化器和统计信息。对于优化器，重点掌握CBO优化器，因为RBO已经逐渐被淘汰。对于统计信息，需要掌握常用的收集和查看方法。

2.1      优化器

（1）优化器的作用

    SQL语句是非过程性的语言，它只说明需要取哪些数据，但是没有说明如何去取这些数据。对于一个SQL语句，可以使用的取数据方式有很多种，所以需要优化器来选择使用哪种方式。每一种取数据的方式就是一种执行计划。

（2）优化器的种类有哪些

    优化器有RBO和CBO两种，RBO是基于规则的优化器，CBO是基于成本的优化器，RBO是早期的优化器，从10g开始，oracle不再为RBO提供服务了，默认的优化器也是CBO了。

（3）RBO有哪些特点

    RBO是基于规则的优化器，它在解析SQL时只关注语句本身，不关注表中的数量，只要表结构没有变化，同一个SQL语句的执行计划是不变的。RBO不会计算成本（cost），所以在执行计划中，cost列为空。RBO的规则是固定的，例如，如果查询列上有索引，RBO会优先选择索引扫描。RBO在选择表的连接顺序时优先按照from后面从右到左的顺序。注意：只是优先，并不是一定，因为还有其他规则会影响RBO的选择。

（4）CBO有哪些特点

    RBO有明显的不足，因为它不关注表中的数据，仅仅根据规则选择的执行计划很可能不是最优的。CBO是针对RBO的不足产生的新一代的优化器，它在分析SQL的过程中会考虑各个表的数据量以及数据分布，因此理论上可以获得最优的执行计划。CBO优化器产生的执行计划的cost不为空，可以根据该列判断当前语句使用的是哪种优化器。CBO会计算每一种执行计划的cost，然后选择cost最小的执行计划。

（5）9i下使用什么优化器

    9i数据库的参数optimizer_mode默认设置为CHOOSE，这种配置的含义是默认使用RBO，但是一些特殊情况使用CBO。这些情况包括：使用了提示（rule提示除外，该提示是选择使用RBO的含义）；该SQL的任一个表被分析过，即有统计信息；使用了一些RBO不支持的特性，例如分区表。

    判断一个SQL使用哪种优化器，通过执行计划的cost列是否为空也很容易判断，注意在9i使用PL/SQL查看执行计划时要把Optimizer goal设置为Choose，如下图。

（6）11g下使用什么优化器

    11g默认使用CBO，除非使用了rule提示。如果SQL中含有分区表，即使使用rule提示仍然使用CBO，因为分区表等高级功能根本不支持RBO。

2.2      统计信息

（1）什么是统计信息

     CBO优化器需要根据表/索引中的数据来选择查询数据的方式，由于表中的数据量很大，不可能每次执行查询时实时的统计表中的数据量以及数据分布，所以需要定期分析数据，把表和索引的数据分布情况保存到数据字典里，以便优化器使用，这就是统计信息。

（2）数据字典中已经保存了表占用的空间大小，为什么还需要统计信息

    数据字典（user_segments）中虽然保存了表/索引占用的空间大小，但是这些信息对于优化器来说是不够的，例如，一个表可能占用了很多的空间，但是可能该表中一条数据都没有。

（3）如何查看统计信息

统计信息保存在数据字典中，主要包括表，表分区，索引，索引分区，列，列的直方图。我们主要关注表和索引上的统计信息，重点关注数据量和数据块数。

（4）如果没有统计信息或统计信息不准确，优化器如何处理

    如果SQL语句涉及的表/索引等没有统计信息，CBO优化器在首次解析该语句时会做动态采样，即到该表/索引中抽取一定比例的数据块来分析表的数据分布情况。当然，对于一个大表来说，这个比例不会很高，否则查询就太慢了，因此动态采样的结果不一定准确，可能选择不好的执行计划，同时使用动态采样会导致首次执行一个SQL时比较慢。如果统计信息不准确，那么就可能误导优化器，导致选择了差的执行计划。

（5）如何生成统计信息

Oracle提供了dbms_stats包，可以很方便的收集统计信息，常用的函数如下表。

2.3      表的连接

    Join是一种试图将两个表结合在一起的谓词，一次只能连接2个表，表连接也可以被称为表关联。实际上应该使用”row source”来代替”表”，因为使用row source更严谨一些，表连接时并不是每次都需要连接整个表。

row source(表)之间的连接顺序对于查询的效率有非常大的影响。通过首先存取特定的表，即将该表作为驱动表，这样可以先应用某些限制条件，从而得到一个较小的row source，使连接的效率较高，这也就是我们常说的要先执行限制条件的原因。一般是在将表读入内存时，应用where子句中对该表的限制条件。

　　目前为止，无论连接操作符如何，典型的连接类型共有3种：

　  嵌套循环(Nested Loops (NL) )

　　哈希连接(Hash Join)

　　排序合并连接(Sort Merge Join, SMJ)

    本文只介绍嵌套循环和哈希连接，排序合并连接在实际应用中很少使用。

3      嵌套循环

3.1      什么是嵌套循环

    嵌套循环是最常见的表的连接方式，在RBO下，优化器倾向使用嵌套循环（RBO不支持哈希连接），下面我们首先通过一个最简单的例子来理解嵌套循环，其中NESTED LOOPS就是嵌套循环的意思。

这个执行计划的含义是以T_USERSERVICEINFO为驱动表（外表），先扫描该表，对该的每一条数据全表扫描T_USERINFO（内表），最后返回匹配的数据。这个过程很像Java中的循环语句。

3.2      如何选择驱动表

3.2.1        以小表为驱动表

    驱动表在执行计划中显示为上面的表，如上图中的T_USERSERVICEINFO。两个表做嵌套循环，驱动表的选择很重要。我们可以简单计算下哪个表做驱动表好。

假设T_USERSERVICEINFO是个大表，有4000万数据，占有100万个块；而T_USERINFO是个小表，有4000条数据，占有100个块。

（1）以大表为驱动表去要访问的数据块：对T_USERSERVICEINFO全表扫描需要访问100万个块，而T_USERSERVICEINFO中的每一条数据都需要全表扫描T_USERINFO一次，需要访问40亿个块（4000万*100），总共访问（40亿+100万）个块。

（2）以小表为驱动表去要访问的数据块：对T_USERINFO全表扫描需要访问100个块，而T_USERINFO中的每一条数据都需要全表扫描T_USERSERVICEINFO一次，需要访问40亿个块（4000*100万），总共访问（40亿+100）个块。

从上面的计算看，当两个表都是全表扫描时，选择小表做驱动表相对好些。不过，两者的查询代价都太大，都是不能接受的，因为内表的数据被访问了N遍（N为驱动表的数据量），这是严重的浪费。

3.2.2        内表的关联列上必须有索引

    如果内表T_USERSERVICEINFO的关联列PHONENUMBER有索引会不会大大提升查询效率呢？我们接下来看下面的例子。

在这个例子中，仍然使用小表（T_USERINFO）为小表，但是对内表的访问不再全表扫描了，而是使用内表的关联列的索引。

此时的执行计划显示为两层的嵌套循环（不同Oracle版本显示的执行计划可能不同）。它的执行步骤是：

（1）全表扫描T_USERINFO。

（2）对第一步的每一条数据访问T_USERSERVICEINFO的索引IX_USERSVC_PHONE，使用第一步查询出来的PHONENUMBER，到T_USERSERVICEINFO表做索引范围扫描。

（3）获取第二步从索引叶子中查询到目标数据的ROWID，分别到T_USERSERVICEINFO表中根据ROWID直接获取目标数据块信息。

（4）返回目标数据的信息。

从上面的过程看，优化器把根据ROWID访问表这个过程也看作是嵌套循环了。了解了执行过程，下来再来计算下查询的代价。假设索引的高度是4，最终有4000条数据命中。

内表有索引时访问的块数：对T_USERINFO全表扫描需要访问100个块，而T_USERINFO中的每一条数据都需要扫描索引IX_USERSVC_PHONE一次，需要访问4000*4=1.6万个块，使用ROWID到表需要访问4000个块，总共需要访问100+1.6万+4000=20100个块，比前面的计算结果提升了上万倍的性能。

感兴趣的话你还可以计算下如果大表为驱动表，小表上有索引的情况，肯定比小表为驱动表差的多，因为仅对大表的全表扫描就需要访问100万个块了。

3.3      带有其他条件的嵌套循环

     实际应用中的嵌套循环往往比上面的例子要复杂的多，下面说明常见的几种场景。

3.3.1        驱动表上有索引

    当b表上有查询条件phonenumber = '123'，此时还是选a表为驱动表好吗？

   虽然b表比a表大，但是b表应用条件（phonenumber = '123'）后只会查出1条数据，那么如果以b表为驱动表，需要访问a表的索引次数最多1次，而反过来需要4000次，所以此时以b表为驱动表好。

    嵌套循环要使用小表（实际是row source）为驱动表，并不能认为表中数据量大的就一定不能做驱动表，实际上只要需要关联的数据集小就应该做驱动表。嵌套循环关注的数据量是需要连接的row source。

3.3.2        内表非关联列上的索引

如果内表在关联列上没有索引，但在其他列上有索引呢？如下例所示，b表可以使用createtime的索引，此时好像内表上使用索引了，但如果根据createtime查出的数据量很大性能仍然是很差的，因为对a表的每一条数据都要遍历根据createtime查出的数据。

重复就是浪费，一次能查出来的数据不要查多次。

3.3.3        rownum条件

    下面看一个带rownum条件的嵌套循环。在执行计划中，rownum条件对应COUNT STOPKEY部分。嵌套循环有一个很大的好处，每一步不需要执行完就可以执行下一步，所以只要查出前100条满足结果的数据那么查询就可以结束。如果目标数据不足100条时，实际上还是需要访问所有数据的。

关于提示

3.4.1        为什么要使用提示

提示是用来调整和固定执行计划的。如果统计信息正确，优化器是可以选出最优的执行计划的。但是在某些情况下我们也可能需要使用提示：

  没有收集统计信息

  临时表上不能收集统计信息

  优化器选择的执行计划不是最优的

    提示不是万能的，对于复杂的SQL，很难用提示调整执行计划，所以尽量把SQL写简单些。

3.4.2        使用提示为什么要加全套提示，怎样才算全套

如果使用了提示则优化器会使用CBO，如果系统中缺少统计信息，那么此时执行计划不稳定，需要加全套提示来固定，提示主要包括三个方面：

  指定表的连接顺序，使用leading、ordered等，leading和ordered不能同时使用，leading只能指定一个表为驱动表，而ordered可以指定多个表的连接顺序。

  指定表的连接方式，使用use_nl、use_hash等，注意它仅表示表连接方式而不表示连接顺序，所以参数中的顺序无关紧要。

  指定表的访问路径，使用full、index等。

3.4.3        常用提示说明

    下面是常用提示的简要说明，后面还有针对这些提示的实例应用。

3.5      带子查询的嵌套循环

3.5.1        以主查询为驱动表的Semi-join

如果主查询的表需要连接的数据量小，并且子查询表的关联列上有索引，那么应该以主查询的表为驱动表，如下图所示，使用in和exists是等价的。

如果想对这种查询指定嵌套循环并且以a表为驱动表，那么可以用nl_sj提示，注意这个提示写在子查询里。此处的s是semi的缩写，表示半连接。此处也可以用no_unnest提示，因为10g后，nl_sj已不建议使用。

3.5.2        以子查询为驱动表的Semi-join

如果子查询的结果集比较小，那么就要子查询为驱动，此时可以用ordered提示。为了固定执行计划还可以加上use_nl等其他提示。

这种查询很常见，它应用在b表的结果集很小的情况。注意此时优化器会先对b表排序驱重，因为优化器认为重复数据没必要和主表做连接，相对而言，连接的代价比去重高。如下图所示，使用in和exists是等价的。

3.5.3        嵌套循环的Anti-join

    Anti-join是指使用not in/not exists的子查询。这种查询只能以主查询为驱动表。一般尽量用not exists，而不用not in。因为只要有一条数据关联列的值为空，not in的结果就是空。当关联列非空时，二者是等价的，如下例所示。

3.6      多表的嵌套循环

    下面是一个典型的多表嵌套循环。ordered指定表的连接顺序a=>b=>c；use_nl指定三个表的连接方式是嵌套循环；index和full指定了三个表的访问路径，此时a表只能是全表扫描，而不能使用索引了，想想问什么？

    多表连接时最重要的是连接的顺序，一般原则是优先连接中间结果少的表。例如，a，b，c三个表的连接，最终结果是100条数据。如果a和b连接结果是100万数据，再和c连接的结果是100条数据；而a和c连接结果是200条数据，再和b连接的结果是100条数据。那么，显然a和c先连接比a和b先连接好。

3.7      带外连接的嵌套循环

    如果是外连接，那么只能以左表为驱动表，这一点在哈希连接时也是如此。如下图所示，即使指定b表为驱动表，优化器仍然选择a表为驱动表。注意此时的执行计划显示为NESTED LOOPS OUTER。

3.8      小技巧

3.8.1        通过rule提示固定执行计划

对上面这个三表嵌套循环，如果使用rule提示是什么效果呢？如下图，rule默认的表的连接顺序是从右至左的：c=>b=>a。而且，RBO优化器默认会使用嵌套循环和索引扫描，如果当前的执行计划是合适的，那么可以使用rule提示固定，因为RBO的执行计划是不会变化的。

  虽然RBO默认的连接顺序是从右至左，但是这只是默认情况，如果有其他条件，RBO认为这个条件的优先级更高，那么RBO会改变连接的顺序。如下图，当b表上有查询条件并且可以走索引扫描，那么优化器会优先访问b表，并且以b表为驱动表和其他表做嵌套循环。

3.8.2        屏蔽索引

    一个表上可能有很多个索引，如果不想使用某个索引可以在列上追加一个表达式，如下图，字符类型可以拼接一个空字符串，数值类型可以加零。

3.9      嵌套循环的使用场景总结

嵌套循环是最重要的连接方式，尤其是在实时性比较高的系统中，因为这种系统的查询要求快速返回，不能访问很多的数据块，而这正是嵌套循环适用的场景。

嵌套循环的使用场景：

（1）驱动表的结果集小（应用过自己的查询条件后），选择驱动表非常重要。

（2）内表的连接列上有索引。

（3）驱动表和内表连接匹配的数据量小，即扫描内表连接列的索引的次数少。

4      哈希连接

4.1      什么是hash连接

    嵌套循环只适用在连接数据量小的情况，如果有上百万数据需要连接，那么就可能需要对内表做上百万次的索引范围扫描，导致内表的很多数据块被重复扫描，而且，索引范围扫描如果需要扫描超过5%的数据很可能就比全表扫描的性能还差了。

实际应用中，这种大数据量的连接是普遍存在的，尤其在后台job和数据分析中，这时候就该考虑应用hash连接了。下面是一个hash连接的实例。下面通过这个例子来说明hash连接的过程。

    （1）选择一个表在内存中使用连接列生成hash表

     这个表就是选择的驱动表（上例中为a表，执行计划中显示在上面），由于需要在内存（PGA）中生成hash表，所以选择数据集较小的表为驱动表较好。如果内存中放不下这个hash表，那么就需要放到临时表空间中，如果临时表空间也满了，那么就会报错了。

这个步骤一般需要全表扫描驱动表，或者说需要连接的数据集。那么，不管另一个表有没有数据，即使使用了rownum的条件，这个全表扫描也是必须做的。

（2）全表扫描探测表，查询hash表

当驱动表生成了hash表后，遍历另一个表（也叫探测表），对探测表的每一条数据使用连接列的值到hash表中匹配，如果匹配上将对应数据保存到结果集中，直到查询结束。

如果查询条件有rownum<=100的条件，那么只要找到100条满足的数据就可以停止探测表的扫描了。

如果hash表没有全部放在内存中，那么探测表就需要遍历多次，分别对hash表的多个分区做连接。

（3）输出hash结果

hash连接结束就得到了连接结果，跟索引扫描不同，hash连接一般是不需要再访问表的，所以hash连接对表的扫描一般是全表扫描或快速全索引扫描。

4.2      带子查询的hash连接

与嵌套循环类似，对in/exists的连接可以使用hash_sj提示，对于not in/not exists的连接可以使用hash_aj提示。

4.2.1        Semi-join

下面第一个例子中的HASH JOIN RIGHT SEMI是优化器做了优化，它不同于第二个中的HASH JOIN SEMI。因为这个语句正常应该以a表为驱动表做hash表，以b表为探测表。当优化器认为b表更小时也可以选择b为驱动表，此时的执行计划就是HASH JOIN RIGHT SEMI。此处使用in和exists是等价的。

4.2.2        Anti-join

    对于hash_aj也类似，优化器也可以选择主查询或子查询为驱动表，区别是当选择子查询为驱动时，执行计划会多一个RIGHT。    

跟嵌套循环类似，尽量用not exists，而不用not in，不过在9i上，如果关联列上有索引，not exists的hash_aj是不生效的，此时只能用not in。

4.3      小技巧

4.3.1        使用快速全索引扫描

如果可能的话，优化器会使用快速全索引扫描替代全表扫描，因为一个索引占用的空间比整个表的空间要小的多，所以我们在执行计划中经常可以看到FAST INDEX FULL SCAN，如下例所示。

4.3.2        update技巧

如果需要使用b表的值更新a表对应的值，并且a和b都是大表，应该怎么做呢？

由于a和b都是大表，需要大量的数据连接，显然应该使用hash连接，但是由于是update语句，下面这样是用不了hash连接的，实际还是嵌套循环。

  方法1：使用视图更新

这个方法要求比较苛刻，关联列在源表中必须是唯一键。

  方法2：PL/SQL程序块使用rowid更新，这个方法比较常用

begin

    for item in (select /*+leading(a) use_hash(a b) */a.rowid, b.operinitinfo

                   from t_userinfo a, t_userserviceinfo b

                  where a.userid = b.userservid ) loop

        update t_userinfo

           set username = item.operinitinfo

         where rowid = item.rowid;   

    end loop;

end;

/

4.3.3        使用并行查询

为了快速获得查询结果，对大表的查询可以设置并行查询。parallel这个提示在数据分析时经常需要使用。

4.4      hash连接使用场景总结

hash连接是非常常用的连接方式，它有以下特点：

（1）适用于较大数据集的连接，并且连接条件必须是等值连接。

（2）尽量选择小的数据集作为驱动表在内存中生成hash表。

（3）设置一个较大的PGA可以加快hash连接的速度，避免使用临时表空间。

（4）多表连接时同样要注意连接顺序，中间结果集尽量小，少做无用功。

    hash连接相对与嵌套循环，它实际上是通过空间来换取时间，因为它需要保存hash表，占用较大的内存，在临时表空间不足的情况下是可能报错的，而嵌套循环一般不会报错，但可能非常慢。

5      分区表

5.1      分区裁剪

分区表在实际项目中广泛应用，它在物理上实现了表数据的分离而逻辑上却仍然是一个表，这样既提高了性能也降低了应用开发的难度，因为分区特性对应用是透明的。

分区裁减是指当查询分区表时，如果查询条件含有分区列，那么优化器会根据分区列的查询条件判断哪些分区是不需要扫描的，这样就可以大大提高查询的性能。为了应用分区裁减，对分区表的查询一般要有分区列的条件。

5.2      分区表的执行计划

分区表的执行计划和普通表略有不同，下面介绍下常见的执行计划。

（1）只有一层分区，扫描所有分区

由于没有分区列的条件，所以需要扫描所有分区：PARTITION RANGE ALL。

（2）只有一层分区，扫描部分分区

由于包含了分区列的条件，所以只需要扫描部分分区：PARTITION RANGE ITERATOR。

（3）二级分区

由于包含了一级分区列的条件，所以一级分区只需要扫描部分分区，但是没有二级分区的查询条件，所以对二级分区只能扫描所有分区：PARTITION HASH ALL。

6      总结

执行计划对开发人员来说一直是很神秘的东西，看起来乱七八糟的，不知道是什么含意，为什么稍微变化了一点性能差别就那么大呢？多表连接的执行计划有很多种，有的看起来非常复杂，想读懂都很困难，所以建议一个SQL连接的表的数量不超过四个，要不然很容易出现性能问题。

本文总结了开发过程中常见的执行计划，重点讲解嵌套循环和哈希连接。看完文章后，希望你能回答出以下几个问题：

（1）什么情况下应该用嵌套循环？

（2）什么情况下应该用hash连接？

（3）如何选择驱动表？

（4）查询分区表为什么要带分区列的条件？

（5）什么是Semi-join和Anti-join？

实际应用中的执行计划可能是千变万化的，本文不可能都覆盖到，不过，只要掌握了原理，多思考，相信你就能举一反三的。由于时间仓促外加水平有限，文中疏漏在所难免，如果你有任何高见欢迎讨论和指正。