I'm having performance problem in a querie when I use a subquery to set an ID = number, and then join that subquery in the main query to look for that ID, this method takes about 150 seconds. But if I delete the subquery and look for the ID = number directly in the main query, it takes 0,5 second.
Here some code as exemple: This is the example of 150 seconds In this I set the cto_in_codigo in the With clause.
WITH CONTRATOS AS (
SELECT CTO_IN_CODIGO FROM MGCAR.CAR_CONTRATO
WHERE CTO_IN_CODIGO = 14393
)
SELECT
PT.PAR_IN_CODIGO,
PTC.PARCOR_IN_INDICE
FROM (
SELECT
MAX(PT.HPAR_IN_CODIGO) OVER (PARTITION BY PT.PAR_IN_CODIGO, PT.CTO_IN_CODIGO) HPAR_IN_CODIGO_MAX,
PT.HPAR_IN_CODIGO,
PT.CTO_IN_CODIGO,
PT.PAR_IN_CODIGO
FROM
QUERIE.PARCELA_TOTAL PT
JOIN CONTRATOS CTO
ON CTO.CTO_IN_CODIGO = PT.CTO_IN_CODIGO
WHERE
PT.PAR_DT_REAJUSTE <= TO_DATE('31/12/2017', 'DD/MM/YYYY')
) PT
LEFT OUTER JOIN (
SELECT
MAX(PTC.PARCOR_IN_CODIGO) OVER (PARTITION BY PTC.PAR_IN_CODIGO, PTC.CTO_IN_CODIGO) PARCOR_IN_CODIGO_MAX,
PTC.PARCOR_IN_CODIGO,
PTC.CTO_IN_CODIGO,
PTC.PAR_IN_CODIGO,
PTC.HPAR_IN_CODIGO,
PTC.PARCOR_IN_INDICE
FROM
QUERIE.PARCELA_TOTAL_CORRECAO PTC
JOIN CONTRATOS CTO
ON CTO.CTO_IN_CODIGO = PTC.CTO_IN_CODIGO
) PTC
ON PTC.CTO_IN_CODIGO = PT.CTO_IN_CODIGO
AND PTC.PAR_IN_CODIGO = PT.PAR_IN_CODIGO
AND PTC.HPAR_IN_CODIGO = PT.HPAR_IN_CODIGO
AND PTC.PARCOR_IN_CODIGO = PTC.PARCOR_IN_CODIGO_MAX
WHERE
PT.HPAR_IN_CODIGO = PT.HPAR_IN_CODIGO_MAX
and this is the 0,5 sec. in this I set the cto_in_codigo inside each query
SELECT
PT.PAR_IN_CODIGO,
PTC.PARCOR_IN_INDICE
FROM (
SELECT
MAX(PT.HPAR_IN_CODIGO) OVER (PARTITION BY PT.PAR_IN_CODIGO, PT.CTO_IN_CODIGO) HPAR_IN_CODIGO_MAX,
PT.HPAR_IN_CODIGO,
PT.CTO_IN_CODIGO,
PT.PAR_IN_CODIGO
FROM
QUERIE.PARCELA_TOTAL PT
WHERE
PT.PAR_DT_REAJUSTE <= TO_DATE('31/12/2017', 'dd/MM/yyyy')
AND PT.CTO_IN_CODIGO = 14393
) PT
LEFT OUTER JOIN (
SELECT
MAX(PTC.PARCOR_IN_CODIGO) OVER (PARTITION BY PTC.PAR_IN_CODIGO, PTC.CTO_IN_CODIGO) PARCOR_IN_CODIGO_MAX,
PTC.PARCOR_IN_CODIGO,
PTC.CTO_IN_CODIGO,
PTC.PAR_IN_CODIGO,
PTC.HPAR_IN_CODIGO,
PTC.PARCOR_IN_INDICE
FROM
QUERIE.PARCELA_TOTAL_CORRECAO PTC
WHERE
PTC.CTO_IN_CODIGO = 14393
) PTC
ON PTC.CTO_IN_CODIGO = PT.CTO_IN_CODIGO
AND PTC.PAR_IN_CODIGO = PT.PAR_IN_CODIGO
AND PTC.HPAR_IN_CODIGO = PT.HPAR_IN_CODIGO
AND PTC.PARCOR_IN_CODIGO = PTC.PARCOR_IN_CODIGO_MAX
WHERE
PT.HPAR_IN_CODIGO = PT.HPAR_IN_CODIGO_MAX
what is confusing to me is that the with clause returns just one row with the cto_in_codigo number, much like if I hard code then inside each query like the second code. What is could be causing this super delay?
