在Oracle 9i中，外连接决定了连接的顺序，比如TEST1.id=TEST2.id(+)，那么表TEST1一定是驱动表（即从表TEST1去join表TEST2），不管采用nest loop join或者hash join。因为外连接中，表TEST1的所有记录都要返回，所以必须用它来作为驱动表，如果反过来用表TEST2作为驱动表，实现比较困难，这个其实很好理解。
select a.* from TEST1 a,TEST2 b where a.id=b.id(+);
------------------------------------------
| Id | Operation | Name |
------------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | HASH JOIN OUTER | |
| 2 | TABLE ACCESS FULL | TEST1 |
| 3 | TABLE ACCESS FULL | TEST2 |
------------------------------------------
在10g版本中，Oracle引入了一个HASH JOIN RIGHT OUTER，可以改变外连接的顺序。Oracle会根据COST来决定hash join的顺序，比如Oracle发现表TEST2比TEST1小很多时，Oracle将会改变hash join的顺序，因为根据hash join的原理，选择比较小的表build hash table效率比较高。看看下面的执行计划：
------------------------------------------
| Id | Operation | Name |
------------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | HASH JOIN RIGHT OUTER | |
| 2 | TABLE ACCESS FULL | TEST2 |
| 3 | TABLE ACCESS FULL | TEST1 |
------------------------------------------
因为是外连接，TEST1表的记录需要全部返回，如果用TEST2表作为驱动表，Oracle一定做了特殊的处理。我们猜测一下Oracle如何处理，首先是采用普通的inner join的方法，将TEST2表在内存中build hash table（hash table A)，然后扫描TEST1表，并进行hash join，如果TEST1表的记录未在hash table A中找到对应的记录，则在另外的内存区域记录(hash table B)，然后将两个hash table合并，返回。这个特性仅适用于hash join，不适用于nest loop join，也没有hash join left outer.
我们再来看看anti join（反连接）的情况：
select a.* from TEST1 a WHERE not exists(select null from TEST2 b where a.id=b.id);
可以等价转换为外连接的形式
select a.* from TEST1 a,TEST2 b where a.id=b.id(+) and b.id is null;(只取TEST1表的内容)
------------------------------------------
| Id | Operation | Name |
------------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | HASH JOIN ANTI | |
| 2 | TABLE ACCESS FULL | TEST1 |
| 3 | TABLE ACCESS FULL | TEST2 |
------------------------------------------
select a.id,b.name from TEST1 a,TEST2 b where a.id=b.id(+) and b.id is null;(取两个表的内容)
------------------------------------------
| Id | Operation | Name |
------------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | FILTER | |
|* 2 | HASH JOIN OUTER | |
| 3 | TABLE ACCESS FULL | TEST1 |
| 4 | TABLE ACCESS FULL | TEST2 |
------------------------------------------
第一个SQL使用了反连接，因为它只需要返回TEST1表并且不在TEST2表中的记录，所以反连接在join的过程中，如果发现没有匹配的记录，只需要在TEST1表的hash table中作标记，然后返回即可。而第二个SQL需要返回两个表的记录，所以必须将TEST2表也build到hash table中，并且多了一个filter的工作（用来过滤b.id is null)，所以反连接就是一种特殊的外连接，而且连接的顺序也是固定的，anti nest loop join的原理也是一样的。
再让我们看看HASH JOIN RIGHT OUTER用在反连接的环境中，我们又有新的发现，那就是HASH JOIN RIGHT ANTI.
select a.* from TEST1 a,TEST2 b where a.id=b.id(+) and b.id is null;(只取TEST1表中的内容)
------------------------------------------
| Id | Operation | Name |
------------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | HASH JOIN RIGHT ANTI | |
| 2 | TABLE ACCESS FULL | TEST2 |
| 3 | TABLE ACCESS FULL | TEST1 |
------------------------------------------
select a.id,b.name from TEST1 a,TEST2 b where a.id=b.id(+) and b.id is null;(取两个表的内容)
------------------------------------------
| Id | Operation | Name |
------------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | FILTER | |
|* 2 | HASH JOIN RIGHT OUTER | |
| 3 | TABLE ACCESS FULL | TEST2 |
| 4 | TABLE ACCESS FULL | TEST1 |
------------------------------------------
可以看到，虽然join的顺序发生了变化，但是如果我们只取inner table中的字段，就可以利用到反连接的特性。小小执行计划的改变，蕴含了更深层次的原理。
–EOF–
Latest Comments
RSS