oracle nl_sj,oracle连接与半连接

http://blog.sina.com.cn/s/blog_7c5a82970101ix2l.html

表关联方式

11g官方文档解释

表连接类型包括

表关联方法

表连接的类型和方法具体如何分配，表连接的方法和类型是相似的，所以会出现表连接的类型与方法出现huixiao

连接

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