表连接的方法-白红宇

表连接的方法

阅读量：5815 次

发布时间：2019-06-18

本文共 21852 字，大约阅读时间需要 72 分钟。

在oracle数据库中，两个表之间的表连接方法有排序合并联结，嵌套循环联结，哈希连接和笛卡儿连接这四种，下面分别介绍着四种联结方法：

（1）排序合并连接

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

如果两个表（这里把它们分别命名为表T1和T2）在做表连接时使用的是排序合并连接，则oracle会依次顺序执行以下步骤：

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

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

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

对于排序合并连接的优缺点及适合场景，我们有如下总结.

a.通常情况下，排序合并连接的执行效率远远不如哈希连接，但前者使用范围更广，因为哈希连接通常只能用于等值连接条件，而排序合并连接还能用于其他连接条件（例如<,<=,>,>=）.

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

(2）嵌套循环连接

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

如果两个表（这里将它们分别命名为表T1和T2）在做表连接时使用嵌套循环连接，则oracle会使用依次顺序执行如下步骤。

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

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

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

对应嵌套循环连接的优缺点及适用场景，我们有如下总结。

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

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

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

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

对于这种单块读而言，如果待访问的索引块或数据块不在buffer cache中，oracle就需要耗费物理I/O去相应的数据文件中获取。显然，在单块读的数量不降低的情况下，如果能减少这种单块读所需要耗费的物理I/O数量，那么嵌套循环连接的执行效率也会随之提高。

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

向量I/O的引入也反映在嵌套循环连接的执行计划上，在oracle 11g中，你会发现明明一次嵌套循环连接就可以处理完毕的sql，但其执行计划的显示内容中嵌套循环连接的数量却由之前的一个变为了现在的两个。

实验说明：

SQL> create index idx_t2 on t2(col2);

Index created.

SQL> select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

PL/SQL Release 11.2.0.4.0 - Production

CORE11.2.0.4.0Production

TNS for Linux: Version 11.2.0.4.0 - Production

NLSRTL Version 11.2.0.4.0 - Production

SQL> set autot traceonly

SQL> set lines 200

SQL> select /*+ ordered use_nl(t2) */ t1.col1,t1.col2,t2.col3 from t1,t2 where t1.col2=t2.col2;

Execution Plan

----------------------------------------------------------

Plan hash value: 1054738919

---------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 3 | 30 | 6(0)| 00:00:01 |

| 1 | NESTED LOOPS | | 3 | 30 | 6(0)| 00:00:01 |

| 2 | NESTED LOOPS | | 3 | 30 | 6(0)| 00:00:01 |

| 3 | TABLE ACCESS FULL | T1 | 3 | 15 | 3(0)| 00:00:01 |

|* 4 | INDEX RANGE SCAN | IDX_T2 | 1 | | 0(0)| 00:00:01 |

| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 5 | 1(0)| 00:00:01 |

---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

4 - access("T1"."COL2"="T2"."COL2")

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

12 consistent gets

0 physical reads

0 redo size

718 bytes sent via SQL*Net to client

524 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

2 rows processed

注意，上述执行计划的显示内容关键字“NESTED LOOP”出现了两次，这说明oracle 11gr2中执行上述sql时确实用了两次嵌套循环连接。这里的第二次嵌套循环连接的被驱动表部分所对应的执行步骤是“TABLE ACCESS BY INDEX ROWID”，这可能是因为oracle想表达此时在通过rowid回表访问T2时把一批rowid组合起来后通过一个向量I/O批量回表，而不是每拿到一个rowid就用一次单块读回表。

SQL> select /*+ optimizer_features_enable('9.2.0') ordered use_nl(t2) */ t1.col1,t1.col2,t2.col3 from t1,t2 where t1.col2=t2.col2;

Execution Plan

----------------------------------------------------------

Plan hash value: 2253255382

---------------------------------------------------------------------

----------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 3 | 30 | 5 |

| 1 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 5 | 1 |

| 2 | NESTED LOOPS | | 3 | 30 | 5 |

| 3 | TABLE ACCESS FULL | T1 | 3 | 15 | 2 |

|* 4 | INDEX RANGE SCAN | IDX_T2 | 1 | | |

----------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

4 - access("T1"."COL2"="T2"."COL2")Statistics

上述optimizer_features_enable hint的作用是让解析sql的优化器的版本回退到oracle 9iR2，这样我们就可以观察同一个sql在oracle 11g之前执行嵌套循环连接的情况。

主要，上述执行计划的显示内容中关键字“NESTED LOOPS”只出现一次，这说明在oralce9iR2中执行上述sql确实只使用了一次嵌套循环连接。

（3）哈希连接

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

在oracle 7.3之前，oracle数据库中的常用的表连接方法就只有排序合并连接和嵌套循环连接这两种，但这两种方法都各有明细的缺陷。对应排序合并连接，如果两个表在施加了目标sql中指定的谓词条件（如果有的话）后得到的结果集很大且需要排序，则排序合并连接的执行效率一定不高；而对于嵌套循环连接，如果驱动表所对应的驱动结果集的记录数很多，即便在被驱动表的连接列上存在索引，此时使用嵌套循环连接的执行效率也同样不高。

为了解决排序合并连接和嵌套循环连接在上述情形下执行效率不高的问题，同时也为了给优化器提供一种新的选择，oracle在oracle 7.3中引入了哈希连接，从理论上来说，哈希连接的执行效率会比排序合并和嵌套循环连接要高，当然，实际情况并不是总这样的。

在oracle 10g及其以后的oracle数据库版本中，优化器（实际上是CBO,因为哈希连接仅使用与CBO），在解析目标sql时是否考虑哈希连接的是否受限于隐含参数hash_join_enbaled.

_HASH_JOIN_ENABLED的默认值是true，表示允许CBO在解析目标sql时考虑哈希连接。当然，即使将该参数的值改成FALSE，使用USE_HASH hint依然可以让CBO在解析目标sql时考虑哈希连接，这说明USE_HASH Hint的优先级比参数hash_join_enabled的优先级要高。

如果两个表（这里将它们分别命名为T1和T2)在做表连接时使用的是哈希连接，则oracle会依次顺序执行以下步骤

首先会根据参数HASH_AREA_SIZE,DB_BLOCK_SIZE和HASH_MULTIBLOCK_IO_COUNT的值来决定hash partition的数量，(hash partition 是一个逻辑上的概念，它实际上是一组HASH BUCKET的集合。所有hash partition的集合就被称为hash table，即一个hash table由多个hash partition所组成，而一个hash partition又是有多个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_value_1和hash_value_2.

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

（5）在构建Si的同时，Oracle会构建一个位图（BITMAP），这个位图用来标记Si所包含的每一个hash Bucket是否有记录（即记录数是否大于0）

（6）如果S的数据量很大，那么在构建S所对应的hash table时，就可能出现PGA的工作区被填满的情况。这时候oracle会把工作区包含记录数最多的hash partition写到磁盘上（TEMP空间）。接着oracle会继续构建S所对应的的hash table，在继续构建的过程中，如果工作区又写满了，则oracle继续重复以上的动作，即挑选包含记录数最多的hash partition并写回磁盘上。如果要构建的记录所对应的的hash partition 已经事先被oracle写回磁盘，则此时oracle就会去磁盘上更新改hash partition。即把该条记录和hash_value_2直接加到这个位于磁盘上的hash partition的相应hash bucket中。注意，极端情绪下可能会出现只有某个hash partition的部分记录还在内存中，该hash partition的剩余部分和余下部分的所有hash partition都已经被已回磁盘上。

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

（8)接着，oracle会对所有的Si按照它们所包含的记录数来排序，然后把这些已经排好序的hash partit按顺序依次且尽可能全部放在内存中（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；如果能找到匹配的hash bucket，则oracle还会遍历该hash bucket中的每一条记录，并校验存储于该hash_bucket中每一条记录的连接列，看是否匹配（即这里要校验S和B中的匹配记录所对应的连接列是否真的相等，因为对于哈希运算而言，不同的值经过哈希运算后的结果可能是相同的）。如果真的匹配，则上述hash_value_1所对应的B中记录的目标sql中的查询列和hash bucket中匹配记录会组合起来，一起作为满足目标连接条件的记录返回。如果找不到匹配的hash bucket，则oracle就会去访问步骤5中构建的的位图。

如果位图显示该HASH bucket在Si中对应的记录数大于0，则说明该hash bucket虽然不在内存中，但它已经被写回磁盘，则此时oracle就会按照hash_value_1的值把相应B中的对应的记录也以hash partition的方式写回到磁盘上，同时和该记录存储在一起的还有该记录的用hash_func_2计算出的hash_value_2的值。如果位图显示该hash bucket在Si中对应的记录数等于0，则oralce就无须把上述hash_value_1所对应的B中的记录写回磁盘了，因为这条记录必然不满足目标SQL的连接条件。这个根据位图来决定是否将hash_value_1所对应B中的记录写回磁盘的动作就是所谓的"位图过滤“（oracle不一定启用位图过滤，因为如果所有的的Si本来就都在内存中，也没有发生过将Si写回磁盘的操作，那么这里oracle就不需要启动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_value_2来构建新的hash table，另外一个记录数较多的会被当作被驱动结果集，然后oracle会用这个驱动结果集hash bucket里记录hash_value_2去构建的新的hash table中找到匹配的记录。注意，对于每一对Sn和Bn而言，oracle始终会选择他们中记录数较少的来作为驱动结果集，索引每一对Sn和Bn的驱动结果集可能会发生变化，这就是所谓的”动态角色互换“

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

(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的工作区，则此时的哈希连接的执行效率会非常高。

用10104事件来跟踪哈希连接：

SQL> conn /as sysdba

Connected.

SQL> oradebug setmypid

Statement processed.

SQL> oradebug event 10104 trace name context forever,level 1;

Statement processed.

SQL> set autot traceonly

SQL> select /*+ ordered use_hash(emp,dept)*/ename,dept.dname from scott.emp,scott.dept where scott.dept.deptno=scott.emp.deptno;

Execution Plan

----------------------------------------------------------

Plan hash value: 1123238657

---------------------------------------------------------------------------

---------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 14 | 308 |6 (0)| 00:00:01 |

|* 1 | HASH JOIN | | 14 | 308 |6 (0)| 00:00:01 |

| 2 | TABLE ACCESS FULL| EMP | 14 | 126 |3 (0)| 00:00:01 |

| 3 | TABLE ACCESS FULL| DEPT |4 | 52 |3 (0)| 00:00:01 |

---------------------------------------------------------------------------

SQL> oradebug tracefile_name;

/u01/app/oracle/diag/rdbms/mecbs/MECBS1/trace/MECBS1_ora_19499.trc

[root@node1 trace]# cat MECBS1_ora_19499.trc

kxhfInit(): enter

kxhfInit(): exit

*** RowSrcId: 1 HASH JOIN STATISTICS (INITIALIZATION) ***

Join Type: INNER join

Original hash-area size: 6617206

Memory for slot table: 2949120

Calculated overhead for partitions and row/slot managers: 3668086

Hash-join fanout: 8

Number of partitions: 8

Number of slots: 24

Multiblock IO: 15

Block size(KB): 8

Cluster (slot) size(KB): 120

Minimum number of bytes per block: 8160

Bit vector memory allocation(KB): 256

Per partition bit vector length(KB): 32

Maximum possible row length: 49

Estimated build size (KB): 0

Estimated Build Row Length (includes overhead): 25

# Immutable Flags:

Not BUFFER(execution) output of the join for PQ

Evaluate Left Input Row Vector

Evaluate Right Input Row Vector

# Mutable Flags:

IO sync

kxhfSetPhase: phase=BUILD

kxhfAddChunk: add chunk 0 (sz=32) to slot table

kxhfAddChunk: chunk 0 (lbs=0x7fb096e61b18, slotTab=0x7fb096e61ce8) successfuly added

kxhfSetPhase: phase=PROBE_1

qerhjFetch: max build row length (mbl=18)

*** RowSrcId: 1 END OF BUILD (PHASE 1) ***

Revised row length: 18

Revised build size: 0KB

kxhfResize(enter): resize to 13 slots (numAlloc=3, max=24)

kxhfResize(exit): resized to 13 slots (numAlloc=3, max=13)

Slot table resized: old=24 wanted=13 got=13 unload=0

*** RowSrcId: 1 HASH JOIN RESIZE BUILD (PHASE 1) ***

Total number of partitions: 8

Number of partitions which could fit in memory: 8

Number of partitions left in memory: 8

Total number of slots in in-memory partitions: 3

kxhfResize(enter): resize to 9 slots (numAlloc=3, max=13)

kxhfResize(exit): resized to 9 slots (numAlloc=3, max=9)

set work area size to: 4006K (9 slots)

*** RowSrcId: 1 HASH JOIN BUILD HASH TABLE (PHASE 1) ***

Total number of partitions: 8

Number of partitions left in memory: 8

Total number of rows in in-memory partitions: 14

(used as preliminary number of buckets in hash table)

Estimated max # of build rows that can fit in avail memory: 122580

### Partition Distribution ###

Partition:0 rows:6 clusters:1 slots:1 kept=1

Partition:1 rows:0 clusters:0 slots:0 kept=1

Partition:2 rows:5 clusters:1 slots:1 kept=1

Partition:3 rows:0 clusters:0 slots:0 kept=1

Partition:4 rows:0 clusters:0 slots:0 kept=1

Partition:5 rows:0 clusters:0 slots:0 kept=1

Partition:6 rows:0 clusters:0 slots:0 kept=1

Partition:7 rows:3 clusters:1 slots:1 kept=1

*** (continued) HASH JOIN BUILD HASH TABLE (PHASE 1) ***

Revised number of hash buckets (after flushing): 14

Allocating new hash table.

*** (continued) HASH JOIN BUILD HASH TABLE (PHASE 1) ***

Requested size of hash table: 4

Actual size of hash table: 8

Number of buckets: 32

Match bit vector allocated: FALSE

*** (continued) HASH JOIN BUILD HASH TABLE (PHASE 1) ***

Total number of rows (may have changed): 14

Number of in-memory partitions (may have changed): 8

Final number of hash buckets: 32

Size (in bytes) of hash table: 256

qerhjBuildHashTable(): done hash-table on partition=7, index=5 last_slot#=2 rows=3 total_rows=3

qerhjBuildHashTable(): done hash-table on partition=2, index=6 last_slot#=0 rows=5 total_rows=8

qerhjBuildHashTable(): done hash-table on partition=0, index=7 last_slot#=1 rows=6 total_rows=14

kxhfIterate(end_iterate): numAlloc=3, maxSlots=9

*** (continued) HASH JOIN BUILD HASH TABLE (PHASE 1) ***

### Hash table ###

# NOTE: The calculated number of rows in non-empty buckets may be smaller

# than the true number.

Number of buckets with 0 rows: 29

Number of buckets with 1 rows: 0

Number of buckets with 2 rows: 0

Number of buckets with 3 rows: 1

Number of buckets with 4 rows: 0

Number of buckets with 5 rows: 1

Number of buckets with 6 rows: 1

Number of buckets with 7 rows: 0

Number of buckets with 8 rows: 0

Number of buckets with 9 rows: 0

Number of buckets with between 10 and 19 rows: 0

Number of buckets with between 20 and 29 rows: 0

Number of buckets with between 30 and 39 rows: 0

Number of buckets with between 40 and 49 rows: 0

Number of buckets with between 50 and 59 rows: 0

Number of buckets with between 60 and 69 rows: 0

Number of buckets with between 70 and 79 rows: 0

Number of buckets with between 80 and 89 rows: 0

Number of buckets with between 90 and 99 rows: 0

Number of buckets with 100 or more rows: 0

### Hash table overall statistics ###

Total buckets: 32 Empty buckets: 29 Non-empty buckets: 3

Total number of rows: 14

Maximum number of rows in a bucket: 6

Average number of rows in non-empty buckets: 4.666667

qerhjFetch: max probe row length (mpl=0)

*** RowSrcId: 1, qerhjFreeSpace(): free hash-join memory

kxhfRemoveChunk: remove chunk 0 from slot table

*** SESSION ID:(16.137) 2014-12-10 15:02:40.560

以上说明上述哈希连接驱动结果集的记录数为14，共有8个哈希分区，32个hash bucket，这个32个hash bucket有29个是空的，3个有记录，包含记录最多的那个hash bucket所含记录的数量是6。

（4）反连接

反连接是一种特殊的连接类型，与内连接和外连接不同的是，oracle数据库里并没有相关的关键字可以在SQL文本中专门表示反连接，以下单独说明：

为了方面说明反连接的含义，我们用t1.x anti=t1.y来表示表T1和T2做反连接，且T1是驱动表，T2是被驱动表，反连接条件为t1.x=t2.y。这里t1.x anti=t1.y的含义是只表T2中有满足条件t1.x=t2.y的记录存在，则表T1中满足t1.x=t2.y的记录就会被丢弃，最后返回的记录就是表T1中那些不满足条件的t1.x=t2.y的记录。

当所子查询展开的时候，oracle经常会把那些外部where条件not exist ，not in或<>ALL的子查询转换成对应的反连接。

例子：

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

COL1 C

---------- -

3 C

Execution Plan

----------------------------------------------------------

Plan hash value: 1275484728

---------------------------------------------------------------------------

---------------------------------------------------------------------------

| 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 |

---------------------------------------------------------------------------

SQL> select * from t1 where not exists(select 1 from t2 where t1.col2=t2.col2);

COL1 C

---------- -

3 C

Execution Plan

----------------------------------------------------------

Plan hash value: 1534930707

-----------------------------------------------------------------------------

-----------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 7 | 3 (0)| 00:00:01 |

| 1 | NESTED LOOPS ANTI | | 1 | 7 | 3 (0)| 00:00:01 |

| 2 | TABLE ACCESS FULL| T1 | 3 | 15 | 3 (0)| 00:00:01 |

|* 3 | INDEX RANGE SCAN | IDX_T2 | 2 | 4 | 0 (0)| 00:00:01 |

-----------------------------------------------------------------------------

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

COL1 C

---------- -

3 C

Execution Plan

----------------------------------------------------------

Plan hash value: 1275484728

---------------------------------------------------------------------------

---------------------------------------------------------------------------

| 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 |

---------------------------------------------------------------------------

这里col2列上没有null值，所以此时这三个sql是等价的，如果连接列col2上有null值，则它们就不完全等价了，这种null值所带来的情况又细分为两种情况

1.表T1的连接列col2出现null值：

SQL> insert into t1 values(4,null);

1 row created.

Execution Plan

----------------------------------------------------------

---------------------------------------------------------------------------------

---------------------------------------------------------------------------------

| 0 | INSERT STATEMENT || 1 | 5 | 1 (0)| 00:00:01 |

| 1 | LOAD TABLE CONVENTIONAL | T1||| ||

---------------------------------------------------------------------------------

Statistics

----------------------------------------------------------

3 recursive calls

5 db block gets

9 consistent gets

2 physical reads

400 redo size

835 bytes sent via SQL*Net to client

785 bytes received via SQL*Net from client

3 SQL*Net roundtrips to/from client

2 sorts (memory)

0 sorts (disk)

1 rows processed

SQL> set autot off

SQL> commit;

Commit complete.

SQL> select * from t1;

COL1 C

---------- -

1 A

2 B

3 C

三条语句的执行结果如下：

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

COL1 C

---------- -

3 C

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

COL1 C

---------- -

3 C

SQL> select * from t1 where not exists(select 1 from t2 where t1.col2=t2.col2);

COL1 C

---------- -

3 C

2.表T2的连接列col2上出现了NULL值

先删除表T1那条列col2为null的值：

SQL> delete from t1 where col1=4;

1 row deleted.

SQL> commit;

Commit complete.

SQL> insert into t2 values(null,'F2');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t2;

C CO

- --

A A2

B B2

D D2

再执行sql：

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

no rows selected

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

no rows selected

SQL> select * from t1 where not exists(select 1 from t2 where t1.col2=t2.col2);

COL1 C

---------- -

3 C

从上述测试中我们可以得出以下结论：

（1）表T1，T2在各自的连接列上col2上一旦有了null值，则上述三sql执行的结果不同的。

（2）NOT IN 和<>all对NULL值敏感，这个意味着not in后面的的子查询或者常量集合一旦有null值出现，则整个sql的执行结果就会为null，即此时的执行结果将不包含任何记录。

（3）not exist对null值不敏感，这意味这个null对not exist的执行结果不会有什么影响。

正是因为not in和<>all对null值敏感的问题，oracle推出了改良的反连接，这种反连接能够处理NULL值，oracle称其为"null_aware anti join" 。执行计划中 HASH JOIN ANTI NA的NA就是Null Aware的缩写。oracle是想告诉我们这里采用不是普通的反连接。而是改良后的，能够处理null的哈希反连接。

oracle是否启用NULL_Aware anti join受隐含参数_OPTIMIZER_NULL_AWARE_ANTIJON控制，其默认值为true，表示启用NULL_Aware anti Join

(5)半连接

半连接是一种特殊的连接类型，于反连接一样，oralce数据库里面也没有相关的关键字可以子在sql文本中专门表示半连接。

为了方面说明反连接的含义，我们用t1.x semi=t1.y来表示表T1和T2做反连接，且T1是驱动表，T2是被驱动表，半连接条件为t1.x=t2.y。这里t1.x semi=t1.y的含义是只要在表T2中找到一条记录满足t1.x=t2.y，则马上停止搜索表T2，并直接返回表T1中满足t1.x=t2.y的记录。也就是说，T2中满足半连接条件t1.x=t2.y的记录即使有多条，表T1也只会返回第一条满足条件的记录。所以半连接和普通的内连接不同，半连接实际上会去重。

当作子查询展开的时候，oracle经常会把那些外部where条件为exist ，in或=any的子查询转换成对应的半连接。

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

COL1 C

---------- -

1 A

2 B

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

COL1 C

---------- -

1 A

2 B

SQL> select * from t1 where exists(select 1 from t2 where t1.col2=t2.col2);