欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

个人见解-在实际应用中Oracle的EXISTS与IN的使用及效率-遇到的问题与想法记录

程序员文章站 2022-03-05 17:09:27
在工作中遇到一个SQL查询中IN的参数会打到11万的数量,所以就想提高一下运行效率就写了另外一种EXISTS写法的SQL执行结果令我十分意外。 关于ORACLE对于IN的参数限制 Oracle 9i 中个数不能超过256,Oracle 10g个数不能超过1000.但是在Oracle 11g中已经解除 ......

在工作中遇到一个sql查询中in的参数会打到11万的数量,所以就想提高一下运行效率就写了另外一种exists写法的sql执行结果令我十分意外。

关于oracle对于in的参数限制

oracle 9i 中个数不能超过256,oracle 10g个数不能超过1000.但是在oracle 11g中已经解除了这个限制

我用的是oracle 11g

执行sql

sql a :

in写法

select distinct

    a.aay002 as codename,

    a.aay003 as codevalue

    from a a left join p p on a.aay002 = p.aay002

    where 1=1

    and a.bya343 is not null  and a.bya343 <> 0

    and a.aae100 = '1'

    and a.aay103 = '2aa'

    and a.aab038 in (

        select unit_code from 

          (select code unit_code, name comename from b 

           where 1=1

           and unit_level in ('2','3','4','5') 

           and is_enabled = '1'

           start with id= '00000000' connect by prior   id = parent_id 

           )

    )

 

order by a.aay002

查询结果:212条数据,耗时:2.773秒

sql b :

 

exists写法

 

select distinct

    a.aay002 as codename,

    a.aay003 as codevalue

    from a a left join p p on a.aay002 = p.aay002

    where 

    exists 

    (select 1 from b 

    where code = a.aab038 

    start with id = '00000000' 

    connect by prior id = parent_id)

    and a.bya343 is not null  and a.bya343 <> 0

    and a.aae100 = '1'

    and a.aay103 = '2aa'

order by a.aay002;

 

a表数据量:9040条

b表数据量:111839条

p表数据量:50条

 查询结果:212条数据,耗时:137.548秒

个人总结:在oracle 11g以后查询sql中in效率比exists快很多

但是在网上查询到的信息是:

举例:

select *  from a where id in(select id from b);

select *  from a where exists (select 1 from b where a.id = b.id);

如:a表有10000条记录,b表有1000000条记录,那么exists()会执行10000次去判断a表中的id是否与b表中的id相等。
如:a表有10000条记录,b表有100000000条记录,那么exists()还是执行10000次,因为它只执行a.length次,可见b表数据越多,越适合exists()发挥效果。
再如:a表有10000条记录,b表有100条记录,那么exists()还是执行10000次,还不如使用in()遍历10000*100次,因为in()是在内存里遍历比较,而exists()需要查询数据库,我们都知道查询数据库所消耗的性能更高,而内存比较很快

结论:exists()适合b表比a表数据大的情况

当a表数据与b表数据一样大时,in与exists效率差不多,可任选一个使用。

与网上结论相驳。原因目前怀疑可能与表结构与索引有关,如有新的进展会继续更新,目前还是用in效率高