Hello good afternoon friends, I hope and are well, I was asked at work to work on this query:
SELECT
CAST(cd_ITVALCNV AS VARCHAR(6)) AS cd_ITVALCNV,
cd_ICTTO,
CAST(cd_IEMISORA AS VARCHAR(8)) AS cd_IEMISORA,
CAST(cd_ISERIE AS VARCHAR(8)) AS cd_ISERIE,
cd_ICUPON,
cd_ISUBFOL,
cd_IFOLADM,
CAST(nb_SORIMOV AS VARCHAR(2)) AS nb_SORIMOV,
cd_FOPERA,
im_CTITEXI
FROM
(
SELECT
cd_ITVALCNV,
cd_ICTTO,
cd_IEMISORA,
cd_ISERIE,
cd_ICUPON,
cd_ISUBFOL,
cd_IFOLADM,
nb_SORIMOV,
FN_MAXFOPERA(cd_ITVALCNV, cd_ICTTO, cd_IEMISORA, cd_ISERIE, cd_ICUPON,
cd_ISUBFOL, cd_IFOLADM, nb_SORIMOV) AS cd_FOPERA,
FN_LASTCTITEXI(cd_ITVALCNV, cd_ICTTO, cd_IEMISORA, cd_ISERIE, cd_ICUPON,
cd_ISUBFOL, cd_IFOLADM, nb_SORIMOV, FN_MAXFOPERA(cd_ITVALCNV, cd_ICTTO,
cd_IEMISORA, cd_ISERIE, cd_ICUPON, cd_ISUBFOL, cd_IFOLADM, nb_SORIMOV))
AS im_CTITEXI
FROM
(
SELECT DISTINCT
cd_ITVALCNV,
cd_ICTTO,
cd_IEMISORA,
cd_ISERIE,
cd_ICUPON,
cd_ISUBFOL,
CASE
WHEN nb_SORIMOV = 'RP'
THEN FN_MAXIFOLADM(cd_ITVALCNV, cd_ICTTO, cd_IEMISORA, cd_ISERIE,
cd_ICUPON, cd_ISUBFOL, nb_SORIMOV)
ELSE cd_IFOLADM
END AS cd_IFOLADM,
nb_SORIMOV
FROM
TVIV023_KARCAS KC
WHERE
cd_ISUBFOL <> 4
)
)
WHERE
im_CTITEXI <> 0
This query depends on three functions that are:
1)
create or replace
FUNCTION FN_LASTCTITEXI (V_itvalcnv IN CHAR,
V_ictto IN NUMBER,
V_iemisora IN CHAR,
V_iserie IN CHAR,
V_icupon IN NUMBER,
V_isubfol IN NUMBER,
V_ifoladm IN NUMBER,
V_sorimov IN CHAR,
V_fopera IN VARCHAR2)
RETURN FLOAT IS
v_ctitexi FLOAT;
BEGIN
SELECT im_CTITEXI
INTO v_ctitexi
FROM (SELECT ROWNUM AS id_ctitexi,
im_CTITEXI
FROM TVIV023_KARCAS
WHERE cd_ITVALCNV = V_itvalcnv
AND cd_ICTTO = V_ictto
AND cd_IEMISORA = V_iemisora
AND cd_ISERIE = V_iserie
AND cd_ICUPON = V_icupon
AND cd_ISUBFOL = V_isubfol
AND cd_IFOLADM = V_ifoladm
AND nb_SORIMOV = V_sorimov
AND cd_FOPERA = V_fopera
ORDER BY 1 DESC)
WHERE ROWNUM = 1;
RETURN v_ctitexi;
END;
2)
create or replace
FUNCTION FN_MAXFOPERA (V_itvalcnv IN CHAR,
V_ictto IN NUMBER,
V_iemisora IN CHAR,
V_iserie IN CHAR,
V_icupon IN NUMBER,
V_isubfol IN NUMBER,
V_ifoladm IN NUMBER,
V_sorimov IN CHAR)
RETURN VARCHAR2 IS
v_fopera TVIV023_KARCAS.cd_FOPERA%TYPE;
BEGIN
SELECT Max(cd_FOPERA)
INTO v_fopera
FROM TVIV023_KARCAS K
WHERE K.cd_ITVALCNV = V_itvalcnv
AND K.cd_ICTTO = V_ictto
AND K.cd_IEMISORA = V_iemisora
AND K.cd_ISERIE = V_iserie
AND K.cd_ICUPON = V_icupon
AND K.cd_ISUBFOL = V_isubfol
AND K.cd_IFOLADM = V_ifoladm
AND K.nb_SORIMOV = V_sorimov;
RETURN v_fopera;
END;
3)
create or replace
FUNCTION FN_MAXIFOLADM (V_itvalcnv IN CHAR,
V_ictto IN NUMBER,
V_iemisora IN CHAR,
V_iserie IN CHAR,
V_icupon IN NUMBER,
V_isubfol IN NUMBER,
V_sorimov IN CHAR)
RETURN NUMBER IS
v_ifoladm NUMBER;
BEGIN
SELECT Max(cd_IFOLADM)
INTO v_ifoladm
FROM TVIV023_KARCAS K
WHERE K.cd_ITVALCNV = V_itvalcnv
AND K.cd_ICTTO = V_ictto
AND K.cd_IEMISORA = V_iemisora
AND K.cd_ISERIE = V_iserie
AND K.cd_ICUPON = V_icupon
AND K.cd_ISUBFOL = V_isubfol
AND K.nb_SORIMOV = V_sorimov
AND K.cd_FREG = (SELECT Max(cd_FREG)
FROM TVIV023_KARCAS D
WHERE D.cd_ITVALCNV = V_itvalcnv
AND D.cd_ICTTO = V_ictto
AND D.cd_IEMISORA = V_iemisora
AND D.cd_ISERIE = V_iserie
AND D.cd_ICUPON = V_icupon
AND D.cd_ISUBFOL = V_isubfol
AND D.nb_SORIMOV = V_sorimov);
RETURN v_ifoladm;
END;
The problem is that it takes too long to finish the query, I wanted to know if you can help me in providing me with suggestions on how to optimize the query without modifying the sentences if possible, I do not really know much about the database, but I They give suggestions of how to solve this problem I would be very grateful, I remain on the side of friends, greetings and excellent afternoon.
Note: Only one table is used and already probe with using indexes and doing everything locally creating the table and doing the functions but still can not.
In the execution plan he throws this at me:
Practically the indices do not take them into account and assign it in this field cd_ISUBFO and the example that I sent from a user who does not have privileges to add indexes,
OF RECORDS ARE: 48582
Another thing is that for the same reason that it takes a long time I do not know exactly how much, what I have seen is takes more than 1 hour and so it continues. : (