• 自定义函数引发的性能问题


    ---执行1次
    select count(1)
      from LOAN_DUEBILLDATA bd
      left join LOAN_CONTRACTDATA bc
        on bd.RelativeSerialNo2 = bc.SerialNo
     where nvl(bd.ActualMaturity, bd.Maturity) <= '2014/08/06'
       and nvl(bd.ActualMaturity, bd.Maturity) >= '2014/07/07'
       and (bd.FinishDate is null or bd.FinishDate = '')
       and (bc.ManageUserID = '00717' or
            getCustomerManager(bc.CustomerId) = '00717')
    ----返回4条记录
    
    ---getCustomerManager函数里的sql执行3619次
    SELECT USERID FROM CUSTOMER_BELONG WHERE CUSTOMERID = :B1 AND BELONGATTRIBUTE='1'
    
    
    select  count(1)
      from LOAN_DUEBILLDATA bd
      left join LOAN_CONTRACTDATA bc
        on bd.RelativeSerialNo2 = bc.SerialNo
     where nvl(bd.ActualMaturity, bd.Maturity) <= '2014/08/06'
       and nvl(bd.ActualMaturity, bd.Maturity) >= '2014/07/07'
       and (bd.FinishDate is null or bd.FinishDate = '')
       and (bc.ManageUserID = '00717' or
            getCustomerManager(bc.CustomerId) = '00717');
    
    
    初步改写为:
    select count(1)
      from LOAN_DUEBILLDATA bd, LOAN_CONTRACTDATA bc, (select CustomerId FROM CUSTOMER_BELONG WHERE BELONGATTRIBUTE='1') be
     where bd.RelativeSerialNo2 = bc.SerialNo(+)
       and nvl(bd.ActualMaturity, bd.Maturity) <= '2014/08/06'
       and nvl(bd.ActualMaturity, bd.Maturity) >= '2014/07/07'
       and (bd.FinishDate is null or bd.FinishDate = '')
       and be.CustomerId = bc.CustomerId
       and (bc.ManageUserID = '00717' or be.customerid = '00717')
    

  • 相关阅读:
    numpy数据集练习——鸢尾花数据集
    git error:gpg failed to sign the data fatal: failed to write commit object
    后台定位Report
    iOS上传构建版本遇到的问题(Xcode8.1)
    动态计算UITableViewCell高度<进阶>
    计算代码运行时间
    安装Homebrew-包管理器
    SDWebImage : NSURLErrorDomain
    nil / Nil / NULL / NSNull
    NSURLCache
  • 原文地址:https://www.cnblogs.com/hzcya1995/p/13352226.html
Copyright © 2020-2023  润新知