Processamento de Consultas
30 pág.

Processamento de Consultas


DisciplinaAdministração de Banco de Dados584 materiais3.179 seguidores
Pré-visualização1 página
Prof. Luiz Vivacqua
Processamento de ConsultasAdministração de Banco de Dados
Processamento de Consultas
Prof. Luiz Vivacqua
Processamento de Consultas
\u2022 Motivação
Fornecedor Fornecimento
Supor que existam 100 fornecedores e 10.000 fornecimentos,
sendo que 50 fornecimentos são referentes a peça P2
Prof. Luiz Vivacqua
Processamento de Consultas
Select nome 
from Fornecedor T1, Fornecimento T2
Where T1.id=T2.id_forn and T2.id_peca=\u2018P2\u2019;
Estratégia 1:
Passo 1- produto cartesiano de fornecedor com fornecimento
R1 \ufffd Fornecedor X Fornecimento
Leitura:10.100 linhas
Gravação:1.000.000 linhas
Passo2 \u2013 Seleção de linhas
R2 \ufffd \u3c3 T1.id=T2.id_forn ^ T2.id_peca=\u2018P2\u2019 (R1)
Leitura:1.000.000 linhas
Gravação: 50 linhas (em memória)
Passo3 \u2013 Projeção do resultado
Resultado \ufffd\u41fnome (R2)
Prof. Luiz Vivacqua
Processamento de Consultas
Select nome 
from Fornecedor T1, Fornecimento T2
Where T1.id=T2.id_forn and T2.id_peca=\u2018P2\u2019;
Estratégia 2:
Passo 1- Seleção das peças iguais a P2 na tabela fornecimento
R1 \ufffd \u3c3 T2.id_peca=\u2018P2\u2019 (Fornecimento )
Leitura:10.000 linhas
Gravação: 50 linhas (em memória)
Passo2 \u2013 Junção de R1 com a tabela Fornecedor
R2 \ufffd Fornecedor |><| R1
Leitura:100 linhas
Gravação: 50 linhas (em memória)
Passo3 \u2013 Projeção do resultado
Resultado \ufffd\u41fnome (R2)
Prof. Luiz Vivacqua
Processamento de Consultas
Select nome 
from Fornecedor T1, Fornecimento T2
Where T1.id=T2.id_forn and T2.id_peca=\u2018P2\u2019;
Estratégia 3: Supondo que existam índices nas colunas Fornecimento(id_peca) e 
Fornecedor(id)
Passo 1- Seleção das peças iguais a P2 na tabela fornecimento
R1 \ufffd \u3c3 T2.id_peca=\u2018P2\u2019 (Fornecimento )
Leitura: 50 linhas
Gravação: 50 linhas (em memória)
Passo2 \u2013 Junção de R1 com a tabela Fornecedor
R2 \ufffd Fornecedor |><| R1
Leitura:50 linhas
Gravação: 50 linhas (em memória)
Passo3 \u2013 Projeção do resultado
Resultado \ufffd\u41fnome (R2)
Prof. Luiz Vivacqua
Processamento de Consultas
Select nome 
from Fornecedor T1, Fornecimento T2
Where T1.id=T2.id_forn and T2.id_peca=\u2018P2\u2019;
Resumo: Número aproximado de acessos a disco para 
leitura / gravação de dados
10010.0002.000.000
Estratégia 3Estratégia 2Estratégia 1
A estratégia 3 é cerca de 20.000 vezes melhor que a estratégia 1
e cerca de 100 vezes melhor que a estratégia 2.
Prof. Luiz Vivacqua
Processamento de Consultas
Prof. Luiz Vivacqua
Processamento de Consultas
Análise Sintática/Validação de nomes
\ufffd Verificação se consulta está formulada corretamente. 
\u2022 O dicionário é consultado para a verificação da existência das colunas, 
tabelas e visões. 
\u2022 Sintaxe é validada para determinar se ela está de acordo com as regras
\u2022 Processamento de visões. As views tem que ser substituídas pelas consultas
que as definem. O dicionário é consultado para obtenção do SQL que define 
a View.
Prof. Luiz Vivacqua
Processamento de Consultas
Fornecimento
\u3c3 T2.id_peca=\u2018P2\u2019
|><|
Fornecedor
|
\u41fnome
Árvore de consulta(Forma intermediária)
|
Prof. Luiz Vivacqua
Processamento de Consultas
Avaliação das operações:
1. seleção de linhas
\u2022 \u201cFull Table Scan\u201d \u2013 varredura de todas as linhas da tabela
\u2022 Leitura no índice e posterior leitura da tabela
2. Função
\u2022 \u201cFull table Scan\u201d
\u2022 Leitura no índice (árvore B) 
3. Junção
\u2022 \u201cNested Loop\u201d
\u2022 \u201cSort-Merge\u201d
\u2022 \u201chash-Join\u201d
Prof. Luiz Vivacqua
Processamento de Consultas
Nested Loop
FOR i = 1 TO 10000
FOR j = 1 TO 100
IF Fornecedor.id[i] == Fornecimento.cod_forn[j]
Then Resultado = Fornecedor[i] x Fornecimento[j]
END
END
Principais Algoritmos de Junção
Prof. Luiz Vivacqua
Processamento de Consultas
\u2022 Nested Loop
\ufffd Não exige índices e pode ser usada com qualquer tipo de condição de 
junção.
\ufffd Dispendiosa, pois examina cada par de tuplas nas duas relações.
\ufffd Custo:
\ufffd Pior caso: 1.000.000 leituras 
\ufffd Melhor caso: 10.100 leituras (caso a tabela fornecedor caiba em 
memória) 
Prof. Luiz Vivacqua
Processamento de Consultas
Sort Merge
Sort em Fornecedor pela coluna codigo -> Temp1
Sort em Fornecimento pela coluna cod_forn -> Temp2
k = 1
FOR i = 1 TO 100
FOR j = k TO 10000
IF Fornecedor.id[i] == Fornecimento.cod_forn[j]
then Resultado = Fornecedor[i] x Fornecimento[j]
IF Fornecedor.id[i] < Fornecimento.cod_forn[j]
then Exit
END
k = j
END
Principais Algoritmos de Junção
Prof. Luiz Vivacqua
Processamento de Consultas
\u2022 Sort-Merge
\ufffd Não exige índices e pode ser usada com qualquer tipo de condição de 
junção.
\ufffd Custo:
\u2022 Pior caso: custo de ordenação(10.100) + varredura(10.100) leituras 
\u2022 Melhor caso: 10.100 leituras (caso as tabelas já estejam ordenadas)
Prof. Luiz Vivacqua
Processamento de Consultas
Hash Join
FOR i = 1 TO 100
k = hash(Fornecedor.id[i])
Temp[k] = Fornecedor[i]
END
FOR j = 1 TO 10000
k = hash(Fornecimento.cod_forn[j])
IF Temp[k].id == Fornecimento.cod_forn[j]
then Resultado = Temp[k] x Fornecimento[j]
END
Principais Algoritmos de Junção
Prof. Luiz Vivacqua
Processamento de Consultas
\u2022Hash-Join
\ufffdAs relações não precisam estar previamente ordenadas pelo atributo de 
junção.
\ufffdNão é necessário a existência de um índice em um dos dois atributos de 
junção.
\ufffdSomente pode ser usado em junções de igualdade (equi-join)
\ufffdCusto:
10.100 leituras 
Prof. Luiz Vivacqua
Processamento de Consultas
\u2022 Principais Estatisticas
\u2013 Tamanho da tabela
\u2022 Nro de linhas
\u2022 Tamanho da linha
\u2013 Colunas
\u2022 Nro de valores distintos
\u2022 Maior valor
\u2022 Menor valor
\u2013 Indices
\u2022 Número de niveis
\u2022 Número de folhas
Prof. Luiz Vivacqua
Processamento de Consultas
OtimizadorEstatísticas Algoritmos
Árvore inicial
Plano de execução
Prof. Luiz Vivacqua
Processamento de Consultas
|
Fornecimento
\u3c3 T2.id_peca=\u2018P2\u2019
|><|
Fornecedor
|
\u41fnome
Árvore de execução
Prof. Luiz Vivacqua
Processamento de Consultas
Plano de Execução:
Prof. Luiz Vivacqua
Processamento de Consultas
Prof. Luiz Vivacqua
Processamento de Consultas
Exemplos do processamento de consultas no 
PostGreSQL
Prof. Luiz Vivacqua
Processamento de Consultas
Tabela T_AGRICOLA
Num de linhas: 1.171.449
Pk: (uf,municipio,produto,ano)
Prof. Luiz Vivacqua
Processamento de Consultas
Tabela T_PROD_AGRO
Num de linhas: 66
Pk: (codigo)
Prof. Luiz Vivacqua
Processamento de Consultas
Prof. Luiz Vivacqua
Processamento de Consultas
Prof. Luiz Vivacqua
Processamento de Consultas
Criando um indice na coluna produto da tabela T_AGRICOLA
create index ind_produto on t_agricola(produto);
Prof. Luiz Vivacqua
Processamento de Consultas
Prof. Luiz Vivacqua
Processamento de Consultas
Criando um indice na coluna nome da tabela T_PROD_AGRO
create index ind_nome_prod on T_PROD_AGRO(nome);
Prof. Luiz Vivacqua
Processamento de Consultas