sql中关于in的效率问题

Question

SELECT * FROM a where aId In(46,47,99,121,313,321,1,62,48,49)
这条语句能不能优化有人说join或EXISTS代替IN会提高效率我研究了半天也没研究出来

Answer 1

但你这种情况只能用in唉，如果你的这些id是存在其他表中的可以用exists替换

Answer 2

http://www.itpub.net/thread-1070206-1-1.html
看看这篇帖子
另外,10g之后，Oracle使用CBO.

Answer 3

--举个例子
SQL> with a(aid) as(
  2  select 46 from dual
  3  union all select 99 from dual
  4  ),
  5  b(id) as(
  6  select 46 from dual
  7  union all select 47  from dual
  8  union all select 99 from dual
  9  union all select 121 from dual
 10  union all select 313 from dual
 11  union all select 321 from dual
 12  union all select 1 from dual
 13  union all select 62 from dual
 14  union all select 48 from dual
 15  union all select 49 from dual
 16  )
 17  select * from a where exists (select * from b where a.aid=b.id);       AID                                                                      
----------                                                                      
        46                                                                      
        99    

Answer 4

我可以这么理解吗？
select * from a where aid exists(select * from b where b.bid = a.aid)
select * from a where aid in(select bid from b )
其中a为主表，b为子表
a>b的情况下用in好一点
a<b的情况下用exists好一点

Answer 5

是
select * from a where exists(select * from b where b.bid = a.aid)

Answer 6

不可以这样理解，你的两个表有的吧，然后在a表的aid建个索引，b的bid建个索引
然后你用两种方法分别看下执行计划，你就清楚了--解释
explain plan for select * from a where aid in(select bid from b)
--查看执行计划
select * from table(dbms_xplan.display());
explain plan for select * from a where aid in(select bid from b)
select * from table(dbms_xplan.display());

Answer 7

--第二个这样子，我拷错了
select * from a where exists(select * from b where b.bid = a.aid);
select * from table(dbms_xplan.display());

Answer 8

select * from a where a.xx in (1,2,3,4,5,6);
select * from a where exists (select 1 from b where  a.xx = b.xx);

Answer 9

非常感谢zhangandli(人生无悔)及各位的热心解答

Answer 10

非常感谢zhangandli(人生无悔)提供的结果不好意思，我用100w条数据做了个测试，in和exists执行计划虽是一样的，但是exists确实比in快些，但愿对你有帮助
发件人：zhangandli 
时间：2012-08-18 08:49:26
--以下的每一步需分开执行drop table a purge;create table a(id int primary key);drop table b purge;create table b(id int primary key,aid int);declarecou int:=0;beginwhile cou<1000000loopinsert into a values(cou);insert into b values(cou,cou);cou := cou+1;end loop;commit;end;/创建索引，以加速检索速度drop index b_aid;create index b_aid on b(aid);in的执行计划explain plan for select * from a where a.id in (select aid from b);select * from table(dbms_xplan.display());exists的执行计划explain plan for select * from a where exists(select 1 from b where a.id=b.aid);select * from table(dbms_xplan.display());说明：由执行计划可以看出in和exists的执行计划是一样的，全为索引扫描in的时间set serveroutput on;declarestartTime pls_integer;beginstartTime := dbms_utility.get_time;execute immediate 'select * from a where a.id in (select aid from b) ';dbms_output.put_line('seconds: '||to_char((dbms_utility.get_time-startTime)/100, '990.90'));end;/时间：0.17秒exists的时间set serveroutput on;declarestartTime pls_integer;beginstartTime := dbms_utility.get_time;execute immediate ' select * from a where exists(select 1 from b where a.id=b.aid)';dbms_output.put_line('seconds: '||to_char((dbms_utility.get_time-startTime)/100, '990.90'));end;/时间：0.10秒说明：exists确实比in用时短，再次执行的话是不准确的，因oracle有高速缓存，一般来说，如果执行相同的语句，第二次执行会比第一次快很多，因此只有第一次才是准确的在b上建立联合索引测试drop index b_aid;create index b_aid on b(id,aid);in的执行计划explain plan for select * from a where a.id in (select aid from b);select * from table(dbms_xplan.display());exists的执行计划explain plan for select * from a where exists(select 1 from b where a.id=b.aid);select * from table(dbms_xplan.display());说明：由执行计划可以看出in和exists的执行计划是一样的，但是全为全表扫描in的执行时间set serveroutput on;declarestartTime pls_integer;beginstartTime := dbms_utility.get_time;execute immediate 'select * from a where a.id in (select aid from b) ';dbms_output.put_line('seconds: '||to_char((dbms_utility.get_time-startTime)/100, '990.90'));end;/时间：0.20秒exists的执行时间set serveroutput on;declarestartTime pls_integer;beginstartTime := dbms_utility.get_time;execute immediate ' select * from a where exists(select 1 from b where a.id=b.aid)';dbms_output.put_line('seconds: '||to_char((dbms_utility.get_time-startTime)/100, '990.90'));end;/时间：0.18秒结论：能用exists替代in的最好用exists