BD I / 2013-02 Processamento de Consultas

BD I / 2013-02Processamento de Consultas Prof. Altigran Soares da Silva IComp/UFAM

Foco: Sistemas relacionais • Outros? Processamento de Consultas Q  Plano de execução da consulta

Exemplo Select B,D From R,S Where R.A = “c”and S.E = 2 and R.C=S.C

Resultado B D 2 x R A B C S C D E a 1 10 10 x 2 b 1 20 20 y 2 c 2 10 30 z 2 d 2 35 40 x 1 e 3 45 50 y 3

Como executar a consulta? - Fazer produto cartesiano - Selecionar as tuplas - Fazer projeção Uma idea

Uma resposta .. RXS R.A R.B R.C S.C S.D S.E a 1 10 10 x 2 a 1 10 20 y 2 . . C 2 10 10 x 2 . .

Álgebra Relacional – pode ser usada para descrever planos de execução Ex: Plano I B,D sR.A=“c” S.E=2  R.C=S.C X R S OU: B,D [sR.A=“c” S.E=2  R.C = S.C (RXS)]

Outra ideia: Plano II B,D sR.A = “c”sS.E = 2 R S Junção natural

R S A B C s (R) s(S) C D E a 1 10 A B C C D E 10 x 2 b 1 20 c 2 10 10 x 2 20 y 2 c 2 10 20 y 2 30 z 2 d 2 35 30 z 2 40 x 1 e 3 45 50 y 3

Plano III Usar os índices sobre R.A e S.C (1) Usar o indíce sobre R.A para selecionar as tuplas onde R.A = “c” (2) Para cada valor de R.C, usar o índice sobre S.C para achar as tuplas de S (3) Eliminar as tuplas de S onde S.E  2 (4) Aplicar o Join sobre as tuplas de R e S e projetar os atributos B e D

=“c” <c,2,10> <10,x,2> E=2? saida: <2,x> prox. tupla: <c,7,15> R S A B C C D E a 1 10 10 x 2 b 1 20 20 y 2 c 2 10 30 z 2 d 2 35 40 x 1 e 3 45 50 y 3 A C I1 I2

Otimização de Consulta • Determinar qual o “melhor” entre os vários possíveis planos de execução de uma consulta

Consulta SQL parse parse tree convert resposta plano lógico p/ a consulta execução regras estatísticas Pi Plano “melhorado” melhor Est. tamanho {(P1,C1),(P2,C2)...} Plano + tamanho Custos estimados Comp. planos físicos {P1,P2,…..}

Exemplo: Consulta SQL SELECT title FROM StarsIn WHERE starName IN ( SELECT name FROM MovieStar WHERE birthdate LIKE ‘%1960’ ); Encontrar os filmes cujos atores nasceram em 1960

Examplo: Parse Tree <Query> <SFW> SELECT <SelList> FROM <FromList> WHERE <Condition> <Attribute> <RelName> <Tuple> IN <Query> title StarsIn <Attribute> ( <Query> ) starName <SFW> SELECT <SelList> FROM <FromList> WHERE <Condition> <Attribute> <RelName> <Attribute> LIKE <Pattern> name MovieStar birthDate ‘%1960’

Examplo: Álgebra Relacional title  StarsIn <condition> <tuple> IN name <attribute> birthdate LIKE ‘%1960’ starName MovieStar

Examplo: Plano lógico da consulta title starName=name  StarsIn name birthdate LIKE ‘%1960’ MovieStar Aplicando regra para condições “IN”

Examplo: Plano Melhorado title starName=name StarsIn name birthdate LIKE ‘%1960’ MovieStar

Examplo: Estimado tamanhos de resultados tamanho estimado StarsIn MovieStar P s

Examplo: Possível plano físico Parâmetros: ordem da junção, tamanho da mem, atributos,... Hash join Paramêtro: Condiçaõ de seleção,... SEQ scan index scan StarsIn MovieStar

Example: Custo estimado L.Q.P P1 P2 …. Pn C1 C2 …. Cn Escolha o melhor!

Estrutura desta Unidade Capitulo 6 6.1 Álgebra para consultas [“bags” vs sets] - Seleção, projeção, junção, … - Elminação de duplicatas, agrupamento, ordenação 6.2 Operadores físicos - Varredura (Scan), ordenação, … 6.3-6.10 Implementação de operadores + estimativa de curstos

Capítulo 7 7.1 Parsing 7.2 Leis da Álgebra 7.3 Parse tree -> Plano lógico de consulta 7.4 Estimativa do tamanho de resultados 7.5-7 Otimização baseada em custos

Estudo do Livro Texto - Capítulos 6,7 Opcionais: • 6.8, 6.9, 6.10, 7.6, 7.7 • Eliminação de duplicadas, agrupamento e agregação

Otimização de consultas - Níveis • Nível de álgebra relacional • Nível do plano de consulta detalhado • Estimativa de custos • Sem índices • Com índices • Gerar e comparar planos

Otimização em Álgebra Relacional • Regras de transformação • Preservando equivalência • O que são boas transformações?

Leis Associativas e Comutativas • Produtos • R x S = S x R • (R x S) x T = R x (S x T) • Junções Naturais • R ⨝ S = S ⨝ R • (R ⨝ S) ⨝ T = R ⨝ (S ⨝ T) • Uniões e Intersecções • R U S = S U R • R U (S U T) = (R U S) U T

Junções Teta • Comutativa • R ⨝C S = S ⨝C R • Não é associativa: • Seja R(a,b), S(b,c) e T(c,d) • (R ⨝R.b>S.b S) ⨝a<d T ≠ R ⨝R.b>S.b (S ⨝a<d T)

Seleções • Tendem a reduzir bastante o tamanho das relações • Portanto, em geral, devem ser processados antes dos outros operadores, ou seja, nos níveis mais baixos da árvore • Regras da álgebra são usadas para “quebrar” condições complexas e aumentar as oportunidades de “descidas” na árvore

Seleções - Regras sp1p2(R) = sp1vp2(R) = sp1 [ sp2 (R)] = sp2 [ sp1 (R)] [ sp1 (R)] U [ sp2 (R)]

Bags vs. Conjuntos • R = {a,a,b,b,b,c} • S = {b,b,c,c,d} • R U S = ? • Opção 1: R U S = {a,a,b,b,b,b,b,c,c,c,d} • Opção 2: R U S = {a,a,b,b,b,c,c,d}

Efeito da opção 2 • sp1vp2 (R) = sp1(R) U sp2(R) • ExemploR={a,a,b,b,b,c} • P1 satisfeitapora,b; P2 satisfeitaporb,c sp1vp2(R) = {a,a,b,b,b,c}sp1(R) = {a,a,b,b,b} e sp2(R) = {b,b,b,c}sp1(R) U sp2 (R) = {a,a,b,b,b,c}

Opção 1 – Mais interessante Senador (……) Deputado (……) T1 = pano,estado Senador; T2 = pano,estado Deputado T1 Ano Estado T2 Ano Estado 97 AM 99 AM 99 AM 99 AM 98 SP 98 AM União?

Projeções • Notação • Para X e Y sendo conjuntos de atributos • XY = X U Y pxy(R) ≠px[py(R)]

s + ⨝ sp (R ⨝ S) = [sp (R)] ⨝ S • p = predicado com atributos de R sq (R ⨝ S) = R ⨝ [sq (S)] • q = predicado com atributos de S • Objetivo é “adiantar” as seleções para antes da junção

s + ⨝: Derivações spq (R ⨝ S) = [sp (R)] ⨝ [sq (S)] spqm (R ⨝ S) = sm[(sp R) ⨝ (sq S)] spvq (R ⨝ S) = [(sp R) ⨝ S]U[R ⨝ (sq S)]

pxz px Regras:p,s combinados Sejam x = subconjunto dos atributos de R z = atributos do predicado de P (subconjunto dos atribs de R) px[sp (R) ] = {sp [ px (R) ]}

p + ⨝ Sejam x = subconjunto dos atributos de R y = subconjunto dos atributos de S z = interseção dos atributos de R e S pxy (R⨝S) = pxy {[pxz (R) ] ⨝[pyz (S) ]}

Projeções • Notação • Para X e Y sendo conjuntos de atributos • XY = X U Y pxy(R) ≠px[py(R)]

pxz px Regras:p,scombinados Sejam x = subconjunto dos atributos de R z = atributos do predicado de P (subconjunto dos atribs de R) px[sp(R)] = {sp [ px (R) ]}

Regrass,U combinadas: sp(R U S)= sp(R) U sp(S) sp(R - S)= sp(R) - S = sp(R) - sp(S)

Quais são as boas transformações? sp1p2 (R) sp1 [sp2 (R)] sp (R ⨝ S)  [sp (R)] ⨝ S R ⨝ S  S ⨝ R px [sp(R)] px {sp [pxz(R)]}

Convenção: Projeções ocorrem antes Exemplo: R(A,B,C,D,E) x={E} P: (A=3)  (B=“gato”) px {sp(R)} vs. pE {sp{pABE(R)}}

E se houverem indíces em A, B? B = “gato” A=3 fazer a interseção de apontadores para encontrar os apontadores que levam as tuplas desejadas

Limites para aplicação de regras: • Nenhuma transformação por aplicação da regra é sempre boa • No entanto, executar seleções mais cedo é geralmente melhor

Mais transformações no livro texto • Eliminação de sub-expressões comuns • Outras operações: • eliminação de duplicatas

Onde estamosProcessamento de Consultas • Nível da Álgebra Relacional • transformações • boas transformações • Nível de plano de consulta detalhado • estimativa de custos • gerar e comparar planos

Estimativa de Custos • (1) Estimativa do tamanho dos resultados • (2) Estimativa do nr. de operações de E/S

Tamanho do resultado • Para obter estimativas do tamanho do resultado, manter estatísticas para cada relação R • T(R) : # tuplas em R • S(R) : # bytes em cada tupla de R • B(R): # blocos p/ armazenar todas as tuplas de R • V(R, A) : # valores distintos em R p/ o atrib. A

A B C D cat 1 10 a Exemplo R A: 20 bytes string B: 4 bytes inteiro C: 8 bytes data D: 5 bytes string cat 1 20 b dog 1 30 a dog 1 40 c bat 1 50 d T(R) = 5 S(R) = 37 V(R,A) = 3 V(R,C) = 5 V(R,B) = 1 V(R,D) = 4

BD I / 2013-02 Processamento de Consultas