REVISÃO PARA 3a PROVA Gerência de Banco de Dados Profa. Sandra de Amo Disciplina GBC053...

REVISÃO PARA 3a PROVA Gerência de Banco de Dados

Profa. Sandra de AmoDisciplina GBC053

Bacharelado em Ciência da Computação

Condições Gerais de Seleção

SELECT *FROM RWHERE (R.A op ‘a’ OR R.A op ‘a1)

AND (R.B op ‘b’ OR R.B op ‘b1’)

op: =, < , > , ≤ , ≥

Tamanho de R = M páginasNúmero de tuplas por página = Pr

Caso 1: sem OR• Um só índice

– Criar índice para atributo que aparece na condição de seleção, com maior “seletividade”

– utilizar este índice para obter os registros satisfazendo a condição da chave do índice

– A medida que se recupera as tuplas satisfazendo esta condição elimina-se as tuplas que não satisfazem alguma das outras condições.

• Diversos índices

– Utiliza-se diversos indices, sobre alguns atributos aparecendo na condição de seleção.

– Para cada condição Ai = ai recupera-se as páginas do arquivo de indice satisfazendo esta condição.

– Ordena-se as entradas de cada índice pelo page-id

– Faz-se a intersecção das entradas com os mesmos page-ids

– Recupera-se as tuplas contidas nas páginas indicadas pelos page-ids e elimina-se aquelas que não satisfazem as outras condições da seleção (para as quais não foram considerados indices).

Caso 2 : com OR

• A = a1 OR B = b1– Indice em A, não há indice em B– Melhor solução : scan (o indice em A não ajuda nada)

• (A = a1 OR B = b1) AND C = c1– Indice em A, não há indice em B, indice em C– Melhor solução: utilizar o indice em C

• A = a1 OR B = b1– Indice em A, indice em B– Melhor solução:

• recupera-se as entradas no arquivo de indice para A = a1 : • recupera-se as entradas no arquivo de indice para B = b1 :• Faz-se a união destes dois conjuntos de entradas• Ordena-se este conjunto pelo page-id

Projeção

SELECT DISTINCT R.A, R.BFROM R

Duas etapas principais : 1. Remover colunas indesejáveis2. Eliminar as duplicatas (o mais difícil)

Projeção usando Ordenação1. Scan de R para produzir as tuplas projetadas (sem os campos indesejáveis) Custo passo 1 = M + T2. Ordena o resultado, utilizando a combinação de todos os atributos da projeção como chave da ordenação

Custo passo 2 (Ordenação) = 2T ([logB-1T/B] + 1)

3. Scan do resultado ordenado para eliminação das tuplas adjacentes repetidas. Custo passo 3 = T T = número de páginas produzidas da relação projetada (T = c.M onde c < 1) T

depende do número e do tamanho dos campos removidos em cada tupla.

Custo total = M + T + 2T ([logB-1T/B] + 1) +T

Otimização: •Pode-se projetar as tuplas durante a primeira iteração da ordenação.•A partir da segunda iteração da ordenação já vai-se eliminando as duplicatas à medida que são criados os subarquivos ordenados.

Projeção usando Hashing

• Usada quando se tem um tamanho de buffer B razoável com relação ao tamanho da relação R.

• Usa a idéia do algoritmo de Hash Join de Junção• Fase do Particionamento + Projeção: produz como resultado a relação R

projetada (ainda sem a eliminação de duplicatas), organizada em partições, segundo uma função hash h, calculada sobre os atributos da projeção.

• Fase da Eliminação das Duplicatas. – Supomos que tamanho de uma partição ≤ B– Para cada partição carregada no buffer, varre-se a partição e elimina-

se as duplicatas. Todos os registros de dados com valores duplicados estão numa mesma partição.

Fase do Particionamento e Projeção de R

Buffer tem capacidade para B páginas,onde B – 1 = número de partições

Página de R

Relação R

Disco Disco

Relação R’ Particionada e Projetada

Pt 1 Pt 2 Pt 3 Pt 6Pt 5Pt 4

Projeta eDistribui usando hash h Sobre a combinação dos atributos projetados

M páginas T páginas

Fase da Eliminação de Duplicatas

Buffer tem capacidade para B páginas,onde B = tamanho de uma partição de R’

Relação R’particionada

DiscoDisco

Relação R’ semduplicatas

Partição n de R’ (inteira)

Página deR’ sem duplicatas

Tamanho mínimo de Buffer

• Fase do Particionamento + Projeção– Cria-se B-1 partições – Registros de cada partição são projetados – T = tamanho da relação projetada R’– Tamanho de uma partição = T/B-1

• Fase de Eliminação das Duplicatas– B ≥ T/B-1 (B-1).B ≥ T– (B-1).B > (B-1).(B-1) ≥ T– Se B-1 ≥ T teremos que (B-1).B ≥ T– Logo, basta considerar B ≥ T + 1 ou equivalentemente B > T

UNION: algoritmo baseado em ordenação

• Fase da Ordenação:– Ordena T1 por todos os atributos– Ordena T2 por todos os atributos

• Fase da Intercalação– Intercala as tuplas de T1 e T2 de modo a obter um

único arquivo ordenado e sem duplicatas.

Fase da Intercalação

Buffer pool

Página de T1 Página de T2 Página de output

115

79

11

226

89

13

1

25678911

CUSTO DA INTERCALAÇÃO = M + N M = número de páginas de T1N = número de páginas de T2

CUSTO TOTAL =2M ([LogB-1 M/B] + 1) + 2N ([LogB-1 N/B] + 1)+ M + N

13

EXCEPT: algoritmo baseado em ordenação

• Fase da Ordenação:– Ordena T1 por todos os atributos– Ordena T2 por todos os atributos

• Fase da Diferença– Intercala as tuplas de T1 e T2 de modo a obter um

único arquivo ordenado e sem duplicatas.

Fase da Diferença

Buffer pool


115

79

11

226

89

13

11. Se T1.r < T2.r Insere T1.r no Output Atualiza o marcador de T1 para o próximo registro de T1 diferente de T1.r2. Se T1.r = T2.r Atualiza os marcadores de T1 e T2 para os próximos registros de T1 dif. de T1.r e de T2 dif. de T2. r 3. Se T1.r > T2.r Atualiza o marcador de T2 para o próximo registro de T2, diferente de T2.r

Fase da Diferença

Buffer pool


115

79

11

226

89

13

1

5


Fase da Diferença

Buffer pool


115

79

11

226

89

13

1

5

7


Fase da Diferença

Buffer pool


115

79

11

226

89

13

1

5

7

11

CUSTO DA DIFERENÇA = M + N M = número de páginas de T1N = número de páginas de T2

CUSTO TOTAL =2M ([LogB-1 M/B] + 1) + 2N ([LogB-1 N/B] + 1)+ M + N


UNION: algoritmo baseado em hash

• Fase da Particionamento:– Particiona T1 em B-1 partições usando o buffer– Particiona T2 em B-1 partições usando o buffer

• Fase da União– Carrega a partição inteira n de T1 no buffer: isto é possível se B > – Ordena internamente os elementos da partição n e elimina as duplicatas– Para cada página da partição n de T2, ordena internamente e elimina as

duplicatas.– Para cada tupla da partição n de T2, varre a partição n de T1 (que está em

memória) e verifica se t está nesta partição. Se não estiver, coloque-a no resultado.

– Retorna a partição n de T1 e as páginas construídas com os elementos da correspondente partição n de T2 que faltavam na partição n de T1.

T1

EXCEPT: algoritmo baseado em hash

• Fase do Particionamento:– Particiona T1 em B-1 partições usando o buffer– Particiona T2 em B-1 partições usando o buffer

• Fase da Diferença– Carrega a partição inteira n de T2 no buffer: isto é possível se B >

(Prove !)– Ordena internamente os elementos da partição n e elimina as

duplicatas– Para cada página da partição n de T1, ordena internamente e elimina

as duplicatas.– Para cada tupla t da partição n de T1, varre a partição n de T2 (que

está em memória) e verifica se t está nesta partição. Se não estiver, coloca-a no resultado.

T2

Algoritmos para Operador GROUP BY

SELECT S.Status, AVG(S.Idade)FROM Sailors SGROUP BY S.Status

Técnicas: • Ordenação• Hashing

Algoritmo para GROUP BY baseado em ordenação

Etapa 1: Ordena-se a relação pelo atributo do Group byEtapa 2: Faz-se um scan da relação ordenada armazenando-se

na variável Agreg o resultado da operação de agregação para cada grupo

Custos:Etapa 1 = 2M([LogB-1 M/B] + 1)Etapa 2 = MCusto total = 2M([LogB-1 M/B] + 1) + M

Algoritmo para GROUP BY baseado em Hash

• Fase do Particionamento– Particiona a relação R pelos atributos de agrupamento – do GROUP BY– Desta maneira: elementos de um mesmo grupo só podem estar numa

mesma partição.– O particionamento é feito de modo que cada partição caiba inteira na

memória. – Para isso, é preciso que o tamanho do buffer (B) seja > M onde M = número de páginas da relação R.

• Fase do Agrupamento – Carrega partição inteira de R– Aplica algoritmo de ordenação interna pelos atributos do

agrupamento (Group By).– Varre a partição (ordenada) e calcula-se o resultado da função de

agregação sobre cada grupo• CUSTO = 2M + M = 3M

Esquema Geral do Otimizador

SQL Parser Consulta SQL

usuário

Coleção de blocos simplesB1, B2, ...., Bn

Otimizador

Melhor Plano de execução

Bloco SQL simples

Plano canônico

Cria planos alternativos

Planos alternativos

Estima custos

Melhor Plano de execução

Transforma em Algebra

Decompor consulta em blocos simples

• Um bloco SQL simples é um comando sem subconsultas aninhadas, onde aparece– somente um SELECT, – somente um FROM – no máximo um WHERE (em FNC) – no máximo um GROUP BY– no máximo um HAVING

Exemplo

SELECT DISTINCT S.sid, Min (R.day) FROM Sailors S, Reservas R, Boats B WHERE S.sid = R.sid AND R.bid = B.bid

AND B.color = ‘red’ AND S.rating = ( SELECT MAX (S2.rating)

FROM Sailors S2 ) GROUP BY S.sid HAVING COUNT (*) > 1

Exemplo• Bloco 1 : bloco interno

SELECT MAX (S2.rating) FROM Sailors S2 Resultado : Relação temporária T(A)

• Bloco 2 : bloco externo SELECT DISTINCT S.sid, Min (R.day) FROM Sailors S, Reservas R, Boats B, T WHERE S.sid = R.sid AND R.bid = B.bid

AND B.color = ‘red’ AND S.rating = T.A GROUP BY S.sid HAVING COUNT (*) > 1

Plano de Execução “Canônico”

σ

Π A,B,...,C

X

R1 R2 Rn

Resultado R é ordenado

O GROUP BY é executado sobre o resultado R ordenado.

O HAVING é aplicado para eliminar certos grupos.

Funções de agregação (MIN,MAX,...) são executadas sobre os grupos finais

Operador de Projeção é executado por último.

Resultado R

PLANOS ALTERNATIVOS Equivalências de Expressões Algébricas

• Seleção– σ c1 ^ c2 ^ ... ^ cn (R) = σ c1 (σ c2 (... (σ cn (R))...)

Vantagens: Permite realizar uma única seleção, verificando todas as condições simultaneamente, em vez de se executar n seleções separadamente em sequência.

– σ c1 (σ c2 (R) ) = σ c2 (σ c1 (R) ) As condições podem ser executadas em qualquer ordem.Vantagem: executar a condição mais seletiva primeiro.

Equivalências de Expressões Algébricas

• Projeção – Π X1 (R) = Π X1 (Π X2 (... (Π Xn (R))...) Onde – cada Xi é um conjunto de atributos – Xi está contido em Xi+1

– Exemplo: Π A (R) = Π A (ΠAB (Π ABC (R)))

Vantagem: Reduz o número de execuções do algoritmo de projeção


• Produto Cartesiano e Junção– Associativa

R (S T) = (R S) TR (S T) = (R S) T

– Comutativa

(R S) = (S R)(R S) = (S R)


• Seleção e Projeção – ΠX σc (R) = σc ΠX (R)

Onde todos os atributos aparecendo na condição c estão contidos em X


• Seleção e Junção– R S = σc (R S)

– σc (R S) = (σc R S) • se todos os atributos de c são atributos de R e não de S

– σc (R S) = (σc R S) • se todos os atributos de c são atributos de R e não de S

Vantagens: junção pode ser feita entre relações menores.

c


• Projeção e Produto Cartesiano– ΠX (R S) = (ΠY R ΠZ S)

• Y = atributos de X que aparecem em R• Z = atributos de X que aparecem em S

– Exemplo: ΠAB (R S) = (ΠA R ΠB S) • onde R(AC) e S(BC)• Vantagem: Produto cartesiano pode ser feito entre

relações menores.


• Projeção e Junção – ΠX (R S) = (ΠY R ΠZ S)

• Y = atributos de X que aparecem em R• Z = atributos de X que aparecem em S• Os atributos envolvidos na condição de junção c devem aparecer

em X

– Exemplo : R(ACD), S(BEC), X = {A, B, C}, condição de junção : R.C = S.C

ΠABC (R S) = (ΠAC R ΠBC S)

c

c c

c

ESTIMATIVAS DE CUSTOS DE PLANOS DE EXECUÇÃO

• Uso de estatísticas sobre tabelas e indices armazenadas no catálogo

• Uso de Histogramas atualizados periodicamente e mantidos no catálogo do sistema.

Estatísticas sobre tabelas e indices armazenadas no catálogo:

• NTuples (R) = Número de tuplas da tabela R• NPages(R) = Número de páginas da tabela R• NKeys(I) = número de chaves distintas do Indice I• INPages(I) = número de páginas do indice I (no caso de B+tree = número de folhas)• IHeight(I) = Altura do Indice (no caso de B+tree)• ILow(I) = menor valor de chave do indice I• IHigh(I) = maior valor de chave do indice I

Pipeline versus Tabelas Materializadas

• Pipeline : resultado de uma operação é transferido para a próxima operação sem a criação de uma tabela em disco.

• Economia de custos de armazenamento e de leitura posterior

• Sempre que o algoritmo do operador para o qual é transferido o resultado permitir, a técnica de pipeline é utilizada.

Estimativa de Custos de um Plano de Execução

Para cada nó da árvore N da árvore, seja Op(N) a operação associada a N.

Tarefas do Estimador de Custos• Estimar do custo de Op(N)• Estimar o tamanho do resultado de Op(N)

Estimativa do Tamanho do Resultado

SELECT <lista de atributos> FROM < lista de relações R1,...,Rk > WHERE <cond1 ^ cond2 ^....^condn>

• Número máximo de tuplas no resultado = M1.M2....Mk, onde Mi = tamanho de Ri • Cláusula WHERE atua como um redutor desta

estimativa • Cada condição do WHERE tem o seu fator de

redução próprio

Fatores de Redução• R.A = valor

– Fator de redução = 1/NKeys(I), caso exista um indice I com chave A para a relação R

– Fator de redução = 1/10, caso contrário (ou utiliza-se estatísticas mantidas no catálogo sobre a distribuição dos valores dos atributos)

• R.A = R.B– Fator de redução = 1/Max(NKeys(IA),NKeys(IB)) se existe

indices IA e IB com chave A e B respectivamente.– Fator de redução = 1/NKeys(I) se somente um dos

atributos é chave de um indice I– Fator de redução = 1/10 caso contrário.

Fatores de Redução

• R.A > valor– Fator de redução = (High(I) – valor) / High(I) – Low(I) caso

exista um indice I com chave A para a relação R– Fator de redução = fração < ½ caso não exista indice ou se

o valor não é aritmético

• R.A IN (Lista de valores) – Fator de redução =

• N*(fator de redução de R.A = valor), onde N = núm. de itens

– Fator de redução = fração < ½ caso não exista índice ou se o valor não é aritmético.

Fatores de Redução

• R.A IN (Subconsulta) – Fator de redução: M/N onde

• M = tamanho do resultado da Subconsulta• N = número de valores do atributo R.A

• NOT (Cond) – Fator de redução: (1 – Fator de Redução(Cond))

• Fator de Redução da Projeção = fração equivalente ao tamanho dos atributos que não são eliminados.

Histograma

0 1 2 3 4 5 6 7 8 9 10 11 12 13 14

0 1 2 3 4 5 6 7 8 9 10 11 12 13 14

2

3 3

12 22

3

1 10

8

4 4

9

3

3 3 33 3 3 3 33 3 3 3 3 3 3

Distribuição real

Distribuição uniforme

Histogramas

Supõe-se um conjunto de N tuplas, com valores do atributo A variando de v1 a v2.

Supõe-se que para cada valor v do atributo A, v1 ≤ v ≤ v2, temos o número de tuplas N(v) com este valor para

Estimativas do número de tuplas com valor x:• Estimativa exata : N(x)• Estimativa usando distribuição uniforme: Média aritméticas

dos valores

Histogramas por Largura Histograma por largura : • particiona-se o conjunto de valores em K grupos, com o mesmo número

de valores cada um. • Para cada grupo de valores, determina a média do número de tuplas do

grupo

Como estimar N(x) usando um histograma por Largura

• Estimativa do número de tuplas com valor x = média do número de tuplas do grupo ao qual x pertence.

Histograma por largura

0 1 2 3 4 5 6 7 8 9 10 11 12 13 14

2.671.33

5

1

5 5 5

0 1 2 3 4 5 6 7 8 9 10 11 12 13 14

2

3 3

12 22

3

1 10

8

4 4

9

3

Uso do Histograma por largura

0 1 2 3 4 5 6 7 8 9 10 11 12 13 14

2.671.33

5

1

5 5 5

Distribuição uniforme: 3 tuplas Distribuição histograma por largura: 5 tuplas

Qual a estimativa da quantidade de tuplas com AGE > 13 ?

0 1 2 3 4 5 6 7 8 9 10 11 12 13 14

3 3 33 3 3 3 33 3 3 3 3 3 3

Histogramas em ProfundidadeHistograma em Profundidade :

• particiona-se o conjunto de valores em X grupos, com aproximadamente o mesmo número de tuplas em cada um.

• Para cada grupo de valores, determina a média do número de tuplas para cada valor do grupo.

Como estimar N(x) usando um histograma por Comprimento

• Verifica em que grupo de valores o valor x se encaixa.• N(x) = média do número de tuplas associado aos valores deste grupo.

Histograma por profundidade

0 1 2 3 4 5 6 7 8 9 10 11 12 13 14

1.80

6

9

2.67

8 tuplas 7 tuplas

1.75

12 tuplas 9 tuplas 9 tuplas

6

Histograma por profundidade

0 1 2 3 4 5 6 7 8 9 10 11 12 13 14

1.80

6

9

2.67

8 tuplas 7 tuplas

1.75

12 tuplas 9 tuplas 9 tuplas

Distribuição histograma por profundidade = 9 tuplas

6

Distribuição uniforme: 3 tuplas

Distribuição histograma por largura: 5 tuplas

Qual a estimativa da quantidade de tuplas com AGE > 13 ?

Exercicio 4

100 tuplas, 20 valores variando de 1 a 98

1 3

4 10

6 4

10 3

22 8

31 9

35 2

43 5

47 7

53 8

58 2

60 7

63 3

65 4

76 3

78 7

80 2

84 4

93 3

98 6

1. Descrever uma distribuição uniforme paraestes dados.

2. Fazer um histograma por largura .

3. Estimar o número de tuplas com valores > 30, supondo uma distribuição uniformedos dados.

4. Estimar o número de tuplas com valores > 30, supondo uma distribuição de acordocom o histograma por largura.

100 tuplas, 20 valores variando de 1 a 98

1 3

4 10

6 4

10 3

22 8

31 9

35 2

43 5

47 7

53 8

58 2

60 7

63 3

65 4

76 3

78 7

80 2

84 4

93 3

98 6

1. Fazer um histograma por profundidade .

2. Estimar o número de tuplas com valores > 30, supondo uma distribuição de acordocom o histograma por largura.

Histogramas comprimidos

• Histogramas por profundidade produzem melhores estimativas do que histogramas por largura

• Histogramas comprimidos– Mantém contadores para valores mais frequentes (por

exemplo idade = 7 e idade = 14)– Para os outros valores, mantém um histograma por

profundidade (de preferência) ou por largura.• Muitos SGBDs utilizam histograma por

profundidade, alguns utilizam mesmo histogramas comprimidos.

GERAÇÃO DE PLANOS COM MÚLTIPLAS RELAÇÕES

A B C D

A B

C

D

A B

C

D

BA C D

PLANOS LINEARES

PLANO BUSHY

PLANO POR PROFUNDIDADE À ESQUERDA

Planos por profundidade à esquerda

• São os únicos a serem considerados:– Quanto maior o número de joins maior o número de planos

alternativos. Por isto opta-se por considerar somente os left-deep.

– Planos left-deep permitem utilizar estratégia pipeline à esquerda com a relação externa. A relação interna é sempre uma relação de base (materializada).

– Repare que não é possível utilizar pipeline à direita de um join. É sempre necessário que a relação interna esteja disponível em sua integralidade, pois é varrida diversas vezes.

– No caso de planos left-deep, este problema não acontece, pois o filho à direita de um Join é sempre uma relação de base (materializada).

REVISÃO PARA 3a PROVA Gerência de Banco de Dados Profa. Sandra de Amo Disciplina GBC053...

Documents

Transcript of REVISÃO PARA 3a PROVA Gerência de Banco de Dados Profa. Sandra de Amo Disciplina GBC053...