---执行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')