1 / 19

Algoritmos para Operações Binárias entre blocos SQL

Algoritmos para Operações Binárias entre blocos SQL. AULA 19 – Parte I Profa. Sandra de Amo GBC053 – BCC. Operações de Conjuntos. (Select B.Bid From Barcos B T1(Bid) Where B.Cor = ‘Verde’) UNION (Select R.Bid From Reservas R T2(Bid) Where R.Day = 09/05/2012). Select B.Bid

arvin
Download Presentation

Algoritmos para Operações Binárias entre blocos SQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Algoritmos para Operações Binárias entre blocos SQL AULA 19 – Parte I Profa. Sandra de Amo GBC053 – BCC

  2. Operações de Conjuntos (Select B.Bid From Barcos B T1(Bid) Where B.Cor = ‘Verde’) UNION (Select R.Bid From Reservas R T2(Bid) Where R.Day = 09/05/2012) Select B.Bid From Barcos B T1(Bid) Where B.Cor = ‘Verde’) EXCEPT (Select R.Bid From Reservas R T2(Bid) Where R.Day = 09/05/2012) T1 - T2 T1 U T2

  3. (Select B.Bid From Barcos B T1(Bid) Where B.Cor = ‘Verde’) INTERSECT (Select R.Bid From Reservas R T2(Bid) Where R.Day = 09/05/2012) T1  T2 Consulta equivalente a T1 T2 Operações de Conjuntos

  4. Técnicas para implementar UNION e EXCEPT Baseadas em Ordenação Baseadas em Hash

  5. 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.

  6. Fase da Intercalação 1 1 2 1 2 2 5 5 6 6 7 8 7 9 9 8 9 11 13 11 13 Página de T2 Página de T1 Página de output Buffer pool CUSTO TOTAL = 2M ([LogB-1 M/B] + 1) + 2N ([LogB-1 N/B] + 1) + M + N CUSTO DA INTERCALAÇÃO = M + N M = número de páginas de T1 N = número de páginas de T2

  7. Exercicio para entregar • Escrever um algoritmo otimizado para fazer a intercalação junto com a ordenação • Etapa 0: Cria-se M/B subarquivos de T1 e M/B subarquivos de T2 ordenados em memória principal • Etapa 1: Ordena-se e simultaneamente intercala-se as duas tabelas. • Calcular o custo deste algoritmo otimizado • Qual o tamanho do buffer para poder utilizar o algoritmo otimizado ? • Sugestão: utilizar ideia análoga à ideia do algoritmo sort-merge join otimizado.

  8. 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.

  9. Fase da Diferença • Se T1.r < T2.r • Insere T1.r no Output • Atualiza os marcadores de T1 • e T2 para os próximos registros • de T1 e T2 dif. de T1.r e T2.r • respectivamente. • 2.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 1 1 2 1 2 5 6 7 8 9 9 11 13 Página de T2 Página de T1 Página de output Buffer pool

  10. Fase da Diferença • Se T1.r < T2.r • Insere T1.r no Output • Atualiza os marcadores de T1 • e T2 para os próximos registros • de T1 e T2 dif. de T1.r e T2.r • respectivamente. • 2.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 1 1 2 1 2 5 5 6 7 8 9 9 11 13 Página de T2 Página de T1 Página de output Buffer pool

  11. Fase da Diferença • Se T1.r < T2.r • Insere T1.r no Output • Atualiza os marcadores de T1 • e T2 para os próximos registros • de T1 e T2 dif. de T1.r e T2.r • respectivamente. • 2.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 1 1 2 1 2 5 5 6 7 7 8 9 9 11 13 Página de T2 Página de T1 Página de output Buffer pool

  12. Fase da Diferença • Se T1.r < T2.r • Insere T1.r no Output • Atualiza os marcadores de T1 • e T2 para os próximos registros • de T1 e T2 dif. de T1.r e T2.r • respectivamente. • 2.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 1 1 2 1 2 5 5 6 7 7 8 11 9 9 11 13 Página de T2 Página de T1 Página de output Buffer pool CUSTO TOTAL = 2M ([LogB-1 M/B] + 1) + 2N ([LogB-1 N/B] + 1) + M + N CUSTO DA DIFERENÇA = M + N M = número de páginas de T1 N = número de páginas de T2

  13. Exercício para entregar • Escrever um algoritmo otimizado para fazer a diferença junto com a ordenação • Etapa 0: Cria-se M/B subarquivos de T1 e M/B subarquivos de T2 ordenados em memória principal • Etapa 1: Ordena-se e simultaneamente faz-se a diferença das duas tabelas. • Calcular o custo deste algoritmo otimizado • Qual o tamanho do buffer para poder utilizar o algoritmo otimizado ? • Sugestão: utilizar ideia análoga à ideia do algoritmo sort-merge join otimizado.

  14. EXCEPT: algoritmo baseado em hash T2 • 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 ! - Exercício) • 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.

  15. 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 > (Prove !) • 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

  16. Cálculo de Custos (para os dois algoritmos) • Fase do Particionamento: • Custo de particionar T1 = 2M • Custo de particionar T2 = 2N • Custo do particiomento 2(M+N) • Fase da Intercalação (ou da Diferença): • M + N • Custo total = 3(M + N)

  17. (Select B.Bid From Barcos B Where B.Cor = ‘Verde’) UNION (Select R.Bid From Reservas R Where R.Day = 01/05/2012) -Calcular o custo da consulta sabendo que: -Tamanho de Barcos = 1000 páginas (Barcos(Bid,Bnome,Cor)) -Campos têm o mesmo tamanho. -Temos 5 cores de barcos (azul, vermelho, amarelo, verde, preto). As cores estão uniformemente distribuídas entre os barcos. -Tamanho de Reservas(Bid,Sid,Day) = 3000 páginas -O tamanho do campo Day = a soma dos tamanhos dos campos Sid e Bid -As reservas foram feitas em 100 dias e estão uniformemente distribuídas nestes dias. -Tamanho do buffer = 40 páginas Exemplo

  18. SOLUÇÃO Construção de T1(bid) • Leitura de Barcos = 1000 • Scan para selecionar e gravar somente os barcos verdes = 1000/5 = 200 • O operador de projeção é executado on the fly (junto com o operador de seleção) – portanto sem custo • Custo total = 1000 + 200/3 = 1067 Construção de T2(bid) • Leitura de Reservas = 3000 • Scan para selecionar e gravar somente as reservas do dia 01/05/2012 = 3000/100 = 30 • O operador de projeção é executado on the fly (junto com o operador de seleção) – portanto sem custo • Custo total = 3000 + 30/4 = 3008 gravação leitura gravação leitura

  19. CUSTO DA CONSULTA Custo do UNION: 2.67 ([Log3967/40] + 1) + 2.8 ([Log39 8/40] + 1) + 67 + 8 = 134.2 + 16.2 + 75 = 268 + 32 + 75 = 375 Custo total da Consulta = 1067 + 3015 + 375 = 4457 I/O

More Related