时间戳

首页 > 解决方案 > SQL 多连接查询-查询调优

sql - SQL 多连接查询-查询调优

问题描述

请让我知道如何调整此查询以获得更好的性能/执行时间。

PS-它是由另一个人开发并交给我的。

附上查询（我知道这是一个巨大的！）--

关于如何使用聚合处理连接和子查询的任何想法都会非常有帮助。

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

标签： sqloracleperformancequery-optimizationsql-tuning

解决方案

移动这一行：

LEFT JOIN gcccond ON gcccond.id_cond = gcibjdnf.id_cond

第一个连接：

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

因为您订购连接的方式，在这些行中：

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

您正在尝试使用gcccond.id_cond尚未在查询中定义的表的列。
所以该FROM子句必须是：

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

同样在ORDER BY子句中，您有 unqualified column danfe。您必须使用表的名称/别名来限定它，例如dnccontrfundo.danfeor gcibjdnf.danfe。

推荐阅读