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 |