Oracle 表连接的方法 （下）

哈希连接

 哈希连接仅适用于CBO Oracle 10g 以后的版本： 隐含参数_HASH_JOIN_ENABLED USE_HASH Hint的参数优先级高于_HASH_JOIN_ENABLED

Oracle中两个表T1和T2 做哈希连接，那么步骤如下:

1 :首先Oracle会根据参数HASH_AREA_SIZE 和 DB_BLOCK_SIZE 和HASH_MULTBLOCK_IO_COUNT 来决定Hash Partition 的数量。（Hash Partition是一个逻辑上概念，包括一组hash bucket ，一个HASH Table又是hash Partiton的集合）

2 两个表按照目前sql中的谓词进行过滤，得到的结果集较少的那个表，作为驱动结果集。
假设T1对应的结果集为S T2对应的结果集为B，设S为驱动结果集，B设为被驱动结果集。

3 Oracle遍历S，读取S中的每一条记录，并对每一条记录按照S1中的连接列，使用Oracle内置的hash函数，计算hash值。Oracle中有两个内置的哈希函数，分别记为hash_func_1 hash_func_2 ,所对应的结果记为hash_vlaue_1 hash_vlaue_2

4 oracle 按照hash_value_1 把相应的S中的记录存储在不同的Hash Partition的不同的hash bucket中去。同时和记录存放在一起还有hash_vaule_2 。存储在hash bucket里的记录并不是目标表的完整行记录，只需要存储位于目标sql中与目标表相关的查询列和连接列。把S中对应的每一个hash Partition 记为Si。

5 在构建Si的同时，Oracle会构建一个位图，这个位图用来标记Si所包含的每一个hash bucket是否有记录。

6 如果S的数量巨大，那可能存在把PGA的工作区 worke area 被填满的情况。这时Oracle会把工作区中包含记录数最多的hash Partition 写到磁盘上，接着Oracle会继续构建S所对应的hash table。如果继续出现被填满的情况，那么就重复上述步骤。 直至结果集S被遍历完成。

7 Oracle会对si进行排序，目的是将记录数较小的hash Partition 写在内存中，记录数大的写到磁盘上。

8 现在Oracle开始遍历B，读取B中每一条记录，并按照该记录在表T2中的连接列做哈希运算，此步骤上面和上面一样。 Oracle会按照该记录对应的hash_value_1 去Si里找相匹配的hash Bucket，如果能找到匹配的hash bucket ，则开始遍历hash bucket中每一条记录，幷校检存储于该hash bucket记录中每一个连接列，是否真的匹配。如果匹配，则返回相应的结果。如果找不到匹配的，则会访问步骤5中的位图。

如果位图的记录显示该hash bucket在si中对应的记录数大于0 ，则说明此hansh bucket不在内存中，但是在磁盘中。此时Oracle就会按照hash value_1 把值相应的B中的记录以hash Partition的方式写回到磁盘，同时存放的还有hash_value_2 。如果位图中显示记录数为0，则表示没有对应的记录。 这个过程被称为 “位图过滤”。
相应把B所对应的每一个hash Partition 记为Bj

9 查找hash bucket 和构建Bj的过程会一直持续下去，直到遍历完成B中的所有记录。

10 内存中的记录已经匹配完成，现在需要匹配磁盘上的Si和Bj。

11 因为构建Si和Bj时，使用的是同样的哈希函数，所以Oracle在处理位于磁盘上的Si和Bj的时候，可以放心的配对处理。即只有对应hash Partition number值相同的Si和Bj才可能产生满足连接条件的记录。这里我们sn和bn来表示位于磁盘上且对应hash Partition number值相同的SI和Bj。

12 对于每一对Sn和Bn，它们之中记录数较少的会被当作驱动结果集，Oracle会用这个驱动结果集hash bucket里记录的hash_value_2来构建新的hash table，另外一个记录数较多的当作被驱动结果集，然后Oracle会用这个被驱动结果集hash bucket里的记录hash_value_2 去上述构建的hash table中找匹配记录。 对于与每一对Sn和Bn而言，Oracle始终会选择他们中记录数较少的作为被驱动结果集，所以每一对Sn和Bn的被驱动结果集都可能发生变化，这就是所谓的动态角色互换。

13 上述步骤中，如果匹配的相应记录，则返回结果。

14 处理完成所有的sn和bn后，此过程结束。

哈希连接的优缺点如下:

1 哈希连接不需要排序
2 哈希连接的驱动表对应的连接的可选择性应尽可能的好，这个会影响hash bucket的数量。而hash bucket 记录数又会直接影响从该hash bucket 中查找匹配记录的效率。
3 哈希连接只适用于CBO，只能用于等值连接
4 哈希连接很适合小表和大表之间做表连接，且连接结果集的记录数较多的情形。

5 当两个表做哈希连接时，如果在施加了目标sql中指定的谓词条件，后得到的数据较小的结果集，所构建的hash table可以容纳在PGA的工作区，则此时哈希链接的效率会很高。

PS： 驱动结果集和被驱动结果，在嵌套循环连接和哈希连接中都存在，而且都是记录数较少的作为驱动结果集，但是原因不同：

嵌套循环连接中，第一层循环的数量是确定的，并且等于驱动结果集的记录数，（可以说是全表扫描）而第 二层查找时，可以按照索引的方式来完成。如果被驱动结果集的索引的可选择性良好的情况下，影响嵌套循环效率的原因就是第一层的全表扫描，因此全表扫描越少越好。

哈希连接中，驱动结果集的所有数据需要构建hash table，如果数据较大，那么占用的缓存和硬盘空间必然增多，尤其当过多的数据正好填满pga的 work area时。被驱动结果集理论上只需要查找和匹配，并不一定要构建hash table，所以数据过多无妨。

笛卡尔连接：

两个表T1 T2做笛卡尔连接的步骤如下

1： 对于T1施加目标sql中的谓词条件，得到结果集1
2 对T2施加目标sql中的谓词条件，得到结果集2
3 遍历1 中的每一条记录，和2中的每一条记录相匹配，得到最后的结果。

反连接：

是一种特殊类型的连接，与内连接和外连接都不同。
含义如下： t1.x.anti = t2.y来表示t1和t2进行反连接。反连接条件为 t1.x=t2.y。那么t1.x.anti = t2.y表示，只要在表T2中有满足条件t1.x=t2.y的记录存在，则T1中满足条件t1.x=t2.y的记录就会被删除，最后返回T1那些不满足条件的记录。

在子查询时，Oracle经常会把 not exists ，not in， <>all 换成对应的反连接。

 not in 和<>all  对null敏感，即如果not in后面的子查询结果集中存在null，那么最终就会返回null，即不包含任何记录， not exists 对null不敏感。

半连接
半连接的意思就是只要在表T2中找到满足条件的值，就立刻返回。半连接和普通的内连接不同，半连接会去重。

Oracle中的exists ，in， =any 会转为对应的半连接。