oracle优化技巧及实例（总结）

水深无声 2021-10-01 03:00 542阅读 0赞

**1.关于exists和in**

in是循环的方式，在内存中处理，

exists是执行数据库查询，

select tpd.personaccountid,sum(nvl(tpd.CREDIT\_SUM, 0)) as bjsr, sum(nvl(tpd.INTEREST\_INCOME, 0)) as bjsr, sum(nvl(tpd.DEBIT\_SUM, 0)) as bjzc from TGP\_PERSON\_DETAIL tpd  
left join TAP\_FUNDBUSINESS tfb  
on tpd.FUNDBUSINESS\_ID = tfb.FUNDBUSINESS\_ID  
where tpd.PERSONACCOUNTID   
in (  
select personaccountid from TGP\_PERSONACCOUNT pa  
left join tgp\_person person  
on pa.personid = person.personid  
where person.zjhm = '330903196209160251'  
) group by tpd.personaccountid

执行100次 耗时0.017S左右

select tpd.personaccountid,sum(nvl(tpd.CREDIT\_SUM, 0)) as bjsr, sum(nvl(tpd.INTEREST\_INCOME, 0)) as bjsr, sum(nvl(tpd.DEBIT\_SUM, 0)) as bjzc from TGP\_PERSON\_DETAIL tpd  
left join TAP\_FUNDBUSINESS tfb  
on tpd.FUNDBUSINESS\_ID = tfb.FUNDBUSINESS\_ID  
where   
exists (  
select personaccountid from (select personaccountid from TGP\_PERSONACCOUNT pa  
left join tgp\_person person  
on pa.personid = person.personid  
where person.zjhm = '330903196209160251') a  
where tpd.personaccountid = a.personaccountid  
) group by tpd.personaccountid

执行100次 耗时0.020S左右

以上都为SELECT FORM a IN b(OR EXISTS B)  B中数据少的时候 采用IN 多的时候采用EXISTS

select tpd.personaccountid,sum(nvl(tpd.CREDIT\_SUM, 0)) as bjsr, sum(nvl(tpd.INTEREST\_INCOME, 0)) as bjsr, sum(nvl(tpd.DEBIT\_SUM, 0)) as bjzc from TGP\_PERSON\_DETAIL tpd  
left join TAP\_FUNDBUSINESS tfb  
on tpd.FUNDBUSINESS\_ID = tfb.FUNDBUSINESS\_ID  
HAVING  
exists (  
select personaccountid from (select personaccountid from TGP\_PERSONACCOUNT pa  
left join tgp\_person person  
on pa.personid = person.personid  
where person.zjhm = '330903196209160251') a  
where tpd.personaccountid = a.personaccountid  
) group by tpd.personaccountid

执行100次 耗时2.36S左右

因此先筛选数据再分组 效率更高

转载于:https://www.cnblogs.com/UUUz/p/10120625.html