O que fazer quando é necessário actualizar um dado registo de uma dimensão?
3. Compara os custos de cada plano possível e escolhe o que tem menor custo
143
Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Planos baseados em regras
O optimizador de queries define o plano baseado em:
- Conjunto prédefinido de regras de precedência (regras de ouro) - Estas regras são:
- Fixas
- Predeterminadas
- Estão ordenadas (da melhor para a menos boa)
- Não dependem de aspectos relativos aos dados tais como volumes das tabelas, distribuição dos índices, etc
- As regras indicam ao optimizador que tipo de acesso a uma dada tabela deve fazer, como deve executar um join, se deve usar um índice ou não, etc
- O Oracle tem um conjunto primário de cerca de 20 regras.
Henrique Madeira, DEI-FCTUC, 2001
Planos baseados em custos
O optimizador faz o seguinte:
145
Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Planos baseados em custos (diagrama)
Parser
Gerador de planos
Execução
Query
Query parsed
Plano de execução
Resultados Estimador
de custos
Gestor de catálogo Optimizador
Avaliador de planos
Henrique Madeira, DEI-FCTUC, 2001
Ojectivos e meios dos planos baseados em custos
• Objectivos:
• Normalmente os custos são calculados para optimização da execução do maior número de queries por unidade de tempo (throughput)
• Pode-se também establecer como objectivo a minimização do tempo de resposta (importante para DW) ou da utilização dos recursos.
• Meios
• Estatísticas sobre os objectos e os dados (clusters, tabelas, índices,..). O comando ANALIZE é o principal método para recolher as estatísticas.
• Sugestões do utilizador (hints)
• ...
147
Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Planos baseados em custos e DW
• Não há actualizações, pelo que a manutenção das estatísticas é pequena.
• A actualização das estatísticas pode ser feita a seguir aos carregamentos periódicos.
• Queries muito complexas, pelo que a análise de custos permite grandes optimizações.
Henrique Madeira, DEI-FCTUC, 2001
Métodos de acesso aos dados
• Todos os dados estão em tabelas.
• Não é difícil encontrar os dados numa base de dados; o problema é fazê-lo da maneira mais eficaz.
• Os planos de execução de queries usam muitos métodos de acesso aos dados que é necessário entender(alguns são novos para DW):
– Full table scan
– Por ROWID (através de índices B*-Tree ou bit-map) – Join indexes
– Hash indexes
– Diferentes tipos de junções – Etc.
149
Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Métodos de junções para DW
• Star join
• Star join em Oracle
• Oracle star transformation
Henrique Madeira, DEI-FCTUC, 2001
Star Join
factos PKs
factos PKs
factos PKs
factos
Dim 1 Dim 2
Dim 3 Dim 4
ji ji
ji ji
1 2
3
4 5
6 7
Conjunto temporário de chaves para factos 8
• Tem de existir um bit-map join index (semelhante a um bit-map) entre a tabela de factos e cada uma das dimensões.
• A query é executada começando pelas dimensões e encontrando as suas entradas nos join indexes.
• São processados todos os bit-map join indexes para encontrar as linhas da tabela de factos que são necessárias (e só essas).
151
Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Star Join em Oracle (Oracle star query)
I n c l u i c o n j u n t o temporário de chaves para factos
linhas
de Dim 4 producto cartesiano
linhas de Dim 3 producto cartesiano linhas de Dim 2 linhas
de Dim 1
factos
Dim 1 Dim 2
Dim 3 Dim 4
1 2
3
4 5
6 7 8
producto cartesiano
• O Oracle não tem join index pelo que o método é realizado fazendo o produto cartesiano entre as linhas seleccionas de cada dimensão.
• Pode ser muito menos eficiente do que o star join original porque os produtos cartesinos podem conter muitas linhas.
Henrique Madeira, DEI-FCTUC, 2001
Oracle star transformation
• Destina-se a resolver os casos em que no Oracle star query os produto cartesianos dos registos selecionados nas dimensões são muito grandes.
• Requer um índice bit-map em cada uma das colunas de chave estrangeira na tabela de factos.
• Estes índices são combinados de modo a encontrar
os registos pretendidos na tabela de factos (o que é
muito semelhante ao star join original).
153
Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Oracle start transformation: execução de queries
SELECT f.*
FROM factos f, dim1 d1, dim2 d2, dim3 d3, dim4 d4 WHERE f.fk1 = d1.pk /* junção */
AND f.fk2 = d2.pk /* junção */
AND f.fk3 = d3.pk /* junção */
AND f.fk4 = d4.pk /* junção */
AND d1.atr1 = 'aaa' /* restrição */
AND d2.atr2 = 'ccc' /* restrição */
AND d3.atr3 = 'eee' /* restrição */
AND d4.atr4 = 'ggg'; /* restrição */
SELECT f.*
FROM factos f
WHERE f.fk1 IN (SELECT pk FROM dim1 WHERE atr1 = 'aaa') AND f.fk2 IN (SELECT pk FROM dim2 WHERE atr2 = 'ccc') AND f.fk3 IN (SELECT pk FROM dim3 WHERE atr3 = 'eee') AND f.fk4 IN (SELECT pk FROM dim4 WHERE atr4 = 'ggg');
Este método obriga à reescrita das queries. Por exemplo:
é transformada em
Henrique Madeira, DEI-FCTUC, 2001
Particionamento
155
Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Particionamento de tabelas e índices
• Decompõe as tabelas é bocados mais pequenos chamados partições
• Muito útil para gerir tabelas (e índices) muito grandes
• Uma vez definidas, os comandos SQL podem manipular as partições em vez da tabela inteira.
• O particionamento é particularmente útil quando as partições ficam em discos diferentes
(no Oracle criando vários tablespaces tendo cada um ficheiros em discos diferentes)• Transparência nas partições: o SGBD decide que partições são usadas na resposta a uma query.
Henrique Madeira, DEI-FCTUC, 2001
Particionamento de tabelas e de índices (cont.)
Em quase todos os SGBD o particionamento de tabelas e índices pode-se combinar livremente. Exemplos:
– Tabela particionada com índices não particionados;
– Tabela particionada com índices particionados;
– Tabela particionada com parte dos índices particionados e com outros não particionados;
– Tabela não particionada com índices particionados (todos ou parte).
157
Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Vantagens e desvantages do particionamento
• Vantagens:
– Acessos mais rápidos (menos dados)
– Pode-se conter o impacto de falhas (backup e recuperação independente para cada partição)
– Muito melhor gestão dos discos.
• Desvantagens:
– Complica ainda mais a administração;
– A sua eficácia depende muito de como são decididas as partições e de como as queries acedem aos dados.
Henrique Madeira, DEI-FCTUC, 2001
Particionamento horizontal e vertical
• Horizontal:
– Cada partição contém parte dos registos da tabela
– A estrutura é a mesma em todas as partições (mas o projecto físico pode ser diferente).
– Conhecido vulgarmente por range partitioningmas inclui na verdade vários métodos de particionamento.
– É de longe o tipo de particionamento mais utilizado.
• Vertical:
– Cada partição tem parte das colunas da tabela – A estrutura é diferente de partição para partição.
159
Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Chave de particionamento (horizontal)
• Cada registo numa tabela particionada tem de ser associado a uma (e só uma) partição.
• Chave de particionamento: atributo ou atributos de uma tabela particionada que permitem associar de forma não ambígua cada registo a uma dada partição.
• A chave de particionamento é usada nas operações de Insert, Update, Delete e Select para encontrar a partição para cada registo.
Henrique Madeira, DEI-FCTUC, 2001
Métodos de particionamento horizontal
• Particionamento por gama de valores (range partitioning)
• Particionamento por lista explícita (list partitioning)
• Particionamento uniforme por chave (hash partitioning)
• Sub-particionamento (composite partitioning)
– Range-hash partitioning – Range-list partitioning
161
Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Particionamento por gama de valores
(range partitioning)
• As partições são criadas de acordo com gamas de valores especificadas para um dado atributo (ou conjunto de atributos).
• Pressupõe que os valores do atributo usado para particionamento forma um conjunto ordenado.
• Muito útil quando os dados se distribuem naturalmente em gamas de valores (e.g., meses do ano, faixas etárias, etc).
• Os melhores resultados (em performance) quando:
– O tamanho das partições resulta razoavelmente uniforme
– As queries coincidem com a lógica do particionamento, levando a que os acessos sejam feitos a um pequeno conjunto de partições.
• Assume que a distribuição dos dados nos atributos usados para o particionamento é conhecida no momento em que se cria a tabela
Henrique Madeira, DEI-FCTUC, 2001
Exemplo de range partitioning (Oracle)
CREATE TABLEsales_range (salesman_id NUMBER(5), salesman_name VARCHAR2(30), sales_amount NUMBER(10), sales_date DATE)
PARTITION BY RANGE(sales_date) (
PARTITIONsales_jan2000 VALUES LESS THAN(TO_DATE('02/01/2000','DD/MM/YYYY')), PARTITIONsales_feb2000 VALUES LESS THAN(TO_DATE('03/01/2000','DD/MM/YYYY')), PARTITIONsales_mar2000 VALUES LESS THAN(TO_DATE('04/01/2000','DD/MM/YYYY')), PARTITIONsales_apr2000 VALUES LESS THAN(TO_DATE('05/01/2000','DD/MM/YYYY')) );
A tabela é criada em quantas partições?
Método de particionamento
Atributo de particionamento
Definição das fronteiras que definem cada partição
Em que tablespaces são criadas as partições?
163
Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Exemplo de range partitioning com vários atributos (Oracle)
CREATE TABLEsales ( invoice_no NUMBER, sale_year INT NOT NULL, sale_month INT NOT NULL, sale_day INT NOT NULL )
PARTITION BY RANGE(sale_year, sale_month, sale_day) ( PARTITION sales_q1 VALUES LESS THAN (1999, 04, 01)
TABLESPACE tsa,
PARTITION sales_q2 VALUES LESS THAN (1999, 07, 01) TABLESPACE tsb,
PARTITION sales_q3 VALUES LESS THAN (1999, 10, 01) TABLESPACE tsc,
PARTITION sales_q4 VALUES LESS THAN (2000, 01, 01) TABLESPACE tsd );
Múltiplos atributos de particionamento
Indicação explícita do tablespace onde fica a partição
Henrique Madeira, DEI-FCTUC, 2001
Particionamento por lista explícita
(list partitioning)
• As partições são criadas de acordo com uma lista de valores (de um dado atributo) explicitamente especificada.
• Muito útil quando os dados não formam conjuntos ordenados nem tem relação entre si (as partições são indicadas explicitamente).
• Só se pode usar um atributo para definir a lista (Oracle)
• Os bons (ou menos bons) resultados no que toca à distribuição uniforme dos dados pelas partições e à relação entre as partições e as queries depende da lista de valores especificada
• Assume que se conhece previamente os valores exactos dos dados do atributos usado para o particionamento
165
Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Exemplo de list partitioning (Oracle)
CREATE TABLE sales_list (salesman_id NUMBER(5), salesman_name VARCHAR2(30), sales_state VARCHAR2(20), sales_amount NUMBER(10), sales_date DATE)
PARTITION BY LIST(sales_state) (
PARTITION sales_west VALUES('California', 'Hawaii'),
PARTITION sales_east VALUES ('New York', 'Virginia', 'Florida'), PARTITION sales_central VALUES('Texas', 'Illinois')
PARTITION sales_other VALUES(DEFAULT) );
Método de particionamento
Definição explícita do valores Partição por defeito para quando
os dados não correspondem a nenhum dos valores especificados
Atributo de particionamento
Henrique Madeira, DEI-FCTUC, 2001
Particionamento uniforme
(hash partitioning)
• As partições são definidas através de uma função de hash, pelo que não dependem directamente dos valores dos atributos.
• Muito útil nas seguintes situações:
– Quando não se sabe à priori como os dados se vão distribuir (por isso é arriscado usar particionamento por gama ou lista);
– Quando se sabe como os dados se distribuem mas é difícil gerar partições regulares;
– Quando o particionamento por gama ou lista leva a que os dados sejam particionados de um modo não favorável face às queries mais frequentes.
167
Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Exemplo de hash partitioning (Oracle)
CREATE TABLE sales_hash (salesman_id NUMBER(5), salesman_name VARCHAR2(30), sales_amount NUMBER(10), week_no NUMBER(2))
PARTITION BY HASH(salesman_id) PARTITIONS 4
STORE IN (data1, data2, data3, data4);
Método de particionamento
Tablespaces onde as partições ficam armazenadas Número de partições
Atributo a que é aplicada a função
de hash
Henrique Madeira, DEI-FCTUC, 2001
Sub-particionamento (Oracle)
• O método de particionamento base é range:
– Range + hash partitioning – Range + list partitioning
• Útil quando os objectos são mesmos muito grandes.
A utilização de hash ou list nas sub-partições segue a mesma lógica de quando estes métodos são
usados em particionamento normal:
– hashpara particionamento regular;
– listpara controlar específicamente as partições.
169
Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Exemplo de range-hash partitioning (Oracle)
CREATE TABLE sales_composite (salesman_id NUMBER(5),
salesman_name VARCHAR2(30), sales_amount NUMBER(10), sales_date DATE)
PARTITION BY RANGE(sales_date) SUBPARTITION BY HASH(salesman_id) SUBPARTITION TEMPLATE(
SUBPARTITION sp1 TABLESPACE data1, SUBPARTITION sp2 TABLESPACE data2, SUBPARTITION sp3 TABLESPACE data3, SUBPARTITION sp4 TABLESPACE data4)
(PARTITION sales_jan2000 VALUES LESS THAN(TO_DATE('02/01/2000','DD/MM/YYYY')) PARTITION sales_feb2000 VALUES LESS THAN(TO_DATE('03/01/2000','DD/MM/YYYY')) PARTITION sales_mar2000 VALUES LESS THAN(TO_DATE('04/01/2000','DD/MM/YYYY')) PARTITION sales_apr2000 VALUES LESS THAN(TO_DATE('05/01/2000','DD/MM/YYYY')) PARTITION sales_may2000 VALUES LESS THAN(TO_DATE('06/01/2000','DD/MM/YYYY')));
Método de particionamento primário (range)
As sub-partições e respectivos tablespaces são indicados por um template
Chave para o particionamento
primário Chave para o
sub-particionamento (hash)
Definição das partições primárias
Henrique Madeira, DEI-FCTUC, 2001
Exemplo de range-list partitioning (Oracle)
CREATE TABLE bimonthly_regional_sales (deptno NUMBER,
item_no VARCHAR2(20), txn_date DATE,
txn_amount NUMBER, state VARCHAR2(2))
PARTITION BY RANGE (txn_date) SUBPARTITION BY LIST (state) SUBPARTITION TEMPLATE(
SUBPARTITION east VALUES('NY', 'VA', 'FL') TABLESPACE ts1, SUBPARTITION west VALUES('CA', 'OR', 'HI') TABLESPACE ts2, SUBPARTITION central VALUES('IL', 'TX', 'MO') TABLESPACE ts3) (
PARTITION janfeb_2000 VALUES LESS THAN (TO_DATE('1-MAR-2000','DD-MON-YYYY')), PARTITION marapr_2000 VALUES LESS THAN (TO_DATE('1-MAY-2000','DD-MON-YYYY')), PARTITION mayjun_2000 VALUES LESS THAN (TO_DATE('1-JUL-2000','DD-MON-YYYY')) );
171
Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Particionamento de índices (Oracle)
• Global indexes: particionados independentemente das tabelas (bons resultados em bases de dados operacionais).
• Local indexes: o particionamento é associado às partições definidas para as tabelas (são estes os mais usados em data warehousing).
Henrique Madeira, DEI-FCTUC, 2001
Discos RAID
Redundant Arrays of Inexpensive Disk
173
Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
RAID - Redundant Arrays of Inexpensive Disk
Objectivos de estudo:
– Entender os problemas e as limitações do sistema de discos numa base de dados;
– Conhecer os principais conceitos da tecnologia RAID;
– Saber quais os benefícios que a tecnologia RAID pode trazer;
– Saber quando é útil usar RAID e em que configuração (nível), dependendo do tipo de base de dados.
Henrique Madeira, DEI-FCTUC, 2001
Evolução da velocidade de processadores
Tempo Velocidade
175
Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Evolução dos discos (custo e velocidade)
In “Computer Architecture: A Quantitative Approach”, J. Hennessy and D. Patterson, Morgan Kaufmann Publishers, Inc. 1996.
O tempo de acesso médio baixou pouco, pois há limites mecanicos à sua melhoria
Tempo Velocidade Custo MBytes
Henrique Madeira, DEI-FCTUC, 2001