Reduce left joins complexity

Question

Hi Im actually working with a query made in oracle 11g and I make a query very big. Is there a way to reduce the left joins Im making?

I don´t know how to reduce them and actually, I think I use every part of the code, can you give me advices of how to reduce the code a little?

SELECT FOLIO_EXP,
       FOLIO,
       DOC,
       REFERENCIA,
       TARJETA,
       CUENTA,
       NOMBRE,
       IMPORTE,
       IMP_REC,
       IMP_REPRE,
       IMP_QUEB,
       FECHA_PAGO,
       MCC,
       COMERCIO,
       PCC,
       FECHA_COM,
       ADQUIRENTE,
       ESTADO,
       "TIPO DE FRAUDE",
       F_FRAUDE,
       F_RECLAMO,
       F_REEMBOLSO,
       DICAMINO
FROM
  (SELECT E.FOLIO_EXP,
          A.FOLIO,
          'D' AS DOC,
          A.REFERENCIA,
          A.TARJETA,
          A.ACCT_NUM AS CUENTA,
          B.NOMBRE_TH AS NOMBRE,
          TO_CHAR(A.MONTO,'999,999.99') IMPORTE,
          D.IMP_REC,
          F.IMP_REPRE,
          DECODE((A.MONTO -D.IMP_REC),NULL,A.MONTO,(A.MONTO -D.IMP_REC)) AS IMP_QUEB,
          D.FECHA_PAGO,
          A.SIC_CDE AS MCC,
          A.COMERCIO,
          C.PCC,
          C.FECHA_COM,
          C.ADQUIRENTE,
          C.ESTADO,
          'INTERNET' AS "TIPO DE FRAUDE",
          A.FECHA_TRANS AS F_FRAUDE,
          A.FECHA_RECLAMO AS F_RECLAMO,
          A.FECHA_BONIFICACION AS F_REEMBOLSO,
          A.USUARIO AS DICAMINO
   FROM OPPF.T00EMISOR A

   LEFT JOIN
     (SELECT A.FECHA,
             A.TARJETA,
             C.PCC,
             C.FECHA_COM,
             C.ADQUIRENTE,
             C.ESTADO
      FROM
        (SELECT TARJETA,
                MAX(FECHA_INFO) FECHA
         FROM OPPF.T00_PCC
         GROUP BY TARJETA) A
      LEFT JOIN
        (SELECT FECHA_INFO,
                TARJETA,
                PCC,
                FECHA_COM,
                ADQUIRENTE,
                ESTADO
         FROM OPPF.T00_PCC)C ON(A.TARJETA = C.TARJETA
                                AND A.FECHA = C.FECHA_INFO)) C ON(TO_CHAR(C.TARJETA) = TO_CHAR(A.TARJETA))

   LEFT JOIN
     (SELECT MAX(SE_REVISO) SE_REVISO,
             NOMBRE_TH,
             TARJETA
      FROM OPPF.T00CASOSRESUELTOS
      GROUP BY TARJETA,
               NOMBRE_TH) B ON(SUBSTR(A.TARJETA,1,16) = SUBSTR(B.TARJETA,1,16)
                               AND A.FECHA_BONIFICACION = B.SE_REVISO)
   LEFT JOIN
     (SELECT MAX(FECHA_PAGO) FECHA_PAGO,
             TARJETA,
             REFERENCIA,
             COUNT(REFERENCIA) N,
             SUM(MONTO_LIQ) AS IMP_REC--, FECHA_TRXS

      FROM OPPF.T00_CC_RECUP
      GROUP BY TARJETA,
               REFERENCIA,
               FECHA_TRXS) D ON(TO_CHAR(D.TARJETA) = TO_CHAR(A.TARJETA)
                                AND D.REFERENCIA = A.REFERENCIA)
   LEFT JOIN
     (SELECT TARJETA,
             COUNT(TARJETA) TRANS,
             COUNT(REFERENCIA) NR,
             REFERENCIA,
             SUM(IMPORTE) IMP_REPRE
      FROM OPPF.T00_REPRESENTACIONES
      GROUP BY TARJETA,
               REFERENCIA) F ON(A.TARJETA = F.TARJETA
                                AND LPAD(TO_CHAR(A.REFERENCIA),23,'0') = LPAD(TO_CHAR(F.REFERENCIA),23,'0'))
   LEFT JOIN
     (SELECT MIN(FOLIO) FOLIO_EXP,
             TARJETA,
             FECHA_BONIFICACION
      FROM OPPF.T00EMISOR --WHERE FECHA_BONIFICACION BETWEEN '02/09/2015' AND '31/12/2015'

      GROUP BY FECHA_BONIFICACION,
               TARJETA
      ORDER BY FECHA_BONIFICACION,
               TARJETA,
               FOLIO_EXP) E ON(E.TARJETA = A.TARJETA
                               AND E.FECHA_BONIFICACION = A.FECHA_BONIFICACION)
   WHERE A.ENTRY_MODE IN ('   ',
                          '1',
                          '01',
                          '001',
                          '0',
                          '00',
                          '012',
                          '010',
                          '12',
                          '10',
                          '11',
                          '011'))
WHERE F_REEMBOLSO = '04/07/2017'

ORDER BY FOLIO,
         REFERENCIA

Is it recommended to use indexes and place this in a sp? The main question is how to reduce the left joins part.

Thanks

Answer 1

As elementary (not advanced) approaches, I'd suggest you to:

1. Take out each subquery to a separated VIEW (while they don't have any parameters).

2. Or else - not sure if this will be useful to you - reorder your query and declare an alias for each subquery in the WITH clause:

   WITH a AS 
   (SELECT ... FROM...),
   b AS 
   (SELECT ... FROM...),
   c AS 
   (SELECT ... FROM...)
   SELECT ... 
   FROM a 
   LEFT JOIN b ... 
   LEFT JOIN c
   

   https://oracle-base.com/articles/misc/with-clause

3. Or else, consider coding the same query logic in a client language. Such huge queries usually turn out a nightmare to be maintained.