oracle优化技巧及实例(总结)
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左右
因此先筛选数据再分组 效率更高
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· AI与.NET技术实操系列:向量存储与相似性搜索在 .NET 中的实现
· 基于Microsoft.Extensions.AI核心库实现RAG应用
· Linux系列:如何用heaptrack跟踪.NET程序的非托管内存泄露
· 开发者必知的日志记录最佳实践
· SQL Server 2025 AI相关能力初探
· 震惊!C++程序真的从main开始吗?99%的程序员都答错了
· winform 绘制太阳,地球,月球 运作规律
· 【硬核科普】Trae如何「偷看」你的代码?零基础破解AI编程运行原理
· 上周热点回顾(3.3-3.9)
· 超详细:普通电脑也行Windows部署deepseek R1训练数据并当服务器共享给他人