Oracle反连接和外连接中NESTED LOOPS无法更改驱动表
Oracle反连接和外连接中NESTED LOOPS无法更改驱动表
先说反连接，现有SQL如下：
select t.* from t where t.col not in (select /*+ nl_aj */ tt.col from tt where tt.col is not null) and t.col is not null; Plan hash value: 1434981293 ------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 17 | | 1 | NESTED LOOPS ANTI | | 1 | 1 | 1 |00:00:00.01 | 17 | |* 2 | TABLE ACCESS FULL| T | 1 | 2 | 2 |00:00:00.01 | 7 | |* 3 | TABLE ACCESS FULL| TT | 2 | 2 | 1 |00:00:00.01 | 10 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("T"."COL" IS NOT NULL) 3 - filter(("TT"."COL" IS NOT NULL AND "T"."COL"="TT"."COL"))
id=3是对以对主表T做全表扫描应用谓词条件得到数据作为结果集1，然后将结果集1中依次每行数据根据条件(t.col=tt.col and tt.col is not null)来匹配表TT的数据，当一旦只要在表TT有一行满足(t.col=tt.col and tt.col is not null)的时候，表T的这行数据就是不要的会直接遗弃然后继续对下一行进行同样的流程一直到结果集1全部数据都进行一遍，这是“NESTED LOOPS ANTI”过程的简单描述。
反证法：假设现在表TT作为驱动表，我虚拟构造一个执行计划如下：
先执行id=3对表TT全表扫描并过滤出tt.col is not null的数据作为结果集1，然后将结果集1中依次每行数据根据条件(tt.col=t.col and t.col is not null)来匹配表T的数据，当有满足条件条件(tt.col=t.col and t.col is not null)的时候，证明匹配的表T（注意不是表TT）的那一批满足条件的数据都是不要的，那除了这一批数据以外表T剩下的其他数据呢？你无法判断剩余的其他数据是否会和结果集1还没进行匹配的行能否匹配的上，因此表TT作为驱动表的算法是无法进行下去的。
虚假的执行计划： -------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | -------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 16 | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 16 | | 2 | NESTED LOOPS RIGHT ANTI | | 1 | 1 | 1 |00:00:00.01 | 16 | |* 3 | TABLE ACCESS FULL | TT | 1 | 2 | 2 |00:00:00.01 | 6 | |* 4 | TABLE ACCESS FULL | T | 2 | 2 | 1 |00:00:00.01 | 10 | --------------------------------------------------------------------------------------------
另外，再《基于Oracle的SQL优化》一书中P58明确提出，反连接中主表一定是驱动表，次表为被驱动表。
也可以换另外一个角度看，反连接满足一定条件是可以改写为left join形式的，比如上边的：select t.* from t where t.col not in (select /*+ nl_aj */ tt.col from tt where tt.col is not null) and t.col is not null;
可以改写为select /*+ use_nl(tt) */ t.* from t left join tt on t.col = tt.col where tt.col is null and t.col is not null;
这个时候以left join角度看也可以得出驱动表一定是表T。
关于left join，且看下边。
说说外连接，以左连接为例子，现有SQL如下：
右边强制表TT做驱动表，但是无法生效。
强制是不生效的: select /*+ use_nl(tt) */ t.col,tt.col from t left join tt on t.col=tt.col; select /*+ leading(tt) use_nl(t tt) */ t.col,tt.col from t left join tt on t.col=tt.col; Plan hash value: 3672464008 Plan hash value: 3672464008 ------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 3 |00:00:00.01 | 25 | | 0 | SELECT STATEMENT | | 1 | | 3 |00:00:00.01 | 25 | | 1 | NESTED LOOPS OUTER| | 1 | 3 | 3 |00:00:00.01 | 25 | | 1 | NESTED LOOPS OUTER| | 1 | 3 | 3 |00:00:00.01 | 25 | | 2 | TABLE ACCESS FULL| T | 1 | 3 | 3 |00:00:00.01 | 7 | | 2 | TABLE ACCESS FULL| T | 1 | 3 | 3 |00:00:00.01 | 7 | |* 3 | TABLE ACCESS FULL| TT | 3 | 1 | 1 |00:00:00.01 | 18 | |* 3 | TABLE ACCESS FULL| TT | 3 | 1 | 1 |00:00:00.01 | 18 | ------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): Predicate Information (identified by operation id): --------------------------------------------------- --------------------------------------------------- 3 - filter(("TT"."COL" IS NOT NULL AND "T"."COL"="TT"."COL")) 3 - filter(("TT"."COL" IS NOT NULL AND "T"."COL"="TT"."COL"))
为什么表TT无法强制作为驱动表？
其实很好理解，从外连接的定义看就知道了，外连接（Outer Join）是对内连接的一种拓展，它是指表连接的连接结果除了包含那些完全满足连接条件的记录之外还会包含驱动表中所有不满足该连接条件的记录。
而外连接就包含了左连接，防偷以上边例子看，最终表T的数据是全部都会出现再最终结果里边，那么表T肯定是全表扫描来最终一次性获取到整表的数据。
若是用表TT做驱动表，会产生多次对表T的全表扫描外，最终主表T的不满足连接条件的记录如何界定？毕竟满足条件的也是表TT的结果集中一条一条根据tt.col=t.col找出来而不是一次性的。
而且，从定义里边已经规定了驱动表必须就是表T了。
