Projeto Físico de Bancos de
Dados
Fabio Porto
LNCC DEXL
Agenda
lIntrodução
lArmazenamento em Disco
l
Estruturas internas de representação
l
Indexação
Introdução
l
Projeto Conceitual BD
–
Projeto lógico de BD - Esquema
Relacional -> definição de relações e R.I.
l
Projeto físico de BD
- Esquema interno
de BD -> criação de tabelas, índices,
distribuição de dados, alocação em
disco,…
Ciclo de vida de projeto de BD
Projeto conceitual Esquema conceitual
Projeto logico
Esquema de banco de dados Normalizado (ex. Relacionall) Projeto físico Necessidades de dados das aplicações Regras de transformação Requisitos de
desempenho Características do sistema Esquema interno
Verificação Regras Normalização
Especificaç
ão física do BD
l Consiste em escolher as técnicas de implementação
em disco(ou memória - in memory databases) do esquema de banco de dados que melhor satisfará à aplicação
l Dado um conjuto de dados D e um conjunto de
consultas Q um BD bd deve armazenar D e garantir um tempo máximo tmaxpara toda q em Q. l Porém, as especificidades de projeto lógico não
devem modificar aplicações desenvolvidas segundo o projeto lógico R.
Independência de dados
l
As aplicações são desenvolvidas sobre uma
visão externa da base de dados e não
devem ser afetadas por mudanças de
características físicas do BD.
Armazenamento Gerenciador de armazenamento Gerenciador de buffer Memória (Buffers)
Páginas de leitura e gravação
Gerenciador de Índice/arquivos/
registros
Comandos de Páginas dados/metadados/índices
Sub-sistema de
Armazenamento
Armazenamento de dados
l Os dados devem persistir por um longo período de
tempo
l O projeto de armazenamento deve considerar:
– O volume da base de dados
– O custo de armazenamento
– A rapidez desejada de acesso
– O perfil de acesso aos dados pelas aplicações
– O hardware disponível – A garantia de persistência
Armazenamento de
base de dados
cache Memoria RAMMemória Flash (EEPROM)
Discos de Estado sólido (SSD) Discos Rígidos Fitas Magnéticas
custo Tempo
Velocidade/custo
l
HD - (5 - 10) ms ,$ 0.05 / GB
l
SSD - cerca de 0.1 ms, $ 1.2 - 2.0 / GB
l
Memória Flash - cerca de 100 ns
l
DRAM - nanosegundos - cerca de $40/GB
l
Obs: Acesso ao disco é o gargalo na
transferência entre disco e memória
Armazenamento BD
cache Meomória Principal
disco Mémoria
virtual File system
1 0 -1 -2 -3 -4 -5 -6 -7 -8 -9 5 6 7 8 9 10 11 12 13 secudário Principal cache sec 10i Byt es 10i SGBD
A lei de Moore
l
A capacidade dos processadores deve
crescer de forma exponencial, duplicando
sua capacidade a cada 18 meses.
– Velocidade de acesso à memória e a disco
Armazenamento em disco
Discos vs Fitas magnéticas
l
Principal diferença:
– Discos oferecem acesso direto;
l Utilizado por dados online;
– Fitas fornecem acesso sequencial;
l backups;
l dados historicos;
l
Técnicas de espelhamento e « stripping »
oferecem garantia de segurança e
disponibilidade
Discos Magnéticos
Trilha Setor Cilindro (imaginario) Pratos de disco Cabeça de leitura e gravação Setores de trilhas Com mesma densidade De dadosArmazenamento Físico
l Uma trilha é dividada em setores de discos. Setores
são agrupados em páginas como unidade de transferência
– Páginas do SO variam em geral entre 2KB a 16KB
l O acesso a disco é direto a paptir de um endereço
(número de cilindro, numero da trilha e numero do setor/bloco)
– A controladora de disco envia o endereço para o dispositivo de leitura ou gravação
l Operações de leitura e escrita transferem os dados
do disco para a memória e vice-versa
Característica de
acesso a disco
l Latência – o tempo para transferir dados para memória a partir de um comando de leitura
– Tempo de busca(seek time);
l Positionamento a cabeça de leitura sobre a trilha desejada; – Tempo de rotação;
l Posiciona a cabeça de leitura sobre o setor desejado l Positionne la tête de lecture sur le secteur; – Tempo de transferência de um bloco; l Comparação
– Busca - 14 µs;
– Transferência +- 0,5 µs por um bloco de 4 KB;
•
Desejável que dados associados sejam gravados sobre um mesmo cilindro.•
Obs:– Enquanto se processa a busca em disco, a CPU pode realizar milhões de instruções. Deriva dai a necessidade de pre-fetch de dados
Sub-sistema de disco
bus
Controladora De disco Pode acessar várias páginas E transferi-las de uma só vez
Custo de leitura de um registro
l
Acesso direto:
– Tempo de latência por bloco (tlb) + busca em
memória+tempo de transferência
l
Acesso sequencial:
– Em média: (tlb)*número de blocos/2 + busca em
memória
Desafio para o
armazenamento em disco
l
Uma estrutura física que:
– Permita acesso direto
– Permita múltiplos formatos de dados
– Variação de tamanho
– Garantindo eficiência
l
SGBD armazena dados em blocos
– Minimiza o número de pedidos de transferência
– Tamanho de cada bloco estabelece uma relação
entre volume de dados transferido e número de requisições
Estrutura de
armazenamento de BD
l Um banco de dados é distribuído por vários arquivos
gerenciados pelo S.O. ou diretamente pelo SGBD
l Um arquivo é formatado em blocos (páginas);
l Cada bloco conterá várias unidades lógicas de
dados (registros ou, em SGBD, tuplas):
l Cada registro conterá valores (valores de atributos)
l Um conjunto de registros será acomodado em um
bloco para ser transferido entre a memória principal e o disco;
Distribuição Física
… … …
Banco de Dados
Arquivo Arquivo Arquivo blocos
…. blocos blocos
Estrutura de armazenamento
em BD
l Em um banco de dados relacional, as relações
(≈arquivos) são compostas por tuplas (≈ registros) agrupados em blocos:
– Cada tupla pode apresentar tamanho diferente
l Os registros apresentam tamanho fixo ou variável;
l Na maioria das implementações um registro não
ultrapassa o tamanho de seu bloco;
l O tamanho de um bloco pode variar segundo o
SGBD, ou devido a configuração do sistema, entre (4KB, 16 KB, 32 KB, 56 KB)
O Tamanho dos blocos
l
Tamanho fixo;
l
Deve acomodar relações pequenas;
l
Grandes relações atravessam vários blocos;
l
Um bloco pode estar distribuído por várias
Formato de Registros Físicos
l
Registros de tamanho fixo e variável
l
Mapeamento das estruturas produzidas no
modelo lógico em representações físicas
Tipos de dados simples
l Strings de caracteres
– char(n) –
l string the caracter de tamanho fixo n.
l ocorrências de valores desse tipo ocupam n bytes; l Strings menores que n são preenchidas com espaços; l Strings tamanhos variável
– VARCHAR(n)
l Suporta strings de tamanho até n, n <= 255; l Ocupa bytes referentes ao tamanho real de cada valor +1; l Timestamp
l 8 bytes inteiros
l Armazenados como o número de segundos antes e depois de meia-noite 01-01-2000.
l Campos numéricos
l 2,4,8 inteiros
l 4,8 ponto flutuante
l Tipos numericos,decimais – suportando valores bastante grandes mas de processamento aritmético mais lento que inteiros ou ponto-flutuante;
Mapeando Tuplas
l
Tuplas (ou objetos) são mapeados em
registros em páginas;
l
Registros são compostos por:
– Cabeçalho;
– Campos;
l
Registros podem ser fixos ou variáveis
Registro de tamanho fixo
Type deposito = record
agencia : char(22); numero-de-conta : char(10); saldo : real
end
Reg 0 Centro A-1 2000 Reg 1 Quitandiha A-10 2500 Reg 2 Itaipava A-3 3000 Reg 3 Araras A-5 7000 Reg 4 Bingen A-8 4000 Reg 5 Rio A-7 3400 Reg 6 Angra A-9 2500 Reg 7 Cabo Frio A-2 2000
Arquivo: Contas bancarias
nomtamanho <tipo,..>
Obs:
• Tamanho de registro fixo: • 22+10+2=34 bytes • Mais o cabeçalho
Cabeçalho
l
Armazena informações de apoio a acesso
aos registros
– Tamanho do registro
– Ponteiro para áreas do registro
Registros de tamanho variável
l Porque tamanhos variável?
– Armazenamento de registros de tipos diferentes. Por
exemplo: armazenamento de alunos e suas notas;
– Tipos de registro contendo um ou mais atributos de tamanho variável (por ex: nome do empregado);
– Tipos de registros contendo atributos opcionais (Nulos) (por ex: número celular do empregado)
l Atributos de tamanho variável ocupa um byte a mais para acodar seu tamanho
Exemplo: Tipo Array
l
Type lista-de-contas = record
agencia : char(22); info-conta : array [1 .. ∝ ] de record; numero-conta : char(10); situação :real; end endExemplo: Tipo atributos de
tamanho variável
l
Type correntista = record
nome : varchar(100);endereço: varchar(200); telefone: char(10);
Registros de tipos diferentes
em um mesmo arquivo
l Relações frequentemente acessadas conjuntamente
são estocadas em um mesmo arquivo
l Fatura Item Fatura – Select F.numero_fatura, i.id-produto
from Fatura f, Item_fatura i where f.idfatura = idfatura
– Vantagens: acesso conjunto requer menos I/O
– Desvantagem: SCAN de apenas uma delas
Atributos de tamanho variável
l
Cabeçalho registra:
– tamanho de cada registro
– Ponteiro para cada campo variável:
l tamanho
l Ex: campo endereço com comprimento variavel
l
Os campos de tamanho simples são
alocados à frente;
Compri- mento total
data telefone nome ponteiro para endereço endereço Tamanho atributo variavel
Estrutura
cabeçalho # info geral agencia Tamanhoda lista conta1conta2 ….
Comprimento total cabeçalho # info geral nome nome tamanho endereço Comprimento total tamanho (null) telefone Info conta com NULL Array 0 10 100
Registros maiores que um
bloco
l Em algumas situações, registros têm tamanho maior
que o de um bloco:
– Audio e video;
– Sequencia de nucleotideo do genoma humano (se armazenado em um único atributo de tipo string);
l Nestes casos, precisamos de uma outra estrutura;
– Cada cabeçalho de registro deve informar se o registro é um fragmento;
– Se for fragmento, um bit para indicar se é o primeiro ou o último;
– Se não for o último, um ponteiro para o próximo fragmento
Registro com fragmentos
Cabeçalho bloco # info geral Cabecalho registro
Registro 1 Registro 2-a
Registro 2b Registro 3
Registros BLOBS
l Vídeos e Audios são em geral mapeados para
objetos do tipo BLOB (Binary Long Objects);
l Um BLOB é armazenado em uma lista de
páginas for do bloco onde suas tuplas se encontram armazenadas;
l Ex:
– Create table filme (
nome varchar(200), gênero varchar(100), filme blob)
Blob data type
cabeçalho# info geral
gênero tamanho nome drama House of cards video Bloco 1 Bloco 2 0001000101000100100100001001001001 00010011110111001001000011110001001 1101001010100100100100100000001001 0001000101000100100100001001001001 00010011110111001001000011110001001 1101001010100100100100100000001001
Registro de tamanho fixo
em bloco
cabeçalho
Bloco 0 Petropolis A-1 2000 Araras A-10 2500 Itaipava A-3 3000 Nome arquivo tamanho
Registro de tamanho variável
em um bloco
Reg 0 Lausanne A-1 200 montana Reg 1 Morges A-10 250 champs-verts Reg 2 Écublens A-3 300 lac dor Reg 3 Berne A-5 700 111 Reg 4 Luzern A-8 400 A-11 500 Reg 5 Bale A-7 340 le primptemps Reg 6 Zurich A-3 250 A-13 320 Reg 7 Lugano A-2 200 valée d’ours
Cabeçalho
(endereço-tamanho)
Problema: Atualização para um tamanho mais longo requer movimentação
Organização de registros em
um bloco
l
Sequencial
– Os registros se sucedem na ordem de inserção
– A eliminação de registros deixa espaços livres
– De tempos em tempos o arquivo deve ser
re-organizado
– Se um bloco é inteiramente preenchido, um outro
é alocado para futuras inserções.
Modificação de dados
em um bloco
l
Inserção em um bloco
– Se há espaço
regx
reg reg reg bloco1
reg reg reg regx bloco1
Modificações de dados
l
Inserções em um bloco
– Se não há espaço contíguo
regx
reg reg reg
reg reg reg
… bloco1
reg …
reg reg reg
reg reg reg
… bloco1
Organização de registros em
blocos
l Ordenados
– Os registros são estocados segundo os valores de campos chaves;
– Se há espaço no bloco
l Insere-se o registro e atualizam-se os ponteiros no cabeçalho – Se não houver espaço
l Cria-se um novo bloco
l Deslocam-se os registros de valores mais altos l Insere-se o novo registro em sua posição com base em seu
valor de atributo chave l Atualizam-se os ponteiros nos blcos
– OBS: alternativa, utilizar estruturas de indexação onde os registros encontram-se nos nós folhas.
Modificação de dados em
blocos ordenados
l
Inserção em bloco
– Não há espaço contíguo
reg1 reg3 reg2 reg4
reg5
reg6 reg7 reg8 1o 2o 3o 4o 1o 2o 3o bloco1 bloco2
Modificação de dados
lInserção em um bloco
– Deslocar as chave mais altas
reg1 reg3 reg2 rec4
reg5 reg6 reg7 reg8 reg4 bloco1
Estrutura de arquivos de
comprimento fixo em disco
Bloco 0 Lausanne A-1 2000 Écublens A-10 2500 Chavannes A-3 3000
Bloco 1 St-sulpice A-5 7000 Morges A-8 4000 Vevey A-7 3400 Montreux A-9 2500 Mapa de bloco
Nome arquivo Qtd Blocs
free Size free proximo
free size
Calculo de espaço em
blocos
l Tamano do bloco: ex. 4k
l Uma página de controle dos blocos
l Tamanho do cabeçalho:
– Inicial - 200 bytes
– Espaço livre: 50 x (ponteiros de endereçamento (2)+tamano(2)= 4 bytes)
l Tamano de cada registro
– Posição do primeiro atributo fixo - 1 Byte – Par (endereço + tamanho) atributos variáveis
l 4 bytes X numero de atributos de tam. Variável – Soma do comprimento dos atributos:
l Fixo - tamanho de alocação l Variável: tamanho máximo
Consequencia sobre
algoritmos
l A maioria dos algoritmos são desenvolvidos
considerando-se que os dados estejam em memória;
l Precisam ser revistos para SGBDs onde os
dados estão em disco:
– Sort, merge, join
l Devem ser projetados algoritmos que:
– reduzam o acesso a disco
– Utilizem as diferentes estruturas de memória de forma
Transferência de blocos
controladora
Memória (Buffer)
Espaço de memória RAM < espaço em disco
Leitura seletiva dos dados em disco
Estruturas de Indexação
Organizaç
ão Indexada
• Objetivo:
• Acesso direto à base por valor chave
• Acesso sequencial segundo ordenação das chaves • Distribue inserções sobre blocos diferentes • Avaliar rapidamente a unicidade de um registro com base em seu valor de chave
• Em alguns casos responder à consultas sem acessar os dados;
Tipos de Indices
l
Índices densos
l
Índices esparsos
l
Índice com chaves duplicadas
l
Índices secundários
l
Árvores B+
l
Bitmaps
Indice denso e esparso
l Dados são mantidos ordenados por um campochave
l Indice denso
– Cada valor de chave de acesso aparece no índice;
– A entrada de um indice contém um ponteiro para a primeira entrada no arquivo com o valor da chave;
l Indice esparso
– Uma entrada no índice é criada somente para alguns valores
– Cada entrada contém o valor da chave e um ponteiro para um registro com o valor
Índices densos
10 20 30 40 50 60 70 80 10 20 30 40 50 60 70 80• Quantidade de blocos de índice inferior aos de dados • Para achar uma chave K log2n , n blocos • O índice pode eventualmente caber em memória
Índices esparsos
10 20 30 40 50 60 70 80 10 30 50 70• para achar K, procurar pelo maior valor X menor ou igual a K • segue-se o ponteiro sequencialmente até achar a chave K • Volume do indice menor que o do índice denso
Pode-se Escolher uma chave
por bloco
Indice Multi-níveis
l Indices esparsos em arquivos muito grandes podem
tb. ocupar um grande numero de blocos
l Uma chave seria encontrada via pesquisa binária
em média log2 n, para n chaves no índice. – Para um índice de 100 blocos levaria aprox. 30 ms
l Logo para se reduzir esse tempo pode-se aplicar ao
índice a mesma estratégia criando níveis superiores que apontem para os blocos de índice
Índices multi-níveis
10 20 30 40 50 60 70 80 10 30 50 70• reduz o número de acesso às páginas de índices a sua altura • A cada nível busca a chave maior ou igual a K
90 100 90 110 130 150 10 90 170 259
Operações índices
l
Exclusões e inserções precisam atualizar os
índices
l
Exclusões - marca-se o registro como
excluído. Se for o único registro com a
chave, elimina-se a entrada no índice. Caso
seja um índice esparso e sua chave for
utilizada no índice, atualizar a chave do
índice;
Operações indice
l Inserção - procura-se a chave de indexação no
índice. Se existir e o índice for denso, insere-se o registro em sua posição na área de dados. Se o índice for esparso, procura-se a maior chave menor que a chave a ser inserida. Se houver espaço no bloco insere-se a chave. Caso não haja, um novo bloco é inserido e as chaves são particionadas em dois grupos.
l Inserções e exclusões em vários níveis aplicam o
mesmo conceito no nível de índices
Índices sobre valores não
chave
10 10 10 20 20 30 30 30 40 50 10 20 30 40 50 etc• a busca sobre o nível de índice se passa da mesma forma
Índices sobre valores não
ordenados
30 10 50 20 40 30 50 30 10 20 10 10 20 20 30 30 30 40 50 50Árvore B+
l
Uma estrutura mais geral para aceder por
meio de um índice
– Mantém o numero de níveis em função da
quantidade de chaves de indexação
– A arvore se mantém balanceada, I.e. todos as
chaves distam igualmente da raiz
– A distância entre a raiz e qualquer nó folha é
constante e igual a (logkn +1), onde K é o número
de chaves por bloco e n é o número de chaves
Estrutura
chaves ponteiros Raiz folha Intermediario K chavesRegras para árvore B+
l A raiz contem um ou mais ponteiros;
– P1 aponta para a chave menores que K; – P2 aponta para chaves maiores ou iguais a K; l Cada nó, fora a raiz, tem entre e K chaves l Nos nós folhas (indice), o último ponteiro aponta para o bloco
folha com os valores de chaves mais altas
l Nos nós internos com {P1, P2,.., Pn} ponteiros haverá n-1
chaves:
– P1 aponta para chaves menores que K1 – P2 aponta para valores entre [k1 e k2) – Pn aponta para valores maiores que K n-1 l Todas as chaves aparecem no nível folha;
(K +1) / 2 !" #$
Nó interno, k=3
57 81 95 chaves <57 57 ≤ chaves <81 81 ≤ chaves <95 chaves ≥95Nó folha, k=3
57 81 95 chaves =57 chave =81 chave =95Ponteiro p/ proximo bloco folha 97 100 110
• ponteiros apontam para a pagina de dados contendo a chave procurada • Último ponteiro aponta para o próximo bloco
Exemplo
899-46-2035 177-32-1776 213-46-8915 238-95-7766 409-56-7008 756-30-7391 341-22-1782 427-34-6542 431-67-9874 RaizNivel Intermediário Nivel folha
109-56-7008 699-46-2035 238-95-7766 787-45-4322 … … … … … … 238-95-7766 … … … … 787-45-4322 756-30-7391 431-67-9874 427-34-6542 409-56-7008 341-22-1782 267-41-2394 177-32-1776 213-46-8915 267-41-2394 K=3 341-22-1782 109-56-7008 431-67-9874 109-56-7008 … … 109-56-7008
Inserção
l Buscar nó folha onde a chave deve ser inserida;
l Se há espaço, a chave é inserida
l Se não há espaço
– Cria um novo nó a direita
– Movimenta as últimas chaves sobre o novo nó;
– As primeiras se mantém no nó original
– A menor chave do nó recém criado sobe ao nível superior. Se o nó é o nível folha, uma cópia da chave se mantem para apontar aos dados
(K +1) / 2 !" #$ k / 2 !" #$
Indice - inserção
5 10 50 25 K=3 <5 5≤ <10 10≤ <50 ≥50 5 10 50 25 split <5 5≤ <10 10<25 ≤ 25≤ <50 ≥50 <25 ≥25 Nivel > 1 25Indice –insert, split nivel
1 10 50 25 60 65 80 70 68 90 93 160 K=4 35 100 150 170 180 25 60 70 93Indice –inseção, split nível
1 10 50 25 60 65 80 70 68 90 93 160 K=4 35 100 150 170 180 Spilt nó
Index - split nível
1 10 25 60 65 35 50 68 80 90 100 150 160 93 70 170 180 Split raiz
• o split leva a menor chave à direita ao nivel superior « 170 » 160 93 70 60 25
Indice - split raiz
1 10 25 60 65 35 50 68 80 90 100 150 160 70 170 180 93 25 60 70 93Indice – Fator de
Preenchimento (Fillfactor)
l Permite fixar uma porcentagem de cada página que
deve manter-se livre durante a carga inicial (criação do índice);
l Deixe espaço para inserções futuras;
l Reduz a quantidade de splits;
1 10 35 50 65 68 80 90 100 150 170 180
Todas as páginas mantêm 50 % de espaço livre (apenas como ilustração)
Cálculo do espaço de um
índice
l Tamanho da chave (tc)
– Soma de seus tamanhos
l Tamanho dos atributos componentes do índice (tbi): l tc + 4 bytes para ponteiro
l Tamanho do bloco (tb)
l Tb=espaço total - espaço de preenchimento
l Total de entradas por bloco (teb):
l Teb=tb / tbi
l Número de blocos folha (nbf):
l Nbf=Total de tuplas /teb
l Número de blocos nível folha-1
l Corresponde ao número de blocos necessários para
armazenar os ponteiros aos blocos folha:
l Nbnf-1= (nbf * 4 + nbf*tc) / tb
DDL - Criação de índice
• Create {unique,nonunique} indexnomindex on nomtableau (col1,col2,...)
[with fillfactor = n]
• Drop index nomindex
Projeto Físico cont.
Índices - Clustered
l Índices clustered mantém as tuplas ordenadas segundo a chave de ordenação do próprio índice. As tuplas de dados são armazenadas no nível folha da árvore B+.
– Índices clustered otimizam acessos do tipo sequencial-indexado, por ex: funcionários cm salário > 1.500,00, alunos com matrículas de 2009 em diante. Transações de inserção concorrentes fora da sequência da chave são favorecidas por alocação distribuída; – A criação de um índice cluster em tabela populada requer o
espaço livre de 1 vez e um terço o tamanho da tabela; – Só pode haver 1 índice cluster por tabela; – chaves primárias criam, por default , índices clustered
Índices - Não Clustered
l Estruturas tradicionais de índices em árvores B+. Os dados são armazenados em uma seqüência de páginas desassociadas do índice.
– As inserções ocorrem sempre na última página, podendo acarretar em concentração de acesso e bloqueios por transações com concorrência de inserção.
– O nível folha da árvore B+ contém páginas onde cada ocorrência contém uma chave e um ponteiro para a página onde a tupla correspondente encontra-se armazenada;
– O nível folha pode ser utilizado em consultas que procuram pela existência de um conjunto de chaves, ou pelo somatório de tuplas, ou ainda para atender a consultas do tipo não igual, uma vez que neste nível aparecem todas as chaves existentes;
– Ideal para consultas em ordem aleatória, como por exemplo: joins, consultas pontuais.
Índices (comando)
• Create {unique,nonunique} {clustered,nonclustered} index nomeíndice on nometabela (col1,col2,...)
[with fillfactor = n]
Outras estruturas
l
Além da estrutura em árvore B+ outras
estruturas de indexação favorecem o
acesso a tipos de dados particulares.
Tabelas Hash em
armazenamento secundário
l
Tabelas hash distribuem chaves por
depósitos através de uma função de
mapeamento.
– Uma função de hashing pode calcular para cada
chave o resto de chave/|depositos|
l
Assim como em memória, a cada chave é
associado um depósito (bucket).
l
Em cada depósito colocam-se as chaves
com mesmo valor de hashing
Exemplo de hash
Bloco=4
Registro/bloco=2
a f d e m c0
1
2
3
chaveF
h(f) = 0
F
h(d) = 0
F
h(m) = 2
Tabela Hash
l Acesso direto – apenas por igualdade de valor
de chave.
l Acesso sequencial – varre os blocos da tabela.
l Cada depósito é armazenado como arquivo;
– Sistema guarda mapeamento entre id do depósito e
arquivo em disco;
l A cada instante um depósito é lido para
Bitmap
l Estrutura de bits mapeando valores para ocorrência
em registros (tuplas)
l Para um dado atributo att, criam-se n vetores um
para cada valor que att possui, todos de tamanho m;
l um bit em um vetor esta ligado se a tupla a que
corresponde tem o valor do bitmap.
l Eficientes em colunas com baixa cardinalidade, ou
seja baixo número de valores distintos. Por exemplo, Sexo.
l O “XOR” entre os bitmaps de uma coluna é ∅ – Pois cada coluna corresponde a um valor distinto;
Bitmap: exemplo
0: linha não atende predicado 1: linha atende predicado Sexo:
M: 110011000101. . . F: 001100111010. . .
– No exemplo acima:
l M aparece nas tuplas {1,2,5,6,10,12…}
l F aparece nas tuplas {3,4,7,11,..}
Bitmap
l
Eficiente na resolução de expressões
conjuntivas sobre diversos atributos
indexados.
Índice Bitmap
(consultando)
Classif_idade 18 – 22 A 23 – 27 B 28 – 33 C 34 - 40 D .... Nível de renda 1000 – 15000 A 15001 – 20000 B 20001 – 30000 C .... Nível educacional 1o grau – A 2o grau – B 3o grau – C .... Select ... from participantewhere classif_idade = ‘B’ and nivel_renda = ‘A’ and nivel_educacional = ‘C’ C(B): 110011001101 R(A): 001100111010 E(C): 100100111000 _______________ XOR: 000000001000
Índices Bitmap
l Podem ser permanentes, criados pelos usuários
l Podem ser criados dinamicamente pelo SGBD durante a
execução de uma consulta.
l Eficientes quando cada um dos predicados no
SQL é pouco restritivos:
select * from tabela_censo where sexo = ‘F’ and
estado = ‘MG’ and estado_civil = ‘S’
Índices Bitmap
restrições
l
Não recomendável para transações de
atualização pois atualização dos vetores é
lenta.
l
Não pode ser declarado como único (não
Desnormalização
Desnormalização
controlada
l Objetivo– evitar custo de junções e agregação sobre dados com alta taxa de acesso;
l Benefício
– ganho de tempo para aplicações de consultas; – melhora no desempenho geral do sistema
l Problemas
– redundância
– aumento no tempo de txns de atualização
Técnicas de
desnormalização
l
Adicionar colunas redundantes
l
adicionar colunas derivadas
l
congregar tabelas
l
duplicar tabelas
Adicionar coluna
redundante
Titulo_autor Autor Id_titulo Id_autor Id_autor NomeTitulo_autor Autor Id_titulo Id_autor Nome Id_autor Nome
• Elimina Joins • exige manutenção de redundância • requer + espaço em disco
Tratamento para
atualização
l
Gatilho (
trigger
) associado à tabela autor
– Se modifica valores da coluna Nome a
atualização é automaticamente e sincronamente realizada em Título-Autor
l
autorização sobre coluna impede
atualização de
Nome
em
Titulo-Autor.
Adicionar colunas
derivadas
Aluno (….CR…)
HistóricoAluno (….nota…)
• Elimina em tempo de execução: - joins
- agregações e cômputos
• Requer manutenção de redundância • Consome mais espaço
CR é derivado das notas do aluno.
• Cálculo do CR é armazenado na tabela
Congregar tabelas
Pessoa (….) PessoaJurídica (…. …) Cliente (….) • Elimina junções • Requer manutenção de consistência • Implementação:Visão materializada
Particionamentos
HorizontalParticionamento
Desnormalização -
implementando
l
Trigger ou stored procedure?
– Depende da necessidade de atualização da
informação:
l trigger imediato, porém aumenta o tempo da transação OLTP;
l SP pode ser uma melhor opção para dados sem exigência de atualização instantânea;
l
Visão materializada
Visão de dados
l
O modelo lógico pode ser acrescido de
visões:
– Expressões relacionais sobre o modelo lógico
– Fornecem uma relação virtual que pode ser mais
favorável à uma aplicação
– Create view v as < expressão de SQL >
Visão Materializada
l Visões podem ter seu conteúdo materializado em
disco;
l Atualizações nas tabelas componentes da visão são,
se possível, refletida no conteúdo da visão.
l Bastante utilizada em:
– carga de sistemas remotos;
– computação móvel; – desnormalização;
Uso de visões
materializadas em
consultas
Usuário envia consulta Reescrita de consulta Geração de plano Geração de plano Comparação planos execuçãoUso de visões
materializadas
l
São usadas implicitamente em consultas de
usuários. Consultas referenciando tabelas de
base da visão podem ser reescritas para que
façam uso de visões materializadas;
l
podem também ser utilizadas explicitamente
em consultas;
l
incluem junções, agrupamentos e funções de
agregação.
Criando uma visão
materializada
l CREATE MATERIALIZED VIEW store_sales_mv
PCTFREE 0 TABLESPACE mviews
STORAGE (INITIAL 16k NEXT 16k PCTINCREASE 0) PARALLEL
BUILD DEFERRED REFRESH COMPLETE ENABLE QUERY REWRITE
AS SELECT s.store_name, SUM(dollar_sales) AS sum_dollar_sales FROM store s, fact f WHERE f.store_key = s.store_key GROUP BY s.store_name;
l CREATE MATERIALIZED VIEW store_sales_mv
PCTFREE 0 TABLESPACE mviews
STORAGE (initial 16k next 16k pctincrease 0) BUILD DEFERRED
REFRESH COMPLETE ON DEMAND ENABLE QUERY REWRITE
AS SELECT s.store_name, SUM(dollar_sales) AS sum_dollar_sales FROM store s, fact f WHERE f.store_key = s.store_key GROUP BY s.store_name;
l CREATE MATERIALIZED VIEW store_avgcnt_mv
PCTFREE 0 TABLESPACE mviews
STORAGE (INITIAL 16k NEXT 16k PCTINCREASE 0) BUILD IMMEDIATE REFRESH COMPLETE ENABLE QUERY REWRITE
AS SELECT store_name, AVG(unit_sales) AS avgcnt_unit_sales, COUNT(DISTINCT(f.time_key)) AS count_days FROM store s, fact f, time t WHERE s.store_key = f.store_key AND f.time_key = t.time_key GROUP BY store_name,t.time_key;
Visão materializada com
log
l CREATE MATERIALIZEDVIEWlog on fact with rowid (store_key, time_key, dollar_sales, unit_sales) including new values;
l CREATE MATERIALIZED VIEW sum_sales
PARALLEL BUILD IMMEDIATE REFRESH FAST ON COMMIT
AS SELECT f.store_key, f.time_key, COUNT(*) AS count_grp, SUM(f.dollar_sales) AS sum_dollar_sales, COUNT(f.dollar_sales) AS count_dollar_sales, SUM(f.unit_sales) AS sum_unit_sales, COUNT(f.unit_sales) AS count_unit_sales
FROM fact f
Autorizações
l
Create materialized view
l
query rewrite
Particionamento
CREATE TABLE sales( 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 (1998, 04, 01) TABLESPACE tsa,
PARTITION sales_q2 VALUES LESS THAN (1998, 07, 01) TABLESPACE tsb,
PARTITION sales_q3 VALUES LESS THAN (1998, 10, 01) TABLESPACE tsc,
PARTITION sales_q4 VALUES LESS THAN (1999, 01, 01) TABLESPACE tsd );
Particionamento
l CREATE TABLE scubagear (...)
STORAGE (INITIAL 10k) PARTITION BY HASH (id) PARTITIONS 16
