表之间的连接

   表之间的连接

表的连接是指在一个SQL语句中通过表与表之间的关联，从一个或多个表检索出相关的数据。连接是通过SQL语句中FROM从句的多个表名，以及WHERE从句里定义的表之间的连接条件来实现的。如果一个SQL语句的关联表超过两个，那么连接的顺序如何呢？ORACLE首先连接其中的两个表，产生一个结果集；然后将产生的结果集与下一个表再进行关联；继续这个过程，直到所有的表都连接完成；最后产生所需的数据。主要包括：a）排序合并连接；b）嵌套循环连接；c）哈希连接；d）笛卡尔积连接。

a） 排序合并连接

1） 判断第一个源表是否已经排序，如果已经排序，则转3），否则转2）；

2） 对第一个源表按照连接关联列进行排序；

3） 判断第二个源表是否已经排序，如果已经排序，则转5），否则转4）；

4） 对第二个源表按照连接关联列进行排序；

5） 对已经排好序的两个源表进行合并操作，并生成最终的结果集。

        在缺乏数据的选择性或者可用的索引时，或者两个源表都过于庞大（所选的数据超过表记录数的5%）时，排序合并连接将比嵌套循环连接更加高效。但是排序合并连接需要较大的临时内存块，以用于排序，这将导致临时表空间占用更多的内存和磁盘I/O。    

SQL> explain plan for SELECT /*+ordered use_merge(e,d) */e.ename,d.deptno FROM lpx_emp e,lpx_dept d 2 WHERE e.deptno = d.deptno; Explained SQL> select * from table(dbms_xplan.display()); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 1209067579 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 14 | 462 | 8 (25)| 00:00:01 | | 1 | MERGE JOIN | | 14 | 462 | 8 (25)| 00:00:01 | | 2 | SORT JOIN | | 14 | 280 | 4 (25)| 00:00:01 | | 3 | TABLE ACCESS FULL| LPX_EMP | 14 | 280 | 3 (0)| 00:00:01 | |* 4 | SORT JOIN | | 4 | 52 | 4 (25)| 00:00:01 | | 5 | TABLE ACCESS FULL| LPX_DEPT | 4 | 52 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("E"."DEPTNO"="D"."DEPTNO") filter("E"."DEPTNO"="D"."DEPTNO") Note PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- ----- - dynamic sampling used for this statement 22 rows selected  

b） 嵌套循环连接

1） 首先根据基于规则RBO或者基于代价CBO的原则，选择两个表中的一个作为驱动表（外部表）。

2） 指定另外一个表为内部表。

3） 从外部表中读取第一行，然后与内部表的数据逐一进行对比，将匹配的记录放到结果集中。

4） 从外部表中读取第二行，再与内部表的数据逐一对比，将匹配的记录添加到结果集中。

5） 重复上述步骤，直到外部表中的所有记录全部处理完毕。

6） 最后产生满足要求的结果集。

使用嵌套循环连接是一种从结果集中提取第一批记录最快速的方法。在驱动行源表(就是正在查找的记录)较小、或者内部行源表已连接的列有惟一的索引或高度可选的非惟一索引时,嵌套循环连接效果是比较理想的。嵌套循环连接比其他连接方法有优势,它可以快速地从结果集中提取第一批记录,而不用等待整个结果集完全确定下来。这样,在理想情况下,终端用户就可以通过查询屏幕查看第一批记录,而在同时读取其他记录。不管如何定义连接的条件或者模式,任何两行记录源可以使用嵌套循环连接,所以嵌套循环连接是非常灵活的。

然而,如果内部行源表(读取的第二张表)已连接的列上不包含索引,或者索引不是高度可选时,嵌套循环连接效率是很低的。如果驱动表的记录非常庞大时,其他的连接方法可能更加有效。

SQL> explain plan for SELECT /*+ordered use_nl(e,d) */e.ename,d.deptno 2 from lpx_emp e,lpx_dept d 3 WHERE e.deptno = d.deptno; Explained SQL> select * from table(dbms_xplan.display()); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 322999853 ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 14 | 462 | 17 (0)| 00:00:01 | | 1 | NESTED LOOPS | | 14 | 462 | 17 (0)| 00:00:01 | | 2 | TABLE ACCESS FULL| LPX_EMP | 14 | 280 | 3 (0)| 00:00:01 | |* 3 | TABLE ACCESS FULL| LPX_DEPT | 1 | 13 | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("E"."DEPTNO"="D"."DEPTNO") Note ----- - dynamic sampling used for this statement 19 rows selected

c） 哈希连接

      当内存能够提供足够的空间时,哈希(HASH)连接是Oracle优化器通常的选择。哈希连接中，优化器根据统计信息，首先选择两个表中的小表，在内存中建立这张表的基于连接键的哈希表；优化器再扫描表连接中的大表，将大表中的数据与哈希表进行比较，如果有相关联的数据，则将数据添加到结果集中。

       当表连接中的小表能够完全cache到可用内存的时候，哈希连接的效果最佳。哈希连接的成本只是两个表从硬盘读入到内存的成本。但是，如果哈希表过大而不能全部cache到可用内存时，优化器将会把哈希表分成多个分区，再将分区逐一cache到内存中。当表的分区超过了可用内存时，分区的部分数据就会临时地写到磁盘上的临时表空间上。因此，分区的数据写磁盘时，比较大的区间（EXTENT）会提高I/O性能。ORACLE推荐的临时表空间的区间是1MB。临时表空间的区间大小由UNIFORM. SIZE指定。

当哈希表构建完成后，进行下面的处理：

1） 第二个大表进行扫描

2） 如果大表不能完全cache到可用内存的时候，大表同样会分成很多分区

3） 大表的第一个分区cache到内存

4） 对大表第一个分区的数据进行扫描，并与哈希表进行比较，如果有匹配的纪录，添加到结果集里面

5） 与第一个分区一样，其它的分区也类似处理。

6） 所有的分区处理完后，ORACLE对产生的结果集进行归并，汇总，产生最终的结果。

当哈希表过大或可用内存有限，哈希表不能完全CACHE到内存。随着满足连接条件的结果集的增加，可用内存会随之下降，这时已经CACHE到内存的数据可能会重新写回到硬盘去。如果出现这种情况，系统的性能就会下降。

当连接的两个表是用等值连接并且表的数据量比较大时，优化器才可能采用哈希连接。哈希连接是基于CBO的。只有在数据库初始化参数HASH_JOIN_ENABLED设为True,并且为参数PGA_AGGREGATE_TARGET设置了一个足够大的值的时候,Oracle才会使用哈希边连接。HASH_AREA_SIZE是向下兼容的参数,但在Oracle9i之前的版本中应当使用HASH_AREA_SIZE。当使用ORDERED提示时,FROM子句中的第一张表将用于建立哈希表。

当缺少有用的索引时，哈希连接比嵌套循环连接更加有效。哈希连接也可能比嵌套循环连接更快，因为处理内存中的哈希表比检索B_树索引更加迅速。

SQL> explain plan for SELECT /*+ordered use_hash(d,e) */e.ename,d.deptno 2 from lpx_emp e,lpx_dept d 3 WHERE e.deptno = d.deptno; Explained SQL> select * from table(dbms_xplan.display()); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 2846336464 ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 14 | 462 | 7 (15)| 00:00:01 | |* 1 | HASH JOIN | | 14 | 462 | 7 (15)| 00:00:01 | | 2 | TABLE ACCESS FULL| LPX_EMP | 14 | 280 | 3 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL| LPX_DEPT | 4 | 52 | 3 (0)| 00:00:01 | ------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("E"."DEPTNO"="D"."DEPTNO") Note ----- - dynamic sampling used for this statement 19 rows selected

d） 笛卡尔积连接

       当两张表进行连接，它们之间没有任何关联条件时，这两张表就会进行笛卡尔乘积连接。笛卡尔乘积连接是使用一个表的每一行依次与另一个表中的所有行匹配。例如：表A和表B做笛卡尔乘积连接，表A有m行记录，表B有n 行记录，则笛卡尔乘积的结果为 m*n条记录。

SQL> explain plan for SELECT e.ename,d.deptno from lpx_emp e,lpx_dept d ; Explained SQL> select * from table(dbms_xplan.display()); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 2944811721 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 56 | 1120 | 8 (0)| 00:00:01 | 1 | MERGE JOIN CARTESIAN| | 56 | 1120 | 8 (0)| 00:00:01 | 2 | TABLE ACCESS FULL | LPX_DEPT | 4 | 52 | 3 (0)| 00:00:01 | 3 | BUFFER SORT | | 14 | 98 | 5 (0)| 00:00:01 | 4 | TABLE ACCESS FULL | LPX_EMP | 14 | 98 | 1 (0)| 00:00:01 -------------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement 15 rows selected

三种连接方式的比较：

 

 

 

 

 

类别	嵌套循环连接	排序合并连接	哈希连接
优化器提示	USE_NL	USE_MERGE	USE_HASH
使用的条件	任何连接	主要用于不等价连接，如<、<=、>、>=; 但是不包括 <>	仅用于等价连接
相关资源	CPU、磁盘I/O	内存、临时空间	内存、临时空间
特点	当有高选择性索引或进行限制性搜索时效率比较高，能够快速返回第一次的搜索结果。	当缺乏索引或者索引条件模糊时，排序合并连接比嵌套循环有效。	当缺乏索引或者索引条件模糊时，哈希连接连接比嵌套循环有效。通常比排序合并连接快。 在数据仓库环境下，如果表的纪录数多，效率高。
缺点	当索引丢失或者查询条件限制不够时，效率很低； 当表的纪录数多时，效率低。	所有的表都需要排序。它为最优化的吞吐量而设计，并且在结果没有全部找到前不返回数据。	为建立哈希表，需要大量内存。第一次的结果返回较慢。