您现在的位置是：首页 >技术杂谈 >oracle max（） 和 inner join 造成的效率过低的优化网站首页技术杂谈

原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

减少统计函数的使用可以优化效率