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）。

　　几种方式比较：

　　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 Mereg

　　Join

　　USE_NL

　　USE_HASH

　　USE_MERGE

　　Anti Join

　　NL_AJ

　　HASH_AJ

　　MERGE_AJ

　　Semi

　　NL_SJ

　　HASH_SJ

　　MERGE_SJ