Otimização de Consultas em SQL
Comparação: Joins em pipeline versus
Joins materializados
AULA 26 – Parte I
Profa. Sandra de Amo
GBC053 – BCC
Exercício 1
Sejam R(A,B), S(B,C) e T(C,D).
M = 1000 = tamanho de R, N = 500 = tamanho de S, P = 800 = tamanho de T.
Os dois Joins utilizam o algoritmo NLJ-pag a pag. O 1o Join é executado
em pipeline. Descrever com detalhes a execução do 1o Join.
T
R
S
Frame 1
Frame 2
Frame 3
Frame 4
R (ext)
S (int)
RxS
T (int)
Frame 5
Output
(R x S) x T
A cada vez que a página do frame 3
se completa, a tabela T é carregada
página a página no frame 4. A junção
da página do frame 3 com todas as páginas
de T é efetuada e armazenada no Frame 5
Frame 1
Frame 2
Frame 3
Frame 4
R (ext)
S (int)
RxS
T (int)
Frame 5
Output
(R x S) x T
Tabela R é carregada 1 única vez
Tabela S é carregada M vezes, onde M = num. de páginas de R
Tabela T é carregada K vezes, onde K = num. de páginas de R x S
Frame 1
Frame 2
Frame 3
Frame 4
R (ext)
S (int)
RxS
T (int)
Frame 5
Output
(R x S) x T
Custo do plano executado em pipeline, sabendo que
M = 1000, N = 500, P = 800
= 1000 + 1000.500 + ((1000*500)/10).800 = 501.000 + 40.000.000 = 40.501.000
Tempo = 405.010 seg = 112,5 horas
Exercício 2
Não é 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 abaixo. Descrever com detalhes a execução do 1o Join e o custo
deste plano.
R
S
T
Frame 1
Frame 2
Frame 3
S (ext)
T (int)
SxT
A cada vez que a página do frame 3
se completa o conteúdo do frame 3 é gravado
em disco (materializado).
Frame 1
R (ext)
Frame 2
S x T (int)
Frame 3
R x (S x T)
Quando a relação S x T for TOTALMENTE gravada
em disco, a relação R começa a ser carregada no Frame 1,
Página a página e no Frame 2 a relação S x T é carregada
M vezes !
Resultado da junção de R com (S x T) é calculado no Frame 3.
Frame 1
Frame 2
Frame 3
S (ext)
T (int)
SxT
Custo de criar S x T = 500 + 500*800 = 400.500
Tamanho de S x T = (500*800/10) = 40.000
Custo de gravar S x T = 40.000
Subtotal = 480.500
Frame 1
R (ext)
Frame 2
S x T (int)
Frame 3
R x (S x T)
Custo de criar R x (S x T) = 1000 + 1000*40.000 = 40.001.000
Custo total do plano = 40.001.000 + 480.500 = 40.481.500
Tempo = 404.815 seg
Plano anterior em pipeline = 405.010 seg
Diferença de 195 seg = 3,25 min
Exercicio 3
Calcular o custo do seguinte plano de execução:
T
R
S
Exercicio 4
Considere o plano left deep, onde o algoritmo utilizado para o Join é o SortMerge Join (não otimizado). Este plano não pode ser executado em
pipeline ! (Por que ???) Calcule seu custo, com um buffer de 5 páginas.
Ordenação de R: 10000
Ordenação de S : 4000
Ordenação de T : 6400
T
R
S
Criação de R x S = 1000 + 500 = 1500
Tamanho de R x S = 50.000
Gravação de R x S = 50.000
Ordenação de R x S = 800.000
Custo de (R x S) x T = 50.000 + 800 =
50.800
Custo total = 50.800 + 800.000 + 50.000
+ 1500 + 20.400 = 922.700
Tempo = 9227 seg = 2,56 horas