Tem como otimizar esta query?

Tuning de Banco, Tuning de SQL, Ferramentas de tuning
Responder
xprata
Rank: Analista Júnior
Rank: Analista Júnior
Mensagens: 93
Registrado em: Ter, 06 Abr 2010 11:31 am
Localização: São Paulo - SP

Senhores, fiquei com esta query gigante...será que seria possível otimizar isto? Alguém tem alguma idéia?
valeu....

Selecionar tudo

SELECT grupo,Sum(dias1),Sum(consumomes1),Sum(estoqueMes1),
                         Sum(dias2),Sum(consumomes2),Sum(estoqueMes2),
                         Sum(dias3),Sum(consumomes3),Sum(estoqueMes3),
                         Sum(dias4),Sum(consumomes4),Sum(estoqueMes4),
                         Sum(dias5),Sum(consumomes5),Sum(estoqueMes5),
                         Sum(dias6),Sum(consumomes6),Sum(estoqueMes6),
                         Sum(dias7),Sum(consumomes7),Sum(estoqueMes7),
                         Sum(dias8),Sum(consumomes8),Sum(estoqueMes8),
                         Sum(dias9),Sum(consumomes9),Sum(estoqueMes9),
                         Sum(dias10),Sum(consumomes10),Sum(estoqueMes10),
                         Sum(dias11),Sum(consumomes11),Sum(estoqueMes11),
                         Sum(dias12),Sum(consumomes12),Sum(estoqueMes12)
FROM
(
--dias
SELECT e.grupo,
       To_Number(To_Char(LAST_DAY(To_Date('01/01/' || to_char(m.data,'YYYY'),'dd/mm/yyyy')),'DD')) dias1,
       0 consumomes1,0 estoqueMes1,
       to_number(To_Char(LAST_DAY(To_Date('01/02/' || to_char(m.data,'YYYY'),'dd/mm/yyyy')),'DD')) dias2,
       0 ConsumoMes2,0 estoqueMes2,
       to_number(To_Char(LAST_DAY(To_Date('01/03/' || to_char(m.data,'YYYY'),'dd/mm/yyyy')),'DD')) dias3,
       0 ConsumoMes3,0 estoqueMes3,
       to_number(To_Char(LAST_DAY(To_Date('01/04/' || to_char(m.data,'YYYY'),'dd/mm/yyyy')),'DD')) dias4,
       0 ConsumoMes4,0 estoqueMes4,
       to_number(To_Char(LAST_DAY(To_Date('01/05/' || to_char(m.data,'YYYY'),'dd/mm/yyyy')),'DD')) dias5,
       0 ConsumoMes5,0 estoqueMes5,
       to_number(To_Char(LAST_DAY(To_Date('01/06/' || to_char(m.data,'YYYY'),'dd/mm/yyyy')),'DD')) dias6,
       0 ConsumoMes6,0 estoqueMes6,
       to_number(To_Char(LAST_DAY(To_Date('01/07/' || to_char(m.data,'YYYY'),'dd/mm/yyyy')),'DD')) dias7,
       0 ConsumoMes7,0 estoqueMes7,
       to_number(To_Char(LAST_DAY(To_Date('01/08/' || to_char(m.data,'YYYY'),'dd/mm/yyyy')),'DD')) dias8,
       0 ConsumoMes8,0 estoqueMes8,
       to_number(To_Char(LAST_DAY(To_Date('01/09/' || to_char(m.data,'YYYY'),'dd/mm/yyyy')),'DD')) dias9,
       0 ConsumoMes9,0 estoqueMes9,
       to_number(To_Char(LAST_DAY(To_Date('01/10/' || to_char(m.data,'YYYY'),'dd/mm/yyyy')),'DD')) dias10,
       0 ConsumoMes10,0 estoqueMes10,
       to_number(To_Char(LAST_DAY(To_Date('01/11/' || to_char(m.data,'YYYY'),'dd/mm/yyyy')),'DD')) dias11,
       0 ConsumoMes11,0 estoqueMes11,
       to_number(To_Char(LAST_DAY(To_Date('01/12/' || to_char(m.data,'YYYY'),'dd/mm/yyyy')),'DD')) dias12,
       0 ConsumoMes12,0 estoqueMes12
FROM movimento m,cadastro  e
WHERE 		m.cd_material = e.cd_material
	and 	  m.data between To_Date('01/01/' || to_char(TO_DATE('01/12/2010','dd/mm/yyyy'),'YYYY'),'dd/mm/yyyy')
  and     last_day(TO_DATE('01/12/2010','dd/mm/yyyy')) + 86399/86400
GROUP BY
         to_char(data, 'YYYY'),e.grupo
HAVING
         to_char(data, 'YYYY') = to_char(TO_DATE('01/12/2010','dd/mm/yyyy'), 'YYYY' )
union
--consumo
SELECT e.grupo,
     0 dias1,SUM(decode(EXTRACT(MONTH FROM data), 1,vl_consumo,0)) ConsumoMes1,0 estoqueMes1,
     0 dias2,SUM(decode(EXTRACT(MONTH FROM data), 2,vl_consumo,0)) ConsumoMes2,0 estoqueMes2,
     0 dias3,SUM(decode(EXTRACT(MONTH FROM data), 3,vl_consumo,0)) ConsumoMes3,0 estoqueMes3,
     0 dias4,SUM(decode(EXTRACT(MONTH FROM data), 4,vl_consumo,0)) ConsumoMes4,0 estoqueMes4,
     0 dias5,SUM(decode(EXTRACT(MONTH FROM data), 5,vl_consumo,0)) ConsumoMes5,0 estoqueMes5,
     0 dias6,SUM(decode(EXTRACT(MONTH FROM data), 6,vl_consumo,0)) ConsumoMes6,0 estoqueMes6,
     0 dias7,SUM(decode(EXTRACT(MONTH FROM data), 7,vl_consumo,0)) ConsumoMes7,0 estoqueMes7,
     0 dias8,SUM(decode(EXTRACT(MONTH FROM data), 8,vl_consumo,0)) ConsumoMes8,0 estoqueMes8,
     0 dias9,SUM(decode(EXTRACT(MONTH FROM data), 9,vl_consumo,0)) ConsumoMes9,0 estoqueMes9,
     0 dias10,SUM(decode(EXTRACT(MONTH FROM data),10,vl_consumo,0)) ConsumoMes10,0 estoqueMes10,
     0 dias11,SUM(decode(EXTRACT(MONTH FROM data), 11,vl_consumo,0)) ConsumoMes11,0 estoqueMes11,
     0 dias12,SUM(decode(EXTRACT(MONTH FROM data), 12,vl_consumo,0)) ConsumoMes12,0 estoqueMes12
FROM movimento m,cadastro  e
WHERE 		m.cd_material = e.cd_material
	and 	  m.data between To_Date('01/01/' || to_char(TO_DATE('01/12/2010','dd/mm/yyyy'),'YYYY'),'dd/mm/yyyy')
  and     last_day(TO_DATE('01/12/2010','dd/mm/yyyy')) + 86399/86400
GROUP BY
         to_char(data, 'YYYY'),e.grupo
HAVING
         to_char(data, 'YYYY') = to_char(TO_DATE('01/12/2010','dd/mm/yyyy'), 'YYYY' )

UNION

--estoque
SELECT e.grupo,
       0 dias1,0 consumomes1,SUM(decode(EXTRACT(MONTH FROM m.data), 1,m.vl_estoque,0)) EstoqueMes1,
       0 dias2,0 consumomes2,SUM(decode(EXTRACT(MONTH FROM m.data), 2,m.vl_estoque,0)) EstoqueMes2,
       0 dias3,0 consumomes3,SUM(decode(EXTRACT(MONTH FROM m.data), 3,m.vl_estoque,0)) EstoqueMes3,
       0 dias4,0 consumomes4,SUM(decode(EXTRACT(MONTH FROM m.data), 4,m.vl_estoque,0)) EstoqueMes4,
       0 dias5,0 consumomes5,SUM(decode(EXTRACT(MONTH FROM m.data), 5,m.vl_estoque,0)) EstoqueMes5,
       0 dias6,0 consumomes6,SUM(decode(EXTRACT(MONTH FROM m.data), 6,m.vl_estoque,0)) EstoqueMes6,
       0 dias7,0 consumomes7,SUM(decode(EXTRACT(MONTH FROM m.data), 7,m.vl_estoque,0)) EstoqueMes7,      
       0 dias8,0 consumomes8,SUM(decode(EXTRACT(MONTH FROM m.data), 8,m.vl_estoque,0)) EstoqueMes8,
       0 dias9,0 consumomes9,SUM(decode(EXTRACT(MONTH FROM m.data), 9,m.vl_estoque,0)) EstoqueMes9,
       0 dias10,0 consumomes10,SUM(decode(EXTRACT(MONTH FROM m.data), 10,m.vl_estoque,0)) EstoqueMes10,
       0 dias11,0 consumomes11,SUM(decode(EXTRACT(MONTH FROM m.data), 11,m.vl_estoque,0)) EstoqueMes11,
       0 dias12,0 consumomes12,SUM(decode(EXTRACT(MONTH FROM m.data), 12,m.vl_estoque,0)) EstoqueMes12
FROM  estoque m, cadastro e
WHERE 		m.cd_material = e.cd_material
	and 	  m.data between To_Date('01/01/' || to_char(TO_DATE('01/12/2010','dd/mm/yyyy'),'YYYY'),'dd/mm/yyyy')
  and     last_day(TO_DATE('01/12/2010','dd/mm/yyyy')) + 86399/86400
GROUP BY
         to_char(m.data, 'YYYY'),e.grupo
HAVING
         to_char(m.data, 'YYYY') = to_char(TO_DATE('01/12/2010','dd/mm/yyyy'), 'YYYY' )
ORDER BY 1

)
GROUP BY grupo
ORDER BY 1
Diego_Mello
Rank: DBA Júnior
Rank: DBA Júnior
Mensagens: 229
Registrado em: Sex, 05 Set 2008 2:59 pm
Localização: Igrejinha - RS
Diego Mello
Igrejinha - RS
www.twitter.com/diegolmello

Cara, essa query está varrendo várias vezes as mesmas tabelas...

To voltando de férias hoje e, na corrida, o que me veio em mente foi isso... :P

Selecionar tudo

SQL> SELECT grupo
  2        ,MAX(dia1) dia1
  3        ,SUM(consumo1) consumo1
  4        ,SUM(estoque1) estoque1
  5        ,MAX(dia2) dia2
  6        ,SUM(consumo2) consumo2
  7        ,SUM(estoque2) estoque2
  8        ,MAX(dia3) dia3
  9        ,SUM(consumo3) consumo3
 10        ,SUM(estoque3) estoque3
 11    FROM (WITH tmp AS (SELECT 1 grupo
 12                             ,TRUNC(SYSDATE) data
 13                             ,1 consumo
 14                             ,1 estoque
 15                         FROM dual
 16                       UNION
 17                       SELECT 2 grupo
 18                             ,TRUNC(SYSDATE) + 2 data
 19                             ,2 consumo
 20                             ,3 estoque
 21                         FROM dual
 22                       UNION
 23                       SELECT 1 grupo
 24                             ,TRUNC(SYSDATE) + 20 data
 25                             ,3 consumo
 26                             ,5 estoque
 27                         FROM dual
 28                       UNION
 29                       SELECT 1 grupo
 30                             ,TRUNC(SYSDATE) + 30 data
 31                             ,2 consumo
 32                             ,1 estoque
 33                         FROM dual
 34                       UNION
 35                       SELECT 1 grupo
 36                             ,TRUNC(SYSDATE) + 60 data
 37                             ,7 consumo
 38                             ,1 estoque
 39                         FROM dual)
 40           SELECT tmp.grupo
 41                 ,DECODE(extract(MONTH FROM tmp.data), 1, LAST_DAY(tmp.data)) dia1
 42                 ,DECODE(extract(MONTH FROM tmp.data), 1, tmp.consumo) consumo1
 43                 ,DECODE(extract(MONTH FROM tmp.data), 1, tmp.estoque) estoque1
 44                 ,DECODE(extract(MONTH FROM tmp.data), 2, LAST_DAY(tmp.data)) dia2
 45                 ,DECODE(extract(MONTH FROM tmp.data), 2, tmp.consumo) consumo2
 46                 ,DECODE(extract(MONTH FROM tmp.data), 2, tmp.estoque) estoque2
 47                 ,DECODE(extract(MONTH FROM tmp.data), 3, LAST_DAY(tmp.data)) dia3
 48                 ,DECODE(extract(MONTH FROM tmp.data), 3, tmp.consumo) consumo3
 49                 ,DECODE(extract(MONTH FROM tmp.data), 3, tmp.estoque) estoque3
 50             FROM tmp)
 51            GROUP BY grupo;
 
     GRUPO DIA1          CONSUMO1   ESTOQUE1 DIA2          CONSUMO2   ESTOQUE2 DIA3          CONSUMO3   ESTOQUE3
---------- ----------- ---------- ---------- ----------- ---------- ---------- ----------- ---------- ----------
         1 31/1/2011            4          6 28/2/2011            2          1 31/3/2011            7          1
         2 31/1/2011            2          3                                                          
 
SQL> 
xprata
Rank: Analista Júnior
Rank: Analista Júnior
Mensagens: 93
Registrado em: Ter, 06 Abr 2010 11:31 am
Localização: São Paulo - SP

Valeu pelo retorno...
mas não entendi...no exemplo que dei as unions trazem informações de tabela diferentes e no seu parece que todas trazem as mesmas informações...
Preciso do consumo, do estqoue e da qtde de dias dentro do mês, pois terei que fazer um cálculo...
Mas vou dar uma olhada para entender melhor..
Valeu
Avatar do usuário
fbifabio
Moderador
Moderador
Mensagens: 199
Registrado em: Ter, 22 Fev 2011 1:51 pm
Localização: São Paulo - SP
Contato:

Pessoal, segue abaixo o link de um artigo que eu publiquei em meu blog sobre o SQL Loader, que talvez possam ajudar a tirar algumas dúvidas:


http://www.fabioprado.net/2010/10/carga ... o-sql.html

[]s

Fábio Prado
Avatar do usuário
fbifabio
Moderador
Moderador
Mensagens: 199
Registrado em: Ter, 22 Fev 2011 1:51 pm
Localização: São Paulo - SP
Contato:

Cara, evite utilizar várias vezes a mesma tabela em uma query. Para evitar isso utilize a cláusula WITH (ver artigo http://www.fabioprado.net/2010/10/claus ... eries.html). você tbém poderia escrever uma visão materializada e utilizar um recurso chamado query rewrite. Eu ensino tudo isso no meu treinamento de tuning (http://www.fabioprado.net/p/treinamentos.html).

[]s

Fábio Prado
www.fabioprado.net
Responder
  • Informação
  • Quem está online

    Usuários navegando neste fórum: Nenhum usuário registrado e 3 visitantes