1 / 17

Otimização de Consultas em SQL Estimativas de Custos

Otimização de Consultas em SQL Estimativas de Custos. AULA 25 Profa. Sandra de Amo GBC053 – BCC 2013-1. 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)

tamah
Download Presentation

Otimização de Consultas em SQL Estimativas de Custos

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

More Related