Hello All,
Request your help in improving performance query:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
"CORE 11.2.0.3.0 Production"
TNS for 64-bit Windows: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
Below query accesses large table multiple times , please suggest approach on how to reduce the PROCESSED_TRXNS table access and limit it to access only once.
SELECT FOLIO_NO ,
SUM(AMOUNT_INVESTED) COST_OF_INVESTMENT,
SUM(CURRENT_VALUATION) MARKET_VALUE ,
SUM(CURRENT_VALUATION)-SUM(AMOUNT_INVESTED) GAINLOSS,
SUM(DIVIDEND_PAYOUT) DIVIDEND_PAYOUT,
LISTAGG(D.SCHEME_NAME,', ') WITHIN GROUP (
ORDER BY FOLIO_NO) SCHEMES,
COUNT(DISTINCT SCHEME_CODE) SCHEMES_COUNT
FROM
(SELECT A.FOLIO_NO ,
D.SHORT_NAME SCHEME_NAME,
A.SCH_CODE SCHEME_CODE,
A.TOTAL_UNITS*C.NAV_VALUE CURRENT_VALUATION,
MOBILE_APP.FN_COSTVALUE_FIFO(a.FOLIO_NO,a.SCH_CODE) AMOUNT_INVESTED,
FN_DP_AMOUNT_SCH(A.FOLIO_NO,A.SCH_CODE) DIVIDEND_PAYOUT,
C.TOTAL_UNITS
FROM CUSTOMER_SCHEMES A,
SCHEME_SETUP D,
MV_LATEST_NAV C
WHERE D.SCHCODE = A.SCH_CODE
AND A.SCH_CODE = C.SCHEME_CODE
AND D.SCHCODE = C.SCHEME_CODE
AND total_units > 0.1
)
where FOLIO_NO = '1010101010'; --INPUT value
GROUP BY FOLIO_NO;
Tables Count:
1. PROCESSED_TRXNS --188579010
2. CUSTOMER_SCHEMES --8208800
3. SCHEME_SETUP --4157
4. TRANSACTION_TYPES --3806
5. MV_LATEST_NAV --3281
Functions:
1. MOBILE_APP.FN_COSTVALUE_FIFO(a.FOLIO_NO,a.SCH_CODE)
2. FN_GET_REDEMPTION_UNITS --this function is part of MOBILE_APP.FN_COSTVALUE_FIFO
3. FN_DP_AMOUNT_SCH
--------------------------------------------------------------------------------------------------------
FUNCTION DDL:
create or replace
FUNCTION FN_COSTVALUE_FIFO (P_FOLIO_NO IN VARCHAR2, P_SCH_CODE IN VARCHAR2)
RETURN NUMBER
AS
V_AMOUNT NUMBER(28,4);
BEGIN
IF P_FOLIO_NO IS NOT NULL AND P_SCH_CODE IS NOT NULL
THEN
SELECT nvl(SUM(COST_VALUE),0) INTO V_AMOUNT FROM (
SELECT FOLIO_NO ,SCH_CODE , ROUND(SUM((UNITS-UNITS_SOLD)*PRICE),2) COST_VALUE
FROM
(SELECT A.FOLIO_NO , a.SCH_CODE ,TRXN_DATE,TRXNDBCR ,PRICE,UNITS ,
CASE
WHEN LEAST (UNITS, NVL(FN_GET_REDEMPTION_UNITS(a.FOLIO_NO, a.SCH_CODE),0)-NVL(SUM(UNITS) OVER (PARTITION BY a.FOLIO_NO , a.SCH_CODE ORDER BY TRXN_DATE ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING),0)) < 0
THEN 0
ELSE LEAST (UNITS, NVL(FN_GET_REDEMPTION_UNITS(A.FOLIO_NO, A.SCH_CODE),0)-NVL(SUM(UNITS) OVER (PARTITION BY A.FOLIO_NO , A.SCH_CODE ORDER BY TRXN_DATE ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING),0))
END UNITS_SOLD
FROM PROCESSED_TRXNS A,TRANSACTION_TYPES B, CUSTOMER_SCHEMES C
WHERE A.TRXN_TYPE_CODE = B.TRXNTYPCOD
AND B.TRXNDBCR IN( 'P','SI','TI','DR')
AND A.FOLIO_NO = P_FOLIO_NO AND A.SCH_CODE = P_SCH_CODE
AND A.FOLIO_NO = C.FOLIO_NO AND A.SCH_CODE = C.SCH_CODE
and c.total_units > 0
ORDER BY TRXN_DATE
) A
GROUP BY
FOLIO_NO ,
SCH_CODE );
END IF;
RETURN V_AMOUNT;
END FN_COSTVALUE_FIFO;
--------------------------------------------------------------------------------------------------------
create or replace
FUNCTION FN_GET_REDEMPTION_UNITS (P_FOLIO_NO IN VARCHAR2, P_SCH_CODE IN VARCHAR2)
RETURN NUMBER
AS
V_REDEEM_UNITS NUMBER (20,4);
BEGIN
SELECT SUM (UNITS) INTO V_REDEEM_UNITS
FROM PROCESSED_TRXNS A, TRANSACTION_TYPES B
WHERE A.TRXN_TYPE_CODE = B.TRXNTYPCOD
AND FOLIO_NO = P_FOLIO_NO
AND SCH_CODE = P_SCH_CODE
AND TRXNDBCR IN ('R','SO','TO');
RETURN V_REDEEM_UNITS;
END ;
FN_DP_AMOUNT_SCH(A.FOLIO_NO,A.SCH_CODE)
--------------------------------------------------------------------------------------------------------
create or replace
FUNCTION FN_DP_AMOUNT_SCH
(P_FOLIO_NO IN VARCHAR2, P_SCH_CODE IN VARCHAR2)
RETURN NUMBER
AS
V_DIVPAY_AMOUNT NUMBER(20,4);
BEGIN
IF P_FOLIO_NO IS NOT NULL AND P_SCH_CODE IS NOT NULL
then
select NVL(SUM(PLOT_AMOUNT),0) into V_DIVPAY_AMOUNT
FROM PROCESSED_TRXNS A,
TRANSACTION_TYPES C
where a.TRXN_TYPE_CODE=C.TRXNTYPCOD
and FOLIO_NO = P_FOLIO_NO
and SCH_CODE = P_SCH_CODE
AND TRXNDBCR = 'DP';
END IF;
RETURN V_DIVPAY_AMOUNT ;
END FN_DP_AMOUNT_SCH;
--------------------------------------------------------------------------------------------------------
Above 3 functions accesses PROCESSED_TRXNS table 3 times based to calculate below values for input parameter i.e. FOLIO_NO.
SUM(AMOUNT_INVESTED) COST_OF_INVESTMENT
SUM(CURRENT_VALUATION) MARKET_VALUE
SUM(CURRENT_VALUATION)-SUM(AMOUNT_INVESTED) GAINLOSS
SUM(DIVIDEND_PAYOUT) DIVIDEND_PAYOUT
all the 4 values can be calculated basis below query depending on the value of column b.TRXNDBCR
select a.TRXN_NO, a.FOLIO_NO, a.SCH_CODE, a.TRXN_DATE,b.TRXNDBCR, a.UNITS, A.PRICE, a.PLOT_AMOUNT
from PROCESSED_TRXNS a, TRANSACTION_TYPES B, customer_schemes c
where a.TRXN_TYPE_CODE = B.TRXNTYPCOD
and a.FOLIO_NO = C.FOLIO_NO and a.SCH_CODE = C.SCH_CODE and C.TOTAL_UNITS >0
and a.FOLIO_NO = '1010101010'
order by a.trxn_date
Appreciate any inputs for the query.
Thanks & Regards,
Bhavin