I wrote a function in the oracle database that calculates saldo and finds the date of the operation. Here's how it works:
It is taking over 20 minutes. Because the average saldo records for per customer is 70-80 and It is looping for 140 000 clients.
How can I optimize my query? Any help would be much appreciated! Thank you.
create function get_date_overdue
(iAccount varchar2, iSaldo number, iDate date := get_operday())
return date
is
version CONSTANT char(14) := '->>26112020<<-';
vDate date;
vSumm number(22) := iSaldo;
vSign integer := 0;
begin
for r in (
select --+ index_desc(s UK_SALDO_ACCOUNT_DAY)
*
from ibs.Saldo@iabs s
where s.ACCOUNT_CODE = iAccount
and s.OPER_DAY between date '2015-01-01' and iDate
) loop
vSumm := vSumm - r.TURNOVER_DEBIT;
if vSign = 0 and vSumm <= 0 then
vDate := r.OPER_DAY;
vSign := 1;
end if;
EXIT WHEN vSign = 1;
end loop;
return vDate;
exception
when NO_DATA_FOUND then return null;
end;
You can use the single query to fetch the OPER_DAY
with the mentioned requirement and then return it from the function as follows:
SELECT OPER_DAY INTO vDate
FROM (SELECT SUM(S.TURNOVER_DEBIT)
OVER(ORDER BY OPER_DAY DESC NULLS LAST) AS SUM_TURNOVER_DEBIT,
OPER_DAY
FROM IBS.SALDO@IABS S
WHERE S.ACCOUNT_CODE = IACCOUNT
AND S.OPER_DAY BETWEEN DATE '2015-01-01' AND IDATE)
WHERE SUM_TURNOVER_DEBIT >= ISALDO
ORDER BY OPER_DAY DESC
FETCH FIRST ROW ONLY;
Here, I have considered that you want to scan from highest OPER_DAY
to lowest OPER_DAY
to sum the TURNOVER_DEBIT
and once the sum of the TURNOVER_DEBIT
becomes equal or more than ISALDO
, the scan should stop and you must return that OPER_DAY
.
A single query can give results way faster than looping through each and every record of the table, do some arithmetic, and take decisions based on that arithmetic.