Otimização de Consultas em SQL

Prévia do material em texto

Otimização de Consultas em SQL 
Exemplos de Estimativas de Custos de 
Planos de Consulta
Adaptação de Slides das aulas da Profa. 
Sandra de Amo da UFU
 R(sid,bid,day,rname) : RESERVA 
S(sid,sname,rating,age) : SAILORS 
B(bid,bname, color) : BOAT
  Consulta: Dê todos os nomes de marinheiros 
com rating > 5 que reservaram o barco com 
bid = 100.
 Dados a considerar para o cálculo de custos dos planos: Nº de 
páginas de R = M = 1000, com 100 tuplas por página Nº de páginas de 
S = N = 500, com 80 tuplas por página
 Nº de barcos reservados = 100 (existem 100 valores de bids distintos 
na tabela R) Nº de valores para o atributo rating em S: 10 (varia de 1 a 
10)
 Hipóteses consideradas para a estimativa dos custos :
 As reservas estão uniformemente distribuídas entre os barcos.
 O número de marinheiros para cada valor de rating é 
aproximadamente o mesmo (distribuição uniforme dos valores de 
rating entre os marinheiros)
 Nº de páginas livres no buffer = 5
Cálculo de Custos de Planos de 
Execução 
Exercício1 : 
Calcule o custo deste plano
R : 1000 páginas
S : 500 páginas
R: 100 tuplas por página
S: 80 tuplas por página
σ
Reservas Sailors
Π
sname
bid=100 and rating > 5
sid=sid 
(scan) (scan)
Junção de laços aninhados por blocos
On-the-fly - Pipeline 
On-the-fly - Pipeline
Tabela externa Tabela interna
Cálculo de Custos de Planos de 
Execução 
Exercício1 : 
Calcule o custo deste plano
R : 1000 páginas = M
S : 500 páginas = N
R: 100 tuplas por página
S: 80 tuplas por página
σ
Reservas Sailors
Π
sname
bid=100 and rating > 5
sid=sid 
(scan) (scan)
Junção de laços aninhados por blocos
On-the-fly - Pipeline 
On-the-fly - Pipeline
Tabela externa Tabela interna
Custo = M + M.N = 1000 + 
1000*500 = 501.000 I/Os
“Empurrando” seleções para baixo na 
árvore de execução
Exercicio 2: 
Calcule o custo deste plano
Número de valores para bid = 100
Rating varia de 1 a 10
Uniformemente distribuidos
Numero de páginas no buffer = 5
σ
Reservas Sailors
Π
sname
bid=100
sid=sid 
(scan) (scan)
Sorte-Merge Join
On-the-fly 
Tabela externa Tabela interna
rating > 5
σ Scan, write to Temp2Scan, write to Temp1
“Empurrando” seleções para baixo na 
árvore de execução
Exercicio 2: 
Calcule o custo deste plano
Número de valores para bid = 100
Rating varia de 1 a 10
Uniformemente distribuidos
Numero de páginas no buffer = 5
σ
Reservas Sailors
Π
sname
bid=100
sid=sid 
(scan) (scan)
Sorte-Merge Join
On-the-fly 
Tabela externa Tabela interna
rating > 5
σ Scan, write to Temp2Scan, write to Temp1
Tamanho 
de Temp 1 
= 1000/100 
= 10 
Tamanho de 
Temp 2 = 
500/2 = 250
Custo de ordenação de 
Temp1 = 10 * (2*nº de 
etapas + 1)
Nº de etapas = [log4 10/5] 
= [log4 2] = [0,5] = 1 
Custo de ordenação de 
Temp 1 = 30
Custo de ordenação 
de Temp2 = 250*(2*nº 
de etapas + 1)
Nº de etapas = [log4 
250/5] = [log4 50] = 
[2,5] = 3 
Custo de ordenação 
de Temp2 = 1750
“Empurrando” seleções para baixo na 
árvore de execução
Exercicio 2: 
Calcule o custo deste plano
Número de valores para bid = 100
Rating varia de 1 a 10
Uniformemente distribuidos
Numero de páginas no buffer = 5
σ
Reservas Sailors
Π
sname
bid=100
sid=sid 
(scan) (scan)
Sorte-Merge Join
On-the-fly 
Tabela externa Tabela interna
rating > 5
σ Scan, write to Temp2Scan, write to Temp1
Custo do Sort-Merge = 
10 + 250 = 260
Custo total do plano = 
Custo de leitura das 
tabelas originais e 
gravação das 
materializadas + Custo da 
ordenação das duas 
tabelas + Custo do Sort-
Merge
“Empurrando” seleções para baixo na 
árvore de execução
Exercicio 2: 
Calcule o custo deste plano
Número de valores para bid = 100
Rating varia de 1 a 10
Uniformemente distribuidos
Numero de páginas no buffer = 5
σ
Reservas Sailors
Π
sname
bid=100
sid=sid 
(scan) (scan)
Sorte-Merge Join
On-the-fly 
Tabela externa Tabela interna
rating > 5
σ Scan, write to Temp2Scan, write to Temp1
Custo do Sort-Merge = 
10 + 250 = 260
Custo total do plano = 
(1000+10+500+250) + (30 
+ 1750 + 260) = 1760 + 
2040 =3800 I/O
“Empurrando” seleções para baixo na 
árvore de execução
Exercicio 3 : 
Calcule o custo deste plano
Número de valores para bid = 100
Rating varia de 1 a 10
Uniformemente distribuidos
Numero de páginas no buffer (B)= 5
σ
Reservas Sailors
Π
sname
bid=100
sid=sid 
(scan) (scan)
Junção de laços aninhados por blocos
melhorado
On-the-fly 
Tabela externa Tabela interna
rating > 5
σ Scan, write to Temp2Scan, write to Temp1
“Empurrando” seleções para baixo na 
árvore de execução
Exercicio 3 : 
Calcule o custo deste plano
Número de valores para bid = 100
Rating varia de 1 a 10
Uniformemente distribuidos
Numero de páginas no buffer (B)= 5
σ
Reservas Sailors
Π
sname
bid=100
sid=sid 
(scan) (scan)
Junção de laços aninhados por blocos
melhorado
On-the-fly 
Tabela externa Tabela interna
rating > 5
σ Scan, write to Temp2Scan, write to Temp1
B-2 = 3
Nº de blocos de 3 páginas = 
[10/3] = 4
Custo da operação de 
junção de Temp1 com 
Temp2 = 4*250 + 10 = 1010
Custo total do plano = Custo 
de leitura das tabelas 
originais e gravação das 
materializadas + Custo da 
junção
“Empurrando” seleções para baixo na 
árvore de execução
Exercicio 3 : 
Calcule o custo deste plano
Número de valores para bid = 100
Rating varia de 1 a 10
Uniformemente distribuidos
Numero de páginas no buffer (B)= 5
σ
Reservas Sailors
Π
sname
bid=100
sid=sid 
(scan) (scan)
Junção de laços aninhados por blocos
melhorado
On-the-fly 
Tabela externa Tabela interna
rating > 5
σ Scan, write to Temp2Scan, write to Temp1
B-2 = 3
Nº de blocos de 3 páginas = 
[10/3] = 4
Custo da operação de 
junção de Temp1 com 
Temp2 = 4*250 + 10 = 1010
Custo total do plano de 
execução = (1000 + 10 + 500 
+ 250) + 1010 = 2770 I/O
“Empurrando” projeções para baixo na 
árvore de execução
Exercicio 4 : 
Calcule o custo deste plano
Número de valores para bid = 100
Rating varia de 1 a 10
Uniformemente distribuídos
Número de páginas no buffer = 5
σ
Reservas Sailors
Π
sname
bid=100
sid=sid 
(scan) (scan)
Junção de laços aninhados por blocos
melhorado
On-the-fly 
Tabela externa Tabela interna
rating > 5
σ
Scan, write to Temp2Scan, write to Temp1
Πsid
Πsid,sname On-the-fly On-the-fly 
“Empurrando” projeções para baixo na 
árvore de execução
Exercicio 4 : 
Calcule o custo deste plano
Número de valores para bid = 100
Rating varia de 1 a 10
Uniformemente distribuídos
Número de páginas no buffer = 5
σ
Reservas Sailors
Π
sname
bid=100
sid=sid 
(scan) (scan)
Junção de laços aninhados por blocos
melhorado
On-the-fly 
Tabela externa Tabela interna
rating > 5
σ
Scan, write to Temp2Scan, write to Temp1
Πsid
Πsid,sname On-the-fly On-the-fly 
Reserva tem 4 
atributos. Sailors tem 
4 atributos. Tamanho 
de Temp1 após a 
projeção = [10/4] = 3 
páginas
Tamanho de 
Temp2 após a 
projeção = [250/2] 
= 125 páginas
“Empurrando” projeções para baixo na 
árvore de execução
Exercicio 4 : 
Calcule o custo deste plano
Número de valores para bid = 100
Rating varia de 1 a 10
Uniformemente distribuídos
Número de páginas no buffer = 5
σ
Reservas Sailors
Π
sname
bid=100
sid=sid 
(scan) (scan)
Junção de laços aninhados por blocos
melhorado
On-the-fly 
Tabela externa Tabela interna
rating > 5
σ
Scan, write to Temp2Scan, write to Temp1
Πsid
Πsid,sname On-the-fly On-the-fly 
Reserva tem 4 
atributos. Sailors tem 
4 atributos. Tamanho 
de Temp1 após a 
projeção = [10/4] = 3 
páginas
Tamanho de 
Temp2 após a 
projeção = [250/2] 
= 125 páginas
Nº de blocos de 3 páginas para 
Temp1 = 1
Custo do Laços aninhado = 3 
+ 1* 125 = 128
“Empurrando” projeções para baixo na 
árvore de execução
Exercicio 4 : 
Calcule o custo deste plano
Número de valores para bid = 100
Rating varia de 1 a 10
Uniformemente distribuídos
Número de páginas no buffer = 5
σ
Reservas Sailors
Π
sname
bid=100
sid=sid 
(scan) (scan)
Junção de laços aninhados por blocos
melhorado
On-the-fly 
Tabela externa Tabela interna
rating> 5
σ
Scan, write to Temp2Scan, write to Temp1
Πsid
Πsid,sname On-the-fly On-the-fly 
Custo de criação e 
armazenamento das 
relações temporárias 
projetadas = 1000 + 3 + 
500 + 125 = 1628
Custo total do plano de 
execução = 1628 + 128 = 1756 
I/Os