SQL Multiple Joins Query-Query tuning

Question

Please let me know the ways in which I can tune this query for better performance/execution time.

PS-it was developed by some another person and handed over to me.

the query is attached (I know it's a huge one!)--

Any idea on how to handle joins and subquery with aggregrates will be really helpful.

SELECT /*+MONITOR*/ gcibjdnf.danfe,
                         gcibjdnf.bjd_situacao,
                         gcibjdnf.obs_rejeicao,
                         gcibjdnf.bjd_tipo_cobranca,
                         gcibjdnf.bjd_data_vencto_cbs,
  (SELECT SUM(gcinfitens.item_valor_contratar)
                            FROM gcinfitens
                           WHERE gcinfitens.danfe = gcibjdnf.danfe) AS nf_vl_contratar,

                         (SELECT Max(id_contgrupo) FROM gcrcontitens  WHERE gcrcontitens.danfe = gcibjdnf.danfe) AS id_contgrupo,

                         (SELECT MIN(nu_interv)
                            FROM gcrcondper
                           WHERE gcrcondper.id_cond = gcccond.id_cond
                         ) AS nu_min_prz,

                         (SELECT MAX(nu_interv)
                            FROM gcrcondper
                           WHERE gcrcondper.id_cond = gcccond.id_cond
                         ) AS nu_max_prz,

                         priper.vl_taxa AS nu_taxa,
                         priper.cd_tp_taxa,
                         priper.cd_indicador,
                         gcccond.nm_cond,
                         gcccond.cd_tp_ctr,
                         priper.sg_mod AS sg_mod_cond,
                         gcccond.dt_validade,
                         gcccond.cd_sit AS cd_sit_cond,
                         gcccond.nu_car_prz,
                         gcccond.cd_base_carencia,
                         gcccond.nu_car_desc,
                         apcconc.cd_loja,
                         apcconc.cd_concess,
                         apcconc.cd_conc_mat,
                         apcconc.nm_conc,
                         apcconc.nm_apelido,
                         apcconc.cd_tp_mercado,
                         dnccontrfundo.dt_emis_ctr
                    FROM gcibjdnf
               LEFT JOIN apcconc           ON TO_CHAR(apcconc.cd_sap_dealer) = gcibjdnf.dealer_sap
               LEFT JOIN gcccond           ON gcccond.id_cond                = gcibjdnf.id_cond
               LEFT JOIN dnccontrfundo     ON dnccontrfundo.danfe            = gcibjdnf.danfe
                                          AND dnccontrfundo.cd_sit      NOT IN ('CA','RE')
               LEFT JOIN gcrcondper priper ON priper.id_cond                 = gcccond.id_cond
                                          AND priper.sq_per = 1
                   WHERE ((    apcconc.cd_concess = '1586297'
                                      OR apcconc.cd_conc_mat = '1586297') AND gcibjdnf.bjd_situacao = 'I' AND bjd_sit_interna IN ('NO', 'SD'))

                ORDER BY apcconc.nm_apelido, danfe

Answer 1

Move this line:

LEFT JOIN gcccond ON gcccond.id_cond = gcibjdnf.id_cond

first of the joins:

FROM
  gcibjdnf
  LEFT JOIN gcccond ON gcccond.id_cond = gcibjdnf.id_cond
..................................

because the way you ordered the joins, in these lines:

.....................
  LEFT JOIN gcrcondper gmin ON gmin.id_cond = gcccond.id_cond
  LEFT JOIN gcrcondper gmax ON gmax.id_cond = gcccond.id_cond
.........................................................

you are trying to use the column gcccond.id_cond of a table not yet defined in the query.
So the FROM clause must be:

FROM
  gcibjdnf
  LEFT JOIN gcccond ON gcccond.id_cond = gcibjdnf.id_cond
  LEFT JOIN gcinfitens ON gcinfitens.danfe = gcibjdnf.danfe
  LEFT JOIN gcrcontitens ON gcrcontitens.danfe = gcibjdnf.danfe
  LEFT JOIN gcrcondper gmin ON gmin.id_cond = gcccond.id_cond
  LEFT JOIN gcrcondper gmax ON gmax.id_cond = gcccond.id_cond
  LEFT JOIN apcconc ON TO_CHAR(apcconc.cd_sap_dealer) = gcibjdnf.dealer_sap
  LEFT JOIN dnccontrfundo ON dnccontrfundo.danfe = gcibjdnf.danfe
  AND dnccontrfundo.cd_sit NOT IN ('CA', 'RE')
  LEFT JOIN gcrcondper priper ON priper.id_cond = gcccond.id_cond
  AND priper.sq_per = 1

Also in the ORDER BY clause you have the unqualified column danfe. You must qualify it with the table's name/alias like dnccontrfundo.danfe or gcibjdnf.danfe.

Answer 2

I like to break down queries like this into smaller pieces, fix a piece, then add another piece fixing things bit by bit.

I'll start with the FROM bit:

select *
FROM gcibjdnf
LEFT JOIN apcconc
  ON TO_CHAR(apcconc.cd_sap_dealer) = gcibjdnf.dealer_sap
LEFT JOIN gcccond 
  ON gcccond.id_cond = gcibjdnf.id_cond
LEFT JOIN dnccontrfundo
  ON dnccontrfundo.danfe = gcibjdnf.danfe
  AND dnccontrfundo.cd_sit NOT IN ('CA','RE')
LEFT JOIN gcrcondper priper
  ON priper.id_cond = gcccond.id_cond
  AND priper.sq_per = 1
WHERE (
  (apcconc.cd_concess = '1586297' OR apcconc.cd_conc_mat = '1586297')
  AND gcibjdnf.bjd_situacao = 'I'
  AND gcibjdnf.bjd_sit_interna IN ('NO', 'SD') -- assume bjd_sit_interna is in gcibjdnf
)

You should fully qualify bjd_sit_interna so everyone knows what table it is in.

I created tables with just the columns named here. This part of the query seems to "work", except that the logic is inconsistent.

• You do a LEFT JOIN of apcconc, so even if there is no match on apcconc you should return a row.
• You have a WHERE clause that requires the apccon row, so any results with no match on apcconc will be filtered out. In essence, your LEFT JOIN has become an INNER JOIN.

Next, I added the ORDER BY clause.

ORDER BY apcconc.nm_apelido, gcibjdnf.danfe; -- danfe must be fully qualified, else ambiguous

Now in the SELECT list, you do not have any direct aggregate functions; you only have them in scalar subqueries. So, you do not need and you must not have a GROUP BY at the end of your overall query!

In fact, your query will run as is, except for the ambiguous column in the ORDER BY.

You may get strange results, though, since your MIN and MAX subqueries on gcrcondper can access rows that do not meet the JOIN condition. Are you sure that is what you want?

Outside of the inconsistent JOIN logic and this MIN/MAX weirdness, your query should work. Before talking about performance, please review the logic to make sure the query is providing the results you want.