ORACLE多表连接

• 多表连接简述

ORACLE多表连接分为三大类：NEXT LOOP、SORT MERGE、HASH JOIN。
每一类又分为三小类，有传统连接，Semi Join， Anti Join（后两种叫做半连接）
（1）NEST LOOP方式：有两个表，驱动表Driving Table，被驱动表Driven Table。驱动表做一次遍历，被驱动表做多次遍历。返回第一条记录速度很快，不需要排序。
可以使用非等值连接。
（2）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方式的执行次数。
（3）HASH JOIN方式：
一个驱动表，一个被驱动表。过程有两个阶段：
准备阶段：对驱动表的连接字段进行哈希操作，产生一系列的Hash Bucket（哈希桶）
探测阶段：依次上去被驱动表每条记录，对连接字段执行相同哈希函数，和驱动表哈希桶进行匹配，这个过程叫探测（Probe）。

• 几种方式比较

ORACLE实现排序都是用二叉树插入排序算法（Binary Insertion Tree）。
内存中的INDEX中，每个节点对应一条记录，每个节点还保存一个父节点和两个子节点的指针。这样在32位系统中，这个开销是12字节，64位系统中，这个开销是24字节。
排序过程是内存和CPU的双重密集操作。
完全内存排序有时候不必磁盘排序快。
如果CPU是资源瓶颈，IO比较空闲，应该减少排序空间大小，使用1-Pass Sort。尤其是在创建索引时，通过减少SORT_AREA_SIZE来提升性能。因为内存排序和磁盘排序，记录比较操作相差不大，但是内存排序中，二叉树可能过高，CPU资源消耗太大。
HASH JOIN内存消耗远小于SORT MERGE，也不需要密集的CPU操作。所以HASH JOIN算法普遍优于SORT MERGE算法。
如果查询关注的是整个记录而不畅部分记录时，HASH JOIN非常类似NEST LOOP，但优于NEST LOOP，因为HASH TABLE构建在PGA中，不需要LATCH保护。
半连接
是针对IN, EXISTS, NOT IN, NOT EXISTS的变形。
子查询在FROM里的叫做IN-LINE VIEW，在WHERE子句中的叫NESTED SUBQUERY（嵌套子查询）。IN, EXISTS, NOT IN, NOT EXISTS都属于嵌套子查询。
对于嵌套子查询，ORACLE处理有两种方式：展开子查询，不展开子查询。
对于嵌套视图，ORACLE处理方法有两种，合并，不合并。
ORACLE 10G以前的优化器会在Optimization之前就展开，不做成本评估。
In、Exists展开结果是变成Semi-Join。Not Exists和Not In是转换成Anti-Join。
对于Inline-View或者其它View，Oracle也会尝试合并到主查询中，这个动作叫做Merge，对应hint是和。这个在执行计划中进行确认就可以。也就是，如果没有VIEW字样，就是发生了MERGE合并；有VIEW字样，就是没有做MERGE合并。
对于子查询展开，这个过程叫做Subquery Unnesting。
Merge和Unnest不同的地方是，对于Distinct、Group by这些子句，Merge可以合并，叫做Complex View Merge，Set和Unnest一样，不能合并。
缺省时，不进行Complex View Merge。使用才能达到Merge效果。
子查询合并到主查询中，好处是优化器可以通判考虑访问路径方式。否则，ORACLE只能针对外层内存查询分别优化。而且可以利用ORACLE提供的Semi-Join、Anti-Join两种连接方式。
不是所有子查询都可以展开，例如，connect by, start with, rownum伪列, set操作符（UNION、UNION ALL、MINUS、INTERSECT）、聚集函数（SUM、COUNT、GROUP BY）不会被展开。
半连接关注重点在于：对于外表某个记录，在内表中找到一个匹配记录就返回外表记录。
不展开查询：
类似NEST LOOP方式，对主查询每条记录都执行一次子查询，在执行计划中叫做FILTER。
ORACLE 10G中，使用提示。（这个是非半连接）
SQL>SELECT ID
FROM A
WHERE EXISTS ( SELECT 1 FROM B WHERE A.ID=B.P_ID);
展开子查询：
SQL>SELECT ID
FROM A
WHERE EXISTS ( SELECT 1 FROM B WHERE A.ID=B.P_ID);
此时执行计划中，会看到HASH JOIN SEMI字样，说明这是一个半连接。
好处是：
对于A表中一条记录，发现B中匹配一条就停止扫描B，转而处理A的下一条记录。
返回结果无需去重，即使A和B记录时1：n，表A每个记录只会返回一次。
从ORACLE 9i开始IN和EXISTS已经没有区别了，执行计划是一样的。
SEMI JOIN的HINT如下：
EXISTS：
SQL>SELECT ID 
FROM A
WHERE EXISTS (SELECT 1 FROM B WHERE A.ID=B.P_ID);
IN：
SQL>SELECT ID FROM  A  WHERE IN (SELECT 1 FROM B WHERE A.ID=B.P_ID);
NOT EXISTS：默认就使用展开的ANTI-JOIN
SQL>SELECT ID FROM A WHERE NOT EXISTS (SELECT 1 FROM B WHERE A.ID=B.P_ID);
NOT IN：
与NOT EXISTS的区别在于处理NULL
NOT IN查看子结果中有没有NULL，如果有NULL，返回FALSE；NOT EXISTS不关心有没有NULL，只关心记录数，如果有记录，返回FALSE。
NOT IN可能在匹配列上，引起性能问题，原因是索引失效。
HINT：

操作	Nest Loop	Hash Join	Sort Merge
join	USE_NL	USE_HAS	USE_MERGE
anti join	NL_AJ	HASH_AJ	MERGE_AJ
semi	NL_SJ	HASH_SJ	MERGE_SJ