oracle中in的效率,个人见解-在实际应用中Oracle的EXISTS与IN的使用及效率-遇到的问题与想法记录...
赞
踩
在工作中遇到一个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效率高
