The NOT EXISTS and NOT IN Constructs
我们将讨论如何评估NOT EXISTS ,NOT IN ,使用Oracle的反连接访问路径的先决条件,以及影响反连接查询优化提示。 然后我们将 看看 反连接 访问路径如何 可以用来做 一些查询 更有效的 几个例子 。
NOT EXISTS and NOT IN 用来检索表中的行为不匹配另一个表中的数据。 在早期,Oracle将使用一个NOT EXISTS and NOT IN 子查询。 对于每个候选行的查询的主体,Oracle将执行子查询。 如果子查询检索一行或多行,然后在候选行会被丢弃。 从概念上讲 这个方法看起来 类似于 嵌套循环的 反连接 。
//not in和not exists也是根据第一个表的数据,在第二个表中进行匹配,只不过是一旦有一个以上的匹配,第一个表的数据就不会显示。
不同于in和exists(两者相同),not in和not exists在处理null值上不同。如果not in返回至少一个null,那么not in就返回flase,第一个表的数据行就不会显示。
对于not exists,只是关心是否返回行,因此返回的无论是null还是非null,都是true,只要有数据返回。
显示没有员工的部门信息。
SELECT D.deptno, D.dname
FROM dept D
WHERE NOT EXISTS
(
SELECT 1
FROM emp E
WHERE E.deptno = D.deptno
)
ORDER BY D.deptno;
Written with a NOT IN clause, the query becomes:
SELECT D.deptno, D.dname
FROM dept D
WHERE D.deptno NOT IN
(
SELECT E.deptno
FROM emp E
)
ORDER BY D.deptno;
这是一个无关子查询,如果子查询中有null返回,那么这个查询的返回总是空,不会返回任何数据。
但是not exists可以正确返回没有职员的部门。
因此在使用not in的时候一定要注意。
使用not in还是not exists,不仅仅是功能上不同,而且在性能上也有很大的不同。
NOT IN会骗过子查询中所涉及表的索引。
//如果子查询中的列上有not null约束,或者查询中有where子句(去除了null),那么not in可以走索引,效果和not exists效果一样。这一点很重要。
如果我们想写的查询显示空的部门在条款,给出相同的结果作为一个不存在的版本不,我们需要添加一个谓词来查询,排除空值。结果查询:
SELECT D.deptno, D.dname
FROM dept D
WHERE D.deptno NOT IN
(
SELECT E.deptno
FROM emp E
WHERE E.deptno IS NOT NULL
)
ORDER BY D.deptno;
确定是使用not exists还是使用not in?关键是看你要怎样处理null值。
如果对于null值,你总是希望返回false,那么应该使用not in。
否则,就可以使用not exists,或者使用not in(需要使用where去除null;或者加上not null约束)。
因此,你必须先考虑怎么来处理空值。 如果你需要在子查询一个空值的存在使表达都不成立,那么你需要使用不构成。 否则 你们 之间的选择 是不存在的 , 不在 ( 与额外的 谓词 如果子查询 会 返回一个空 值 的能力 ) 。
.如果你不需要关注null值的处理。那么你就应该关注是nested还是hash,或者sort merge。
对于第一个表的谓词列,选择性很高,子查询的表的连接列上有选择性很高的索引的时候,系统会优先选择nested loop。
第一个表的谓词选择性很低,第二个表中的连接列的选择性很低,那么系统只能选择hash或者merge。
如果我们不想使用filter、nested loops anti-join,那么我们可以多考虑使用not in,而不是not exists。但是有时候,oracle可能会更加喜欢not exists。
总之是出了问题要看执行计划。
通常我们不想使用filter,但是有可能filer的效果更好,因为filter在处理子查询的时候,当碰到第一个匹配的结果的时候,就结束这一次的子查询,进行下一个。因此效果可能也挺好,具体还是要看执行统计信息。
如果执行计划不是很准,就进行trace,然后tkprof,这一个很好的习惯,特别是在具体分析sql语句的时候。
我们在c/s的时候经常使用,在b/s中,照样可用。
1、必须首先确认业务上需要如何处理null值,然后确定使用not in还是not exists。
2、下一步就是这个子查询是否受益于merge、hash join,那么你可能需要使用not in。
3、如果你希望使用not in,但是不希望因为null的问题导致结果是false,那么你要确认返回的子查询结果没有null值。
4、如果感觉不能受益于merge、hash join,而且也不需要not in的特殊语义,那么可以选择使用not exists。
这个时候,oracle可能会选择使用效率更高的filter,而不是nested loops anti-join。
我们将讨论如何评估NOT EXISTS ,NOT IN ,使用Oracle的反连接访问路径的先决条件,以及影响反连接查询优化提示。 然后我们将 看看 反连接 访问路径如何 可以用来做 一些查询 更有效的 几个例子 。
NOT EXISTS and NOT IN 用来检索表中的行为不匹配另一个表中的数据。 在早期,Oracle将使用一个NOT EXISTS and NOT IN 子查询。 对于每个候选行的查询的主体,Oracle将执行子查询。 如果子查询检索一行或多行,然后在候选行会被丢弃。 从概念上讲 这个方法看起来 类似于 嵌套循环的 反连接 。
//not in和not exists也是根据第一个表的数据,在第二个表中进行匹配,只不过是一旦有一个以上的匹配,第一个表的数据就不会显示。
不同于in和exists(两者相同),not in和not exists在处理null值上不同。如果not in返回至少一个null,那么not in就返回flase,第一个表的数据行就不会显示。
对于not exists,只是关心是否返回行,因此返回的无论是null还是非null,都是true,只要有数据返回。
显示没有员工的部门信息。
SELECT D.deptno, D.dname
FROM dept D
WHERE NOT EXISTS
(
SELECT 1
FROM emp E
WHERE E.deptno = D.deptno
)
ORDER BY D.deptno;
Written with a NOT IN clause, the query becomes:
SELECT D.deptno, D.dname
FROM dept D
WHERE D.deptno NOT IN
(
SELECT E.deptno
FROM emp E
)
ORDER BY D.deptno;
这是一个无关子查询,如果子查询中有null返回,那么这个查询的返回总是空,不会返回任何数据。
但是not exists可以正确返回没有职员的部门。
因此在使用not in的时候一定要注意。
使用not in还是not exists,不仅仅是功能上不同,而且在性能上也有很大的不同。
NOT IN会骗过子查询中所涉及表的索引。
//如果子查询中的列上有not null约束,或者查询中有where子句(去除了null),那么not in可以走索引,效果和not exists效果一样。这一点很重要。
如果我们想写的查询显示空的部门在条款,给出相同的结果作为一个不存在的版本不,我们需要添加一个谓词来查询,排除空值。结果查询:
SELECT D.deptno, D.dname
FROM dept D
WHERE D.deptno NOT IN
(
SELECT E.deptno
FROM emp E
WHERE E.deptno IS NOT NULL
)
ORDER BY D.deptno;
确定是使用not exists还是使用not in?关键是看你要怎样处理null值。
如果对于null值,你总是希望返回false,那么应该使用not in。
否则,就可以使用not exists,或者使用not in(需要使用where去除null;或者加上not null约束)。
因此,你必须先考虑怎么来处理空值。 如果你需要在子查询一个空值的存在使表达都不成立,那么你需要使用不构成。 否则 你们 之间的选择 是不存在的 , 不在 ( 与额外的 谓词 如果子查询 会 返回一个空 值 的能力 ) 。
.如果你不需要关注null值的处理。那么你就应该关注是nested还是hash,或者sort merge。
对于第一个表的谓词列,选择性很高,子查询的表的连接列上有选择性很高的索引的时候,系统会优先选择nested loop。
第一个表的谓词选择性很低,第二个表中的连接列的选择性很低,那么系统只能选择hash或者merge。
如果我们不想使用filter、nested loops anti-join,那么我们可以多考虑使用not in,而不是not exists。但是有时候,oracle可能会更加喜欢not exists。
总之是出了问题要看执行计划。
通常我们不想使用filter,但是有可能filer的效果更好,因为filter在处理子查询的时候,当碰到第一个匹配的结果的时候,就结束这一次的子查询,进行下一个。因此效果可能也挺好,具体还是要看执行统计信息。
如果执行计划不是很准,就进行trace,然后tkprof,这一个很好的习惯,特别是在具体分析sql语句的时候。
我们在c/s的时候经常使用,在b/s中,照样可用。
1、必须首先确认业务上需要如何处理null值,然后确定使用not in还是not exists。
2、下一步就是这个子查询是否受益于merge、hash join,那么你可能需要使用not in。
3、如果你希望使用not in,但是不希望因为null的问题导致结果是false,那么你要确认返回的子查询结果没有null值。
4、如果感觉不能受益于merge、hash join,而且也不需要not in的特殊语义,那么可以选择使用not exists。
这个时候,oracle可能会选择使用效率更高的filter,而不是nested loops anti-join。