文章详情页_Oracle 表连接

表连接就是指多个表之间用连接条件连接在一起，使用表连接的目的就是从多个表获取存储在这些表中的不同维度的数据。

驱动表

驱动表，又称为外层表，就是在嵌套循环和哈希连接中，用来最先获得数据，并以此表的数据作为依据；

在 CBO 优化器模式下，Oracle 会根据表的统计信息，自动选择驱动表，也可以使用 Hint 手动指定驱动表：

/*+ leading(table_name) */

表连接的顺序

不管目标 SQL 中有多少个表做表连接，Oracle 在实际执行该 SQL 时都只能先两两做表连接，再依次执行这样的两两表连接过程，直到目标 SQL 中所有的表都已连接完毕。

两表关联
优化器需要决定这两个表中谁是驱动表，谁是被驱动表；
多表关联
优化器需要决定这些表中谁和谁先做表连接，然后决定这个表连接结果所在的结果集再和剩余表中哪一个继续做表连接，这个过程会一直持续下去，直到所有的表都已连接完为止。

表连接的类型

Oracle 数据库中的表连接分为内连接和外连接这两种类型，表连接的类型会直接决定表连接的结果。

内连接

内连接 Inner Join 是指表连接的连接结果只包含那些完全满足连接条件的记录。

tab1 inner join tab2 on (condition)

外连接

外连接 Outer Join 是指表连接的连接结果除了包含那些完全满足连接条件的记录之外还会包含驱动表中所有不满足该连接条件的记录。

左连接

tab1 left join tab2 on (condition)

位于关键字 left join 左边的 tab1 会作为该表连接的驱动表；此时的连接结果除了包含 tab1 和 tab2 中所有满足该连接条件的记录外，还会包含驱动表（即 tab1）中所有不满足该连接条件的记录，同时，驱动表中所有不满足该连接条件的记录所对应的被驱动表（即 tab2）中的查询列均会以 NULL 值来填充。

右连接

tab1 right join tab2 on (condition)

位于关键字 right join 右边的 tab2 会作为该表连接的驱动表；此时的连接结果除了包含 tab1 和 tab2 中所有满足该连接条件的记录外，还会包含驱动表（即 tab2）中所有不满足该连接条件的记录，同时，驱动表中所有不满足该连接条件的记录所对应的被驱动表（即 tab1）中的查询列均会以 NULL 值来填充。

全连接 Full Join

tab1 full join tab2 on (condition)

此时的连接结果除了包含 tab1 和 tab2 中所有满足该连接条件的记录外，还会包含 tab1 和 tab2 中所有不满足该连接条件的记录，同时 tab1 和 tab2 中所有不满足该连接条件的记录所对应的另一个表中的查询列均会以 NULL 值来填充。

SQL> create table t1 as select * from dept where deptno in (10, 20);

Table created


SQL> create table t2 as select * from dept where deptno in (10, 30);

Table created


SQL> select t1.*, t2.* from t1 inner join t2 on t1.deptno = t2.deptno;

DEPTNO DNAME          LOC           DEPTNO DNAME          LOC
------ -------------- ------------- ------ -------------- -------------
    10 ACCOUNTING     NEW YORK          10 ACCOUNTING     NEW YORK


SQL> select t1.*, t2.* from t1 left join t2 on t1.deptno = t2.deptno;

DEPTNO DNAME          LOC           DEPTNO DNAME          LOC
------ -------------- ------------- ------ -------------- -------------
    10 ACCOUNTING     NEW YORK          10 ACCOUNTING     NEW YORK
    20 RESEARCH       DALLAS     


SQL> select t1.*, t2.* from t1 right join t2 on t1.deptno = t2.deptno;

DEPTNO DNAME          LOC           DEPTNO DNAME          LOC
------ -------------- ------------- ------ -------------- -------------
    10 ACCOUNTING     NEW YORK          10 ACCOUNTING     NEW YORK
                                        30 SALES          CHICAGO   


SQL> select t1.*, t2.* from t1 full join t2 on t1.deptno = t2.deptno order by t1.deptno;

DEPTNO DNAME          LOC           DEPTNO DNAME          LOC
------ -------------- ------------- ------ -------------- -------------
    10 ACCOUNTING     NEW YORK          10 ACCOUNTING     NEW YORK
    20 RESEARCH       DALLAS                              
                                        30 SALES          CHICAGO

表连接的方法

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

序号	表连接方法	描述
1	Sort Merge Join	排序合并连接
2	Nested Loops Join	嵌套循环连接
3	Hash Join	哈希连接
4	Cross Join	笛卡尔连接
5	Star Join	星型连接

排序合并连接

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

oracle-sort-merge-join

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

首先以目标 SQL 中指定的谓词条件（如果有的话）去访问表 T1，然后对访问结果按照表 T1 中的连接列来排序，排好序后的结果集我们记为结果集 S1；
接着以目标 SQL 中指定的谓词条件（如果有的话）去访问表 T2，然后对访问结果按照表 T2 中的连接列来排序，排好序的结果集我们记为结果集 S2；
最后对结果集 S1 和结果集 S2 执行合并操作，从中取出匹配记录来作为排序合并连接的最终执行结果。

合并操作的执行步骤：

首先遍历结果集 S1，即先取出结果集 S1 中的第一条记录；
然后去结果集 S2 中按照连接条件判断是否存在匹配记录；
然后再取出结果集 S1 中的第二条记录；
按照同样的连接条件再去结果集 S2 中判断是否存在匹配的记录，直到最后遍历完结果集 S1 中所有的记录。

排序合并的优缺点及适用场景：

通常情况下，排序合并连接的执行效率会远不如哈希连接，但前者的使用范围更广，因为哈希连接通常只能用于等值连接条件，而排序合并连接还能用于其他连接条件（如： < 、> 、<= 、>=），但不支持（like 、<>）连接条件；
通常情况下，排序合并连接并不适合 OLTP 类型的系统，其本质原因是因为对于 OLTP 类型的系统而言，排序是非常昂贵的操作，当然，如果能避免排序，那么即使是 OLTP 类型的系统，也还是可以使用排序合并连接的；
从严格意义上说，排序合并连接并不存在驱动表的概念。

嵌套循环连接

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

oracle-nested-loops-join

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

首先，优化器会按照一定的规则来决定表 T1 和表 T2 中谁是驱动表，谁是被驱动表，驱动表用于外层循环，被驱动表用于内层循环，这里假设 T1 表是驱动表，T2 表是被驱动表；
接着以目标 SQL 中指定的谓词条件（如果有的话）去访问驱动表 T1，访问驱动表 T1 后得到的结果集我们记为驱动结果集 S1；
然后遍历驱动结果集 S1 并同时遍历被驱动表 T2，即先取出驱动结果集 S1 中的第一条记录，接着遍历被驱动表 T2 并按照连接条件去判断 T2 中是否存在匹配的记录，然后再取出驱动结果集 S1 中的第二条记录，按照同样的连接条件再去遍历被驱动表 T2 并判断 T2 中是否还存在匹配的记录，直到遍历完驱动结果集 S1 中的所有记录为止。

嵌套循环连接的特点：

外层循环（驱动表）所对应的结果集有多少条记录，遍历被驱动表 T2 的内层循环就要做多少次。

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

如果驱动表所对应的驱动结果集的记录数较少，同时被驱动表的连接列上又存在唯一性索引，那么此时使用嵌套循环连接的执行效率就会非常高；但如果驱动表所对应的驱动结果集的记录数很多，即便在被驱动表的连接列上存在索引，此时使用嵌套循环连接的执行效率也不会高；
大表也可以作为嵌套循环连接的驱动表，关键看目标 SQL 中指定的谓词条件（如果有的话）能否将驱动结果集的数据量降下来；
嵌套循环连接可以实现快速响应，即它可以第一时间先返回已经连接过且满足连接条件的记录，而不必等待所有的连接操作全部做完后才返回连接结果。

哈希连接

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

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

a. 首先 Oracle 会根据参数 HASH_AREA_SIZE 、DB_BLOCK_SIZE 和 _HASH_MULTIBLOCK_IO_COUNT 的值来决定 Hash Partition 的数量（Hash Partition 是一个逻辑上的概念，所有 Hash Partition 的集合就被称之为 Hash Table，即一个 Hash Table 是由多个 Hash Partition 所组成，而一个 Hash Partition 又是由多个 Hash Bucket 所组成）；
b. 表 T1 和表 T2 在施加了目标 SQL 中指定的谓词条件（如果有的话）后得到的结果集中数据量较小的那个结果集会被 Oracle 选为哈希连接的驱动结果集，这里我们假设表 T1 所对应的结果集的数据量相对较小，我们记为 S；T2 所对应的结果集的数据量相对较大，我们记为 B；显然这里 S 是驱动结果集，B 是被驱动结果集；
c. 接着 Oracle 会遍历驱动结果集S，读取 S 中的每一条记录，并对 S 中的每一条记录按照该记录在表 T1 中的连接列做哈希运算，这个哈希运算会使用两个内置哈希函数，这两个哈希函数会同时对该连接列计算哈希值，我们把这两个内置哈希函数分别记为 hash_func_1 和 hash_func_2，它们所计算出来的哈希值分别记为 hash_value_1 和 hash_value_2；
d. 然后 Oracle 会按照 hash_value_1 的值把相应的驱动结果集S 中的对应记录存储在不同 Hash Partition 的不同 Hash Bucket 里，同时和该记录存储在一起的还有该记录用 hash_func_2 计算出来的 hash_value_2 的值。我们把驱动结果集S所对应的每一个 Hash Partition 记为 Si；
e. 在构建 Si 的同时，Oracle 会构建一个位图（BITMAP），这个位图用来标记 Si 所包含的每一个 Hash Bucket 是否有记录（即记录数是否大于0）；
f. 如果驱动结果集S 的数据量很大，那么在构建驱动结果集S 所对应的 Hash Table 时，就可能会出现 PGA 的工作区（WORK AREA）被填满的情况，这时候Oracle会把工作区中现有的 Hash Partition 中包含记录数最多的 Hash Partition 写到磁盘上（TEMP表空间）；接着 Oracle 会继续构建驱动结果集S 所对应的 Hash Table，在继续构建的过程中，如果工作区又满了，则 Oracle 会继续重复上述挑选包含记录数最多的 Hash Partition 并写回到磁盘上的动作；
g. 上述构建驱动结果集S 所对应的 Hash Table 的过程会一直持续下去，直到遍历完驱动结果集S 中的所有记录为止；
h. 接着，Oracle 会对所有的 Si 按照它们所包含的记录数来排序，然后 Oracle 会把这些已经排好序的 Hash Partition 按顺序依次、并且尽可能的全部放到内存中（PGA 的工作区），当然，如果实在放不下的话，放不下的那部分 Hash Partition 还是会位于磁盘上。如果所有的 Si 本来就都在内存中，也没发生过将 Si 写回到磁盘的操作，那这里根本就不需要排序了；
i. 至此 Oracle 已经处理完驱动结果集S，现在可以来开始处理被驱动结果集B 了；
j. Oracle 会遍历被驱动结果集B，读取 B 中的每一条记录，并对 B 中的每一条记录按照该记录在表 T2 中的连接列做哈希运算，这个哈希运算和步骤c 中的哈希运算是一模一样的，即这个哈希运算还是会用步骤c 中的 hash_func_1 和 hash_func_2，并且也会计算出两个哈希值 hash_value_1 和 hash_value_2；接着 Oracle 会按照该记录所对应的哈希值 hash_value_1 去 Si 里找匹配的 Hash Bucket；如果能找到匹配的 Hash Bucket，则 Oracle 还会遍历该 Hash Bucket 中的每一条记录，并会校验存储于该 Hash Bucket 中的每一条记录的连接列，看是否是真的匹配（即这里要校验驱动结果集S 和被驱动结果集B 中的匹配记录所对应的连接列是否真的相等，因为对于 Hash 运算而言，不同的值经过哈希运算后的结果可能是一样的），如果是真的匹配，则上述 hash_value_1 所对应 B 中的记录的位于目标 SQL 中的查询列和该 Hash Bucket 中的匹配记录便会组合起来，一起作为满足目标 SQL 连接条件的记录返回；如果找不到匹配的 Hash Bucket，则 Oracle 就会去访问步骤e 中构建的位图，如果位图显示该 Hash Bucket 在 Si 中对应的记录数大于0，则说明该 Hash Bucket 虽然不在内存中，但它已经被写回到了磁盘上，则此时 Oracle 就会按照上述 hash_value_1 的值把相应被驱动结果集B 中的对应记录也以 Hash Partition 的方式写回到磁盘上，同时和该记录存储在一起的还有该记录用 hash_func_2 计算出来的 hash_value_2 的值；如果位图显示该 Hash Bucket 在 Si 中对应的记录数等于0，则 Oracle 就不用把上述 hash_value_1 所对应被驱动结果集B 中的记录写回到磁盘上了，因为这条记录必然不满足目标 SQL 的连接条件。这个根据位图来决定是否将上述 hash_value_1 所对应被驱动结果集B 中的记录写回到磁盘的动作就是所谓的"位图过滤"；我们把被驱动结果集B 所对应的每一个 Hash Partition 记为 Bj；
k. 上述去 Si 中查找匹配 Hash Bucket 和构建 Bj 的过程会一直持续下去，直到遍历完被驱动结果集B 中的所有记录为止；
l. 至此 Oracle 已经处理完所有位于内存中的 Si 和对应的 Bj，现在只剩下位于磁盘上的 Si 和 Bj 还未处理；
m. 因为在构建 Si 和 Bj 时用的是同样的哈希函数 hash_func_1 和 hash_func_2，所以 Oracle 在处理位于磁盘上的 Si 和 Bj 的时候可以放心的配对处理，即只有对应 Hash Partition Number 值相同的 Si 和 Bj 才可能会产生满足连接条件的记录。这里我们用 Sn 和 Bn 来表示位于磁盘上且对应 Hash Partition Number 值相同的 Si 和 Bj；
n. 对于每一对儿 Sn 和 Bn，它们之中记录数较少的会被当作驱动结果集，然后 Oracle 会用这个驱动结果集的 Hash Bucket 里记录的 hash_value_2 来构建新的 Hash Table，另外一个记录数较大的会被当作被驱动结果集，然后 Oracle 会用这个被驱动结果集的 Hash Bucket 里记录的 hash_value_2 去上述构建的新 Hash Table 中找匹配记录。注意，对每一对儿 Sn 和 Bn 而言，Oracle 始终会选择它们中记录数较少的来作为驱动结果集，所以每一对儿 Sn 和 Bn 的驱动结果集都可能会发生变化，这就是所谓的"动态角色互换"；
o. 步骤n 中如果存在匹配记录，则该匹配记录也会作为满足目标 SQL 连接条件的记录返回；
p. 上述处理 Sn 和 Bn 的过程会一直持续下去，直到遍历完所有的 Sn 和 Bn 为止。

哈希连接的优缺点及适用场景：

哈希连接不一定会排序，或者说大部分情况下都不需要排序；
哈希连接的驱动表所对应的连接列的可选择性应尽可能好，因为这个可选择性会影响对应 Hash Bucket 中的记录数，而 Hash Bucket 中的记录数又会直接影响从该 Hash Bucket 中查找匹配记录的效率；
哈希连接只适用于 CBO，它也只能用于等值连接条件
哈希连接很适合小表和大表之间做表连接且连接结果集的记录数较多的情形，特别是在小表的连接列的可选择性非常好的情况下；
当两个表做哈希连接时，如果在施加了目标 SQL 中指定的谓词条件（如果有的话）后得到的数据量较小的那个结果集所对应的 Hash Table 能够完全被容纳在内存中（PGA 的工作区），则此时的哈希连接的执行效率会非常高。

其它连接

笛卡尔连接

笛卡尔连接 Cross Join 又称为笛卡尔乘积 Cartesian Product 它是一种两个表在做表连接时没有任何连接条件的表连接方法。

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

首先以目标 SQL 中指定的谓词条件（如果有的话）访问表 T1，此时得到的结果集我们记为结果集 S1，这里假设结果集 S1 的记录数为 m；
接着以目标 SQL 中指定的谓词条件（如果有的话）访问表 T2，此时得到的结果集我们记为结果集 S2，这里假设结果集 S2 的记录数为 n；
最后对结果集 S1 和结果集 S2 执行合并操作，从中取出匹配记录来作为笛卡尔连接的最终执行结果。因为没有表连接条件，所以在对结果集 S1 和结果集 S2 执行合并操作时，对于结果集 S1 中的任意一条记录，结果集 S2 中的所有记录都满足条件，即它们都会是匹配记录，所以上述笛卡尔连接的连接结果的记录数就是 m 和 n 的乘积。

SQL> select t1.col1, t2.col3 from t1 cross join t2;

执行计划
----------------------------------------------------------
Plan hash value: 1323614827

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     9 |    54 |     8   (0)| 00:00:01 |
|   1 |  MERGE JOIN CARTESIAN|      |     9 |    54 |     8   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL  | T2   |     3 |     9 |     3   (0)| 00:00:01 |
|   3 |   BUFFER SORT        |      |     3 |     9 |     5   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL | T1   |     3 |     9 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------

星型连接

星型连接 Star Join 通常用于数据仓库类型的应用，它是一种单个事实表（Fact Table）和多个维度表（Dimension Table）之间的连接。

星型连接的各维度表之间没有直接的关联条件，其事实表和各维度表之间是基于事实表的外键列和对应维度表的主键列之间的连接，并且通常在事实表的外键列上还会存在对应的位图索引。

半连接（SEMI）

半连接是一种特殊的连接类型，当两张表进行关联，只返回匹配上的数据并且只会返回一张的表的数据

当做子查询展开时，Oracle 经常会把那些外部 WHERE 条件为 EXISTS 、IN 的子查询转换为对应的半连接。

SQL> select * from t1 where col2 in (select col2 from t2);

执行计划
----------------------------------------------------------
Plan hash value: 1713220790

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     3 |    21 |     6   (0)| 00:00:01 |
|*  1 |  HASH JOIN SEMI    |      |     3 |    21 |     6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T1   |     3 |    15 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   |     3 |     6 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

半连接等价改写

半连接会做去重操作。

控制半连接执行计划

反连接（ANTI）

反连接是一种特殊的连接类型。

当做子查询展开时，Oracle 经常会把那些外部 WHERE 条件为 NOT EXISTS 、NOT IN 的子查询转换为对应的反连接。

SQL> select * from t1 where col2 not in (
  select col2 from t2
);

执行计划
----------------------------------------------------------
Plan hash value: 1275484728

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     7 |     6   (0)| 00:00:01 |
|*  1 |  HASH JOIN ANTI NA |      |     1 |     7 |     6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T1   |     3 |    15 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   |     3 |     6 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

反连接等价改写

控制反连接执行计划

FILTER

表连接相关 Hint

表访问相关 Hint

FULL

FULL 是针对单个目标表的 Hint，它的含义是让优化器对目标表执行全表扫描。

语法：

/*+ full(目标表) */

示例：

SQL> select /*+ full(e) */ e.empno, e.ename, e.sal, e.job from emp e where empno = 7369;

执行计划
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    22 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    22 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

ROWID

ROWID 是针对单个表目标表的 Hint，它的含义是让优化器对目标表执行 ROWID 扫描，只有目标 SQL 中使用了含 ROWID 的 WHERE 条件时 ROWID Hint 才有意义。

语法：

/*+ rowid(目标表) */

示例：

SQL> select rowid from emp where empno = 7369;

SQL> select /*+ rowid(e) */ e.empno, e.ename, e.sal, e.job from emp e where rowid = 'AAAVNtAAEAAAACXAAA'; 

执行计划
----------------------------------------------------------
Plan hash value: 1116584662

-----------------------------------------------------------------------------------
| Id  | Operation                  | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |      |     1 |    34 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY USER ROWID| EMP  |     1 |    34 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

表连接顺序相关 Hint

ORDERED

ORDERED 是针对多个目标表的 Hint，它的含义是让优化器对多个目标表执行表连接操作时，按照它们在目标 SQL 的 FROM 子句中出现的顺序从左至右依次进行连接。

语法：

/*+ ordered */

示例：

SQL> create table jobs as select empno, job from emp;

SQL> select /*+ ordered */ e.ename, j.job, e.sal, d.deptno 
from emp e, dept d, jobs j 
where e.empno = j.empno and e.deptno = d.deptno 
and d.loc = 'CHICAGO' 
order by e.ename;

执行计划
----------------------------------------------------------
Plan hash value: 3938020864

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     5 |   235 |    10  (10)| 00:00:01 |
|   1 |  SORT ORDER BY       |      |     5 |   235 |    10  (10)| 00:00:01 |
|*  2 |   HASH JOIN          |      |     5 |   235 |     9   (0)| 00:00:01 |
|*  3 |    HASH JOIN         |      |     5 |   140 |     6   (0)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| EMP  |    14 |   238 |     3   (0)| 00:00:01 |
|*  5 |     TABLE ACCESS FULL| DEPT |     1 |    11 |     3   (0)| 00:00:01 |
|   6 |    TABLE ACCESS FULL | JOBS |    14 |   266 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------

从执行计划中可以看出，该 SQL 的表连接顺序确实是 EMP -> DEPT -> JOBS，这说明 ORDERED Hint 生效了。

LEADING

LEADING 是针对多个目标表的 Hint，它的含义是让优化器将我们指定的多个表的连接结果作为目标 SQL 表连接过程中的驱动结果集，并且将 LEADING Hint 中从左至右出现的第一个目标表作为整个表连接过程中的首个驱动表。

语法：

/*+ leading(目标表1 目标表2 目标表3 ··· ) */

示例：

SQL> create table emp_temp as select * from emp;

SQL> select /*+ leading(t e) */ e.ename, j.job, e.sal, d.deptno 
from emp e, jobs j, dept d, emp_temp t 
where e.empno = j.empno and e.deptno = d.deptno and e.ename = t.ename and d.loc = 'CHICAGO' 
order by e.ename;

执行计划
----------------------------------------------------------
Plan hash value: 937897748

----------------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |     5 |   270 |    13   (8)| 00:00:01 |
|   1 |  SORT ORDER BY        |          |     5 |   270 |    13   (8)| 00:00:01 |
|*  2 |   HASH JOIN           |          |     5 |   270 |    12   (0)| 00:00:01 |
|*  3 |    HASH JOIN          |          |     5 |   175 |     9   (0)| 00:00:01 |
|*  4 |     HASH JOIN         |          |    14 |   336 |     6   (0)| 00:00:01 |
|   5 |      TABLE ACCESS FULL| EMP_TEMP |    14 |    98 |     3   (0)| 00:00:01 |
|   6 |      TABLE ACCESS FULL| EMP      |    14 |   238 |     3   (0)| 00:00:01 |
|*  7 |     TABLE ACCESS FULL | DEPT     |     1 |    11 |     3   (0)| 00:00:01 |
|   8 |    TABLE ACCESS FULL  | JOBS     |    14 |   266 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------

从执行计划中可以看出，表的连接顺序是 EMP_TEMP -> EMP -> DEPT -> JOBS，且此时 EMP_TEMP 表是首个驱动表，然后将 EMP_TEMP 和 EMP 表的连接结果作为驱动结果集再和 DEPT、JOBS 表做后续的表连接。

表连接方法相关 Hint

序号	表连接方法	说明
1	USE_MERGE	排序合并连接
2	NO_USE_MERGE	不做排序合并连接
3	USE_NL	嵌套循环连接
4	NO_USE_NL	不做嵌套循环连接
5	USE_HASH	哈希连接
6	NO_USE_HASH	不做哈希连接

USE_MERGE

USE_MERGE 是针对多个目标表的 Hint，它的含义是让优化器将我们指定的多个表作为被驱动表与其它表或结果集做排序合并连接。

语法：

/*+ use_merge(目标表1 目标表2 目标表3 ··· 目标表n) */

示例1：

SQL> select /*+ use_merge(e) */ * from emp e, dept d 
where e.deptno = d.deptno;

执行计划
----------------------------------------------------------
Plan hash value: 844388907

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |    14 |   812 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN                  |         |    14 |   812 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    80 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |         |    14 |   532 |     4  (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL         | EMP     |    14 |   532 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

示例2：

SQL> select /*+ leading(e) use_merge(j d t) */ e.ename, j.job, e.sal, d.deptno 
from emp e, jobs j, dept d, emp_temp t 
where e.empno = j.empno and e.deptno = d.deptno and e.ename = t.ename and d.loc = 'CHICAGO' 
order by e.ename;

执行计划
----------------------------------------------------------
Plan hash value: 979133658

---------------------------------------------------------------------------------------------
| Id  | Operation                        | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |          |     5 |   270 |    16  (32)| 00:00:01 |
|   1 |  MERGE JOIN                      |          |     5 |   270 |    16  (32)| 00:00:01 |
|   2 |   SORT JOIN                      |          |     5 |   235 |    12  (34)| 00:00:01 |
|   3 |    MERGE JOIN                    |          |     5 |   235 |    11  (28)| 00:00:01 |
|   4 |     SORT JOIN                    |          |    14 |   504 |     7  (29)| 00:00:01 |
|   5 |      MERGE JOIN                  |          |    14 |   504 |     6  (17)| 00:00:01 |
|   6 |       TABLE ACCESS BY INDEX ROWID| EMP      |    14 |   238 |     2   (0)| 00:00:01 |
|   7 |        INDEX FULL SCAN           | PK_EMP   |    14 |       |     1   (0)| 00:00:01 |
|*  8 |       SORT JOIN                  |          |    14 |   266 |     4  (25)| 00:00:01 |
|   9 |        TABLE ACCESS FULL         | JOBS     |    14 |   266 |     3   (0)| 00:00:01 |
|* 10 |     SORT JOIN                    |          |     1 |    11 |     4  (25)| 00:00:01 |
|* 11 |      TABLE ACCESS FULL           | DEPT     |     1 |    11 |     3   (0)| 00:00:01 |
|* 12 |   SORT JOIN                      |          |    14 |    98 |     4  (25)| 00:00:01 |
|  13 |    TABLE ACCESS FULL             | EMP_TEMP |    14 |    98 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

NO_USE_MERGE

NO_USE_MERGE 是针对多个目标表的 Hint，它是 USE_MERGE 的反义 Hint，其含义是不让优化器将我们指定的多个表作为被驱动表与其它表或结果集做排序合并连接。

语法：

/*+ no_use_merge(目标表1 目标表2 目标表3 ··) */

示例：

SQL> select /*+ no_use_merge(e) */ * from emp e, dept d 
where e.deptno = d.deptno;

执行计划
----------------------------------------------------------
Plan hash value: 615168685

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    14 |   812 |     6   (0)| 00:00:01 |
|*  1 |  HASH JOIN         |      |    14 |   812 |     6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPT |     4 |    80 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMP  |    14 |   532 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

从执行计划中可以看出，该执行计划走的是哈希连接，说明该 NO_USE_MERGE Hint 生效了。

USE_NL

USE_NL 是针对多个目标表的 Hint，它的含义是让优化器将我们指定的多个表作为被驱动表与其它表或结果集做嵌套循环连接。

语法：

/*+ use_nl(目标表1 目标表2 目标表3 ··· 目标表n) */

示例：

SQL> select /*+ leading(e) use_nl(t d) */ e.ename, e.sal, d.deptno 
from emp e, dept d, emp_temp t 
where e.deptno = d.deptno and e.ename = t.ename and d.loc = 'CHICAGO' 
order by e.ename;

执行计划
----------------------------------------------------------
Plan hash value: 2931147376

-------------------------------------------------------------------------------------------
| Id  | Operation                      | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |          |     5 |   155 |    25   (4)| 00:00:01 |
|   1 |  SORT ORDER BY                 |          |     5 |   155 |    25   (4)| 00:00:01 |
|   2 |   NESTED LOOPS                 |          |     5 |   155 |    24   (0)| 00:00:01 |
|   3 |    NESTED LOOPS                |          |     5 |   120 |    17   (0)| 00:00:01 |
|   4 |     TABLE ACCESS FULL          | EMP      |    14 |   182 |     3   (0)| 00:00:01 |
|*  5 |     TABLE ACCESS BY INDEX ROWID| DEPT     |     1 |    11 |     1   (0)| 00:00:01 |
|*  6 |      INDEX UNIQUE SCAN         | PK_DEPT  |     1 |       |     0   (0)| 00:00:01 |
|*  7 |    TABLE ACCESS FULL           | EMP_TEMP |     1 |     7 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

NO_USE_NL

NO_USE_NL 是针对多个目标表的 Hint，它是 USE_NL 的反义 Hint，其含义是不让优化器将我们指定的多个表作为被驱动表与其他表或结果集做嵌套循环连接。

USE_HASH

USE_HASH 是针对多个目标表的 Hint，它的含义是让优化器将我们指定的多个表作为被驱动表与其他表或结果集做哈希连接。

语法：

/*+ use_hash(目标表1 目标表2 目标表3 ··· 目标表n) */

示例1：

SQL> select /*+ use_hash(e) */ * from emp e, dept d 
where e.deptno = d.deptno;

执行计划
----------------------------------------------------------
Plan hash value: 615168685

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    14 |   812 |     6   (0)| 00:00:01 |
|*  1 |  HASH JOIN         |      |    14 |   812 |     6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPT |     4 |    80 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMP  |    14 |   532 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

示例2：

SQL> select /*+ leading(e) use_hash(t d) */ e.ename, e.sal, d.deptno 
from emp e, dept d, emp_temp t 
where e.deptno = d.deptno and e.ename = t.ename and d.loc = 'CHICAGO' 
order by e.ename;

执行计划
----------------------------------------------------------
Plan hash value: 3069814489

---------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |     5 |   150 |    10  (10)| 00:00:01 |
|   1 |  SORT ORDER BY       |          |     5 |   150 |    10  (10)| 00:00:01 |
|*  2 |   HASH JOIN          |          |     5 |   150 |     9   (0)| 00:00:01 |
|*  3 |    HASH JOIN         |          |     5 |   120 |     6   (0)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| EMP      |    14 |   182 |     3   (0)| 00:00:01 |
|*  5 |     TABLE ACCESS FULL| DEPT     |     1 |    11 |     3   (0)| 00:00:01 |
|   6 |    TABLE ACCESS FULL | EMP_TEMP |    14 |    84 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------

NO_USE_HASH

NO_USE_HASH 是针对多个目标表的 Hint，它是 USE_HASH 的反义 Hint，其含义是不让优化器将我们指定的多个表作为被驱动表与其他表或结果集做哈希连接。

语法：

/*+ no_use_hash(目标表1 目标表2 目标表3 ··· 目标表n) */

示例：

SQL> select /*+ no_use_hash(e) */ * from emp e, dept d 
where e.deptno = d.deptno;

执行计划
----------------------------------------------------------
Plan hash value: 844388907

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |    14 |   812 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN                  |         |    14 |   812 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    80 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |         |    14 |   532 |     4  (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL         | EMP     |    14 |   532 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

注意事项及解决方案：
在 USE_NL Hint 或 USE_HASH Hint 中指定的目标表应该是嵌套循环连接或哈希连接中的被驱动表，如果指定的表并不能作为嵌套循环连接或哈希连接的被驱动表，则此时 Oracle 要么忽略该 USE_NL Hint 或 USE_HASH Hint，要么会忽略该表。
对于上述中存在的情况，可以使用 LEADING Hint（指明驱动表）配合 USE_NL Hint 或 USE_HASH Hint 一起使用，以便让优化器走出我们想要的执行计划。

表连接特性总结

表连接特性

数据准备：

SQL> drop table s1 cascade constraints purge;
SQL> drop table s2 cascade constraints purge;

SQL> create table s1(id number not null, n number, contents varchar2(4000));
SQL> create table s2(id number not null, s1_id number not null, n number, contents varchar2(4000));

SQL> execute dbms_random.seed(0);

SQL> insert into s1 select rownum, rownum, dbms_random.string('a', 50) from dual connect by level <= 100 order by dbms_random.random;
SQL> insert into s2 select rownum, rownum, rownum,  dbms_random.string('b', 50) from dual connect by level <= 100000 order by dbms_random.random;

SQL> commit;

表的访问次数

通过 statistics_level 方法获取执行计划，从执行计划中看 Starts 列对应的取值。

Nested Loops Join

# 驱动表访问1次，被驱动表访问N次

SQL> set linesize 1000;
SQL> set pagesize 1000;

SQL> alter session set statistics_level = all;

SQL> select /*+ leading(s1) use_nl(s2) */ * from s1 inner join s2 on s1.id = s2.s1_id;

SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));
Plan hash value: 3728883945

-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |    100 |00:00:00.56 |     100K|
|   1 |  NESTED LOOPS      |      |      1 |    100 |    100 |00:00:00.56 |     100K|
|   2 |   TABLE ACCESS FULL| S1   |      1 |    100 |    100 |00:00:00.01 |      14 |
|*  3 |   TABLE ACCESS FULL| S2   |    100 |      1 |    100 |00:00:00.55 |     100K|
-------------------------------------------------------------------------------------

从执行计划可以看出，驱动表 s1 只访问了1次，但被驱动表 s2 访问了100次（因为驱动表返回的结果集记录数为100，所以被驱动表访问了100次）

# 驱动表访问1次，被驱动表访问0次

SQL> alter session set statistics_level = all;

SQL> select /*+ leading(s1) use_nl(s2) */ * from s1 inner join s2 on s1.id = s2.s1_id where s1.n = 999;

SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));
Plan hash value: 3728883945

-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      0 |00:00:00.01 |       7 |
|   1 |  NESTED LOOPS      |      |      1 |      1 |      0 |00:00:00.01 |       7 |
|*  2 |   TABLE ACCESS FULL| S1   |      1 |      1 |      0 |00:00:00.01 |       7 |
|*  3 |   TABLE ACCESS FULL| S2   |      0 |      1 |      0 |00:00:00.01 |       0 |
-------------------------------------------------------------------------------------

从执行计划可以看出，驱动表 s1 只访问了1次，但被驱动表 s2 访问了0次（因为驱动表返回的结果集记录数为0，所以被驱动表访问了0次）
结论：
Nested Loops Join 中，驱动表被访问0次或1次，被驱动表被访问0次或N次，N由驱动表返回的结果集的记录数决定

Hash Join

# 驱动表访问1次，被驱动表访问1次

SQL> alter session set statistics_level = all;

SQL> select /*+ leading(s1) use_hash(s2) */ * from s1 inner join s2 on s1.id = s2.s1_id;

SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));
Plan hash value: 3598693092

----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |    100 |00:00:01.10 |    1017 |       |       |          |
|*  1 |  HASH JOIN         |      |      1 |    100 |    100 |00:00:01.10 |    1017 |   960K|   960K| 1250K (0)|
|   2 |   TABLE ACCESS FULL| S1   |      1 |    100 |    100 |00:00:00.01 |       6 |       |       |          |
|   3 |   TABLE ACCESS FULL| S2   |      1 |    100K|    100K|00:00:00.28 |    1011 |       |       |          |
----------------------------------------------------------------------------------------------------------------

从执行计划可以看出，驱动表 s1 只访问了1次，被驱动表 s2也只访问了1次

# 驱动表访问1次，被驱动表访问0次

SQL> alter session set statistics_level = all;

SQL> select /*+ leading(s1) use_hash(s2) */ * from s1 inner join s2 on s1.id = s2.s1_id where s1.n = 999;

SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));
Plan hash value: 3598693092

-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      0 |00:00:00.01 |       7 |
|*  1 |  HASH JOIN         |      |      1 |      1 |      0 |00:00:00.01 |       7 |
|*  2 |   TABLE ACCESS FULL| S1   |      1 |      1 |      0 |00:00:00.01 |       7 |
|   3 |   TABLE ACCESS FULL| S2   |      0 |    103K|      0 |00:00:00.01 |       0 |
-------------------------------------------------------------------------------------

从执行计划可以看出，驱动表 s1 只访问了1次，被驱动表 s2 访问了0次
结论：
Hash Join 中，驱动表被访问0次或1次，被驱动表也是被访问0次或1次

Merge Sort Join

# 驱动表访问1次，被驱动表访问1次

SQL> alter session set statistics_level = all;

SQL> select /*+ leading(s1) use_merge(s2) */ * from s1 inner join s2 on s1.id = s2.s1_id;

SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));
Plan hash value: 655147337

--------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |    100 |00:00:00.58 |    1012 |
|   1 |  MERGE JOIN         |      |      1 |    100 |    100 |00:00:00.58 |    1012 |
|   2 |   SORT JOIN         |      |      1 |    100 |    100 |00:00:00.01 |       7 |
|   3 |    TABLE ACCESS FULL| S1   |      1 |    100 |    100 |00:00:00.01 |       7 |
|*  4 |   SORT JOIN         |      |    100 |    103K|    100 |00:00:00.58 |    1005 |
|   5 |    TABLE ACCESS FULL| S2   |      1 |    103K|    100K|00:00:00.28 |    1005 |
--------------------------------------------------------------------------------------

从执行计划可以看出，驱动表 s1 只访问了1次，被驱动表 s2也只访问了1次

# 驱动表访问1次，被驱动表访问0次

SQL> alter session set statistics_level = all;

SQL> select /*+ leading(s1) use_merge(s2) */ * from s1 inner join s2 on s1.id = s2.s1_id where s1.n = 999;

SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));
Plan hash value: 655147337

--------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |      0 |00:00:00.01 |       7 |
|   1 |  MERGE JOIN         |      |      1 |      1 |      0 |00:00:00.01 |       7 |
|   2 |   SORT JOIN         |      |      1 |      1 |      0 |00:00:00.01 |       7 |
|*  3 |    TABLE ACCESS FULL| S1   |      1 |      1 |      0 |00:00:00.01 |       7 |
|*  4 |   SORT JOIN         |      |      0 |    103K|      0 |00:00:00.01 |       0 |
|   5 |    TABLE ACCESS FULL| S2   |      0 |    103K|      0 |00:00:00.01 |       0 |
--------------------------------------------------------------------------------------

从执行计划可以看出，驱动表 s1 只访问了1次，被驱动表 s2 访问了0次
结论：
Merge Sort Join 中，驱动表被访问0次或1次，被驱动表也是被访问0次或1次

表驱动顺序与性能

通过 statistics_level 方法获取执行计划，从执行计划中看 Buffers 列和 OMem 列对应的取值。

Nested Loops Join

# 大表做驱动表，小表做被驱动表

SQL> set linesize 1000;
SQL> set pagesize 1000;

SQL> alter session set statistics_level = all;

SQL> select /*+ leading(s2) use_nl(s1) */ * from s1 inner join s2 on s1.id = s2.s1_id where s1.n = 19;

SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));
Plan hash value: 3297466926

-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:02.62 |     701K|
|   1 |  NESTED LOOPS      |      |      1 |      1 |      1 |00:00:02.62 |     701K|
|   2 |   TABLE ACCESS FULL| S2   |      1 |    103K|    100K|00:00:00.27 |    1006 |
|*  3 |   TABLE ACCESS FULL| S1   |    100K|      1 |      1 |00:00:01.53 |     700K|
-------------------------------------------------------------------------------------

# 小表做驱动表，大表做被驱动表

SQL> alter session set statistics_level = all;

SQL> select /*+ leading(s1) use_nl(s2) */ * from s1 inner join s2 on s1.id = s2.s1_id where s1.n = 19;

SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));
Plan hash value: 3728883945

-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.01 |    1014 |
|   1 |  NESTED LOOPS      |      |      1 |      1 |      1 |00:00:00.01 |    1014 |
|*  2 |   TABLE ACCESS FULL| S1   |      1 |      1 |      1 |00:00:00.01 |       8 |
|*  3 |   TABLE ACCESS FULL| S2   |      1 |      1 |      1 |00:00:00.01 |    1006 |
-------------------------------------------------------------------------------------

结论：
当大表做驱动表进行 Nested Loops Join 时， Buffers 为 701K；
当小表做驱动表进行 Nested Loops Join 时， Buffers 为1014；
说明在 Nested Loops Join 中，表的驱动顺序对性能是有影响的。

Hash Join

# 大表做驱动表，小表做被驱动表

SQL> alter session set statistics_level = all;

SQL> select /*+ leading(s2) use_hash(s1) */ * from s1 inner join s2 on s1.id = s2.s1_id where s1.n = 19;

SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));
Plan hash value: 3432715687

---------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:01.06 |    1013 |       |
|*  1 |  HASH JOIN         |      |      1 |      1 |      1 |00:00:01.06 |    1013 |    11M|
|   2 |   TABLE ACCESS FULL| S2   |      1 |    103K|    100K|00:00:00.26 |    1005 |       |
|*  3 |   TABLE ACCESS FULL| S1   |      1 |      1 |      1 |00:00:00.01 |       8 |       |
---------------------------------------------------------------------------------------------

# 小表做驱动表，大表做被驱动表

SQL> alter session set statistics_level = all;

SQL> select /*+ leading(s1) use_hash(s2) */ * from s1 inner join s2 on s1.id = s2.s1_id where s1.n = 19;

SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));
Plan hash value: 3598693092

---------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.99 |    1013 |       |
|*  1 |  HASH JOIN         |      |      1 |      1 |      1 |00:00:00.99 |    1013 |   960K|
|*  2 |   TABLE ACCESS FULL| S1   |      1 |      1 |      1 |00:00:00.01 |       7 |       |
|   3 |   TABLE ACCESS FULL| S2   |      1 |    103K|    100K|00:00:00.25 |    1006 |       |
---------------------------------------------------------------------------------------------

结论：
当大表做驱动表进行 Hash Join 时， Buffers 为 1013，但 OMem(内存消耗) 为11M；
当小表做驱动表进行 Hash Join 时， Buffers 为 1013，但 OMem(内存消耗) 为 960K；
说明在 Hash Join 中，表的驱动顺序对性能是有影响的。

Merge Sort Join

# 大表做驱动表，小表做被驱动表

SQL> alter session set statistics_level = all;

SQL> select /*+ leading(s2) use_merge(s1) */ * from s1 inner join s2 on s1.id = s2.s1_id where s1.n = 19;

SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));
Plan hash value: 3314566573

----------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |      1 |00:00:01.00 |    1012 |       |
|   1 |  MERGE JOIN         |      |      1 |      1 |      1 |00:00:01.00 |    1012 |       |
|   2 |   SORT JOIN         |      |      1 |    103K|     20 |00:00:01.00 |    1005 |  9762K|
|   3 |    TABLE ACCESS FULL| S2   |      1 |    103K|    100K|00:00:00.25 |    1005 |       |
|*  4 |   SORT JOIN         |      |     20 |      1 |      1 |00:00:00.01 |       7 |  2048 |
|*  5 |    TABLE ACCESS FULL| S1   |      1 |      1 |      1 |00:00:00.01 |       7 |       |
----------------------------------------------------------------------------------------------

# 小表做驱动表，大表做被驱动表

SQL> alter session set statistics_level = all;

SQL> select /*+ leading(s1) use_merge(s2) */ * from s1 inner join s2 on s1.id = s2.s1_id where s1.n = 19;

SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));
Plan hash value: 655147337

----------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |      1 |00:00:00.56 |    1012 |       |
|   1 |  MERGE JOIN         |      |      1 |      1 |      1 |00:00:00.56 |    1012 |       |
|   2 |   SORT JOIN         |      |      1 |      1 |      1 |00:00:00.01 |       7 |  2048 |
|*  3 |    TABLE ACCESS FULL| S1   |      1 |      1 |      1 |00:00:00.01 |       7 |       |
|*  4 |   SORT JOIN         |      |      1 |    103K|      1 |00:00:00.56 |    1005 |  9762K|
|   5 |    TABLE ACCESS FULL| S2   |      1 |    103K|    100K|00:00:00.27 |    1005 |       |
----------------------------------------------------------------------------------------------

结论：
当大表做驱动表进行 Hash Join 时， Buffers 为 1012，OMem(内存消耗) 分别为 9762K 和 2048；
当小表做驱动表进行 Hash Join 时， Buffers 为 1012，OMem(内存消耗) 分别为 2048 和 9762K，OMem 开销是一样的；
说明在 Merge Sort Join 中，表的驱动顺序对性能并无影响。

表连接是否有排序

通过 autotrace traceonly statistics 方法获取统计信息，从执行计划中看 sorts 列对应的取值。

Nested Loops Join

SQL> set linesize 1000;
SQL> set pagesize 1000;

SQL> set autotrace traceonly statistics;

SQL> select /*+ leading(s1) use_nl(s2) */ * from s1 inner join s2 on s1.id = s2.s1_id where s1.n = 19;
统计信息
-----------------------------------------------------
          0  recursive calls
          0  db block gets
       1014  consistent gets
          0  physical reads
          0  redo size
       1032  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

结论：
从统计信息中，可以看出 sorts(memory) 和 sorts(disk)，都是0，说明没有排序。

Hash Join

SQL> set autotrace traceonly statistics;

SQL> select /*+ leading(s1) use_hash(s2) */ * from s1 inner join s2 on s1.id = s2.s1_id where s1.n = 19;
统计信息
----------------------------------------------------
          0  recursive calls
          0  db block gets
       1013  consistent gets
          0  physical reads
          0  redo size
       1032  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

结论：
从统计信息中，可以看出 sorts(memory) 和 sorts(disk)，都是0，说明没有排序。

Merge Sort Join

SQL> set autotrace traceonly statistics;

SQL> select /*+ leading(s1) use_merge(s2) */ * from s1 inner join s2 on s1.id = s2.s1_id where s1.n = 19;
统计信息
-----------------------------------------------------
          0  recursive calls
          0  db block gets
       1012  consistent gets
          0  physical reads
          0  redo size
       1032  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          1  rows processed

结论：
从统计信息中，可以看出 sorts(memory) 的取值为 2，说明有排序，而且是 2 次排序。

各连接的使用限制

Nested Loops Join

Nested Loops Join 没有限制，支持任何连接类型。

Hash Join

Hash Join 仅支持等值连接条件，不支持< 、> 、<> 和 like 的连接条件

# 连接条件为 ">"，结果无法根据 Hint 走 Hash Join

SQL> set linesize 1000;
SQL> set pagesize 1000;

SQL> set autotrace traceonly explain;

SQL> select /*+ leading(s1) use_hash(s2) */ * from s1 inner join s2 on s1.id > s2.s1_id where s1.n = 19;
执行计划
----------------------------------------------------------
Plan hash value: 3728883945

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  5183 |    20M|   276   (1)| 00:00:04 |
|   1 |  NESTED LOOPS      |      |  5183 |    20M|   276   (1)| 00:00:04 |
|*  2 |   TABLE ACCESS FULL| S1   |     1 |  2028 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| S2   |  5183 |    10M|   273   (1)| 00:00:04 |
---------------------------------------------------------------------------

Merge Sort Join

Merge Sort Join 支持 = 、> 、< 连接条件，不支持 <> 和 like 的场景

SQL> set autotrace traceonly explain;

SQL> select /*+ leading(s1) use_merge(s2) */ * from s1 inner join s2 on s1.id <> s2.s1_id where s1.n = 19;
执行计划
----------------------------------------------------------
Plan hash value: 3728883945

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |   103K|   402M|   276   (1)| 00:00:04 |
|   1 |  NESTED LOOPS      |      |   103K|   402M|   276   (1)| 00:00:04 |
|*  2 |   TABLE ACCESS FULL| S1   |     1 |  2028 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| S2   |   103K|   201M|   273   (1)| 00:00:04 |
---------------------------------------------------------------------------

表连接优化思路

表连接优化

嵌套循环连接

确保用在局部扫描的 OLTP 场景 NL 连接是只适合用在 OLTP 场景的，就是应用在有大量访问，且每个访问最终返回的记录数很少的场景。
驱动表的限制条件要考虑建立索引

SQL> set linesize 120;
SQL> set pagesize 1000;

SQL> create index idx_s1 on s1(n);

SQL> alter session set statistics_level = all;

SQL> select /*+ leading(s1) use_nl(s2) */ * from s1 inner join s2 on s1.id = s2.s1_id 
where s1.n = 19;

SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));
Plan hash value: 2907673067

-------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |      1 |        |      1 |00:00:00.01 |    1009 |
|   1 |  NESTED LOOPS                |        |      1 |      1 |      1 |00:00:00.01 |    1009 |
|   2 |   TABLE ACCESS BY INDEX ROWID| S1     |      1 |      1 |      1 |00:00:00.01 |       3 |
|*  3 |    INDEX RANGE SCAN          | IDX_S1 |      1 |      1 |      1 |00:00:00.01 |       2 |
|*  4 |   TABLE ACCESS FULL          | S2     |      1 |      1 |      1 |00:00:00.01 |    1006 |
-------------------------------------------------------------------------------------------------

从执行计划可以看出：嵌套循环连接方式，对驱动表的限制条件建立索引后，BUFFERS 为 1009

被驱动表连接条件要考虑建立索引

SQL> create index idx_s2 on s2(s1_id);

SQL> alter session set statistics_level = all;

SQL> select /*+ leading(s1) use_nl(s2) */ * from s1 inner join s2 on s1.id = s2.s1_id 
where s1.n = 19;

SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));
Plan hash value: 3862948895

-----------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |        |      1 |        |      1 |00:00:00.01 |       7 |
|   1 |  NESTED LOOPS                 |        |      1 |      1 |      1 |00:00:00.01 |       7 |
|   2 |   NESTED LOOPS                |        |      1 |      1 |      1 |00:00:00.01 |       6 |
|   3 |    TABLE ACCESS BY INDEX ROWID| S1     |      1 |      1 |      1 |00:00:00.01 |       3 |
|*  4 |     INDEX RANGE SCAN          | IDX_S1 |      1 |      1 |      1 |00:00:00.01 |       2 |
|*  5 |    INDEX RANGE SCAN           | IDX_S2 |      1 |      1 |      1 |00:00:00.01 |       3 |
|   6 |   TABLE ACCESS BY INDEX ROWID | S2     |      1 |      1 |      1 |00:00:00.01 |       1 |
----------------------------------------------------------------------------------------------------

从执行计划可以看出：嵌套循环连接方式，对被驱动表的连接条件建立索引后，BUFFERS 为 7

确保小结果集先驱动，大结果集被驱动

哈希连接

确保用在全扫描的 OLAP 场景，就是最终返回的记录数比较多；
明确该 SQL 是否限制 Hash Join Hash 连接有很多限制，比如连接条件必须是等值连接，像 < 、> 、<> 、like 等不可作为连接条件
两表无任何索引倾向 Hash Join 有时候为了考虑平衡，表中某些列不能建索引，在两表无任何索引的时候，一般 SQL 会倾向于使用 Hash 连接；
两表限制条件建立索引（看返回量）

# 两表限制条件无索引时的执行计划

SQL> set linesize 1000;
SQL> set pagesize 1000;

SQL> alter session set statistics_level = all;

SQL> select /*+ leading(s1) use_hash(s2) */ * from s1 inner join s2 on s1.id = s2.s1_id where s1.n = 19 and s2.n = 12;

SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));
Plan hash value: 3598693092

-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      0 |00:00:00.01 |    1012 |
|*  1 |  HASH JOIN         |      |      1 |      1 |      0 |00:00:00.01 |    1012 |
|*  2 |   TABLE ACCESS FULL| S1   |      1 |      1 |      1 |00:00:00.01 |       7 |
|*  3 |   TABLE ACCESS FULL| S2   |      1 |     11 |      1 |00:00:00.01 |    1005 |
-------------------------------------------------------------------------------------

从执行计划可以看出，Hash Join 连接在两表限制条件无索引时，被驱动表 S2 预估行E-Rows 为 11，实际行 A-Rows 为 1；逻辑读 Buffers 为 1012

# 两表限制条件有索引时的执行计划

SQL> create index idx_s1 on s1(n);
SQL> create index idx_s2 on s2(n);

SQL> alter session set statistics_level = all;

SQL> select /*+ leading(s1) use_hash(s2) */ * from s1 inner join s2 on s1.id = s2.s1_id where s1.n = 19 and s2.n = 12;

SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));
Plan hash value: 3977012969

-------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |      1 |        |      0 |00:00:00.01 |       5 |
|*  1 |  HASH JOIN                   |        |      1 |      1 |      0 |00:00:00.01 |       5 |
|   2 |   TABLE ACCESS BY INDEX ROWID| S1     |      1 |      1 |      1 |00:00:00.01 |       2 |
|*  3 |    INDEX RANGE SCAN          | IDX_S1 |      1 |      1 |      1 |00:00:00.01 |       1 |
|   4 |   TABLE ACCESS BY INDEX ROWID| S2     |      1 |      1 |      1 |00:00:00.01 |       3 |
|*  5 |    INDEX RANGE SCAN          | IDX_S2 |      1 |      1 |      1 |00:00:00.01 |       2 |
-------------------------------------------------------------------------------------------------

从执行计划可以看出，Hash Join 连接在两表限制条件无索引时，被驱动表 S2 预估行E-Rows 为 1，实际行 A-Rows 为 1；逻辑读 Buffers 为 5

确保小结果集先驱动，大的被驱动

# 大结果集先驱动， 小的被驱动

SQL> drop index idx_s1;
SQL> drop index idx_s2;

SQL> alter session set statistics_level = all;

SQL> select /*+leading(s2) use_hash(s1) */ * from s1 inner join s2 on s1.id = s2.s1_id;

SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));
Plan hash value: 3432715687

---------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |    100 |00:00:01.13 |    1017 |       |
|*  1 |  HASH JOIN         |      |      1 |    100 |    100 |00:00:01.13 |    1017 |    11M|
|   2 |   TABLE ACCESS FULL| S2   |      1 |    100K|    100K|00:00:00.28 |    1004 |       |
|   3 |   TABLE ACCESS FULL| S1   |      1 |    100 |    100 |00:00:00.01 |      13 |       |
---------------------------------------------------------------------------------------------

从执行计划可以看出， Hash Join 连接在大结果集做驱动表时，内存消耗 Omem 为 11M

# 小结果集先驱动， 大的被驱动

SQL> alter session set statistics_level = all;

SQL> select /*+leading(s1) use_hash(s2) */ * from s1 inner join s2 on s1.id = s2.s1_id;

SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));
Plan hash value: 3598693092

---------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |    100 |00:00:01.05 |    1017 |       |
|*  1 |  HASH JOIN         |      |      1 |    100 |    100 |00:00:01.05 |    1017 |   960K|
|   2 |   TABLE ACCESS FULL| S1   |      1 |    100 |    100 |00:00:00.01 |       6 |       |
|   3 |   TABLE ACCESS FULL| S2   |      1 |    100K|    100K|00:00:00.27 |    1011 |       |
---------------------------------------------------------------------------------------------

从执行计划可以看出， Hash Join 连接在小结果集做驱动表时，内存消耗 Omem 为 960K

尽量保证 PGA 能容纳 Hash 运算

如果 Hash Join 占用 Hash Aera 内存区过多，这时候我们可以考虑增大 sort_area_size 的值（hash_area_size = 2 * sort_area_size）

SQL> show parameter sort_area_size;
SQL> show parameter hash_area_size;

SQL> alter system set sort_area_size = 524288 scope = spfile;

排序合并连接

确保用在全扫描的 OLAP 扫描

一般来说排序合并连接倾向于吞吐量比较大的操作，也就是更适合用在 OLAP 场景，就是最终返回的记录数比较多。

SQL 是否限制使用 Merge Sort Join 连接

排序合并连接有很多连接条件限制，除了 < 、> 可以外，<> 、like 皆不可用

两表限制条件创建索引（看返回量）

# 两表限制条件皆无索引

SQL> set linesize 1000;
SQL> set pagesize 1000;

SQL> alter session set statistics_level = all;

SQL> select /*+ leading(s2) use_merge(s1) */ * from s1 inner join s2 on s1.id = s2.s1_id where s1.n = 19 and s2.n = 12;

SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));
Plan hash value: 3314566573

--------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |      0 |00:00:00.01 |    1010 |
|   1 |  MERGE JOIN         |      |      1 |      1 |      0 |00:00:00.01 |    1010 |
|   2 |   SORT JOIN         |      |      1 |      1 |      1 |00:00:00.01 |    1004 |
|*  3 |    TABLE ACCESS FULL| S2   |      1 |      1 |      1 |00:00:00.01 |    1004 |
|*  4 |   SORT JOIN         |      |      1 |      1 |      0 |00:00:00.01 |       6 |
|*  5 |    TABLE ACCESS FULL| S1   |      1 |      1 |      1 |00:00:00.01 |       6 |
--------------------------------------------------------------------------------------

# 两表限制条件有索引

SQL> create index idx_s1 on s1(n);
SQL> create index idx_s2 on s2(n);

SQL> alter session set statistics_level = all;

SQL> select /*+ leading(s2) use_merge(s1) */ * from s1 inner join s2 on s1.id = s2.s1_id where s1.n = 19 and s2.n = 12;

SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));
Plan hash value: 2480973787

--------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |        |      1 |        |      0 |00:00:00.01 |       5 |
|   1 |  MERGE JOIN                   |        |      1 |      1 |      0 |00:00:00.01 |       5 |
|   2 |   SORT JOIN                   |        |      1 |      1 |      1 |00:00:00.01 |       3 |
|   3 |    TABLE ACCESS BY INDEX ROWID| S2     |      1 |      1 |      1 |00:00:00.01 |       3 |
|*  4 |     INDEX RANGE SCAN          | IDX_S2 |      1 |      1 |      1 |00:00:00.01 |       2 |
|*  5 |   SORT JOIN                   |        |      1 |      1 |      0 |00:00:00.01 |       2 |
|   6 |    TABLE ACCESS BY INDEX ROWID| S1     |      1 |      1 |      1 |00:00:00.01 |       2 |
|*  7 |     INDEX RANGE SCAN          | IDX_S1 |      1 |      1 |      1 |00:00:00.01 |       1 |
--------------------------------------------------------------------------------------------------

避免取多余列导致排序的尺寸过大

# Merge Sort Join 取所有字段的情况

SQL> set linesize 1000;
SQL> set pagesize 1000;

SQL> alter session set statistics_level = all;

SQL> select /*+ leading(s2) use_merge(s1) */ * from s1 inner join s2 on s1.id = s2.s1_id where s1.n = 19;

SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));
Plan hash value: 3314566573

-------------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |      1 |00:00:01.25 |    1010 |          |
|   1 |  MERGE JOIN         |      |      1 |      1 |      1 |00:00:01.25 |    1010 |          |
|   2 |   SORT JOIN         |      |      1 |    100K|     20 |00:00:01.25 |    1004 | 8677K (0)|
|   3 |    TABLE ACCESS FULL| S2   |      1 |    100K|    100K|00:00:00.44 |    1004 |          |
|*  4 |   SORT JOIN         |      |     20 |      1 |      1 |00:00:00.01 |       6 | 2048  (0)|
|*  5 |    TABLE ACCESS FULL| S1   |      1 |      1 |      1 |00:00:00.01 |       6 |          |
-------------------------------------------------------------------------------------------------

从执行计划可以看出，取所有字段的 User_Mem 是 8677K

# Merge Sort Join 取部分字段的情况

SQL> alter session set statistics_level = all;

SQL> select /*+ leading(s2) use_merge(s1) */ s1.id from s1 inner join s2 on s1.id = s2.s1_id where s1.n = 19;

SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));
Plan hash value: 3314566573

-------------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |      1 |00:00:01.13 |    1010 |          |
|   1 |  MERGE JOIN         |      |      1 |      1 |      1 |00:00:01.13 |    1010 |          |
|   2 |   SORT JOIN         |      |      1 |    100K|     20 |00:00:01.13 |    1004 | 2188K (0)|
|   3 |    TABLE ACCESS FULL| S2   |      1 |    100K|    100K|00:00:00.28 |    1004 |          |
|*  4 |   SORT JOIN         |      |     20 |      1 |      1 |00:00:00.01 |       6 | 2048  (0)|
|*  5 |    TABLE ACCESS FULL| S1   |      1 |      1 |      1 |00:00:00.01 |       6 |          |
-------------------------------------------------------------------------------------------------

从执行计划可以看出，取部分字段的 User_Mem 是 2188K

PGA 能完成排序则避免磁盘排序

如果 Merge Sort Join占用 Hash Aera 内存区寸多大，这时候我们可以考虑增大 PGA

参考资料

《基于 Oracle 的 SQL 优化 - 崔华》

《收获不止SQL优化 - 梁敬彬》

《SQL 优化核心思想 - 罗炳森》

半联结与反联结

深入解析：半连接与反连接的原理和等价改写方法

原创文章，转载请注明出处：http://www.opcoder.cn/article/17/