Aprenda PL/SQL

Tem como otimizar esta query?

Tuning de Banco, Tuning de SQL, Ferramentas de tuning
  

Mensagemem Seg, 10 Jan 2011 5:58 pm

Senhores, fiquei com esta query gigante...será que seria possível otimizar isto? Alguém tem alguma idéia?
valeu....
Código: Selecionar todos
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
xprata
Localização: São Paulo - SP

Mensagemem Ter, 11 Jan 2011 10:53 am

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

Código: Selecionar todos
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>
Diego_Mello
Localização: Igrejinha - RS

Diego Mello
Igrejinha - RS
www.twitter.com/diegolmello

Mensagemem Ter, 11 Jan 2011 7:33 pm

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
xprata
Localização: São Paulo - SP

Mensagemem Ter, 22 Fev 2011 3:29 pm

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
fbifabio
Localização: São Paulo - SP

Mensagemem Qui, 21 Jul 2011 3:04 pm

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
fbifabio
Localização: São Paulo - SP



Voltar para DBA Tuning

Quem está online

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