Otimização de Consultas em Data Warehouse (DW)

1. Universidade Federal de Campina Grande Mestrado em Ciência da Computação Disciplina Banco de Dados Multidimensionais Otimização de Consultas em Data Warehouse (DW) Cláudio E. C. Campelo http://claudiocampelo.com Campina Grande – PB , Setembro de 2006

2. Roteiro • Motivação; • Background; • Materialized Views; • Query ReWrite; • Aproximações;

3. Motivação • Grande quantidade de dados; • Urgência da Informação; • Informações de nível gerencial – agregações; • Tempo de Resposta Aceitável: Cinco Segundos!

4. Background • Modelo Star (Estrela): existe uma tabela dominante no centro, chamada tabela de fatos, com múltiplas junções conectando-a a outras tabelas, sendo estas chamadas de tabelas de dimensão. • Modelo Snow Flake (Floco de Neve): consiste em uma extensão do modelo Estrela onde cada uma das "pontas da estrela" passa a ser o centro de outras estrelas. • Não aconselhável; • Desnormalização (~3FN);

5. Background • OLTP X OLAP

6. Background • Índices BITMAP • Um vetor de bits por valor de atributo; • O tamanho do BITMAP é o número de registros da relação indexada; • Por exemplo, dia da semana teria 7 vetores; • Podemos associar cada bit a faixas de valores, por exemplo 0 to 20.000 reais, 20.000,01 a 35.000 reais;

7. Background • Índices BITMAP • Vantagem: Maior facilidade para manipulação interna de vários BITMAPs para responder uma consulta; • Eficiente em Queries com muitos predicados; • Comando CREATE BITMAP INDEX

8. Background • Query com Índices BITMAP

9. Materialized Views (MV) • Pode contar agregações e junções; • O otimizador decide se usa a MV; • Alterações nas consultas são transparentes ao usuário/desenvolvedor -Não é necessário alterar a consulta SQL ou mesmo a aplicação; • Aplicações e ferramentas de query ad hoc podem se beneficiar.

10. Materialized Views (MV) • Interessante quando a quantidade de dados é bem maior que o resultado; • Pode ser útil também quando envolve junções e projeções custosas!

11. Materialized Views (MV) • Quais visões criar para ajudar o otimizador ? • Como prover atualizações eficientes nas MV se os dados base mudam? Dica: Na dúvida se seria necessário, o SQL Access Advisor (Oracle 10g) pode ajudar fazendo uma análise a partir da carga de trabalho (workload);

12. Query ReWrite • Vários tipos possíveis; • A mais óbvia e simples: Quando os textos são iguais; • É necessário aproveitar a mesma MV para várias consultas! Dica: O Explain Plan ajuda a descobrir, através do plano de execução da consulta, os meios de acesso que o Oracle está utilizando para acessar as tabelas do banco de dados. A seguir, mostraremos o resultado exibido pelo Explain Plan, para todas as consultas exibidas como exemplo.

13. Query ReWrite • Transparência:

14. Nosso Exemplo • Esquema em estrela:

15. Agregate Computation • Exemplo 1: MV que computa a soma e o total dos preços das compras por mês, por produto; • Observar a cláusula ENABLE QUERY REWRITE;

16. Agregate Computation • Exemplo 1: CREATE MATERIALIZED VIEW monthly_sales_mv ENABLE QUERY REWRITE AS SELECT t.month, p.product_id, SUM(ps.purchase_price) as sum_of_sales, COUNT (ps.purchase_price) as total_sales FROM time t, product p, purchases ps WHERE t.time_key = ps.time_key AND ps.product_id = p.product_id GROUP BY t.month, p.product_id;uto; Observar a cláusula ENABLE QUERY REWRITE;

17. Agregate Computation • Exemplo 2: Computa a média dos preços das compras por mês, por produto; SELECT t.month, p.product_id, AVG(ps.purchase_price) as avg_sales FROM time t, product p, purchases ps WHERE t.time_key = ps.time_key AND ps.product_id = p.product_id GROUP BY t.month, p.product_id;

18. Agregate Computation • O otimizador pode utilizar a MV monthly_sales_mv para calcular o AVG ! • Explain Plan do Ex. 2:

19. JoinBack • Exemplo 3: Total de compras por mês, por categoria do produto SELECT t.month, p.category, SUM(ps.purchase_price) as sum_of_sales FROM time t, product p, purchases ps WHERE t.time_key = ps.time_key AND ps.product_id = p.product_id GROUP BY t.month, p.category;

20. JoinBack • A categoria não está na MV; • A coluna product_id, chave primária de PRODUCT, está na MV; • O otimizador faz a Junção da MV com PRODUTO para obter a categoria; • Explain Plan do Ex. 3:

21. Query ReWrite Usando Dimensão • Um DW típico que possui hierarquias nas dimensões; • Por exemplo, dia → mês → ano… • Oracle permite criar objetos dimensões com o comando CREATE DIMENSION; • Este objeto não consome espaço além dos metadados; • Oracle assume que o DBA sabe o que está fazendo!

22. Query ReWrite Usando Dimensão • Exemplo de Dimensão Oracle: CREATE DIMENSION time_dim LEVEL time_key IS time.time_key LEVEL month IS time.month LEVEL quarter IS time.quarter LEVEL year IS time.year HIERARCHY calendar_rollup ( time_key CHILD OF month CHILD OF quarter CHILD OF year ) ATTRIBUTE time_key determines (day_of_week, holiday) ATTRIBUTE month determines (month_name);

23. Query ReWrite Usando Dimensão • Exemplo 4: Compras por ano SELECT t.year, p.category, SUM(ps.purchase_price) as sum_of_sales FROM time t, product p, purchases ps WHERE t.time_key = ps.time_key AND ps.product_id = p.product_id GROUP BY t.year, p.category;

24. Query ReWrite Usando Dimensão • O otimizador pode utilizar a MV monthly_sales_mv, uma vez que possui informações sobre a hierarquia; • Utiliza ainda a técnica anterior (JoinBack) para obter os valores da coluna year a partir de month na MV; • Explain Plan do Ex. 4: • Dica: Se o resultado do ReWrite não for o esperado, use a procedure DBMS_MVIEW .EXPLAIN_REWRITE (a partir do Oracle 9i) para ajudar a diagnosticar o problema

25. Query ReWrite Usando Dimensão • A cláusula ATTRIBUTE determina uma ralação um-para-um; • É possível determinar day_of_week a partir de time_key.; • Por exemplo (ver Exemplo 5), calculamos a soma das compras para "January" em cada ano. • Ainda é possível usar a MV monthly_sales_mv !

26. Query ReWrite Usando Dimensão • Exemplo 5: SELECT t.year, p.category, SUM(ps.purchase_price) as sum_of_sales FROM time t, product p, purchases ps WHERE t.time_key = ps.time_key AND ps.product_id = p.product_id AND t.month_name = 'January‘ • Note que no WHERE há atributos que não estão na MV!

27. Query ReWrite Usando Dimensão • Explain Plan do Ex. 5:

28. Filtered Data • Até agora, vimos exemplos contendo uma MV com todos os dados da tabela PURCHASE; • A partir do Oracle 9i, é possível utilizar reescrever a consulta a partir de uma MV contendo apenas um sobconjunto dos dados!

29. Filtered Data • Nossa MV do Exemplo 1 foi modificada para conter apenas os dados de 1997 a 2002: CREATE MATERIALIZED VIEW five_yr_monthly_sales_mv ENABLE QUERY REWRITE AS SELECT t.month, p.product_id, SUM(ps.purchase_price) as sum_of_sales, COUNT (ps.purchase_price) as total_sales FROM time t, product p, purchases ps WHERE t.time_key = ps.time_key AND ps.product_id = p.product_id AND t.year between 1997 and 2002 GROUP BY t.month, p.product_id;

30. Filtered Data • Agora, o otimizador utiliza esta nova MV caso a consulta seja referente a dados nesse subconjunto, por exemplo a consulta do Exemplo 6 (soma das compras em 2000): SELECT t.month, p.product_id, SUM(ps.purchase_price) as sum_of_sales FROM time t, product p, purchases ps WHERE t.time_key = ps.time_key AND ps.product_id = p.product_id AND t.year = 2000 GROUP BY t.month, p.product_id;

31. Filtered Data • No Oracle 9i, se a condição da consulta não está na MV, ela não é utilizada; • No 10g, ele fornece uma solução híbrida; • Na consulta do Exemplo 7, deseja-se compras mensais de 2000 a 2003; • Neste exemplo, o Oracle 10g usa a MV para os dados de 2000 a 2002;

32. Filtered Data • Explain Plan do Ex. 6:

33. Filtered Data • Exemplo 7: Compras mensais de 2000 a 2003 SELECT t.month, p.product_id, SUM(ps.purchase_price) as sum_of_sales FROM time t, product p, purchases ps WHERE t.time_key = ps.time_key AND ps.product_id = p.product_id AND t.year BETWEEN 2000 and 2003

34. Filtered Data • Explain Plan do Ex. 7:

35. Stale Materialized Views • O que acontece quando a tabela base é alterada? • A Query ReWrite continua usando a MV? • Depende do parâmetro QUERY_REWRITE_INTEGRITY

36. Stale Materialized Views • Valores possíveis para QUERY_REWRITE_INTEGRITY: • STALE_TOLERATED: Usa a MV mesmo quando a tabela base é alterada; • TRUSTED: Assume que a MV fornecida não é “Staled”. Usado também para declarações em dimensões e constraints; • ENFORCED (Default): Garante os mesmos resultados sempre, ou seja, não usa MV “Staled” ou Relacionamentos TRUSTED;

37. Partition Change Tracking • Oracle9i introduziu o Partition Change Tracking (PCT); • Sabe-se qual parte da MV corrsponde a parte alterada da tabela base; • MV pode continua sendo usada para outras partes!

38. Partition Change Tracking • Exemplo 8: Nossa MV foi alterada, adicionando DBMS_MVIEW.PMARKER CREATE MATERIALIZED VIEW monthly_sales_pct_mv ENABLE QUERY REWRITE AS SELECT DBMS_MVIEW.PMARKER(ps.rowid) pm, t.month, p.product_id, SUM(ps.purchase_price) as sum_of_sales, COUNT (ps.purchase_price) as total_sales FROM time t, product p, purchases ps WHERE t.time_key = ps.time_key AND ps.product_id = p.product_id GROUP BY DBMS_MVIEW.PMARKER(ps.rowid), t.month, p.product_id;

39. Partition Change Tracking • Com a tabela PURCHASES particionada pelo time_key, ao adicionar uma nova partição para Abril de 2003, não afetará a consulta do Exemplo 9 (para março de 2002): SELECT t.month, p.product_id, SUM(ps.purchase_price) FROM time t, product p, purchases ps WHERE t.time_key = ps.time_key AND ps.product_id = p.product_id AND ps.time_key >= TO_DATE('01-03-2002', 'DD-MM-YYYY') AND ps.time_key < TO_DATE('01-04-2002', 'DD-MM-YYYY') GROUP BY t.month, p.product_id; • Oracle 10g adiciona soluções híbridas para esta abordagem!

40. Partition Change Tracking • Explain Plan do Ex. 9:

41. Query Rewrite com várias MVs • Soluções híbridas do Oracle fazem uso não apenas de MV + Tabela Base, mas também de várias MV; • Exemplo: Suponha que temos diversas MVs monthly_sales_1990-1994, monthly_sales_1995_to_2000, monthly_sales_2001_to_2005, etc.

42. Query Rewrite com várias MVs • Exemplo 10: Usa-se as 3 MVs SELECT t.month, p.product_id, SUM(ps.purchase_price) as sum_of_sales, FROM time t, product p, purchases ps WHERE t.time_key = ps.time_key AND ps.product_id = p.product_id AND t.year between 1993 and 2003 GROUP BY t.month, p.product_id;

43. Query Rewrite com várias MVs • Explain Plan do Ex. 10:

44. Query Rewrite com várias MVs • Exemplo 11: Usa-se monthly_sales_1990_to_1994 e monthly_sales_1995_to_2000 e obt’’em os dados de 1989 a partir da tabela base SELECT t.month, p.product_id, SUM(ps.purchase_price) as sum_of_sales FROM time t, product p, purchases ps WHERE t.time_key = ps.time_key AND ps.product_id = p.product_id AND t.year between 1989 and 1999 GROUP BY t.month, p.product_id;

45. Query Rewrite com várias MVs • Explain Plan do Ex. 11:

46. Aproximações • Utilização de amostras estatísticas dos dados; • Técnicas de aproximação podem promover resultados interessantes; • Atributos cuja adição de novos valores não alteram os agregados (Média Salarial);

47. Aproximações • Suponha R sendo uma tabela de fatos e as demais de dimensão; • Colhendo uma amostra R_ de R e então fazendo joins baseados em R_ -> S _, T _; • Se a consulta envolve R, S, T então a query pode retornar bons resultados utilizando R _, S _, T _.

48. Aproximações

49. Aproximações

50. Aproximações