Oracle的表连接查询及其内部运行机制

23r111

一、内连接

内连接查询操作列出与连接条件匹配的数据行，它使用比较运算符比较被连接列的列值。内连接分三种：

1、等值连接：在连接条件中使用等于号(=)运算符比较被连接列的列值，其查询结果中列出被连接表中的所有列，包括其中的重复列。

2、不等连接： 在连接条件使用除等于运算符以外的其它比较运算符比较被连接的列的列值。这些运算符包括>、>=、<=、<、!>、!<和<>。

3、自然连接：在连接条件中使用等于(=)运算符比较被连接列的列值，但它使用选择列表指出查询结果集合中所包括的列，并删除连接表中的重复列。

二、外连接

返回到查询结果集合中的不仅包含符合连接条件的行，而且还包括左表(左外连接时)、右表(右外连接时)或两个边接表(全外连接)中的所有数据行。

三、交叉连接

交叉连接不带WHERE 子句，它返回被连接的两个表所有数据行的笛卡尔积，返回到结果集合中的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。例，titles表中有6类图书，而publishers表中有8家出版社，则下列交叉连接检索到的记录数将等于6*8=48行。

实例说明：

一、内连接

select *

from [Book] as b,[Student] as s

where b.StudentId=s.StudentId

         等价于如下（也可以不要关键字inner，此为系统默认）

select *

from [Book] as b inner join [Student] as s

ON b.StudentId=s.StudentId

         结果为：

         执行过程

相当于内连接的向右连接。以from  [Book] inner join [Student]等式右边为基准，即以Student表（等式右表，s表）的s.StudentId为基准，遍历Book表（等式左表，Book表）中与之匹配的b.StudentId，然后拼接返回。结果含有重复的列，b.StudentId和s.StudentId。

         说明

这与where b.StudentId=s.StudentId或者s.StudentId=b.StudentId位置没有关系。它仅仅代表满足条件而已，不判定谁为基准。以下外连接，交叉连接相同操作。

二、外连接

1、左外连接

         代码

select *

from [Book] as b left join [Student] as s

ON b.StudentId=s.StudentId

等价于

select *

from [Book] as b left join [Student] as s

ON b.StudentId=s.StudentId(+)

         结果

         执行过程

即以from [Book] left join [Student]的Book表为基准，即以Book表(b表)的b.StudentId为基准。遍历Student表(s表)中与之匹配的b.StudentId。若b.StudentId含有s.StudentId匹配项，则进行拼接，然后遍历Student表的下一条s.StudentId，当查询完毕则进入下一条b.StudentId。若b.StudentId没有相应s.StudentId匹配项时，则显示左表的项，拼接右表的项显示为NULL。

2、右外连接

         代码

select *

from [Book] as b right join [Student] as s

ON b.StudentId=s.StudentId

等价于

select *

from [Book] as b right join [Student] as s

ON b.StudentId(+)=s.StudentId

         结果

         执行过程

即以from [Book] right join [Student]的Student表为基准，即以Student表(s表)的s.StudentId为基准。遍历Book表(b表)中与之匹配的s.StudentId。若s.StudentId含有b.StudentId匹配项，则进行拼接，然后遍历Book表的下一条b.StudentId，当查询完毕则进入下一条s.StudentId。若s.StudentId没有相应b.StudentId匹配项时，则显示右表的项，拼接左表的项显示为NULL。

3、全外连接

         代码

select *

from [Book] as b full outer join [Student] as s

ON b.StudentId=s.StudentId

         结果

         执行过程

即以from [Book] full outer join [Student]中先以Book表进行左外连接，然后以Student表进行右外连接。

三、交叉连接

         代码

select *

from [Book] as b CROSS Join [Student] as a

Order by b.BookId

         结果

         执行过程

即是按照Order排序的Id，把要Join的右表无条件拼接过来。这样依次执行，这样这种记录便为两个表的记录的笛卡尔积。

Oracle数据库系统会使用一种机制，来决定哪一种组合性能最好。这种机制称为基于成本的优化器(Cost-Based Optimization，简称为CBO)，

在查看sql执行计划时，我们会发现表的连接方式有多种，对表的连接方式进行介绍以便更好看懂执行计划和理解sql执行原理。

表的连接：在电信、金融等领域的数据库相关应用中所占比例
Nested Loops Join（嵌套循环连接）70%：返回少量记录
Hash Join（哈希连接）20%：基于吞吐量的，返回大量的数据，但是哈希算法不算排序，由PGA中的HASH_AREA_SIZE来控制。虽然比排序合并更高效一些，但是也有一些限制。
Merge Sort Join（排序合并连接）10%：由PGA中的SORT_AREA_SIZE来控制。


hint : /*+leading(t1) use_nl(t2)*/
use_nl:强制使用嵌套循环连接方式
leading(t1)：强制先访问t1表，t1表作为驱动表，t2表是被驱动表。
在嵌套循环连接方式中，驱动表返回多少条记录，被驱动表就访问多少次。要特别注意驱动表的顺序，小的结果集先访问，大的结果集后访问，才能保证被驱动表的访问次数降到最低，从而提升性能。支持所有SQL连接条件写法，没有任何限制。


hint : /*+leading(t1) use_hash(t2)*/
在哈希连接中：驱动表和被驱动表都只会访问0次或者1次。要特别注意驱动表的顺序，小的结果集先访问，大的结果集后访问，才能保证被驱动表的访问次数降到最低，从而提升性能。哈希连接不支持不等值连接（<>）、不支持<和>的连接方式、也不支持LIKE的连接方式。


hint : /*+leading(t1) use_merge(t2)*/
在合并排序中：根本没有驱动和被驱动的概念，t1表和t2表都只会访问0次或者1次。无论哪张表在前都无妨。排序合并连接不支持不等值连接（<>）、也不支持LIKE的连接方式。但是支持<和>的连接方式。


总结：
嵌套循环连接和哈希连接有驱动顺序，驱动表的顺序不同将影响表连接的性能，而排序合并没有驱动概念，无论哪张表在前都无妨。
除了嵌套循环连接不需要排序之外，排序合并和哈希连接都消耗内存，排序合并需要排序，但是哈希连接不需要排序，消耗内存是用于建立HASH表。排序只取部分字段，消耗的内存就越少。
适用于NL连接的场景：
（1）两表关联返回的记录不多，最佳情况是驱动表的结果集返回1条或少量的及条记录，而被驱动表仅匹配到1条或少量的几条记录，这种情况即便T1表和T2表的记录很大，但是也非常迅速。
（2）遇到不等值查询等导致哈希和排序合并连接被限制使用，不得不使用NL连接。
（3）驱动表的限制条件所在列有索引，被驱动表的连接条件所在列有索引。
hash连接和merge sort连接：连接条件的索引对它们起不到传递作用，索引的连接条件起不到快速检索的作用，但是限制条件如果有合适的索引可以快速检索到少量记录，还是可以提升性能的。
哈希连接：两表关联等值查询，在没有任何索引的情况下，Oracle倾向于走哈希算法，增大PGA中的HASH_AREA_SIZE是优化哈希连接的有效方法。对于内存自动管理的，只要增大PGA就行。
排序合并连接：索引的连接条件虽然没有检索作用，却有消除排序的作用。缺陷：即使在连接条件的两个列都建过索引，却只能消除一张表的排序。增大内存排序区，避免在排序尺寸过大时在磁盘中排序。