【转载】看懂Oracle执行计划、表连接方式

2025年03月07日 15:25 · 阅读(47) ·

转载说明

本文来自博客园，作者：Iven_lin
原文链接：看懂Oracle执行计划、表连接方式

引言

看懂 Oracle 执行计划

之前看了了一本 Oracle 优化的书，收货不止，SQL 优化感觉受益匪浅因此记录一下

一：什么是 Oracle 执行计划？

执行计划是一条查询语句在 Oracle 中的执行过程或访问路径的描述

二：怎样查看 Oracle 执行计划？

因为公司用的 PLSQL 是英文版的(同时我也保存了中文版的截图)

①：配置执行计划需要显示的项

工具 —> 首选项 —> 窗口类型 —> 计划窗口 —> 根据需要配置要显示在执行计划中的列

  • 执行计划的常用列字段解释：
执行计划字段 执行计划字段解释
基数（Rows） Oracle 估计的当前操作的返回结果集行数
字节（Bytes） 执行该步骤后返回的字节数
耗费（COST）、CPU 耗费 Oracle 估计的该步骤的执行成本，用于说明 SQL 执行的代价，理论上越小越好（该值可能与实际有出入）
时间（Time） Oracle 估计的当前操作所需的时间

②：打开执行计划

在 SQL 窗口执行完一条 select 语句后按 F5 即可查看刚刚执行的这条查询语句的执行计划

注：在 PLSQL 中使用 SQL 命令查看执行计划的话，某些 SQL*PLUS 命令 PLSQL 无法支持，比如 SET AUTOTRACE ON

三：看懂 Oracle 执行计划

①：执行顺序

根据 Operation 缩进来判断，缩进最多的最先执行；（缩进相同时，最上面的最先执行）

例：上图中 INDEX RANGE SCAN 和 INDEX UNIQUE SCAN 两个动作缩进最多，最上面的 INDEX RANGE SCAN 先执行；

同一级如果某个动作没有子 ID 就最先执行
同一级的动作执行时遵循最上最右先执行的原则

例：上图中 TABLE ACCESS BY GLOBAL INDEX ROWID 和 TABLE ACCESS BY INDEX ROWID 两个动作缩进都在同一级，则位于上面的 TABLE ACCESS BY GLOBAL INDEX ROWID 这个动作先执行；

这个动作又包含一个子动作 INDEX RANGE SCAN，则位于右边的子动作 INDEX RANGE SCAN 先执行；

图示中的 SQL 执行顺序即为：

INDEX RANGE SCAN —> TABLE ACCESS BY GLOBAL INDEX ROWID —> INDEX UNIQUE SCAN —> TABLE ACCESS BY INDEX ROWID —> NESTED LOOPS OUTER —> SORT GROUP BY —> SELECT STATEMENT, GOAL = ALL_ROWS

（ 注：PLSQL 提供了查看执行顺序的功能按钮(上图中的红框部分) ）

②：对图中动作的一些说明

1.上图中 TABLE ACCESS BY … 即描述的是该动作执行时表访问（或者说 Oracle 访问数据）的方式

表访问的几种方式：（非全部）

表访问方式 说明
TABLE ACCESS FULL 全表扫描
TABLE ACCESS BY ROWID 通过 ROWID 的表存取
TABLE ACCESS BY INDEX SCAN 索引扫描
（1）TABLE ACCESS FULL（全表扫描）

Oracle 会读取表中所有的行，并检查每一行是否满足 SQL 语句中的 Where 限制条件；

全表扫描时可以使用多块读（即一次 I/O 读取多块数据块）操作，提升吞吐量；

使用建议：数据量太大的表不建议使用全表扫描，除非本身需要取出的数据较多，占到表数据总量的 5% ~ 10% 或以上

（2）TABLE ACCESS BY ROWID（通过 ROWID 的表存取）

先说一下什么是 ROWID？

ROWID 是由 Oracle 自动加在表中每行最后的一列伪列，既然是伪列，就说明表中并不会物理存储 ROWID 的值；

你可以像使用其它列一样使用它，只是不能对该列的值进行增、删、改操作；

一旦一行数据插入后，则其对应的 ROWID 在该行的生命周期内是唯一的，即使发生行迁移，该行的 ROWID 值也不变。

ROWID 详解：基于64 位编码的 18 个字符显示，其实 rowid 的存储方式是：10 个字节即 80 位存储，其中数据对象编号需要 32 位，相关文件编号需要 10 位，块编号需要 22，位行编号需要 16 位，由此，我们可以得出：

number 最大值
32bit 的 object number 每个数据库最多有 4G 个对象
10bit 的 file number 每个对象最多有 1022 个文件（2 个文件预留）
22bit 的 block number 每个文件最多有 4M 个 BLOCK
16bit 的 row number 每个 BLOCK 最多有 64K 个 ROWS

示例：ROWID

  1. select rowid,
  2.        substr(rowid, 1, 6) "OBJECT",
  3.        substr(rowid, 7, 3) "FILE",
  4.        substr(rowid, 10, 6) "BLOCK",
  5.        substr(rowid, 16, 3) "ROW"
  6.   from fnd_user fus
  7.  where fus.user_id = 1;

通过 dbms_rowid 这个包，可以直接的得到具体的 rowid 包含的信息：

  1. select dbms_rowid.rowid_object(rowid) object_id,
  2.        dbms_rowid.rowid_relative_fno(rowid) file_id,
  3.        dbms_rowid.rowid_block_number(rowid) block_id,
  4.        dbms_rowid.rowid_row_number(rowid) num,
  5.        rownum
  6.   from fnd_user fus
  7.  where rownum < 5

让我们再回到 TABLE ACCESS BY ROWID 来：

行的 ROWID 指出了该行所在的数据文件、数据块以及行在该块中的位置，所以通过 ROWID 可以快速定位到目标数据上，这也是 Oracle 中存取单行数据最快的方法；

（3）TABLE ACCESS BY INDEX SCAN（索引扫描）

在索引块中，既存储每个索引的键值，也存储具有该键值的行的 ROWID。

一个数字列上建索引后该索引可能的概念结构如下图：

所以索引扫描其实分为两步：

Ⅰ：扫描索引得到对应的 ROWID

Ⅱ：通过 ROWID 定位到具体的行读取数据

————————索引扫描延伸—————————-

索引扫描又分五种

索引扫描 描述
INDEX UNIQUE SCAN 索引唯一扫描
INDEX RANGE SCAN 索引范围扫描
INDEX FULL SCAN 索引全扫描
INDEX FAST FULL SCAN 索引快速扫描
INDEX SKIP SCAN 索引跳跃扫描
a) INDEX UNIQUE SCAN（索引唯一扫描）

针对唯一性索引（UNIQUE INDEX）的扫描，每次至多只返回一条记录；

表中某字段存在 UNIQUE、PRIMARY KEY 约束时，Oracle 常实现唯一性扫描；

b) INDEX RANGE SCAN（索引范围扫描）

使用一个索引存取多行数据；

发生索引范围扫描的三种情况：

  • 在唯一索引列上使用了范围操作符（如：>， <，<>， >=，<=，between）
  • 在组合索引上，只使用部分列进行查询（查询时必须包含前导列，否则会走全表扫描）
  • 对非唯一索引列上进行的任何查询
c) INDEX FULL SCAN（索引全扫描）

进行全索引扫描时，查询出的数据都必须从索引中可以直接得到（注意全索引扫描只有在 CBO 模式下才有效）

延伸阅读：Oracle 优化器简述

———————————- 延伸阅读：Oracle 优化器简述 ———————————-

Oracle 中的优化器是 SQL 分析和执行的优化工具，它负责生成、制定 SQL 的执行计划。

Oracle 的优化器有两种：

Oracle 的优化器 描述
RBO（Rule-Based Optimization）
基于规则的优化器 		RBO 有严格的使用规则，只要按照这套规则去写 SQL 语句，无论数据表中的内容怎样，也不会影响到你的执行计划；

换句话说，RBO对数据“不敏感”，它要求 SQL 编写人员必须要了解各项细则；

RBO 一直沿用至 ORACLE 9i，从 ORACLE 10g 开始，RBO 已经彻底被抛弃。
CBO（Cost-Based Optimization）
基于代价的优化器 		CBO 是一种比 RBO 更加合理、可靠的优化器，在 ORACLE 10g 中完全取代 RBO；

CBO 通过计算各种可能的执行计划的“代价”，即 COST，从中选用 COST 最低的执行方案作为实际运行方案；

它依赖数据库对象的统计信息，统计信息的准确与否会影响 CBO 做出最优的选择，也就是对数据“敏感”。
d) INDEX FAST FULL SCAN（索引快速扫描）

扫描索引中的所有的数据块，与 INDEX FULL SCAN 类似，但是一个显著的区别是它不对查询出的数据进行排序（即数据不是以排序顺序被返回）

e) INDEX SKIP SCAN（索引跳跃扫描）

Oracle 9i 后提供，有时候复合索引的前导列（索引包含的第一列）没有在查询语句中出现，Oracle 也会使用该复合索引，这时候就使用的 INDEX SKIP SCAN;

什么时候会触发 INDEX SKIP SCAN 呢？

前提条件：表有一个复合索引，且在查询时有除了前导列（索引中第一列）外的其他列作为条件，并且优化器模式为 CBO 时

当 Oracle 发现前导列的唯一值个数很少时，会将每个唯一值都作为常规扫描的入口，在此基础上做一次查找，最后合并这些查询；

例如：

假设表 emp 有 ename（雇员名称）、job（职位名）、sex（性别）三个字段，并且建立了如 create index idx_emp on emp (sex, ename, job) 的复合索引；

因为性别只有 '男' 和 '女' 两个值，所以为了提高索引的利用率，Oracle 可将这个复合索引拆成 ('男', ename, job)，('女', ename, job) 这两个复合索引；

当查询 select * from emp where job = 'Programmer' 时，该查询发出后：

Oracle 先进入 sex 为'男'的入口，这时候使用到了 ('男', ename, job) 这条复合索引，查找 job = 'Programmer' 的条目；

再进入 sex 为'女' 的入口，这时候使用到了 ('女', ename, job) 这条复合索引，查找 job = 'Programmer' 的条目；

最后合并查询到的来自两个入口的结果集。

2.上图中的 NESTED LOOPS … 描述的是表连接方式

JOIN 关键字用于将两张表作连接，一次只能连接两张表，JOIN 操作的各步骤一般是串行的（在读取做连接的两张表的数据时可以并行读取）；

表（row source）之间的连接顺序对于查询效率有很大的影响，对首先存取的表（驱动表）先应用某些限制条件（Where 过滤条件）以得到一个较小的 row source，可以使得连接效率提高。

-延伸阅读：驱动表（Driving Table）与匹配表（Probed Table）

————————————-延伸阅读：驱动表（Driving Table）与匹配表（Probed Table）————————————-

表名称 说明
驱动表（Driving Table） 表连接时首先存取的表，又称外层表（Outer Table），
这个概念用于 NESTED LOOPS（嵌套循环） 与 HASH JOIN（哈希连接）中；

如果驱动表返回较多的行数据，则对所有的后续操作有负面影响，
故一般选择小表（应用 Where 限制条件后返回较少行数的表）作为驱动表。
匹配表（Probed Table） 又称为内层表（Inner Table），
从驱动表获取一行具体数据后，会到该表中寻找符合连接条件的行。

故该表一般为大表（应用 Where 限制条件后返回较多行数的表）。

表连接的几种方式

表连接方式 说明
SORT MERGE JOIN 排序-合并连接
NESTED LOOPS 嵌套循环
HASH JOIN 哈希连接
CARTESIAN PRODUCT 笛卡尔积

参考图片（图片来自超锅）：

注：这里将首先存取的表称作 row source 1，将之后参与连接的表称作 row source 2；

（1）SORT MERGE JOIN（排序-合并连接）

假设有查询：select a.name, b.name from table_A a join table_B b on (a.id = b.id)

内部连接过程：

a) 生成 row source 1 需要的数据，按照连接操作关联列（如示例中的 a.id）对这些数据进行排序

b) 生成 row source 2 需要的数据，按照与 a) 中对应的连接操作关联列（b.id）对数据进行排序

c) 两边已排序的行放在一起执行合并操作（对两边的数据集进行扫描并判断是否连接）

延伸：

如果示例中的连接操作关联列 a.id，b.id 之前就已经被排过序了的话，连接速度便可大大提高，因为排序是很费时间和资源的操作，尤其对于有大量数据的表。

故可以考虑在 a.id，b.id 上建立索引让其能预先排好序。不过遗憾的是，由于返回的结果集中包括所有字段，所以通常的执行计划中，即使连接列存在索引，也不会进入到执行计划中，除非进行一些特定列处理（如仅仅只查询有索引的列等）。

排序-合并连接的表无驱动顺序，谁在前面都可以；

排序-合并连接适用的连接条件有： < , <= , = , > , >= ，不适用的连接条件有： <> , like

（2）NESTED LOOPS（嵌套循环）

内部连接过程：

a) 取出 row source 1 的 row 1（第一行数据），遍历 row source 2 的所有行并检查是否有匹配的，取出匹配的行放入结果集中

b) 取出 row source 1 的 row 2（第二行数据），遍历 row source 2 的所有行并检查是否有匹配的，取出匹配的行放入结果集中

c) ……

若 row source 1 （即驱动表）中返回了 N 行数据，则 row source 2 也相应的会被全表遍历 N 次。

因为 row source 1 的每一行都会去匹配 row source 2 的所有行，所以当 row source 1 返回的行数尽可能少并且能高效访问 row source 2（如建立适当的索引）时，效率较高。

延伸：

嵌套循环的表有驱动顺序，注意选择合适的驱动表。

嵌套循环连接有一个其他连接方式没有的好处是：可以先返回已经连接的行，而不必等所有的连接操作处理完才返回数据，这样可以实现快速响应。

应尽可能使用限制条件（Where 过滤条件）使驱动表（row source 1）返回的行数尽可能少，同时在匹配表（row source 2）的连接操作关联列上建立唯一索引（UNIQUE INDEX）或是选择性较好的非唯一索引，此时嵌套循环连接的执行效率会变得很高。若驱动表返回的行数较多，即使匹配表连接操作关联列上存在索引，连接效率也不会很高。

（3）HASH JOIN（哈希连接）

哈希连接只适用于等值连接（即连接条件为 = ）

HASH JOIN 对两个表做连接时并不一定是都进行全表扫描，其并不限制表访问方式；

内部连接过程简述：

a) 取出 row source 1（驱动表，在 HASH JOIN 中又称为 Build Table） 的数据集，然后将其构建成内存中的一个 Hash Table（Hash 函数的 Hash KEY 就是连接操作关联列），创建 Hash 位图（bitmap）

b) 取出 row source 2（匹配表）的数据集，对其中的每一条数据的连接操作关联列使用相同的Hash函数并找到对应的 a) 里的数据在 Hash Table 中的位置，在该位置上检查能否找到匹配的数据

延伸阅读：Hash Table 相关

————————延伸阅读：Hash Table 相关————————

来自Wiki的解释：

In computing, a hash table (hash map) is a data structure used to implement an associative array, a structure that can map keys to values. A hash table uses a hash function to compute an index into an array of buckets or slots, from which the desired value can be found.

散列（hash）技术：在记录的存储位置和记录具有的关键字key之间建立一个对应关系 f ，使得输入key后，可以得到对应的存储位置 f(key)，这个对应关系 f 就是散列（哈希）函数；

采用散列技术将记录存储在一块连续的存储空间中，这块连续的存储空间就是散列表（哈希表）；

不同的 key 经同一散列函数散列后得到的散列值理论上应该不同，但是实际中有可能相同，相同时即是发生了散列（哈希）冲突，解决散列冲突的办法有很多，比如 HashMap 中就是用链地址法来解决哈希冲突；

哈希表是一种面向查找的数据结构，在输入给定值后查找给定值对应的记录在表中的位置以获取特定记录这个过程的速度很快。

HASH JOIN 的三种模式
OPTIMAL HASH JOIN
ONEPASS HASH JOIN
MULTIPASS HASH JOIN
1) OPTIMAL HASH JOIN

OPTIMAL 模式是从驱动表（也称 Build Table）上获取的结果集比较小，可以把根据结果集构建的整个 Hash Table 都建立在用户可以使用的内存区域里。

连接过程简述：

Ⅰ：首先对 Build Table 内各行数据的连接操作关联列使用 Hash 函数，把 Build Table 的结果集构建成内存中的 Hash Table。如图所示，可以把 Hash Table 看作内存中的一块大的方形区域，里面有很多的小格子，Build Table 里的数据就分散分布在这些小格子中，而这些小格子就是 Hash Bucket（见上面 Wiki 的定义）。

Ⅱ：开始读取匹配表（Probed Table）的数据，对其中每行数据的连接操作关联列都使用同上的 Hash 函数，定位 Build Table 里使用 Hash 函数后具有相同值数据所在的 Hash Bucket。

Ⅲ：定位到具体的 Hash Bucket 后，先检查 Bucket 里是否有数据，没有的话就马上丢掉匹配表（Probed Table）的这一行。如果里面有数据，则继续检查里面的数据（驱动表的数据）是否和匹配表的数据相匹配。

2): ONEPASS HASH JOIN

从驱动表（也称 Build Table）上获取的结果集较大，无法将根据结果集构建的 Hash Table 全部放入内存中时，会使用 ONEPASS 模式。

连接过程简述：

Ⅰ：对 Build Table 内各行数据的连接操作关联列使用 Hash 函数，根据 Build Table 的结果集构建 Hash Table 后，由于内存无法放下所有的 Hash Table 内容，将导致有的 Hash Bucket 放在内存里，有的 Hash Bucket 放在磁盘上，无论放在内存里还是磁盘里，Oracle 都使用一个 Bitmap 结构来反映这些 Hash Bucket 的状态（包括其位置和是否有数据）。

Ⅱ：读取匹配表数据并对每行的连接操作关联列使用同上的 Hash 函数，定位 Bitmap上Build Table 里使用 Hash 函数后具有相同值数据所在的 Bucket。如果该 Bucket 为空，则丢弃匹配表的这条数据。如果不为空，则需要看该 Bucket 是在内存里还是在磁盘上。

如果在内存中，就直接访问这个 Bucket 并检查其中的数据是否匹配，有匹配的话就返回这条查询结果。

如果在磁盘上，就先把这条待匹配数据放到一边，将其先暂存在内存里，等以后积累了一定量的这样的待匹配数据后，再批量的把这些数据写入到磁盘上（上图中的 Dump probe partitions to disk）。

Ⅲ：当把匹配表完整的扫描了一遍后，可能已经返回了一部分匹配的数据了。接下来还有 Hash Table 中一部分在磁盘上的 Hash Bucket 数据以及匹配表中部分被写入到磁盘上的待匹配数据未处理，现在 Oracle 会把磁盘上的这两部分数据重新匹配一次，然后返回最终的查询结果。

3): MULTIPASS HASH JOIN

当内存特别小或者相对而言 Hash Table 的数据特别大时，会使用 MULTIPASS 模式。MULTIPASS 会多次读取磁盘数据，应尽量避免使用该模式。

3.上图中的 … OUTER 描述的是表连接类型；

表连接的两种类型：

表连接类型 说明
INNER JOIN 内连接
OUTER JOIN 外连接

示例数据说明：

现有 A、B 两表

A 表信息如下：

B 表信息如下：

下面的例子都用 A、B 两表来演示。

1）INNER JOIN（内连接）

只返回两表中相匹配的记录。

INNER JOIN 又分为两种：

INNER JOIN（内连接） 类型 说明
等值连接 连接条件为 =
非等值连接 连接条件为 非 = ，如 >, >=, <, <= 等

等值连接用的最多，下面以等值连接举例：

内连接的两种写法：

Ⅰ： select a.id A_ID, a.name A_NAME, b.id B_ID, b.name B_NAME from A a inner join B b on (a.id = b.id)

Ⅱ： select a.id A_ID, a.name A_NAME, b.id B_ID, b.name B_NAME from A a join B b on (a.id = b.id)

连接时只返回满足连接条件 (a.id = b.id)的记录：

2） OUTER JOIN（外连接）

OUTER JOIN 分为三种：

OUTER JOIN（外连接） 类型 说明
LEFT OUTER JOIN 可简写为 LEFT JOIN，左外连接
RIGHT OUTER JOIN RIGHT JOIN，右外连接
FULL OUTER JOIN FULL JOIN，全外连接
a) LEFT JOIN（左连接）

返回的结果不仅包含符合连接条件的记录，还包含左边表中的全部记录。（若返回的左表中某行记录在右表中没有匹配项，则右表中的返回列均为空值）

两种写法：

Ⅰ：select a.id A_ID, a.name A_NAME, b.id B_ID, b.name B_NAME from A a left outer join B b on (a.id = b.id)

Ⅱ：select a.id A_ID, a.name A_NAME, b.id B_ID, b.name B_NAME from A a left join B b on (a.id = b.id)

返回结果：

b) RIGHT JOIN（右连接）

返回的结果不仅包含符合连接条件的记录，还包含右边表中的全部记录。（若返回的右表中某行记录在左表中没有匹配项，则左表中的返回列均为空值）

两种写法：

Ⅰ：select a.id A_ID, a.name A_NAME, b.id B_ID, b.name B_NAME from A a right outer join B b on (a.id = b.id)

Ⅱ：select a.id A_ID, a.name A_NAME, b.id B_ID, b.name B_NAME from A a right join B b on (a.id = b.id)

返回结果：

c) FULL JOIN（全连接）

返回左右两表的全部记录。(左右两边不匹配的项都以空值代替)

两种写法：

Ⅰ：select a.id A_ID, a.name A_NAME, b.id B_ID, b.name B_NAME from A a full outer join B b on (a.id = b.id)

Ⅱ：select a.id A_ID, a.name A_NAME, b.id B_ID, b.name B_NAME from A a full join B b on (a.id = b.id)

返回结果：

延伸阅读： (+) 操作符

——————————-延伸阅读： (+) 操作符—————————-

(+) 操作符是 Oracle 特有的表示法，用来表示外连接（只能表示 左外、右外 连接），需要配合 Where 语句使用。

特别注意：(+) 操作符在左表的连接条件上表示右连接，在右表的连接条件上表示左连接。

如：

Ⅰ：select a.id A_ID, a.name A_NAME, b.id B_ID, b.name B_NAME from A a, B b where a.id = b.id(+)

查询结果：

实际与左连接 select a.id A_ID, a.name A_NAME, b.id B_ID, b.name B_NAME from A a left join B b on (a.id = b.id) 效果等价

Ⅱ：select a.id A_ID, a.name A_NAME, b.id B_ID, b.name B_NAME from A a, B b where a.id(+) = b.id

查询结果：

实际与右连接 select a.id A_ID, a.name A_NAME, b.id B_ID, b.name B_NAME from A a right join B b on (a.id = b.id) 效果等价