SQL语句中的=和in,exists及<>和not in,not exists等连接字的执行速度,平常写sql语句,选用哪几个执行速度快? 大家习惯使用哪几个?

解决方案 »

  1.   

    http://www.itpub.net/viewthread.php?tid=936584汗,发成图片链接了,呵呵。2楼的成语解释的非常到位,鉴定完毕!
      

  2.   

    这样的话,in适合内外表都很大的情况,exists适合外表结果集很小的情况。
      

  3.   

    【借此一学,好好学习,天天向上】
    EXISTS、IN、NOT EXISTS、NOT IN的区别:
    in适合内外表都很大的情况,exists适合外表结果集很小的情况。
    exists 和 in 使用一例 
    =========================================================== 
    今天市场报告有个sql及慢,运行需要20多分钟,如下:
    update p_container_decl cd
    set cd.ANNUL_FLAG='0001',ANNUL_DATE = sysdate
    where exists(
    select 1
    from (
    select tc.decl_no,tc.goods_no
    from p_transfer_cont tc,P_AFFIRM_DO ad
    where tc.GOODS_DECL_NO = ad.DECL_NO
    and ad.DECL_NO = 'sssssssssssssssss'
    ) a
    where a.decl_no = cd.decl_no
    and a.goods_no = cd.goods_no
    )
    上面涉及的3个表的记录数都不小,均在百万左右。根据这种情况,我想到了前不久看的tom的一篇文章,说的是exists和in的区别,
    in 是把外表和那表作hash join,而exists是对外表作loop,每次loop再对那表进行查询。
    这样的话,in适合内外表都很大的情况,exists适合外表结果集很小的情况。而我目前的情况适合用in来作查询,于是我改写了sql,如下:
    update p_container_decl cd
    set cd.ANNUL_FLAG='0001',ANNUL_DATE = sysdate
    where (decl_no,goods_no) in
    (
    select tc.decl_no,tc.goods_no
    from p_transfer_cont tc,P_AFFIRM_DO ad
    where tc.GOODS_DECL_NO = ad.DECL_NO
    and ad.DECL_NO = ‘ssssssssssss’
    ) 让市场人员测试,结果运行时间在1分钟内。问题解决了,看来exists和in确实是要根据表的数据量来决定使用。请注意not in 逻辑上不完全等同于not exists,如果你误用了not in,小心你的程序存在致命的BUG: 
    请看下面的例子:
    create table t1 (c1 number,c2 number);
    create table t2 (c1 number,c2 number);insert into t1 values (1,2);
    insert into t1 values (1,3);
    insert into t2 values (1,2);
    insert into t2 values (1,null);select * from t1 where c2 not in (select c2 from t2);
    no rows found
    select * from t1 where not exists (select 1 from t2 where t1.c2=t2.c2);
    c1 c2
    1 3正如所看到的,not in 出现了不期望的结果集,存在逻辑错误。如果看一下上述两个select语句的执行计划,也会不同。后者使用了hash_aj。
    因此,请尽量不要使用not in(它会调用子查询),而尽量使用not exists(它会调用关联子查询)。如果子查询中返回的任意一条记录含有空值,则查询将不返回任何记录,正如上面例子所示。
    除非子查询字段有非空限制,这时可以使用not in ,并且也可以通过提示让它使用hasg_aj或merge_aj连接。
      

  4.   

    用表连接替换EXISTS
    通常来说 , 采用表连接的方式比EXISTS更有效率
      

  5.   

    2. 用EXISTS替代IN
      在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接。在这种情况下, 使用EXISTS(或NOT EXISTS)通常将提高查询的效率。
      低效:
      SELECT * 
      FROM EMP (基础表) 
      WHERE EMPNO > 0 
      AND DEPTNO IN (SELECT DEPTNO 
      FROM DEPT 
      WHERE LOC = ‘MELB’)
      高效:
      SELECT * 
      FROM EMP (基础表) 
      WHERE EMPNO > 0 
      AND EXISTS (SELECT ‘X’ 
      FROM DEPT 
      WHERE DEPT.DEPTNO = EMP.DEPTNO 
      AND LOC = ‘MELB’)
      (相对来说,用NOT EXISTS替换NOT IN 将更显著地提高效率,下面将指出)
      3. 用NOT EXISTS替代NOT IN
      在子查询中,NOT IN子句将执行一个内部的排序和合并。 无论在哪种情况下,NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历)。 为了避免使用NOT IN ,我们可以把它改写成外连接(Outer Joins)或NOT EXISTS.
      例如:
      SELECT … 
      FROM EMP 
      WHERE DEPT_NO NOT IN (SELECT DEPT_NO 
      FROM DEPT 
      WHERE DEPT_CAT=’A’);
      为了提高效率。改写为:
    (方法一: 高效)
      SELECT …. 
      FROM EMP A,DEPT B 
      WHERE A.DEPT_NO = B.DEPT(+) 
      AND B.DEPT_NO IS NULL 
      AND B.DEPT_CAT(+) = ‘A’
      (方法二: 最高效)
      SELECT …. 
      FROM EMP E 
      WHERE NOT EXISTS (SELECT ‘X’ 
      FROM DEPT D 
      WHERE D.DEPT_NO = E.DEPT_NO 
      AND DEPT_CAT = ‘A’);
      4. 用表连接替换EXISTS
      通常来说 , 采用表连接的方式比EXISTS更有效率
      SELECT ENAME 
      FROM EMP E 
      WHERE EXISTS (SELECT ‘X’ 
      FROM DEPT 
      WHERE DEPT_NO = E.DEPT_NO 
      AND DEPT_CAT = ‘A’);
      (更高效)
      SELECT ENAME 
      FROM DEPT D,EMP E 
      WHERE E.DEPT_NO = D.DEPT_NO 
      AND DEPT_CAT = ‘A’ ;
      (在RBO的情况下,前者的执行路径包括FILTER,后者使用NESTED LOOP)
      

  6.   

    一般情况not exists的效率比not in的效率要高很多,
    不过exists和in的差距不是很大