SQL Tuning

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
  

Poston Thu, 23 Aug 2007 12:20 pm

Dear friends of the forum I have problem of performance in this \"small query\" (rs) will be someone who can see something to help me improve????



select sum (dif_res), sum (dif_com) RESIDENTIAL commercial, sum (dif_ind) INDUSTRIAL, sum (dif_pub) PUBLIC from (select ct_refaturadas. ident_lig, ct_refaturadas. data_ref, ct_refaturadas.DATA_EMISSAO, ct_geral. total_anterior, the total total total_atual ct_geral ct_refaturadas., val_extra_antes ct_refaturadas., val_extra_depois., (com_post * val_por_eco_dep)-(com_ant * val_por_eco_ant) dif_com (res_post * val_por_eco_dep)-(res_ant * val_por_eco_ant) dif_res (ind_post * val_por_eco_dep)-(ind_ant * val_por_eco_ant) dif_ind(pub_post * val_por_eco_dep)-(pub_ant * val_por_eco_ant) dif_pub, ct_geral. cod_extra from (select extra. cod_extra, sum (extra value) val_extra_antes, account. total, account., ident_conta. data_ref,. ident_lig,. data_emissao, (nvl (conta.com _ TC, 0) + nvl (. res_ct, 0) + nvl (. ind_ct, 0) + nvl (. pub_ct, 0)) as tot_economias, trunc (sum (extra value)/(nvl (conta.com _ TC, 0) + nvl (account. res_ct, 0) + nvl (. ind_ct, 0) + nvl (. pub_ct, 0)), 2) val_por_eco_ant, nvl (conta.com _ TC, 0) com_ant, nvl (. res_ct, 0) res_ant, nvl (. ind_ct, 0) ind_ant, nvl (. pub_ct, 0) From pub_ant, Where Account extra extra. = ident_conta. ident_conta and cod_extra = 0 and extra conta.in _ original = 1 and account. cod_tipo_nf in (1, 5) and account. cod_situacao_nf = group by 2 extra cod_extra, account. total, account., ident_conta. data_ref,. ident_lig,. data_emissao, nvl (conta.com _ TC, 0), nvl (. res_ct, 0), nvl (. ind_ct, 0), nvl (. pub_ct, 0) order by account. ident_lig) ct_geral, (select extra. cod_extra, sum (extra value) val_extra_depois, account. total, account., ident_conta_ant. data_ref,. ident_lig,.DATA_EMISSAO, (nvl (conta.com _ TC, 0) + nvl (. res_ct, 0) + nvl (. ind_ct, 0) + nvl (. pub_ct, 0)) as tot_economias, trunc (sum (extra value)/(nvl (conta.com _ TC, 0) + nvl (. res_ct, 0) + nvl (. ind_ct, 0) + nvl (. pub_ct, 0)) .2) val_por_eco_dep, nvl (conta.com _ TC, 0) com_post, nvl (. res_ct, 0) res_post, nvl (. ind_ct, 0) ind_post, nvl (. pub_ct, 0) From pub_post, Where Account extra extra. = ident_conta. ident_conta and cod_extra = 0 and extra conta.in _original = 0 AND count.COD_SITUACAO_NF IN (1, 3, 4) and account. cod_tipo_nf IN (1, 5) group by cod_extra, extra account. total, account., ident_conta_ant. data_ref,. ident_lig,. data_emissao, nvl (conta.com _ TC, 0), nvl (. res_ct, 0), nvl (. ind_ct, 0), nvl (. pub_ct, 0) order by account. ident_lig) ct_refaturadas Where ct_refaturadas = ct_geral. data_ref data_ref. AND ct_refaturadas.IDENT_LIG = ct_geral.IDENT_LIG and ct_refaturadas.DATA_EMISSAO between To_Date (' ', ' DD/MM/YYYY ') and To_Date (' ', ' DD/MM/YYYY '))
fabiopdb
Location: rio de janeiro

Poston Thu, 23 Aug 2007 1:37 pm

With the query so messed up it will be difficult to find. I'm going to do you a favor:-D Below, I will put your query INDENTED!
Tip: make an Explain this query, but is difficult to guess.
Code: Select all
select sum(dif_res) RESIDENCIAL,
       sum(dif_com) COMERCIAL,
       sum(dif_ind) INDUSTRIAL,
       sum(dif_pub) PUBLICO
  from (select ct_refaturadas.ident_lig,
               ct_refaturadas.data_ref,
               ct_refaturadas.DATA_EMISSAO,
               ct_geral.total as total_anterior,
               ct_refaturadas.total total_atual,
               ct_geral.val_extra_antes,
               ct_refaturadas.val_extra_depois,
               (com_post * val_por_eco_dep) - (com_ant * val_por_eco_ant) dif_com,
               (res_post * val_por_eco_dep) - (res_ant * val_por_eco_ant) dif_res,
               (ind_post * val_por_eco_dep) - (ind_ant * val_por_eco_ant) dif_ind,
               (pub_post * val_por_eco_dep) - (pub_ant * val_por_eco_ant) dif_pub,
               ct_geral.cod_extra
          from (select extra.cod_extra,
                       sum(extra.valor) val_extra_antes,
                       conta.total,
                       conta.ident_conta,
                       conta.data_ref,
                       conta.ident_lig,
                       conta.data_emissao,
                       (nvl(conta.com_ct, 0) + nvl(conta.res_ct, 0) +
                       nvl(conta.ind_ct, 0) + nvl(conta.pub_ct, 0)) as tot_economias,
                       trunc(sum(extra.valor) /
                             (nvl(conta.com_ct, 0) + nvl(conta.res_ct, 0) +
                              nvl(conta.ind_ct, 0) + nvl(conta.pub_ct, 0)),
                             2) val_por_eco_ant,
                       nvl(conta.com_ct, 0) com_ant,
                       nvl(conta.res_ct, 0) res_ant,
                       nvl(conta.ind_ct, 0) ind_ant,
                       nvl(conta.pub_ct, 0) pub_ant
                  From extra, Conta
                 Where extra.ident_conta = Conta.ident_conta
                   and extra.cod_extra = 0
                   and conta.in_original = 1
                   and conta.cod_tipo_nf in (1, 5)
                   and conta.cod_situacao_nf = 2
                 group by extra.cod_extra,
                          conta.total,
                          conta.ident_conta,
                          conta.data_ref,
                          conta.ident_lig,
                          conta.data_emissao,
                          nvl(conta.com_ct, 0),
                          nvl(conta.res_ct, 0),
                          nvl(conta.ind_ct, 0),
                          nvl(conta.pub_ct, 0)
                 order by conta.ident_lig) ct_geral,
               (select extra.cod_extra,
                       sum(extra.valor) val_extra_depois,
                       conta.total,
                       conta.ident_conta_ant,
                       conta.data_ref,
                       conta.ident_lig,
                       CONTA.DATA_EMISSAO,
                       (nvl(conta.com_ct, 0) + nvl(conta.res_ct, 0) +
                       nvl(conta.ind_ct, 0) + nvl(conta.pub_ct, 0)) as tot_economias,
                       trunc(sum(extra.valor) /
                             (nvl(conta.com_ct, 0) + nvl(conta.res_ct, 0) +
                              nvl(conta.ind_ct, 0) + nvl(conta.pub_ct, 0)),
                             2) val_por_eco_dep,
                       nvl(conta.com_ct, 0) com_post,
                       nvl(conta.res_ct, 0) res_post,
                       nvl(conta.ind_ct, 0) ind_post,
                       nvl(conta.pub_ct, 0) pub_post
                  From extra, Conta
                 Where extra.ident_conta = Conta.ident_conta
                   and extra.cod_extra = 0
                   and conta.in_original = 0
                   AND CONTA.COD_SITUACAO_NF IN (1, 3, 4)
                   and conta.cod_tipo_nf IN (1, 5)
                 group by extra.cod_extra,
                          conta.total,
                          conta.ident_conta_ant,
                          conta.data_ref,
                          conta.ident_lig,
                          conta.data_emissao,
                          nvl(conta.com_ct, 0),
                          nvl(conta.res_ct, 0),
                          nvl(conta.ind_ct, 0),
                          nvl(conta.pub_ct, 0)
                 order by conta.ident_lig) ct_refaturadas
         Where ct_refaturadas.data_ref = ct_geral.data_ref
           AND ct_refaturadas.IDENT_LIG = ct_geral.IDENT_LIG
           and ct_refaturadas.DATA_EMISSAO between To_Date('', 'DD/MM/YYYY') and
               To_Date('', 'DD/MM/YYYY'))
dr_gori
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered


  • See also
    Replies
    Views
    Last Post


Return to SQL

Who is online

Users browsing this forum: No registered users and 2 guests