《海量数据库解决方案》学习笔记：表连接

表的连接方式：

         嵌套循环连接（NestedLoops Join）

         排序合并连接（SortMerge Join）

         哈希连接（HashJoin）

         半连接（Semi Join）

         笛卡儿连接（CartesianJoin）

         外连接（OuterJoin）

         索引连接（IndexJoin）

参考学习以及引用的文章：

http://space.itpub.net/17203031/viewspace-697012

http://www.oracleblog.org/study-note/study-hash-join/

http://dxswzj.iteye.com/blog/1554273

1.嵌套循环连接（Nested Loops Join）

a.执行步骤：

         确定一个驱动表（outertable）,另一个表为inner table，以驱动表中的每一行数据为基准，不断的与inner表中查找满足连接条件的对应的行。

b.适用情况：

         适用于驱动表的记录集比较小（小于1万行），而且inner表需要有有效的访问方法（Index）。

d.优化：

如下：

select count(*) from obj1;

23638

select count(*) from obj2;

23184

 

找出obj1表中object_name的前面部分与obj2表的object_name相同的记录

SQL> set timing on

SQL> set autot trace exp stat

SQL> selecta.*,b.object_name

    from obj1 a, obj2 b

    where substr(a.object_name,1,length(b.object_name))=b.object_name;

优化成：

SQL>select a.*,b.object_name

from obj1 a, obj2 b

where substr(a.object_name,1,length(b.object_name))=b.object_name

andsubstr(a.object_name,1,4)=substr(b.object_name,1,4)

and length(b.object_name)>=4

union all

select a.*,b.object_name

from obj1 a,obj2 b

wheresubstr(a.object_name,1,length(b.object_name))=b.object_name

and length(b.object_name)<=3;

时间由  00: 03: 27.44变为00: 00:07.85，当然还可进一步优化，比如索引，这次优化是将nested loop join变为hash join。

具体执行计划参见：nested loop join tuning

2.排序合并连接（Sort Merge Join）

排序合并连接的最大特征是在扫描的同时执行连接，而不是像嵌套循环连接那样为了执行表连接而需要按随机方式读取数据。排序合并连接的主要目的是为了缩减在嵌套循环连接中发生的随机读取量。

由于排序是在内存中进行的，所以效率会随着排序区（Sort Area Size）大小的不同而不同。

在连接条件中主要使用的是LIKE、BETWEEN、>、>=、<、<=，而不是“=”比较运算条款的情况下，该方式比嵌套循环有效。若使用非“=”比较运算符，则哈希连接无法被使用。

3.哈希连接（Hash Join）

a.实现：

         hashJoin的基本算法是在内存中建立hash table，小表叫做build input，理想状态下，build input在内存中；大表叫做probe input.

         在实际的情况下，buildinput不一定能完全放在内存中，此时，和probe input一样，build input的溢出部分，会在磁盘上用hash函数分割成小的不连续的分区。

         HashJoin分2个阶段完成：

1.      parttitioning阶段：即在内存中存放buildinput，若放不下，则和probe input一样，磁盘上利用hash函数将input分割成小的不连续的分区。

2.      join阶段：在相同的键值上，将buildinput和probe input的分区进行一一配对，并且join.

 

 

b.适用情况：

         适用于记录集比较大的情况。

特征：

         哈希函数最基本的特征就是利用了哈希函数。而且以分区(Partition)为单位进行处理，所以即使在海量数据中，也仍然能够获得较快的执行速度。

哈希连接的最大好处就是摆脱了对海量数据处理时的随机读取和排序操作。

c.关于HASH

         得用哈希函数的过程称为“Hashing”，它的主要功能就是把任意长度的数据转换为固定的字符序列。利用转换后的固定长度字符序列寻找某个数据比利用原来的数据寻找数据更加有效。

4.半连接（semi join）

半连接与普通表连接相似，是在使用了子查询的情况下，所实现的子查询与主查询之间的连接。

5. 笛卡尔链接 (CARTESIAN JOIN)

笛卡尔链接是指在SQL语句中没有写出表链接的条件，优化器把第一个表的每一条记录和第二个表的所有记录相链接。如果第一个表的记录数为m，第二个表的记录数为n，则会产生m×n条记录数。

下面的查询，未指名链接条件，就会产生笛卡尔链接。

SQL>select a.user_name,b.dev_no 

from user_info a ,dev_info b;

由于笛卡尔链接会导致性能很差的SQL，因此一般也很少用到。

6.外连接（Outer Join）

         外连接是指以将要连接的两个表中的某个表为基准，即使连接时另一个对象表中没有找到对应的行，也同样要返回基准表中所有行的一种连接方式。

         还有一种就是两边都发生外连接的情况，在这种情况下以两边的表为基准。

7.索引连接（index join）

         索引连接是指在某个查询语句中所使用到的某个表列存在一个以上的索引时，按照哈希连接的方式将这些索引连接起来的方法。