一次SQL Tuning引出来的not in , not exists 语句的N种写法

rollingpig

最初由 susan_j 发布
[B]想请问rollingpig, 被删除的数据量是多少? [/B]

3000左右

rwq_

最初由 rollingpig 发布
[B]在 9i 里面，in 和 exists已经基本一样，Oracle会在内部自动作转换

倒是 我提到的其中几个转换值的深究

1。in / exists ==> join

2。Add distinct in IN Clause or subquery

3. Add condition where rownum < 2 in  exists clause (这个忘了演示了)

4, not exists / not in  ==> outer join + is null condition
(更正一下，是outer join + is null condition 不是 not null condition ) [/B]

4, not exists / not in ==> outer join + is null condition
这个想法不错，测试一下。

gozheng

以前我碰到楼主一样的问题，数据量不大，可用not EXIST  和NOT IN 都很慢。
我最后也是用outer join + is null condition解决的。

yaanzy

可以看看：
http://blog.itpub.net/post/1263/10217

rollingpig

赫赫，不小心发现了Oracle 写的not exists 和 not in 了。
就在 大名鼎鼎 的statspack里面，?/rdbms/admin/sppurge.sql

这下估计很多人都有一个很好的实例了

[php]
普通的
delete from stats$statspack_parameter sp
where instance_number = :inst_num
   and dbid            = :dbid
   and not exists (select 1
                     from stats$snapshot s
                    where s.dbid            = sp.dbid
                      and s.instance_number = sp.instance_number);


经过tuning的

alter session set hash_area_size=1048576;
delete --+ index_ffs(st)
  from stats$sqltext st
where (hash_value, text_subset) not in
       (select --+ hash_aj full(ss) no_expand
               hash_value, text_subset
          from stats$sql_summary ss
         where (   (   snap_id     < :lo_snap
                    or snap_id     > :hi_snap
                   )
                   and dbid            = :dbid
                   and instance_number = :inst_num
               )
            or (   dbid            != :dbid
                or instance_number != :inst_num)
        );


...
[/php]

大家有空可以测试一下效率，对比一下我提到的各种转换形式

lwxxrq

最初由 rollingpig 发布
[B]在 9i 里面，in 和 exists已经基本一样，Oracle会在内部自动作转换[/B]

我认为还是不一样的，不过我们的环境是10g，in还是经常会导致巨大的逻辑I/O，改成exists就立刻少了非常多逻辑I/O，尽管有的时候在sqlplus里看起来执行计划是一样的。

rollingpig

哦
可能在有一些情况下会一致
有些情况则会不一致

To all,
实际上，不能够说是那种变换比其他好，或者是说，跟实际数据的情况有很大联系，比如表的大小，Not IN 部分的大小，COLUMN的聚合度，都会对最后的执行效率产生影响。


我所举的例子只是提供大家一种思路（N种思路)  ,在某些情况下，如果你的一些带有(not) in / exists（甚至是JOIN）的语句的执行效率不好了，不妨尝试一下不同的变换形式，有时候会起到意想不到的效果。

cc59

对.通常执行计划的一些比较.

有时候in和exists是完全一样的.

具体应该是与数据大小等有关系的.

flyerchen2000

感觉就是所有的武器都试一遍，比较一下，选最好的

haochunpeng

最初由 flyerchen2000 发布
[B]感觉就是所有的武器都试一遍，比较一下，选最好的 [/B]

同感，既然不能下定论，只能在实际情况中都试一下。收藏这个帖子。