oracle连接种类整理

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：

操作

Nest Loop

Hash Join

Sort Mereg

Join

USE_NL

USE_HASH

USE_MERGE

Anti Join

NL_AJ

HASH_AJ

MERGE_AJ

Semi

NL_SJ

HASH_SJ

MERGE_SJ