`
Just-Do-it
  • 浏览: 29648 次
  • 性别: Icon_minigender_1
最近访客 更多访客>>
社区版块
存档分类
最新评论

oracle中关于in和exists,not in 和 not exists、关联子查询、非关联子查询

    博客分类:
  • SQL
阅读更多
oracle中关于in和exists,not in 和 not exists

in和exists

    in 是把外表和内表作hash 连接,而exists是对外表作loop循环,每次loop循环再对内表进行查询。

not exists:做NL,对子查询先查,有个虚表,有确定值,所以就算子查询有NULL最终也有值返回

not in:做hash,对子查询表建立内存数组,用外表匹配,那子查询要是有NULL那外表没的匹配最终无值返回。
    一直以来认为exists比in效率高的说法是不准确的。



如果查询的两个表大小相当,那么用in和exists差别不大。


如果两个表中一个较小,一个是大表,则子查询表大的用exists,子查询表小的用in:


例如:表A(小表),表B(大表)
1:
select * from A where cc in (select cc from B)

效率低,用到了A表上cc列的索引;
select * from A where exists(select cc from B where cc=A.cc)

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



相反的
2:
select * from B where cc in (select cc from A)

效率高,用到了B表上cc列的索引;
select * from B where exists(select cc from A where cc=B.cc)

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



not in 和not exists
如果查询语句使用了not in 那么内外表都进行全表扫描,没有用到索引;
而not extsts 的子查询依然能用到表上的索引。
所以无论那个表大,用not exists都比not in要快。

一直听到的都是说尽量用exists不要用in,因为exists只判断存在而in需要对比值,所以exists比较快,但看了看网上的一些东西才发现根本不是这么回事。
下面这段是抄的
Select * from T1 where x in ( select y from T2 )
执行的过程相当于:
select *
  from t1, ( select distinct y from t2 ) t2
where t1.x = t2.y;
select * from t1 where exists ( select null from t2 where y = x )
执行的过程相当于:
for x in ( select * from t1 )
   loop
      if ( exists ( select null from t2 where y = x.x )
      then
         OUTPUT THE RECORD
      end if
end loop
从我的角度来说,in的方式比较直观,exists则有些绕,而且in可以用于各种子查询,而exists好像只用于关联子查询(其他子查询当然也可以用,可惜没意义)。
由于exists是用loop的方式,所以,循环的次数对于exists影响最大,所以,外表要记录数少,内表就无所谓了,而in用的是hash join,所以内表如果小,整个查询的范围都会很小,如果内表很大,外表如果也很大就很慢了,这时候exists才真正的会快过in的方式。
 

not in 和not exists
如果查询语句使用了not in 那么内外表都进行全表扫描,没有用到索引;
而not extsts 的子查询依然能用到表上的索引。
所以无论那个表大,用not exists都比not in要快。

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



典型的连接类型共有3种:
排序 - - 合并连接(Sort Merge Join (SMJ) )
嵌套循环(Nested Loops (NL) )
哈希连接(Hash Join)
嵌套循环和哈希连接的算法还是有不同,在理论上哈希连接要快过排序和nl,当然实际情况比理论上有复杂的多,不过两者还是有差异的.



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

关联子查询需要在内部引用外部表,而非关联子查询不要引用外部表。对于父查询中处理的记录来说,一个关联子查询是每行计算一次,然而一个非关联子查询只会执行一次,而且结果集被保存在内存中(如果结果集比较小),或者放在一张oracle临时数据段中(如果结果集比较大)。一个“标量”子查询是一个非关联子查询,返回唯一记录。如果子查询仅仅返回一个记录,那么oracle优化器会将结果缩减为一个常量,而且这个子查询只会执行一次。

/*select * from emp where deptno in (select deptno from dept where dept_name='admin');*/

2.如何选择?

  根据外部查询,以及子查询本身所返回的记录的数目。如果两种查询返回的结果是相同的,哪一个效率更好?

  关联子查询的系统开销:对于返回到外层查询的记录来说,子查询会每次执行一次。因此,必须保证任何可能的时候子查询都要使用索引。

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

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



3结论:1)在使用一个关联子查询是,使用in 或者 exists子句的子查询执行计划通常都相同

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

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

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

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

       1)使用in的非关联子查询(子查询唯一)

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

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

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

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



5。not in和not exists调整

  1)not in 非关联子查询:转化为in写法下的minus子句

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

    可以重写:在一个等值连接中指定外部链接条件,然后添加select distinct

    eg:select distinct ... from a,b where a.col1 = b.col1(+) and b.col1 is null

6。在子查询中使用all any

引自:http://chenwenchaopy.blog.163.com/blog/static/1434909352011219101250116/
分享到:
评论

相关推荐

    oracle中exists_和in的效率问题详解

    Oracle 中 EXISTS 和 IN 的效率问题详解 EXISTS 和 IN 都是 Oracle 中的集合操作符,但它们在使用和执行效率上有所不同。本文将深入探讨 EXISTS 和 IN 的使用场景、执行机制和效率问题。 EXISTS 的使用场景和机制 ...

    in和exists的区别

    在Oracle数据库中,"IN"和"EXISTS"都是用于查询某个集合的元素是否存在于另一个集合中的关键字。然而,它们在处理数据时的效率和适用场景有所不同,这主要取决于涉及的数据量以及表之间的关联。 首先,让我们来看看...

    sql in,exists,not in,not exists区别

    IN、EXISTS、NOT IN、NOT EXISTS 是 SQL 中四种常用的条件判断运算符,它们之间的区别主要体现在使用场景、执行效率和语法结构上。 IN IN 是一种条件判断运算符,用于判断某个值是否存在于一个列表中。其基本语法...

    经典SQL查询总结关于Exists,not Exists.in ,not in效率的说明。

    **IN** 和 **NOT IN** 子句通常用于检查主查询中的某字段的值是否存在于(或不存在于)子查询返回的集合中。 1. **IN** - **语法结构**: ```sql SELECT * FROM t1 WHERE t1.c1 IN (SELECT c2 FROM t2); ``` ...

    oracle数据库关于exists使用

    在Oracle数据库中,`EXISTS` 是一种常用的子查询操作符,用于判断子查询是否有结果返回。如果子查询至少返回一行数据,则 `EXISTS` 表达式的结果为真;反之,则为假。 **1.1 Exists 的基本语法** ```sql SELECT ...

    Oracle In和exists not in和not exists的比较分析

    在Oracle数据库中,`IN`、`EXISTS`、`NOT IN` 和 `NOT EXISTS` 是四个常用的子查询操作符,它们在SQL查询语句中扮演着不同的角色,且各有其性能特点。以下是对这些操作符的详细分析和比较。 1. `IN` 操作符: `IN` ...

    sql case when exists not exists in not in

    在SQL查询中,`CASE WHEN`、`EXISTS`、`NOT EXISTS`以及`IN`和`NOT IN`是常用的操作符,它们用于处理复杂的条件判断和数据筛选。这些概念对于理解和编写高效的SQL语句至关重要,尤其是在数据分析和数据库管理中。 `...

    EXISTS_和_NOT_EXISTS

    如果你想要查询在主表中不存在于子查询结果中的记录,使用 `NOT EXISTS` 可能比使用 `LEFT JOIN` 或 `NOT IN` 更直观。 - 使用场景:`EXISTS` 常用于检查关联或匹配的记录,而 `NOT EXISTS` 用于排除这些匹配的记录...

    in和exists性能解析

    在数据库查询语言中,`IN` 和 `EXISTS` 子句是两种常见的用于关联两个表的方法,它们各自有其独特的性能特点和适用场景。本文将深入解析Oracle中`IN`与`EXISTS`的性能差异,以及如何根据具体需求选择最合适的查询...

    sql exists和not exists用法

    这是因为`IN`和`NOT IN`需要将主查询中的每一行都与子查询结果进行比较,而`EXISTS`和`NOT EXISTS`则只需判断子查询是否返回至少一行数据即可,无需获取所有数据行。 **性能对比:** - **IN 和 NOT IN**:需要将主...

    exists SQL用法详解、exists和not exists的常用示例

    在SQL查询中,`EXISTS`和`NOT EXISTS`是两个非常重要的子查询操作符,它们主要用于判断子查询是否返回结果。本篇文章将详细介绍这两个关键字的用法,并通过实例进行解析。 首先,`EXISTS`的语法是:主查询中的条件...

    Oracle 子查询

    本文将深入探讨Oracle中的子查询概念,包括其类型、语法、以及如何在实际场景中高效地应用。 #### 子查询概述 子查询,顾名思义,就是在主查询中嵌入一个或多个查询语句。根据返回结果的不同,子查询主要分为两类...

    Oracle数据库应用教程--子查询与高级查询.pptx

    在关联子查询中,可以使用EXISTS和NOT EXISTS操作符。嵌套子查询是在子查询中包含有子查询。 在使用子查询时,需要注意以下几点:子查询需要使用括号括起来。子查询要放在比较操作符的右边。当子查询的返回值是一个...

    sql语句优化之用EXISTS替代IN、用NOT EXISTS替代NOT IN的语句

    在子查询中,NOT IN子句将执行一个内部的排序和合并,无论在哪种情况下,NOT IN都是最低效的(因为它对子查询中的表执行了一个全表遍历)。为了避免使用NOT IN,我们可以把它改写成外连接(Outer Joins)或NOT ...

    NOT EXISTS ⇔ NOT IN ⇒ NOT NULL 问题 结果不一样 疑问 如果等效

    在SQL查询语言中,`NOT EXISTS`、`NOT IN` 和 `NOT NULL` 是三种用于排除特定条件的数据记录的方法。然而,在实际应用中,这三种语法有着不同的应用场景和执行逻辑,有时初学者可能会误以为它们是等效的,但实际上...

    QL中exists 与 in_的比较

    在数据库查询语言QL中,`exists`与`in_`(通常写作`IN`)是两种常见的用于子查询的语法结构,它们各自有着独特的应用场景和性能表现。理解这两种语法的区别对于优化查询效率至关重要。 ### `exists`与`in_`的基本...

    浅析SQL中WHERE EXISTS子查询.pdf

    浅析SQL中WHERE EXISTS子查询 SQL(Structured Query Language)是一种功能强大、通用的关系数据库语言,而SQL语言的核心是SELECT语句,其中WHERE子句的条件表达式表现得灵活而复杂,和谓词DISJunctive所形成的嵌套...

    Oracle: minus | in | exists

    在Oracle数据库中,`MINUS`、`IN` 和 `EXISTS` 是三种不同的SQL查询操作符,它们各自用于特定的数据处理场景。了解并熟练掌握这些操作符对于优化查询性能和编写复杂的SQL语句至关重要。 1. **MINUS 操作符** `MINUS...

Global site tag (gtag.js) - Google Analytics