Otimização de Consultas em SQL Estimativas de Custos

1. Otimização de Consultas em SQLEstimativas de Custos AULA 25 Profa. Sandra de Amo GBC053 – BCC 2013-1

2. Catálogo do Sistema Informações armazenadas no catálogo, necessárias no processo de otimização: • Informações gerais: • Tamanho do buffer pool (espaço livre) • Tamanho de uma página em disco • Informações sobre as tabelas • Informações sobre índices • Informações sobre visões • Estatísticas sobre tabelas e indices: atualizadas periodicamente • Informações sobre usuários: contas, autorizações de acesso (escrita, leitura), etc. • Alguns SGBDs mais sofisticados: • Histogramas sobre a distribuição dos valores de cada atributo. • A partir destas informações é possivel ter informação mais acurada sobre o tamanho dos resultados de uma operação, sobre a seletividade de um método de acesso, etc.

3. Informações sobre tabelas, índices e visões • Tabelas • Nome da tabela, nome ou identificador do arquivo, estrutura de arquivo (heap, ordenado, hash) • Nome e tipo de cada atributo • Nome dos índices existentes para cada tabela • As restrições de integridade (chaves primárias, chaves candidatas, chaves estrangeiras, etc) para cada tabela • Indices • O nome do índice e sua estrutura (hash, b+tree) • A chave do índice • Visões • Nome da visão e sua definição (o código da consulta SQL que a define)

4. Como o catálogo é armazenado : Coleção de Tabelas Exemplo • Sailors(sid:integer, sname:string, rating:integer, age:real) • Reservas(sid:integer, bid:integer, day:dates, rname:string) • Tabela do catálogo: • Attribute_Cat(atname:string, relname:string, type:string, position:integer) • Catálogo pode ser consultado usando SQL ! • Escolha das tabelas do catálogo e seus esquemas depende do implementador do SGBD.

5. Uma tabela do catálogo

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

7. SELECT S.sname FROM Reservas R, Sailors S WHERE R.sid = S.sid AND R.bid = 100 AND S.rating > 5 Plano de Execução Π sname σ bid=100 and rating > 5 sid=sid Reservas Sailors

8. Complementando o plano: como será implementado ? Π Projeção simples sname σ bid=100 and rating > 5 Scan Simple Nested Loops página a página sid=sid Reservas Sailors (scan) (scan) Tabela externa Tabela interna

9. Complementando o plano: como será implementado ? Π On-the-fly sname σ bid=100 and rating > 5 On-the-fly Simple Nested Loops página a página sid=sid Reservas Sailors (scan) (scan) Tabela externa Tabela interna

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

11. 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)

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

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

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

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

16. Exercícios • É possível utilizar a estratégia de pipeline para implementar um duplo join utilizando o algoritmo NLJ/p-p para os dois Joins com o plano linear à esquerda ? C A B SIM

17. Exercícios • É possível utilizar a estratégia de pipeline para implementar um duplo join utilizando o algoritmo NLJ/p-p para os dois Joins com o plano linear à direita ? A B C NÃO