四种联结方法——《Oracle SQL 高级编程》笔记1

联接的方法有四种：嵌套循环联接、散列联接、排序-合并联接和笛卡尔连接


每个联接方法都有两个分支。所访问的第一章表称为驱动表（the driving table），访问的第二张表则称为内层表或被驱动表（inner 或 driven-to table）。优化器通过使用统计信息和 where 子句中的筛选条件计算每个表分别返回多少行数据来确定那张表是驱动表，预估大小最小（块、数据行、字节）的表通常被作为驱动表。


嵌套循环联接
嵌套循环联接使用一次访问运算所得到的结果集中的每一行来与另一个表进行对碰。如果结果集的大小是有限的，并且在用来联接的列上建有索引的话，这种联接的效率通常是最高的。嵌套循环联结运算成本主要是读取外层表中每一行并将其所匹配的内层表中的行联结所需的成本。
这种联接的强大之处在于所使用的内存是非常少的。因为数据行集一次只加工一行，所需开支也是非常小的。嵌套循环联接的基本度量就是为了准备最终结果集所需要访问的数据块数目。


例：
select empno, ename, loc 
from emp, dept
where emp.deptno = dept.deptno;


这个查询处理过程如下伪代码：
for each row in (select empno, ename, deptno from emp) loop
  for (select dname, loc from dept where deptno = outer.deptno) loop
    if match then pass the row on to the next step
    if inner join and no match then discard the row
    if outer join and no match set inner column values to null and pass the row on to the next step
  end loop;
end loop;




排序-合并联接
排序-合并联接独立地读取需要联接的两张表，对每张表中满足 where 子句的数据行按照联结键进行排序，然后对排序后的数据行集进行合并。对这种联接方法来说，排序的开销是最大的，但是一旦数据行集排序完成了，合并的过程是非常快的。为了进行合并，数据库轮流操作两个列表，比较最上面的数据行，丢弃在排序队列中比另一列表中的最上面一行出现的早的数据行，并只返回匹配的行。


例：
select empno, ename, loc 
from emp, dept
where emp.deptno = dept.deptno;


这个查询处理过程如下伪代码：
select empno, ename, deptno from emp order by deptno
select dname, loc, deptno from dept order by deptno
compare the rowsets and return rows where deptno in both lists match
for an outer join, compare the rowsets and return all rows from the first list 
setting column values for the other table to null


排序-合并联接一般最适合于数据筛选条件有限并返回有限数据行的查询。如果没有可用的更直接访问数据的索引时，排序-合并联接也通常是较好的选择。总而言之，在条件为非等式的时候排序-合并联接通常是最好的选择。如果数据行源非常大，排序-合并联接就可能是唯一可行的选择。




散列联接（hash join）
散列联接首先应用 where 子句中的筛选标准来独立地读取要进行联结的两个表。基于表和索引的统计信息，被确定为返回最少行数的包被完全散列化到内存中。这个散列化了的表包含了原表的所有数据行并被基于将联结键转化为散列值的随机行数载入到散列桶中。只要有足够的内存空间，这个散列表将一直放在内存中，然而如果没有足够的内存，散列表将会被写入到临时磁盘空间。
下一步就是读取另一个较大的表，并对联结键列应用散列函数。然后利用这个散列值对较小的在内存中的散列表进行探测以寻找匹配的第一个表的行数据所在的散列桶。每个散列桶都有一个放在其中的数据散列表（通过位图来表示）。这个列表被用来与探测行进行匹配。如果匹配成功则返回一行数据，否则丢弃。较大的表只读取一次，并检查其中的每一行来寻找匹配。这与嵌套循环联结的不同之处在于此处内层表被多次读取。因此事实上在这个例子中，较大的表是驱动表，因此仅读取一次，而较小的散列表则被探测很多次。与嵌套回路联结执行计划不同，在执行计划的输出中较小的散列表放在前面而较大的探测表放在后面。


例：
select empno, ename, loc 
from emp, dept
where emp.deptno = dept.deptno;


这个查询处理过程如下伪代码：
determine the smaller row set, or in the case of an outer join, use the outer joined table
select dname, loc, deptno from dept
hash the deptno column and build a hash table
select empno, ename, deptno from emp
hash the deptno column and probe the hash table
if match made, check bitmap to confirm row match 
if no match made, discard the row


决定哪个表是最小的不仅取决于数据行数，还取决于这些行的大小，因为整个行都必须要存放在散列表中。当数据行源较大并且结果集也比较大的情况下，将更倾向于考虑散列联接。或者，如果要联结的其中一张表确定总是返回同一数据行源，也很可能会选用散列联结，因为这样仅访问一次这张表。最后，如果较小的表可以放到内存中，散列联结也会很受欢迎。
散列联结仅在相等联结的情况下才能进行。




笛卡尔联结
笛卡尔联结发生在当一张表中的所有行与另一张表中的所有行联结的时候。因此，这种联接所得到的结果集总是=rows table A * rows table B。通常当连接条件被忽略或忽视以致没有指定的联结列，所能做的唯一可能的运算就是将一张表中的所有内容与另一张表中的所有内容联结起来的时候会进行笛卡尔联结。




例：
select empno, ename, loc 
from emp, dept;


这个查询处理过程如下伪代码：
determine the table
select dname, loc, deptno from dept
select empno, ename, deptno from emp
for each row in dept match it to every row in emp retaining all rows

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30168575/viewspace-1592084/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30168575/viewspace-1592084/