sql中关于in的效率问题

[阿木已被某人占用]

SELECT * FROM a where aId In(46,47,99,121,313,321,1,62,48,49)
这条语句能不能优化

有人说join或EXISTS代替IN会提高效率

我研究了半天也没研究出来

[人生无悔]

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

[fw0124]

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

[人生无悔]

--举个例子
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    

[阿木已被某人占用]

我可以这么理解吗？
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好一点

[阿木已被某人占用]

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

[人生无悔]

不可以这样理解，你的两个表有的吧，然后在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());

[人生无悔]

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

[阿木已被某人占用]

谢谢，虽然还是迷迷糊糊的

[陈字文]

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);

[阿木已被某人占用]

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

[阿木已被某人占用]

非常感谢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

);

declare

cou int:=0;

begin

while cou<1000000

loop

insert 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;

declare

startTime pls_integer;

begin

startTime := 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;

declare

startTime pls_integer;

begin

startTime := 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;

declare

startTime pls_integer;

begin

startTime := 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;

declare

startTime pls_integer;

begin

startTime := 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