oracle max（）和 inner join 造成的效率过低的优化

二小姐姐

于 2023-05-26 16:45:03 发布

阅读量1.2k

点赞数

文章标签： oracle 数据库

本文链接：https://blog.csdn.net/dandelionoooo/article/details/130889962

版权

文章展示了对原SQL查询的优化过程，主要通过减少统计函数的使用，如MAX和GROUPBY，以及利用ROW_NUMBER()窗口函数，提高查询效率。优化后的代码更专注于数据过滤和子查询的精简，确保在满足特定业务条件时，能更快地获取所需信息。

摘要由CSDN通过智能技术生成

原sql：

select max(id) AS id from (
SELECT C_LOANACCOUNTCODE, c_projectcode,MAX(ID) as id,C_DEPCODE,c_risk_level,c_projectname,C_LOANACCOUNTNAME,c_bistype FROM trisk_apply_his
inner join （

select REGEXP_SUBSTR(？, '[^,]+', 1, LEVEL, 'i') AS ADDUSERCD
from DUAL
CONNECT BY LEVEL <= LENGTH(？) - LENGTH(replace(？, ',', '')) + 1
）on instr(',' || c_depcode || ',', ',' || ADDUSERCD || ',') > 0
WHERE trisk_apply_his.c_flow_status = 2 AND D_FLOW_FINISH_DATE < to_date('2023-05-23','yyyy-mm-dd')+1

group by C_LOANACCOUNTCODE, c_projectcode,C_DEPCODE,c_risk_level,c_projectname,C_LOANACCOUNTNAME,c_bistype

)
group by c_projectcode,C_LOANACCOUNTCODE

优化后的代码：

SELECT
max(id) id
from (
select C_LOANACCOUNTCODE, c_projectcode,c_depcode,id from (
SELECT C_LOANACCOUNTCODE, c_projectcode,c_depcode,id ,row_number() over(partition by ID order by C_LOANACCOUNTCODE, c_projectcode ,c_depcode desc) rn FROM trisk_apply_his
WHERE trisk_apply_his.c_flow_status = 2 AND D_FLOW_FINISH_DATE < to_date(?,'yyyy-mm-dd')+1
) where rn = 1
) trisk_apply_his where exists (
select * from (
select REGEXP_SUBSTR(?, '[^,]+', 1, LEVEL, 'i') AS ADDUSERCD
from DUAL
CONNECT BY LEVEL <= LENGTH(?) - LENGTH(replace(?, ',', '')) + 1
) dep where instr(',' || trisk_apply_his.c_depcode || ',', ',' || dep.ADDUSERCD || ',') > 0
) group by C_LOANACCOUNTCODE, c_projectcode