not in、outer joins与not exists ORA-01795: 列表中的最大表达式数为 1000

 

OT IN:
select *
  from rateparam1.Work_Order_Def a
 where workorder_id not in
       (select b.workorder_id from rateparam1.work_order_process_log b);

OUTER JOINS:        
select * from rateparam1.Work_Order_Def a,rateparam1.work_order_process_log b 
where a.workorder_id = b.workorder_id(+)
      and b.workorder_id is null;

NOT EXISTS:
select *
  from rateparam1.Work_Order_Def a
 where not exists (select *
          from rateparam1.work_order_process_log b
         where a.workorder_id = b.workorder_id)

A表大于B表，查找A>B的部分（A中存在，B中不存在）。
select * from A
        where not exists (select * from B
                        where A.** = B.**
                              and ...)---B表＝A表的部分

NOT IN < OUTER JOIN < NOT EXISTS

   在子查询中,NOT IN子句将执行一个内部的排序和合并. 无论在哪种情况下,NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历). 为了避免使用NOT IN ,我们可以把它改写成外连接(Outer Joins)或NOT EXISTS
    如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引；而not extsts 的子查询依然能用到表上的索引。
  通过使用EXIST，Oracle系 统会首先检查主查询，然后运行子查询直到它找到第一个匹配项，这就节省了时间。Oracle系统在执行IN子查询时，首先执行子查询，并将获得的结果列表 存放在在一个加了索引的临时表中。在执行子查询之前，系统先将主查询挂起，待子查询执行完毕，存放在临时表中以后再执行主查询。这也就是使用EXISTS 比使用IN通常查询速度快的原因。  
  同时应尽可能使用NOT EXISTS来代替NOT IN，尽管二者都使用了NOT（不能使用索引而降低速度），NOT   EXISTS要比NOT IN查询效率更高。

 

 

 

 

 

 用EXISTS替代IN、用NOT EXISTS替代NOT IN：

　　在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下,

 使用EXISTS(或NOT EXISTS)通常将提高查询的效率. 在子查询中,

NOT IN子句将执行一个内部的排序和合并. 无论在哪种情况下,

NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历).

 

为了避免使用NOT IN ,我们可以把它改写成外连接(Outer Joins)或NOT EXISTS.

　　例子：　

　（高效）SELECT * FROM EMP (基础表) WHERE EMPNO > 0 AND EXISTS (SELECT ‘X’

 FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = ‘MELB’)　

　(低效)SELECT * FROM EMP (基础表) WHERE EMPNO > 0 AND DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE LOC = ‘MELB’)　　　　

外大内小用in ,外小内大用exists

select count(*)

from A a

where a.tradedate = '20090202'

   and a.no in

       (select b.no from B b)  

 如果A表数据量大 而B表数据量小 

这样直接过滤了很多数据 不用遍历整张表大表啊

过滤后数据量小了，那么运算次数也少了

select count(*)本来就不会花费很多时间啊，

因为只返回一个记录

但select *

会花费很多时间

 那exists效率也会很高，因为exists只返回TRUE或者FALSE