Oracle里另外一些典型的执行计划

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

在之前的文章里写了 Oracle 里常见的执行计划，可以参考文章：http://www.linuxidc.com/Linux/2017-02/141090.htm，这篇文章里介绍的是其他的一些典型的执行计划。

1. AND-EQUAL(INDEX MERGE)

AND-EQUAL 又称为 INDEX MERGE, 顾名思义，INDEX MERGE 就是指如果 where 条件里出现了多个针对不同单列的等值条件，并且这些列上都有单键值的索引，则 Oracle 可能会以相应的单个等值条件去分别扫描这些索引；然后 Oracle 会合并这些扫描单个索引所得到的 rowid 集合，如果能从这些集合中找到相同的 rowid，那么这个 rowid 就是目标 SQL 最终执行结果所对应的 rowid。最后，Oracle 只需要用这些 rowid 回表就能得到目标 SQL 的最终执行结果。

AND-EQUAL 在执行计划中对应的关键字就是“AND-EQUAL”，我们可以使用 Hint 来强制让 Oracle 走 AND-EQUAL。

看一个实例：

从上述显示内容中可以看出，现在此 SQL 的执行计划走的是对索引 IDX_MGR 和 IDX_DEPTNO 的 AND-EQUAL。

2. INDEX JOIN

INDEX JOIN 很容易引起误解，因为它并不是指通常意义上针对多表的表连接。这里 INDEX JOIN 指的是针对单表上的不同索引之间的连接。

还以上面的 EMP_TEMP 为例，已经在列 MGR 和 DEPTNO 上分别创建了两个单键值的 B *Tree 索引，如果此时执行 SQL 语句“select mgr,deptno from emp_temp”，因为这里要查询的列 MGR 和 DEPTNO 均可来源于索引 IDX_MGR 和 IDX_DEPTNO(不考虑 NULL 值)，不用回表，所以除了常规的执行方法之外，Oracle 还可以采用如下方法：分别扫描索引 IDX_MGR 和 IDX_DEPTNO，得到的结果集分别记为结果集 1 和结果集 2，然后将结果集 1 和 2 做一个连接，连接条件就是“结果集 1.rowid= 结果集 2.rowid”，这样得到的最终连接结果 (不用回表) 就是上述 SQL 的执行结果。

很显然，针对上述 SQL 的 INDEX JOIN 的执行效率是不如我们直接在列 MGR 和 DEPTNO 上建一个组合索引，然后直接扫描该组全索引的效率高。INDEX JOIN 只是为 CBO 提供了一种可选的执行路径，大多数情况下，它只是额外多出的一种选择而已。

看一下例子：

从上述显示内容可以看出，现在目标 SQL 的执行计划走的是对索引 IDX_MGR 和 IDX_DEPTNO 的 HASH JOIN。

3. VIEW

Oracle 在处理包含视图的 SQL 时，根据该视图是否能做为视图合并(View Merging)，其对应的执行计划有如下两种形式。

• 如果可以做视图合并，则 Oracle 在执行该 SQL 时可以直接针对该视图的基表，此时 SQL 的执行计划中很可能不会出现关键字“VIEW”(不能完全依赖关键字“VIEW”的出现与否来判断 Oracle 是否做了视图合并，因为对于某些 SQL 而言，即使 Oracle 已经做了视图合并但其所对应的执行计划中可能还会显示关键字“VIEW”)。

• 如果不能做视图合并，则 Oracle 将把该视图看作一个整体并独立地执行它，此时 SQL 的执行计划中将会出现关键字“VIEW”。

看一个实例，还是使用上面的 EMP_TEMP 表：

从上述显示内容中可以看出，现在 SQL 的执行计划走的是对表 EMP_TEMP 的全表扫描，并且全表扫描进的过滤查询条件是 filter((“ENAME”=’CLARK’ AND “JOB”=’MANAGER’)). 显然这里 Oracle 做了视图合并，直接查询的视图 EMP_MGR_VIEW 的基表 EMP_TEMP，并且把针对视图的 where 条件推到了视图的内部，和原先创建视图时的限制条件做了合并。

现在修改视图 EMP_MGR_VIEW 的定义，其创建语句中加入 ROWNUM 关键字，这样新创建的同名视图 EMP_MGR_VIEW 将不能再做视图合并：

从上述显示内容中可以看出，现在该 SQL 的执行计划中包含了关键字“VIEW”，即表明这里 Oracle 并没有对视图 EMP_MGR_VIEW 做视图合并，视图 EMP_MGR_VIEW 被 Oracle 当作一个整体来独立执行。

4. FILTER

FILTER 直译过来就是过滤、筛选的意思，它是一种特殊的执行计划，所对应的执行过程就是如下三步：

得到一个驱动结果集

根据一定的过滤条件从上述驱动结果集中滤除不满足条件的记录

结果集中剩下的记录就会返回给最终用户或者继续参与一下个执行步骤。

看一个实例，还是使用上面的视图 EMP_MGR_VIEW：

从上述的显示内容可以看出，现在该 SQL 的执行计划走的是嵌套循环连接，并没有出现我们希望的 FILTER 类型的执行计划。这是因为 Oracle 在这里做了子查询展开(Subquery Unnexting)，即把子查询和它外部的 SQL 做了合并，转化成视图 VW_NOS_1 和表 EMP 做连接。

这里使用 Hint 禁掉子查询展开后重新执行上述 SQL:

从上述显示内容中可以看出，现在该 SQL 走的就是我们希望的 FILTER 类型执行计划。

FILTER 类型的执行计划实际上是种改良的嵌套循环连接，它并不像嵌套循环连接那样，驱动结果集中的有多少记录就得访问多少次被驱动表。

用一个实验验证：

注意到上述显示内容中 id= 2 的执行步骤所对应的列 A -Rows 的值为 3，id= 3 的执行步骤所对应的列 Starts 的值为 2，说明虽然全表扫描 T1 所得到的驱动结果集的数量为 3，但走 Filter 类型的执行计划时访问被驱动表 T2 的实际次数却不是 3，而是 2. 这是因为表 T 数量虽然是 3，但其列 COL2 的 distinct 值的数量却只有 2，所以在用过滤条件“where col2 in(select /*+ no_unnest */ col2 from t2)”去过滤表 T1 中的数据时，只用访问两次表 T2 就可以了。

5. SORT

SORT 就是排序的意思，执行计划中的 SORT 通常会以组合的方式出现，这些组合方式包括但不限于如下这几种：

• SORT AGGREGATE
• SORT UNIQUE
• SORT JOIN
• SORT GROUP BY 
• SORT ORDER BY
• BUFFER SORT

执行计划中即使出现了关键字“SORT”，也不一定意味着就需要排序，比如 SORT AGGREGATE 和 BUFFER SORT 就不一定需要排序。

看一个实例，还是使用上面的 EMP_TEMP 表：

从上述显示内容可以看出，现在 SQL 的执行计划走的是 SORT AGGREGATE，这里执行的 SQL 只是求了一个 sum 值，很显然这里不需要排序的。统计信息中的 sort(memroy)和 sort(disk)的值均为 0，也说明 Oracle 在执行此 SQL 时并没有做任何排序操作，所以我们说 SORT AGGREGATE 并不一定需要排序，这其中的关键字“SORT”具有一定的迷惑性。

下面再做实例：

上述 SQL 的含义是既要排序又要去重，它对应的执行计划就会是 SORT UNIQUE

从上述显示内容中可以看出，现在该 SQL 的执行计划走的是对 EMP 和 EMP_TEMP 的排序合并连接。SORT JOIN 类型的执行计划通常会出现在排序合并连接中，它是排序合并连接所对应的执行计划第一步要做的事情。

再执行如下 SQL：

上述 SQL 的含义是只需要单纯的排序，它对应的执行计划就会是 SORT ORDER BY:

接着执行下面的 SQL:

上述 SQL 的含义是既要排序又要分组，所以它对应的执行计划就会是 SORT GROUP BY:

最后执行如下 SQL：

从上述显示内容可以看出，现在该 SQL 的执行计划走的是对表 EMP_TEMP 和表 EMP 上主键 PK_EMP 的笛卡儿连接，因为上述 SQL 中没有指定连接条件。此处执行计划的步骤是首先全表扫描表 EMP_TEMP，扫描结果记为结果集 1；接着对表 EMP 上的主键 PK_EMP 做索引快速全扫描，并将扫描结果 load 进 PGA 中，然后对结果集 1 和结果集 2 做笛卡儿连接，最后笛卡儿连接的结果就是上述 SQL 的最终执行结果。执行计划中关键字“BUFFER SORT”就是表示 Oracle 会借用 PGA 并把扫描结果 load 进去，这样做的好处是省掉了相应的缓存在 SGA 中所带来的种种额外开销(如持有、释放相关 Latch 等)。PGA 常常用来做排序，这可能就是“BUFFER SORT”中关键字 SORT 的由来。

需要注意的是，BUFFER SORT 不一定会排序，也可能会排序，也可能不会。

看一个 SQL 是否排序，最直观的方法就是查看其统计信息中 ”sorts(memory)” 和 ”sorts(disk)” 的值，如果这两个指标的值大于 0，则说明该 SQL 在执行时经历过排序。但遗憾的是，这两个指标对 BUFFER SORT 而言是不准的，此时我们就需要借助目标 SQL 真实执行计划中 ”Column Projection Information” 部分 ”keys” 的值来判断到底所对应的 BUFFER SORT 有没有排序。”#keys” 的值就表示该执行步骤实际排序列的数量，如果 ”#keys” 值大于 0 时，则表示该执行步骤确实排过序了。

看如下 SQL：

注意到上述显示内容中“统计信息”部分的 sorts(memory)的值为 7，但由于该 SQL 中出现了 ID= 3 的执行步骤“BUFFER SORT”，所以这并不一定能说明该 SQL 在执行时经历过排序。

我们来看一下执行坟墓中 id= 3 的执行步骤“BUFFER SORT”所对应的“#keys”的值：

从上述显示内容中可以看出，Id= 3 的执行步骤“BUFFER SORT”所对应的“#keys”的值为 0，说明该 SQL 在执行“BUFFER SORT”时确实没有排序，排序的数量为 0。

这就验证了我们之前提到的观点：统计信息中 sorts(memory)和 sorts(disk)的值对于 BUFFER SORT 而言是不准的，Oracle 在执行 BUFFER SORT 时可能不需要排序。

6. UNION/UNION ALL

UNION/UNION ALL 表示对两个结果集进行合并，如果它们出现在执行计划中也表示相同的含义。

UNION 和 UNION ALL 的区别是：UNION ALL 仅仅是简单地将两个结果集合并，并不做任何额外的处理；而 UNION 除了将两个结果集简单合并之外，还会对合并后的结果集做排序和去重，即 UNION 相当于先做 UNION ALL，然后再对 UNION ALL 之后的结果集做 SORT UNIQUE

看一个实例：

从上述显示内容中可以看出，现在该 SQL 的执行计划走的是对表 EMP 和 EMP_TEMP 全表扫描后的结果集的 UNION ALL,UNION ALL 在执行计划中对应的关键字就是 UNION-ALL。表 EMP 有 13 条记录，表 EMP_TEMP 有 12 条记录，UNION ALL 合并后的结果集总是 25。

把 UNION ALL 改为 UNION：

从上述显示内容可以看出，现在该 SQL 的执行计划走的是对 EMP 和 EMP_TEMP 全表扫描的结果集的 UNION，UNION 在执行计划中对应的关键字就是 ”UNION-ALL” 和 ”SORT UNIQUE”, 即表示 UNION 相当于在 UNION ALL 的基础上做排序和去重。表 EMP_TEMP 的数据全部来源于表 EMP，所以这里 UNION 操作返回结果集的复数就是表 EMP 的行数 14。

7. CONCAT

CONCAT 就是 IN-List 扩展 (IN-List Expansion) 或 OR 扩展(OR Expansion)，IN-List 扩展 /OR 扩展在执行计划中对应的关键字是“CONCATENATION”，使用 Hint 来强制让 Oracle 走 IN-List 扩展 /OR 扩展。

看一下实例：

从上述显示内容可以看出，现在该 SQL 的执行计划走的是对表 EMP 和主键索引 PK_EMP 的 IN-List 迭代。

使用 Hint 让 Oracle 强制走 IN-List 扩展

从上面显示内容可以看出，Hint 失效了，还是走 IN-List 迭代。使用如下两个事件在当前 Session 中将 IN-List 迭代禁掉，并将输入参数 no_invalidate 的值设为 false 后重新收集一下统计信息，以便后续再次执行上述 SQL 时不会没用之前走 IN-List 迭代的执行计划：

从上述显示内容中可以看出，现在该 SQL 的执行计划变成了我们想要的 IN-List 扩展，在执行计划中对应的关键字就是 CONCATENATION。这里 CONCATENATION 的含义就相当于 UNION ALL，即上述 SQL 就相当于 UNION ALL 改写为如下的形式：

select empno,ename from emp where empno=7782

union all

select empno,ename from emp where empno=7698

union all

select empno,ename from emp where empno=7654

8. CONNECT BY

CONNECT BY 是 Oracle 数据库中层次查询 (Hierachical Queries) 所对应的关键字，如果出现在执行中也是表示同样的含义。

看一下实例：

从上述内容可以看到 KING 是 PRESIDENT，它所在记录的 MGR 的值为 NULL，表示 KING 没有上级。

我们执行如下 SQL，��KING 所在的记录开始，将所有人按照上下级关系分成显示出来：

查看执行计划：

从上述显示内容可以看出，现在该 SQL 的执行计划走的就是 CONNECT BY，在执行计划中我们也能看到 CONNECT BY 关键字。

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

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