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

your question is not clear .. whihc error you have ? "error "%s: Invalid Identifier" in SQL Developer." ??? or " "GCCCOND.ID_COND", invalid identifier" ...the first is related to a param that is not in your query .. the second is a column name ... be sure you have ... anyway you are using aggregation function without group and this should raise another error — ScaisEdge, Jun 29 '19 at 16:52
Please in code questions give a [mre]--cut & paste & runnable code; example input with desired & actual output (including verbatim error messages); tags & clear specification & explanation. That includes the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) PS You have a syntax error. Read the grammar. Show as much as possible that constituent subexpressions are OK. Make it clear that your question is about *that error* & ask re your overall goal later in a new post. PS Clearly there is non-minimal code/data here. — philipxy, Jun 29 '19 at 20:38
(The following is not the syntax problem but--) Learn what LEFT JOIN ON returns: INNER JOIN ON rows UNION ALL unmatched left table rows extended by NULLs. Always know what INNER JOIN you want as part of an OUTER JOIN. A WHERE or INNER JOIN ON that requires a right [sic] table column to be not NULL after an OUTER JOIN ON removes any rows extended by NULLs, ie leaves only INNER JOIN ON rows, ie "turns OUTER JOIN into INNER JOIN". You have that. — philipxy, Jun 29 '19 at 20:39
Please read [this post on asking Oracle tuning questions](https://stackoverflow.com/a/34975420/146325). Not only will it help you ask a better question it might help help tune the query yourself. — APC, Jun 29 '19 at 21:46

forpas · Answer 1 · 2019-06-29T17:00:38.507

2

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.

edited Jun 29 '19 at 17:00

answered Jun 29 '19 at 16:54

forpas

160,666
10
38
76

Thanks for your respons :)..I tried, the error is removed .However I am getting a new error now: "Not a single-group group function". This is showing on the first line of the query, i.e., "SELECT gcibjdnf.danfe," – Sugandha Mishra Jun 29 '19 at 17:01
This problem is caused from your aggregate columns SUM(), MIN(), MAX(). I don't know what you want to do but I think you need a `group by` clause. – forpas Jun 29 '19 at 17:29
All columns that aren't aggregated have to be in the GROUP BY clause (so it'll be a *huge* one). – Littlefoot Jun 29 '19 at 19:21
Really huge, but I'm not sure about the requirement. The query involves many tables and it is fetching so many columns, that I don't see the point of aggregation. – forpas Jun 29 '19 at 19:27
@SugandhaMishra Please ask a new question in a new post. But compose a [mre] because that will pin down code introducing mistakes & help you find mistakes yourself & reduce the need for asking questions. – philipxy Jun 29 '19 at 20:40

score 1 · Answer 2 · answered Jun 30 '19 at 10:43

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.

I felt the same for the aggregrate functions, although was not sure about it as I am not an expert in SQL. As this query was handed over to me I would analyze the logic first. Thanks :) — Sugandha Mishra, Jun 30 '19 at 12:23

SQL Multiple Joins Query-Query tuning

2 Answers2