Oracle - Indexes

Question

I have been wondering how I can improve this Query in Oracle:

SELECT  FN_FORMAT_PEROPE(PEROPE) AS PERIODO,
        NVL((SELECT COUNT(1) FROM PG_PAGO P INNER JOIN PG_DETALLE_PAGO DP ON P.IDPAGO = DP.IDPAGO WHERE DP.ESTADO = 1 AND P.PEROPE = R.PEROPE),0) AS NRO_EMITIDOS,
        NVL((SELECT SUM(DP.MONTO) FROM PG_PAGO P INNER JOIN PG_DETALLE_PAGO DP ON P.IDPAGO = DP.IDPAGO WHERE DP.ESTADO = 1 AND P.PEROPE = R.PEROPE),0) AS MONTO_EMITIDO,
        NVL((SELECT COUNT(1) FROM PG_PAGO P INNER JOIN PG_DETALLE_PAGO DP ON P.IDPAGO = DP.IDPAGO WHERE DP.ESTADO = 2 AND P.PEROPE = R.PEROPE),0) AS NRO_ABONADOS,
        NVL((SELECT SUM(DP.MONTO) FROM PG_PAGO P INNER JOIN PG_DETALLE_PAGO DP ON P.IDPAGO = DP.IDPAGO WHERE DP.ESTADO = 2 AND P.PEROPE = R.PEROPE),0) AS MONTO_ABONADO,
        NVL((SELECT COUNT(1) FROM PG_PAGO P INNER JOIN PG_DETALLE_PAGO DP ON P.IDPAGO = DP.IDPAGO WHERE DP.ESTADO = 3 AND P.PEROPE = R.PEROPE),0) AS NRO_RECHAZADOS,
        NVL((SELECT SUM(DP.MONTO) FROM PG_PAGO P INNER JOIN PG_DETALLE_PAGO DP ON P.IDPAGO = DP.IDPAGO WHERE DP.ESTADO = 3 AND P.PEROPE = R.PEROPE),0) AS MONTO_RECHAZADO,
        NVL((SELECT COUNT(1) FROM PG_PAGO P INNER JOIN PG_DETALLE_PAGO DP ON P.IDPAGO = DP.IDPAGO WHERE DP.ESTADO = 4 AND P.PEROPE = R.PEROPE),0) AS NRO_INDEBIDOS,
        NVL((SELECT SUM(DP.MONTO) FROM PG_PAGO P INNER JOIN PG_DETALLE_PAGO DP ON P.IDPAGO = DP.IDPAGO WHERE DP.ESTADO = 4 AND P.PEROPE = R.PEROPE),0) AS MONTO_INDEBIDO,
        NVL((SELECT COUNT(1) FROM PG_PAGO P /*INNER JOIN PG_DETALLE_PAGO DP ON P.IDPAGO = DP.IDPAGO WHERE DP.ESTADO = 5*/ WHERE P.ESTADO = 5 AND P.PEROPE = R.PEROPE),0) AS NRO_RECUPEROS,
        NVL((SELECT SUM(DP.MONTO) FROM PG_PAGO P INNER JOIN PG_DETALLE_PAGO DP ON P.IDPAGO = DP.IDPAGO WHERE DP.ESTADO = 5 AND P.PEROPE = R.PEROPE),0) AS MONTO_RECUPERO
        FROM PG_RESOLUCIONES R ORDER BY R.PEROPE ASC;

Consider:

PG_PAGO and PG_DETALLE_PAGO have about 35.000.000 records.
I use indexes for both tables, but it doesn't work, because this query is too slow.

Thanks for your help.

Boneist · Accepted Answer · 2018-01-25 15:42:29Z

3

Assuming the commenting out of the inner join in your query is a testing artefact, you could grab the results from the *_pago tables in one go using a pivoted query along the lines of this:

SELECT *
FROM   (SELECT p.perope,
               dp.estado,
               dp.monto
        FROM pg_pago p
             INNER JOIN pg_detalle_pago dp ON p.idpago = dp.idpago
        WHERE  dp.estado IN (1, 2, 3, 4, 5))
PIVOT (COUNT(*) AS nro,
       SUM(dp.monto) AS monto
       FOR estado IN (1 AS emitidos,
                      2 AS abonados,
                      3 AS rechazados,
                      4 AS indebidos,
                      5 AS recuperos));

N.B. untested. Also, this works in Oracle 11g and above.

Once you've got that set of results, it should be easy to join it back to your pg_resoluciones table and alias the columns as required.

answered Jan 25, 2018 at 15:42

Boneist

23.6k1 gold badge28 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

aamapanqui Over a year ago

I just tested in Oracle 11g and it works awesome! thanks for your help :)

APC · Accepted Answer · 2018-01-25 15:46:49Z

The key to solving your database performance is to query the tables once instead of ten times. Here is one way to achieve that (caveat: untested, may contain errors)

select  fn_format_perope(r.perope) as periodo
        , sum ( case when dp.estado = 1 and then 1 else 0 end ) as nro_emitidos
        , sum ( case when dp.estado = 1 and then dp.monto else 0 end ) as monto_emitido
        , sum ( case when dp.estado = 2 and then 1 else 0 end ) as nro_abonados
        , sum ( case when dp.estado = 2 and then dp.monto else 0 end ) as monto_abonado
        , sum ( case when dp.estado = 3 and then 1 else 0 end ) as nro_rechazados
        , sum ( case when dp.estado = 3 and then dp.monto else 0 end ) as monto_rechazado
        , sum ( case when dp.estado = 4 and then 1 else 0 end ) as nro_indebidos
        , sum ( case when dp.estado = 4 and then dp.monto else 0 end ) as monto_indebido
        , sum ( case when dp.estado = 5 and then 1 else 0 end ) as nro_recupero
        , sum ( case when dp.estado = 5 and then dp.monto else 0 end ) as monto_recupero
from pg_resoluciones r 
     left outer join pg_pago p 
        on p.perope = r.perope
     left outer join pg_detalle_pago dp 
       on p.idpago = dp.idpago 
order by r.perope asc;

You need to use outer joins for PG_PAGO and PG_DETALLE_PAGO because it seems they may not have matching records (inferred from your use of NVL() in the scalar queries).

This version will work on any version of Oracle. The PIVOT version which @Boneist has written is more elegant, and that's probably the appproach you should take if you're on 11g or higher.

Collectives™ on Stack Overflow

Oracle - Indexes

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related