Otimização de Consultas em SQL Parte I - Planos de Execução

1. Otimização de Consultas em SQLParte I - Planos de Execução AULA 24 – Parte I Profa. Sandra de Amo GBC053 – BCC

2. Principais etapas do processo • Transformar os blocos simples SQL em expressões da álgebra relacional • Enumerar os possíveis planos de execução da expressão da álgebra relacional correspondendo à consulta • Estimar o custo de cada plano • Escolher o plano com menor custo

3. Esquema Geral do Otimizador Bloco SQL simples usuário Consulta SQL SQL Parser Transforma em Algebra Coleção de blocos simples B1, B2, ...., Bn Plano canônico Cria planos alternativos Otimizador Planos alternativos Estima custos Melhor Plano de execução Melhor Plano de execução

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

5. Bloco simples • SELECT <lista atributos> FROM <lista relações> WHERE <condição em FNC> GROUP BY HAVING

6. Exemplo R(sid,bid,day,rname) : RESERVA S(sid,sname,rating,age) : SAILORS B(bid,bname, color) : BOAT • Para cada sailor com o mais alto status (rating) e que fez pelo menos 2 reservas de barcos vermelhos, dê seu identificador e a data mais recente em que fez reserva de barco vermelho.

7. Exemplo (continuação) 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

8. Exemplo (continuação) • 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

9. Bloco SQL  Expressão algébrica ΠA,B,..., MIN (C) Projeção sobre os atributos do SELECT Having .... Group by ... σcondições do WHERE Seleção sobre as condições do WHERE R1 X R2 X ... X Rn Produto Cartesiano das relações do FROM

10. ΠA,B,...,Min(C) Plano de Execução “Canônico” ΠA,B,...,C ΠA,B,...,C Having .... Group by A σcondições do WHERE σ R1 X R2 X ... X Rn X • Resultado R é ordenado • O GROUP BY é executado • sobre o resultado R ordenado. • O HAVING é aplicado para eliminar certos grupos. • Funções de agregação são executadas sobre os grupos finais R1 R2 Rn

11. O que é um plano de execução ? • Plano de execução correspondente à uma expressão algébrica E • Sequência de operações equivalente à expressão E, isto é, produzindo o mesmo resultado que E. • Para cada operação da sequência (projeção, seleção, junção), um algoritmo é especificado para implementar tal operação.

12. Exemplo Π Projeção com ordenação Π Projeção com ordenação σ Seleção usando indice B+tree no atributo A Seleção usando indice Hash no atributo B σ X Hash Join X Sort Merge Join R S R S

13. O que é plano de execução canônico? • É o plano de execução correspondente exatamente à consulta SQL recebida pelo processador de consultas.

14. Exemplo R(sid,bid,day,rname) : RESERVA S(sid,sname,rating,age) : SAILORS B(bid,bname, color) : BOAT Quais os dias em que foram reservados barcos vermelhos ?

15. Plano 1= plano canônico da consulta Select R.Day From R, B Where R.Bid = B.Bid AND B.Color = ‘Vermelho’ ΠDay σcolor = ‘vermelho’ R B

16. Planos Alternativos Transformar a expressão algébrica “canônica” (Πσ x ) em outra expressão equivalente. Utilizar algoritmos alternativos para implementar as operações algébricas

17. Plano 2 (otimizado) Select R.Day From R Where R.Bid IN ( Select B.Bid From B Where B.Color = ‘Vermelho’ ) ΠDay R ΠBid σcolor = ‘vermelho’ B

18. Otimização de Consultas em SQLEquivalência de Expressões da Algebra Relacional AULA 24 – Parte II Profa. Sandra de Amo GBC053 – BCC

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

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

21. Equivalências de Expressões Algébricas Produto Cartesiano e Junção Associativa R (S T) = (R S) T R (S T) = (R S) T Comutativa (R S) = (S R) (R S) = (S R)

22. Equivalências de Expressões Algébricas 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 Exercício: Mostrar que isto não é verdade caso existam atributos de c que não aparecem em X

23. Equivalências de Expressões Algébricas 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

24. Exercício Dê um exemplo para mostrar que as propriedades não são válidas caso c contenha atributos de ambas as relações R e S. R S S S R σ B=b1( ) R A= A= A= S σ B=b1( R) σ B=b1( R)