oracle中关于in和existsnot in 和 not existsWord文档下载推荐.docx

oracle中关于in和existsnot in 和 not existsWord文档下载推荐.docx

《oracle中关于in和existsnot in 和 not existsWord文档下载推荐.docx》由会员分享，可在线阅读，更多相关《oracle中关于in和existsnot in 和 not existsWord文档下载推荐.docx（9页珍藏版）》请在冰豆网上搜索。

select*fromAwhereexists（selectccfromBwherecc=A.cc）

效率高，用到了B表上cc列的索引。

相反的

2：

select*fromBwhereccin（selectccfromA）

效率高，用到了B表上cc列的索引；

select*fromBwhereexists（selectccfromAwherecc=B.cc）

效率低，用到了A表上cc列的索引。

notin和notexists

如果查询语句使用了notin那么内外表都进行全表扫描，没有用到索引；

而notextsts的子查询依然能用到表上的索引。

所以无论那个表大，用notexists都比notin要快。

一直听到的都是说尽量用exists不要用in，因为exists只判断存在而in需要对比值，所以exists比较快，但看了看网上的一些东西才发现根本不是这么回事。

下面这段是抄的

Select*fromT1wherexin（selectyfromT2）

执行的过程相当于:

select*

fromt1,（selectdistinctyfromt2）t2

wheret1.x=t2.y;

select*fromt1whereexists（selectnullfromt2wherey=x）

forxin（select*fromt1）

loop

if（exists（selectnullfromt2wherey=x.x）

then

OUTPUTTHERECORD

endif

endloop

从我的角度来说，in的方式比较直观，exists则有些绕，而且in可以用于各种子查询，而exists好像只用于关联子查询（其他子查询当然也可以用，可惜没意义）。

由于exists是用loop的方式，所以，循环的次数对于exists影响最大，所以，外表要记录数少，内表就无所谓了，而in用的是hashjoin，所以内表如果小，整个查询的范围都会很小，如果内表很大，外表如果也很大就很慢了，这时候exists才真正的会快过in的方式。

也就是说，in和exists需要具体情况具体分析，notin和notexists就不用分析了，尽量用notexists就好了。

典型的连接类型共有3种：

排序--合并连接（SortMergeJoin（SMJ））

嵌套循环（NestedLoops（NL））

哈希连接（HashJoin）

嵌套循环和哈希连接的算法还是有不同，在理论上哈希连接要快过排序和nl，当然实际情况比理论上有复杂的多，不过两者还是有差异的.

1关联子查询与非关联子查询

关联子查询需要在内部引用外部表，而非关联子查询不要引用外部表。

对于父查询中处理的记录来说，一个关联子查询是每行计算一次，然而一个非关联子查询只会执行一次，而且结果集被保存在内存中（如果结果集比较小），或者放在一张oracle临时数据段中（如果结果集比较大）。

一个“标量”子查询是一个非关联子查询，返回唯一记录。

如果子查询仅仅返回一个记录，那么oracle优化器会将结果缩减为一个常量，而且这个子查询只会执行一次。

/*select*fromempwheredeptnoin（selectdeptnofromdeptwheredept_name='

admin'

）;

*/

2.如何选择？

根据外部查询，以及子查询本身所返回的记录的数目。

如果两种查询返回的结果是相同的，哪一个效率更好？

关联子查询的系统开销：

对于返回到外层查询的记录来说，子查询会每次执行一次。

因此，必须保证任何可能的时候子查询都要使用索引。

非关联子查询的系统开销：

子查询只会执行一次，而且结果集通常是排好序的，并保存在临时数据段中，其中每一个记录在返回时都会被父级查询引用，在子查询返回大量记录的情况下，将这些结果集排序回增大系统的开销。

所以：

如果父查询只返回较少的记录，那么再次执行子查询的开销不会非常大，如果返回很多数据行，那么直查询就会执行很多次。

如果子查询返回较少的记录，那么为内存中保存父查询的结果集的系统开销不会非常大，如果子查询返回多行，那么需要将结果放在临时段上，然后对数据段排序，以便为负查询中的每个记录服务。

3结论：

1）在使用一个关联子查询是，使用in或者exists子句的子查询执行计划通常都相同

2）exists子句通常不适于子查询

3）在外部查询返回相对较少记录时，关联子查询比非关联子查询执行得要更快。

4）如果子查询中只有少量的记录，则非关联子查询会比关联子查询执行得更快。

4子查询转化：

子查询可以转化为标准连接操作

1）使用in的非关联子查询（子查询唯一）

条件：

1）在整个层次结构中最底层数据表上定义唯一主键的数据列存在于子查询的select列表中

2）至少有个定义了唯一主键的数据列在select列表中，而且定义唯一主键的其他数据列都必须有指定的相等标准，不管是直接指定，还是间接指定。

2）使用exists子句的关联子查询

对于相关条件来说，该子查询只能返回一个记录。

5。

notin和notexists调整

1）notin非关联子查询：

转化为in写法下的minus子句

2）notexists关联子查询：

这种类型的反连接操作会为外部查询中每一个记录进行内部查询，除了不满足子查询中where条件的内部数据表以外，他会过滤掉所有记录。

可以重写：

在一个等值连接中指定外部链接条件，然后添加selectdistinct

eg:

selectdistinct...froma,bwherea.col1=b.col1（+）andb.col1isnull

6。

在子查询中使用allany

1. 

简介

本文简要介绍了关联子查询、非关联子查询、IN&

EXISTS、NOTIN&

NOTEXISTS之间的区别；

同时对不同数据库版本下CBO对IN&

EXISTS&

NOTIN&

NOTEXISTS的处理做了一定的阐述。

2. 

os、数据库版本以及测试数据

os：

windows2000serversp4

db：

oracle10.1.0.2

settimeon

settimingon

droptableouter_large_t

/

createtableouter_large_t

（idnumber,

c1varchar2（100）,

c2varchar2（100）

）

createindexidx_outer_large_tonouter_large_t（id）

droptableouter_small_t

createtableouter_small_t

asselect*fromouter_large_t

where1=2

createindexidx_outer_small_t_idonouter_small_t（id）

droptableinner_large_t

createtableinner_large_t

c3varchar2（100）,

c4varchar2（100）

createindexidx_inner_large_t_1oninner_large_t（id,c3）

droptableinner_small_t

createtableinner_small_t

createindexidx_inner_small_toninner_small_t（id,c3）

3. 

2.关联子查询和非关联子查询

测试数据:

truncatetableouter_large_t

truncatetableinner_large_t

declare

begin

foriin1..50000loop

insertintoouter_large_tvalues（i,'

test'

'

endloop;

foriin30000..100000loop

insertintoinner_large_tvalues（i,'

commit;

end;

analyzetableouter_large_tcomputestatisticsfortableforallindexes

analyzetableinner_large_tcomputestatisticsfortableforallindexes

非关联子查询形如：

selectcount（*）fromouter_large_t

whereidnotin

（selectidfrominner_large_t）

子查询与父查询没有关联。

关联子查询形如：

selectcount（*）fromouter_large_touter_t

wherenotexists

（selectidfrominner_large_twhereid=outer_t.id）

子查询与父查询存在关联id=outer_t.id。

非关联子查询对于exists和notexists