oracle连接种类整理

叁歲伎倆 2022-08-05 00:46 246阅读 0赞

ORACLE多表连接分为三大类：NEXT LOOP、SORT MERGE、HASH JOIN。

每一类又分为三小类，有传统连接，Semi Join， Anti Join。（后两种叫做半连接）

**NEST LOOP方式：**

有两个表，驱动表Driving Table，被驱动表Driven Table。

驱动表做一次遍历，被驱动表做多次遍历。

返回第一条记录速度很快，不需要排序。

可以使用非等值连接。

**SORT MERGE方式：**

两个表地位一样。每个表都要先排序，然后进行合并，返回记录集。

排序首先在内存中进行，能在内存中完成的叫做Optimal Sort，也叫In-Memory Sort。如果需要借助磁盘缓冲，叫做外部排序External Sort。

在外部排序中，运行run是指一次对磁盘做IO。

如果一次输入就能完成整个数据集的排序叫做1路排序1-Pass Sort。需要多次输入输出操作的叫多路排序Multi-Pass Sort。

从性能角度来看Optimal Sort>1-Pass Sort>Multi-Pass Sort

执行计划中

OMem：代表使用Optimal排序需要的内存估量。

1Mem：代表使用1-Pass排序需要的内存估量。

O/1/M：代表实际Optimal、1-Pass、Multi-Pass方式的执行次数。

**HASH JOIN方式：**

一个驱动表，一个被驱动表。过程有两个阶段：

准备阶段：对驱动表的连接字段进行哈希操作，产生一系列的Hash Bucket（哈希桶）

探测阶段：依次上去被驱动表每条记录，对连接字段执行相同哈希函数，和驱动表哈希桶进行匹配，这个过程叫探测（Probe）。

**a外连接（****LEFT/RIGHT/FULL OUTER JOIN）**

**b内连接（inner join）：显示关联上的2表都有的信息，不像外连接偏向显示某个表全部数据**

**c自连接**

select c1.cno,c1.coursename,c1.pno,c2.coursename  from courses c1,courses c2 where c1.pno=c2.cno

**d自然连接(NATURAL JOIN)**：指明了两表进行自然连接，并且连接是基于两表中所有同名字段的。

**selectempno,ename,loc,deptno from emp natural join dept**

注意：最好不要使用这种，因为当修改表结构的时候相关的sql也需要修改

**e笛卡尔连接(CROSS JOIN)**：a\*b

**f半连接（SEMI join or ****Anti join****)****:****in 与 exists方式的关联SEMI join,**

## Anti-join当使用not exists/not in的时候会用到，两者在处理null值的时候会有所区别 ##

**类似NEST LOOP方式，对主查询每条记录都执行一次子查询，在执行计划中叫做FILTER。**

HINT：

<table style="margin:0px; padding:0px; color:rgb(70,70,70); font-family:simsun; font-size:14px"> 
 <tbody> 
 <tr> 
 <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> 操作 </td> 
 <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> Nest Loop </td> 
 <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> Hash Join </td> 
 <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> Sort Mereg </td> 
 </tr> 
 <tr> 
 <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> Join </td> 
 <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> USE_NL </td> 
 <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> USE_HASH </td> 
 <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> USE_MERGE </td> 
 </tr> 
 <tr> 
 <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> Anti Join </td> 
 <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> NL_AJ </td> 
 <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> HASH_AJ </td> 
 <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> MERGE_AJ </td> 
 </tr> 
 <tr> 
 <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> Semi </td> 
 <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> NL_SJ </td> 
 <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> HASH_SJ </td> 
 <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> MERGE_SJ </td> 
 </tr> 
 </tbody> 
</table>