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
Roteiro
•
•
•
•
•
Motivação;
Background;
Materialized Views;
Query ReWrite;
Aproximações;
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!
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);
Background
• OLTP X OLAP
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;
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
Background
• Query com Índices BITMAP
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.
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!
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);
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.
Query ReWrite
• Transparência:
Nosso Exemplo
• Esquema em estrela:
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;
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;
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;
Agregate Computation
• O otimizador pode utilizar a MV
monthly_sales_mv para calcular o AVG !
• Explain Plan do Ex. 2:
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;
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:
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!
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);
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;
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
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 !
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!
Query ReWrite Usando Dimensão
• Explain Plan do Ex. 5:
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!
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;
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;
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;
Filtered Data
• Explain Plan do Ex. 6:
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
Filtered Data
• Explain Plan do Ex. 7:
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
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;
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!
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;
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!
Partition Change Tracking
• Explain Plan do Ex. 9:
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.
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;
Query Rewrite com várias MVs
• Explain Plan do Ex. 10:
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;
Query Rewrite com várias MVs
• Explain Plan do Ex. 11:
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);
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 _.
Aproximações
Aproximações
Aproximações
Conclusões
•
•
•
•
Muitas possibilidades de melhorias;
Responsabilidades ao DBA!
Ferramentas são esseciais;
Novas versões do Oracle têm adicionado
melhorias consideráveis;
Bibliografia Consultada
• Oracle 9i Data Warehousing Guide. Junho/2005.
• Oracle OLAP Application Developer's Guide 10g Release 2.
Agosto/2005.
• Oracle Database Performance Tuning Guide 10g Release 2.
Junho/2005.
• Hillson, Susan et Al. Improve Results with Query Rewrite. Oracle
Magazine Setembro/Outubro, 2003.
• Shasha, Dennis e Bonnet, Philippe. Data Warehouse Tuning:
What’s Different About Data Warehouses. Dbazine, Abril/2005. A
partir de Database Tuning: Principles, Experiments, and
Troubleshooting Techniques, Morgan Kaufmann Publishers, 2003.