analise de desempenho e otimizacao de consultas sql - TCC On-line

FLAVIO
ANALISE
DE DESEMPENHO
AUGUSTO
WEBER
E OTIMIZAVAO
CURITIBA
2002
DE CONSUlTAS
SQl
FLAvlO
ANALISE
DE DESEMPENHO
AUGUSTO
WEBER
E OTIMIZAt;:AO
DE CONSULTAS
SQL
Monografia apre5entada como requisito parcial
conclu~o
do
Curso
de
Tecnologia
a
em
Processamento de Dados da Universidade Tuiuti
do Parana.
Orientador:
CURITIBA
2002
Prof. Elaini
Al1gelotti
FLAvlO
ANALISE
Disserta9ao
Tecnologia
AUGUSTO
DE DESEMPENHO
aprovada
E OTIMIZACAO
como requisito
em Processamento
WEBER
parcial
DE CONSULTAS
para obten9ao
de Dados da Universidade
de grau no Curso
Tuiuti do Parana,
comissao formada pelos professores:
Orientador:
Prof. Elaini Angelotti
Faculdade
de Ciencias
Prof. Giulliana
Faculdade
Prof. Andn'ia
Faculdade
Curitiba,
Exatas e Tecnologia,
TUIUTI
Martins de Souza Vicentin
de Ciencias
Exatas e Tecnologia,
TUIUTI
Exatas e Tecnologia,
TUIUTI
de Jesus
de Ciencias
12 de novembro
de 2002
SQL
de
pela
SUMARIO
LISTA DE FIGURAS
v
LlSTA DE TABELAS
RESUMO
•.............•............................•...................................................
vi
•...................................•••••.•••...•.....•••....•........................................••.•....•. vii
1.INTRODUyAO
..•...••...•......••.••.•..•••..•••..................•....................•....•...••...•••••.........
3
1.1.0BJETIVOS
..
...4
1.2. METODOLOGIA
5
.................................
5
1.2.1. Da Pesquisa
1.2.2. Do Desenvolvimento
1.3. SUMARIO
2. REVISAO
_
....
DE LITERATURA
2.1. FUNDAMENTOS
2.1.1. Arquitetura
2.1.2. Sistema
de Gerenciamento
2.1.4. Modelo
Relacional_
2.1.5. Normaliza9i\o
2.2.2. Estruturas
Group
Having.
.
SOL..
2.3. INDICES
_.
19
..... __ .
.. _.22
24
_...
_.
By_
.. _
__ ..
..
___________ .
.
37
.
38
.
.42
__._.._.._.._..
._
44
__ ._ .. .46
2.4.1.0timizador_
_
da consulta
2.4.3. Sele9aO do indice _
36
36
.. _ ..
2.3.2. Indice Nao Setorizado
2.4.4. Sele9aO da uniao
_.34
.
2.3.1. Indice Setorizado_
2.4.2. Analise
22
_
.. __ ...
.
Externas.
17
_
_
2.2.6. UniOes
2.2.7. Uniiles
.15
............................................
Agregadas
2.2.5. Clausula
.10
.......................
Basicas
2.2.4. Clausula
.8
..
_
_
da Linguagem
6
...
.
SOL_
2.2.1. Composi9i\o
6
DE DADOS
de Banco de Dados (SGBD).
Entidade-Relacionamento.
2.2.3. Fun90es
DE BANCO
de Banco de Dados _. _.
2.1.3. Modelo
2.2. LlNGUAGEM
6
....••....•...•..........•.....••...............................................
DE SISTEMAS
de Sistemas
5
............
_
DOS CAPITULOS
..
..
.
_
.
.__ .
iii
51
. __ .52
.54
.59
2.5. OUTROS
METODOS
2.5.1. Unindo tabelas
DE APRIMORAMENTO
62
62
2.5.2. Avaliando
a condi9aO rna is restritiva
2.5.3. Utilizando
varreduras
2.5.4. Evitando
0
2.5.5. Evitando
a clausula
2.5.6. Evitando
grandes
2.5.7. Utilizando
DE DESEMPENHO
adequadarnente
63
de tabela ...
operador
........................................................
OR ..
.
64
HA VING ..
opera90es
procedirnentos
64
.
64
de classifica9ao
64
armazenados
65
2.6. SOL SERVER
66
2.6.1. Arquitetura
do SOL Server ..
2.6.2. Arquitetura
Cliente
2.7. MONITORANDO
3.0
BANCO
.
66
I Servidor
69
0 DESEMPENHO
DAS CONSUlTAS
71
DE DADOS .......................•.....••...•••....•............•..........•...••................
4.IMPLEMENTA~AO
4.1. cLAUSULA
DE CONSULTAS
83
.
4.3. cLAUSULAS
85
SARGABLE
87
4.4. IN DICE SETORIZADO..
.
4.5. iNDICE
NAo-SETORIZADO
4.6. INDICE
DE COBERTURA..
4.7. cLAUSULA
ORDER
95
4.9. ORDEM
DAS TABELAS
4.10. CONDlyAO
DO INDICE
NUMA JUN~AO
MAIS RESTRITIVA
4.12. PREDICADO
93
BY
DOS ATRIBUTOS
4.11. ANSI JOIN
97
.
..
IN X OPERADOR
GROUP
4.14. CLAUSUlA
HAVING
NUMA JUNYAO
..
.
OR
.
........................
103
106
BY ...
.
PROCEDURES
4.16. SELEyAO
DE UNIAo..
4.17. cLAUSULA
DISTINCT
.
5. CONCLUSAO
118
124
126
X cLAUSULAS
NOT EXISTS
I LEFT OUTER
.....................................•............•.•..............................................
BIBLIOGRAFICAS
111
115
.
X SUBCONSUlTA
NOT IN
109
113
4.15. STORED
4.19. cLAUSULA
100
X JOIN SOL-92
4.13. cLAUSULA
89
91
.
4.8. ORDEM
FONTES
82
SQL .•••..................................................
WHERE
4.2. SELECT'..
4.18. JOIN
75
.................................................•...........•..•....•.........
iv
JOIN .128
131
134
LlSTA DE FIGURAS
1 Os tres niveis de abstra9ao
2 Urn exernplo de diagrarna
de dados
09
E-R
17
3 Arvore B padrao para urn indice do SOL Server
.44
4 Arvore B para urn indice setorizado
.45
. .
5 Arvore B para um Indice naD setorizado
.
.
.
.47
6~~~~~~~
M
7 Pagina de indice de nlvel folha
.48
8 Diagrama
Entidade-Relacionamento
do banco de dados PUBS..
.
76
LlSTA DE TABELAS
1 Tabela de Fornecedores
. 25
...
.
2 Tabela de Pec;as ....
.25
3 Tabela de Projetos
25
4 Tabela de FornecedorPec;aProjeto
.
.
5 Tabela Autores ...
.
6 Indices da Tabela Autores .
7 Tabela TituloAutor
26
.
77
.
..
8 indices da Tabela TituloAutor
77
77
.
.
9 Tabela Titulos ..
77
....................... 78
10 Indices da Tabela Titulos
78
11 Tabela Direito_Autoral
78
12 Indices da Tabela Direito_Autoral
13 Tabela Vendas ....
78
.................................................................
.
14 Indices da Tabela Vendas ..
15 Tabela Livrarias ..
.
79
79
79
16 Indices da Tabela Livrarias
79
17 Tabela Descontos
80
18 Tabela Editoras
80
.
19 Indices da Tabela Editoras ..
20 Tabela Empregado
21 indices da Tabela Empregado
80
..................... 80
...
.
.
vi
~1
22 Tabela Cargos
81
23 Indices da Tabela Cargos
81
24 Tabela de numere de linhas de cada labela..
25 TabeJa tecnicas de otimiza~es ...
.
.
vii
81
132
RESUMO
o aprimaramenta de desempenha de cansultas SOL e de fundamental
importancia para estudantes e profissionais de informatica que tenham interesse
au trabalhem na area de banco de dadas. Ista acarre, devida a necessidade de se
abter dados cada vez mais rapido, de modo a aumentar a produtividade,
estabelecer fortes la9as de fidelidade com clientes e evitar custos desnecessarias
com hardware e/au software na tentativa de alcan,ar melhares tempos de
respostas atraves das cansultas. Neste contexto, este prajeta esta facada no
principal fatar que favorece a aumento do desempenho de cansultas SOL: a
maneira como sao canstruidas as cansultas para atingirem a tempo de resposta
desejada, satisfazenda a demanda de salicita,iles feitas par parte das usuarias
junto aa banco de dadas do sistema.
viii
1.INTRODU!;AO
o tema deste projeto de gradua~ao envolve um importante assunto da area
de Banco de Dados que ;,
(Structered
0
Desempenho e Otimiza~ao de Consultas SOL
Query Language).
Atrav;,s do estudo do desempenho e otimiza~ao de consultas SOL,
procura-se a minimizac;ao do tempo de res posta do servidor de banco de dad os.
Essa minimizac;ao influencia na produtividade de trabalho coletivo
naQ degrada
0
desempenho
operacional
a
medida que
dos sistemas.
Dessa forma, todo sistema que acessa urn banco de dados necessita obter
as respostas as consultas em urn tempo habil, pois esta em jogo naD somente a
paciencia do usuario e credibilidade perante
0
monetarios obtidos em transacyOes comerciais.
instituic;Oes financeiras
e
Par exemplo, no case de
como baneos, a lentidao do sistema pode causar a perda
de clientes e conseqOentemente
importante
mesma, mas tambem valores
que, ao se melhorar
sellS
0
respectivos investimentos. Outro fator
desempenho de consultas SOL, pode-se
evitar gastos com investimentos na troca de software e/ou hardware, se os
mesmos estiverem sendo adquiridos na tentativa de
S8
conseguir melhor
desempenho do sistema.
Ouando se estuda
0
aprimoramento de desempenho de consultas SOL,
percebe-se que, mesmo alterando ajustes de configura9Bo e/ou incluindo
hardware mais potente, as mudan9as com rela9Bo ao aplicativo freqOentemente
surtem maiores efeitos de desempenho junto as consultas. Os sistemas de banco
de dados, sejam de qualquer plataforma, podem ser extraordinariamente nipidos
e eficientes com aplicalivos bem planejados e implementados. Em contrapartida,
num sistema onde os aplicativos estejam mal planejados ou implementados de
2
forma deficiente,
0
Aplicativos
projetados
bern
enormemente
para ganhos
A chave
considerar
banco de dados tera
0
para
S8
urn desempenho
e implementados
abaixo do esperado.
de forma
eficiente
contribuem
de desempenho.
obter urn aplicativo
fator desempenho
bern projetado
nao considera-Io
apenas
de desempenho.
0 desenvolvedor
ao termino
da constrw;ao
e
e implementado
par todo a cicio de desenvolvimento
do mesma e
para entao realizar os testes
precisa considerar
0
desempenho
antes de
come,ar a escrever 0 c6digo do aplicativo.
Hardware
principalmente
e
software
a forma
apropriados,
banco
com que as consultas
SQl
de
dados
normalizado,
sao construfdas,
e
sao itens que
colaboram para a melhora do desempenho de um sistema de banco de dados.
Este projeto esta facado
desempenho
de consultas
para atingirem
no principal
SOL:
a
fator
que
favorece
0
mane ira como sao construidas
aumento
do
as consultas
tempo de resposta desejado, satisfazendo a demanda de
0
solicita,Oes feitas por parte dos usuarios junto ao banco de dados do sistema.
1.1.0BJETIVOS
o objetivo
deste
projeto
de graduac;:ao e apresentar
lecnicas
de otimizac;:ao
empregadas na constru,ao de consultas SOL com a finalidade de melhorar
desempenho
das mesmas.
Estas
consultas
serao
implementadas
de dados criado para este prop6sito e sera utilizado
Server
7.0
para
demonstrar,
atraves
de
suas
0
0
em um banco
software Microsoft SOL
ferramentas,
como
monitorar
e
realizar
os
ajustar estas consultas.
Para alcan9ar
0
objetivo
seguintes objetivos especificos:
geral descrito
acima,
faz-se
necessario
3
•
Compreender
as fundamentos
de Banco de Dados;
• Apresentar a linguagem SOL utilizada para a constru9ao de consultas;
•
Implementar
consultas
Sal que possam
servir
para
demonstrar
a
otimiza~ao das mesmas;
•
Descrever
as tecnicas
de Otimiza9~o
empregadas
para
melhorar
0
desempenho das consultas;
• Demonstrar,
gerenciador
atraves
de
ferramentas
especificas
do
software
de banco de dados utilizado, a execw;:ao e monitoramento
das consultas.
1.2. METOOOLOGIA
1.2.1. Da Pesquisa
Na primeira
incluindo
etapa
deste projeto serao reunidas
as tontes
bibliograficas,
livros, apostilas, revistas, artigos peri6dicos, publica<;Oes
avulsas e
materia is disponibilizados em sites da internet.
Na segunda etapa, atraves da leitura e estudo das diversas fontes
bibliograficas obtidas no item acima, sera abordado 0 embasamento te6rico do
projeto. Serao abordados os fundamentos de banco de dados, a linguagem SOL,
as tecnicas de otimizayao e
0
sistema gerenciador de banco de dados Microsoft
SOL Server.
1.2.2. Do Desenvolvimento
Na terceira etapa deste projeto, sera criado um banco de dados para ser
utilizado nas implementa90es de consultas SOL, as quais serao executadas e
monitoradas no sistema gerenciador de banco de dados Microsoft SOL Server
4
7.0.
0 banco de dados sera baseado
acompanha
0 SOL
Server.
Onze
Algumas
ser:ao
compostas
registros.
Para gerar os dados
no banco
tabelas
de centenas
de dados
constituirao
exemplo
esse
de registros,
que serao armazenados
"Pubs"
banco
outras
de
que
dad os.
de milhares
no banco de dados,
de
serao
uti liz ados scripts SOL.
Na quarta
parte,
Sal
consultas
aprimoramento
SQl
com
do
0
SOL
capitulo,
fundamentara.o
fundamentos
0
no
presente
Server.
ferramentas
A
ferramenta
com
os
base
em
metodos
de
de desempenho
das
anteriormente.
a melhora
de
deste trabalho
qual
sao
trabalho.
de banco de dados,
de indices,
a descri9i10
Dados)
de dados estudados
auxilio
SOL
utilizando
espedficas
mais
10, Statistics
de
execu9a.o
e
importante
0
e
Query
Time e Showplan.
DOS CAP!TULOS
o desenvolvimento
segundo
as consultas
intuitiva,
que inclui as 0P90es Statistics
1.3. SUMARIO
conceito
forma
e ultima parte, sera comprovada
monitoramento
Analyzer',
implementadas
de
de desempenho
Na quinta
consultas
serao
escritas
No terceiro
capitulo,
testes para a analise
passando
do SGBD
(SOL Server)
come9a
as
Os term os abordados
pela importante
pelos metod os de otimiza9i10
do ambiente
utilizado
de gradua9ao
apresentadas
(Sistema
efetivamente
bases
te6ricas
va.o desde
os principais
linguagem
de consultas
SOL, pelo
e finalizando
de Gerenciamento
no
que
com
de Banco
e descrito
e execu9ao
0 banco de dados que serviu como base de
das consultas
propostas
neste trabalho.
0 Query Analyzer e uma ferramenta que compOe 0 SGBD Microsoft SOL Server utilizada para
executar e analisar consultas SOL.
I
de
.
As consultas
mencionadas
quarto capitulo, juntamente
no paragrafo
anterior serao apresentadas
com dados a respeito de tempos de execw;ao
no
e suas
respectivas analises.
No quinto capitulo
e
realizada a conclusao deste trabalho de gradua,ao,
bem como os trabalho futures que podem advir do mesmo.
2. REVISAo
DE LlTERATURA
2.1. FUNDAMENTOS
Atuairnente,
grande
a utilizayi'io
importAncia
aumentando
processos
DE SISTEMAS
para
para
0
empresas,
DE DADOS
de banco
de dados
facilitando
e possibilitando
possam
otimizados.
aumento
de
ser
produtividade
tern
acesso
0
dos mesm05
envolvendo-as
contribuem
de sistemas
as
a seguranga
DE BANCO
a chance
Todas
de
de
dad as,
de que as
essas
e reduyao
sido
aDs
vantagens
custos
de
uma
empresa.
Conforme
sistema
descreve
(Date,
computadorizado
2000),
urn sistema
de armazenamento
de banco
de registros,
en tend ida como sendo urn reposit6rio para uma cole9~o
de dados
au seja,
e urn
pode
ser
de arquivos de dados
computadorizados.
e armazenar
Seu propos ito geral
e atualizar
essas
informayOes
banco de dados
podem
ou a organizaga.o
a que
para
auxiliar
quando
ser qualquer
0
sistema
no processo
geral
informagOes
solicitado.
e permitir
usuario buscar
ao
As informayoes
coisa que tenha
significado
contidas
para
0
neste
individuo
deve servir, ou seja, tudo a que seja necessaria
de tam ada
de decisOes
de neg6cios
desse
individuo ou des sa organiza<;:~o.
Dentre
as opera<;:6es
mais importantes
possiveis
de serem
realizadas
nesses
sao (Date, 2000):
•
Acrescentar
novos arquivos,
vazios,
•
Inserir novos dados em arquivos;
•
Buscar
dados de arquivos;
•
Alterar
dados em arquivos;
ao banco de
dad os;
arquivos,
os
7
•
Eliminar dados de arquivos;
•
Remover arquivos do banco de dados.
Os componentes
de urn sistema de banco de dados foram apresentados
par (Date, 2000) como: dados, usuarios, hardware e software.
Os dados
de urn banco de dados,
estao
na.o apenas
compartilhados. Por integrado, se quer dizer que
imaginado
em grande parte a redundancia
compartilhadas
mas
banco de dados pode ser
como uma unifica<;:ao de varios arquivos de dados,
dizer que por,oes
sendo eliminada
entre esses arquivos. Per compartHhado,
se quer
isoladas de dados do banco de dados podem ser
entre diferentes
usuarios pode ter acesso
dados
0
integrados,
a
usuarios,
no sentido
de que
cad a urn desses
mesma pon;:ao dos dados. Uma vez que
e integrado e compartilhado, ele sera percebido por diferentes
0
banco de
usuarios de
diversos modos.
Com rela<;:a.o80
•
Volumes
usados
hardware, os componentes do mesmo consistem em:
de armazenamento
para
guardar
as
secundario,
dados
tais como discos magneticos,
armazenados,
juntamente
com
os
dispositivos de E/S, controles de dispositivos, entre outres;
• Processador(es) e mem6ria prinCipal, utilizados para dar suporte
execuga.o do software do sistema que gerencia
0
a
banco de dados.
Com rela,M aos usuarios de um banco de dados, existem tres classes que
devem ser levadas em consideragc3.o:
• Programadores de aplical'Oes, responsaveis pela elabora,8o
de
programas aplicativos de banco de dados;
•
Usuarios finais, que interagem
esta,Oes de trabalho;
com
0
sistema a partir de terminais
ou
•
Administradores
Entre
software
Tadas
qual
0
ha uma
banco de dados fisico e os usuarios do sistema
chamada
de Sistema
as salicita,Oes
e 0 componente
2.1.1. ArquitetLira
2.1.1.1.
de banco de dados.
de Gerenciamento
de acessa
de Banco
aa banco de dadas
sao tratadas
mais importante de software de todo
de Sistemas
0
camada de
de Dadas
(SGBD).
pela SGBD,
a
sistema.
de Banco de Dadas
Niveis da Arquitetura
Hi! tres niveis
de
abstra,aa,
segundo
Silberschatz
et al.
(1999),
que
contribuem para a maior beneficia que urn banco de dados pade proporcionar
usuario. Estes tres nlveis formam uma visao abstrata dos dadas, onde
oculta determinadas
detalhes
sabre a forma de armazenamento
0
ao
sistema
e manutenc;:aa
desses dadas. A seguir, a descrjc;:~a dos mesmos:
•
Nivel
fisica:
e
mais
0
baixo
e descreve
como
os dados
estaa
armazenados;
•
Nivel
•
e
16gico:
armazenadas
a
nivel
media
e
e quais os relacionamentos
Nivel de visao:
e
0
descreve
nlvel mais alto e descreve
de dad 05 para as usuarias.
quais
dados
estao
entre eles;
apenas
parte do banco
Figura 1 - Os trIOSniveis de abstra9ao de dados
Nivel de visao
I Visao 2 I
~
I
I
Nivel
L6gico
I
Nivel
Fisico
I
I
I
FONTE: Siberschatz et al. (1999, p. 5)
Date (2000) refere-se ao nlvel lisico como sendo
16gico como
sendo
quais compOem
o nivel
enquanto
0
nivel conceitual
a arquitetura
externo
S8
de urn sistema
preocupa
que a nivel conceitual
banco dentro da comunidade
Para Date,
0
de
0
e nrvel de vis:io como
0
0
nlvel
nivel externo,
os
de banco de dados.
com as percep90es
esta
nlvel interno,
preocupado
dos usuarios
com a
individuais,
percep<;<3o geral do
usuarios.
nivel externo
e0
nlvel do usuario individual, tanto pode ser
um programadar de aplica90es ou um usuario final. 0 programador de aplica90es,
utiliza,,; uma linguagem de programa9ao convencional (PUI, C++, Java) ou uma
linguagem
proprietaria2
especifica
para
0
sistema
em
quest~o. Para
0
usuiuio
final, a linguagem a ser utilizada sera uma linguagem de consulta ou alguma
linguagem
l
de usa especial,
dirigida par
formularios ou menus.
Linguagem utilizada reslritamenle para uma determinada aplica~.
10
o nivel
conceitual, segundo Date,
e uma
representac;~o de todo
0
conteudo
de informagOesdo banco de dados, em uma forma abstrata em relag80 ao modo
como as dados sao armazenados
como as dados
conceitual
sao visualizados
fisicamente.
Tambem
par qualquer
e uma visao dos dados "como eles realmente
o nivel interno Eo
sera diferente
0
modo
usuario em particular. A visa.o
sao~
uma representagao de baixo nivel do banco de dados. Ele
consiste em muitas ocorrencias de cad a urn dos varios tipos de registros internos.
Urn registro interne representa
2.1.2.
0
registro armazenado.
Sistema de Gerenciamento de Banco de Dados (SGBD)
Silberschatz et al. (1999) define
0
SGBD como uma constituigao de
conjuntos de dados associ ados a urn conjunto de programas
dados e que permitem ao usuario
0
para acesso a esses
acesso para consultas e aiteragOes desses
dados.
Em um Sistema de Banco de Dados pode,se destacar como vantagens de
utilizagao as seguintes (Date, 2000):
•
Compartilhamento
o compartilhamento
dos Oados
nao significa apenas que as aplicagoes existentes
possam compartilhar os dados do banco de dados, mas tambem que
pod em ser desenvolvidas
mesmos
dados, ou seja,
de novas aplica90es
dados.
novas
e passivel
aplica<;:oes para
satisfazer
sem ter de acrescentar
operar
sabre
aos requisitos de
as
dad as
novos dados ao banco de
11
•
Reduy30
de Redundancia
cad a aplicar;30 tern seus pr6prios
Nos sistemas sem banco de dados,
arquivos
particulares.
Esse
fata
pode
redundancia
nos dados armazenados,
espa90
armazenamento.
de
integrados
e a redundancia
redundancia
razoes
esses
eliminada.
au tecnicas
a
uma
consideravel
corn desperdicio
Porem,
deve ser eliminada.
comerciais
levar
resultante
no
podem
ser
arquivos
Contudo,
nem
sempre
tad a
mas sim controlacta, pOis, as vezes,
plausiveis
para
manter
varias
ha
c6pias
distintas dos mesmos dados.
•
Controle
Quando
para
0
de Inconsistencias
houver duas au mais entradas
mesma fata e
0
diferentes
no banco de dados
banco de dados nao tiver conhecimento
dessa
duplica,ao, essas entradas poderao nao concordar entre si. Nessas
circunstAncias. a banco de dados estara inconsistente.
inconsiste!ncia deve-se
controlar a redundancia,
anteriormente, tornando-a conhecida para
0
SGBD,
o banco de dados nunca se tornara inconsistente
do usuario, garantindo
das entradas
processo
que qualquer
tarnbem seja aplicada
mudan9a
0
comentado
qual garantira que
sob
0
ponto de vista
realizada
automaticamente
e conhecido como propaga,ao
Para eliminar a
conforme
em qualquer
as outras. Esse
de atualiza,Oes.
• Suporte a Transagoes
As transa90es
uma ordem
sao unidades ou seqi..i€mcias de trabalho
logica, de farrna manual
realizadas
au automaticamente
em
contra urn
banco de dados (Plew e Stephens, 2000). A vantagem de uma
12
transac;ao
S8
deve
ao fata de que
operac;oes que a compOem
0
sistema
serao realizadas.
nao seja realizada, todas as Qutras sertllo
garante
Casa
que todas as
operac;ilo
alguma
desfeitas. As transac;Oes,
segundo Gunderloy e Jorden (2001) sao identificadas pelas seguintes
propriedades:
Atomicidade:
Atomicidade
conceito de que a transa<;ao
e
uma
e uma
maneira
de
S8
referir
unidade de trabalho.
uma transavilo termina, nenhum ou todo
0
ao
Quando
trabalho efetuado
durante a transavao e efetivado no banco de dados.
Consistimcia:
Quando uma transavilo
e
efetivada ou desfeita,
tudo precisa ser deixado em um estado consistente. Significa que
nenhuma das operavoes ocorridas durante a transavM pode
violar as restric;Oesau as regras do banco de dad as.
Isola~ao: Casa duas transayOes estiverem sendo executadas ao
mesmo tempo, as transaC;Oesna.o podera.o ver uma a Dutra.
Cada transavilo
encontra
Qutras
0
e
isolada da outra. Uma transavilo
nunca
banco de dados em um estado intermediario devido a
transaC;Oes.
Durabilidade:
Ap6s efetivar uma transavilo, ela permanecera
assim, au seja,
0
trabalho realizado par uma transagilo
e gravado
de forma permanente.
• Conserva9ao da Integridade
Integridade e a preocupavi'io de assegurar que os dados no banco de
dados estilo corretos. Alem de controlar a redundancia, deve-se ter 0
13
controle centralizado do banco de dados. Isso
e
feito atraves de
restric;oes de integridade (au regras do neg6cio) a serem verificadas
sempre que for executada alguma opera~ao de atualiza~ao.
o
principal objetivo de um SGBD, segundo Silberschatz et al. (1999),
proporcionar urn ambiente
e armazenamento
das
tanto conveniente
e
quanta eficiente para a recuperac;~o
informac;Oes do banco
de
dad as. Para que isla seja
possivel, um SGBD possui as seguintes fun~Oes(Date, 2000):
• Definigiio de dados
o
SGBD deve ter a capacidade de receber definigoes de dados
(esquemas extemas, conceitual e interno) em forma fonte e convertelos para a forma objeto apropriada. Para isso,
0
SGBD deve ineluir
componentes de processador de DDL (Data Definition Language Linguagem de Defini~ao de Dados) ou compilador de DDL para cada
uma das diversas linguagens de definigao de dados.
•
Manipula~ao
o
de dad os
SGBD deve ter a capacidade de lidar com solicitagOes do usuario
para buscar, atualizar au excluir dados existentes no banco de dados au
para acrescentar
novos dados
aD
banco de dad as. Ele deve incluir urn
componente processador de DML (Data Manipulation Language Linguagem de Manipulagao de Dados) ou compilador de DML para lidar
com a linguagem de manipulagao de dados. As solicitagoes de DML
podem ser planejada ou nao-planejadas:
Uma solicitagao planejada
e aquela para a qual a necessidade foi
14
que a solicita980
fisicamente
e executada, sendo 0 banco de dados ajustado
de modo a garantir urn born desempenho
da mesma.
Em contrapartida, uma solicita9ao nao-planejada e uma consulta
ad hoc, isto
e, uma solicita980 cuja
com antecedencia.
Dessa forma,
0
necessidade nao foi prevista
banco de dados pade ou nao
estar adaptado de forma ideal para a
Otimizac;ao
execw;ao da mesma.
e execuc;ao
As solicita9DeS, planejadas ou nao, devem ser processadas pelo
componente otimizador, cujo prop6sito
e determinar
um modo eficiente
de implementar a requisi980.
•
Seguranc;a
o
SGBD
e integridade
de dados
deve monilorar solicita90es de usuarios e rejeitar toda
tentativa de violar as restri90es de seguran9a e integridade do banco de
dados.
•
Recuperac;ao
e concorr€mcia
de dados
Havera algum componente de software
geralmente
chamado
processamento
recuperac;i1o
•
Dicionario
e
gerenciador
de transac;oes,
de
transa90es
que deve
ou
impor certes
ao SGBD,
monitor
de
controles
de
concorr~ncia.
de Dadas
o dicionario
de dados pode ser considerado
Ele contem dados sobre os dados, isto
do sistema.
relacionado
0
banco de dados em si.
e, defini90es
de outros objetos
15
•
Desempenho
o
e
desempenho do SGBD
obtido com base nas fun~Oes descritas
anteriormente.
A fun,ao geral do SGBD
e fornecer
a interface do usuario para
0
sistema
de banco de dadas. Essa interface pade ser definida como a fronteira do sistema,
abaixo da qual tudo
e invisivel
para
0
usuario.
2.1.3. Modelo Entidade-Relacionamento
o
(1999)
modelo Entidade-Relacionamento (E-R) e descrito por Silberschatz et al.
como tendo par base a percep~ao do munda real como urn conjunto de
objetos basicos, chamados
entidades,
e do relacionamento
entre eles.
o termo entidade, segundo Gunderloy e Jorden (2001), e um objeto ou um
conceito unico do munda
livra, ou pode ter exist~ncia
real. A entidade
conceitual.
urn evento, como par exemplo
relacionamentos
o
pode ter existe!ncia
como uma empresa.
a hera marcada
fisica, como urn
Pode, inclusive, ser
de uma consulta ao medico. Os
sao as liga90es que existem entre as entidades.
modelo E-R passui regras. Uma importante
banco de dados deve respeitar,
0
contelJdo do
e 0 Mapeamento de Cardinalidades
(Silberschatz
et aI., 1999), as quais expressam
0
regra, a qual
numero de entidades as quais a outra
entidade se relaciona por meio daquele conjunto de relacionamentos.
tipos de relacionamentos
num modelo
Ha
tres
E-R: um para urn, um para muitos (au
l11uitos para urn) e muitos para muitos.
Segundo Gunderloy e Jorden (2001), cada entidade armazenada em urn
banco de dados
necessita
ter lima
chave
primaria,
a qual consiste
em
uma
16
caracteristica
entidade
(mica ou um conjunto
de Qutras do mesmo
chave
primaria
tabela
atraves
de
ullla
simples,
A importa.ncia
entidade,
de colunas
das chaves
que
que
tipo.
serve
de uma (mica coluna,
combinayao
mecanisme
que
de caracterfsticas
0
banco
e a base
A estrutura
primiuias
5e
dais tipos
identificar
•
Retangulos,
•
Elipses,
Losangos,
a
de uma
a qual precisa
de uma tabela.
vern do fata de que eras sao
utiliza
E-R, composto
representando
primarias:
as registros
composta,
para
implementar
poder obter as informar;Oes
representando
a
todos as registros
16gica de urn banco de dados
por meio de urn diagrama
de distinguir
de chaves
todos
e a chave primaria
para identificar
de dados
para
Existem
para
unico capaz
os conjuntos
principal
da
do banco de dadas.
pode ser expressa
dos seguintes
0
a integridade
graficamente
construtores:
de entidades;
os atributos;
representando
os relacionamentos
entre
os conjuntos
de
entidades;
•
Linhas,
un indo os atributos
entidades
A seguir,
contendo
respectivos
0
de entidades
e
0
conjunto
de
aos seus relacionamentos.
um exemplo
relacionarnento
atributos:
aos conjuntos
de diagrama
entre
as
E-R. modelando
entidades
cliente
um sistema
e
conta,
bancario
com
seus
17
Figura 2 - Um exemplo de diagrama E-R.
FONTE: Silberschatz et al. (1999, p.S)
2.1.4. Modelo Relacional
Silberschatz et al. (1999) conceitua
0
modele relacional como sendo um
modele que utiliza urn conjunto de tabelas que possuem
um nome unico e que
representam tanto os dados como a rela,ao entre eles. Cada tabela possui
multiplas colunas e cad a caluna possui urn nome unico. Este modelo estabeleceuse como sendo
0
primeiro modele de dados utilizado para aplica,Oes comerciais.
As colunas de uma tabela sao chamadas
de atributos e para
cad a atributo,
ha um conjunto de valores permitidos, chamado dominic do atributo. A tabela
e
designada pelo termo rela,Bo e cada linha da tabela por tupla.
Os relacionamentos entre as tabelas (rela,Oes) em um modele relacional
sao feitos atraves de atributos que sao comuns em duas au mais tabelas.
18
o
modele relacional, conforme Date (2000), apresenta os seguintes
aspectos:
•
Aspecto
estrutural:
os dados no banco de dados
usuario
como tabelas.
sao percebidos pelo
• Aspecto de integridade: essas tabelas obedecem a certas restri90es
de integridade,
•
Aspecto
manipulativo:
os operadores
disponiveis
para que
0
usuario
possa manipular essas tabelas derivam tabelas de outras tabelas.
Desses, tres s~o muito importantes: 5ele93o, proje93o e jun93o.
A opera9iio de sele9iio (ou restri9iio) extrai linhas especificas de
uma tabela.
A opera9iio de Proje9i!0 extrai colunas especificas de uma
tabela.
A opera,ao de jun9iio une duas tabelas com base em valores
comuns
de uma coluna.
o resultado de cada uma das tres opera,Oes (sele,80,
e
outra
tabela.
Essa
propriedade
e
chamada
de
proje,ao e jun,iio)
fechamento
de
sistemas
relacionais, conforme afirma Date (2000). Pelo fato de que a saida de qualquer
opera,iio
e do mesmo tipo de objeto que a entrada (ambas sao tabelas),
de uma operat;a.o pode S8 tornar a entrada
obter uma proje,iio de uma jun,iio
exemplo. lsto significa que
e
de Dutra. Oessa forma,
e
a saida
passivel
ou uma jun,ao de duas sele,~es, por
passivel escrever expressOes aninhadas, au seja,
expressoes em que os proprios operandos sao representados par express6es
gerais, em vez de simples nomes de tabelas.
19
Essas opera90es
sao todas realizadas
sabre
urn conjunto
de Iinhas de
cad a vez, au seja, as operandos e as resultados sao tabelas completas.
Alem dessas opera<;Oes,Date (2000) ainda descreve outros operadores,
que sao:
•
Produto:
retorna uma
relaryao contendo todas as tuplas possiveis que
sao uma combina<;aode duas tuplas;
•
Uniao:
retorna uma relayao contendo todas as tuplas que pertencem
a
ambas au a cada uma de duas rela90es especificadas;
•
Intersec;ao:
pertenc8m
• Diferen~a:
pertencem
retorna
uma
relayao
contendo
as duas relayOes especificadas
todas
as
tuplas
que
ao mesma tempo:
retorna uma rela<;ao contendo todas as tuplas que
a
primeira
e
nao
a
segunda
entre
duas
relayOes
especificadas;
•
Oivisola: tom a duas rela90es
uma
unarias
e uma relayao
biniuia e retarna
relaryao contendo todas as tuplas de uma unica relayao unaria que
aparecem na rela<;aobinaria, coincidindo com todas as tuplas da outra
relaryao una ria.
2.1.5. Normaliza9ao
A normaliza<;2o e de fundamental importancia em lIlll sistema de bancos
relacional, pois permite uma maneira eficiente de se planejar
Para Plew e Stephens (2000), a normaliza9ao
e
0
banco de dados.
um processo de redu<;ao
de redllndancias nos dados de um banco de dados. Por redundancia de dados
entende-se a duplica9aOdos mesmos.
20
Para complementar a defini,ao
dada por Plew e Stephens (2000),
Gunderloye Jorden (2001) conceituam a normaliza,ao como lim processa de se
reunir todos as dados armazenados
em urn banco de dados e separa-Ios
tabelas, com a finalidade de se abter uma estrutura de armazenamento
Essa efici~ncia S8 refere
as dados
fiquem
apresentando
Um
contides
organizados
contribuindo
e as alterac;6es
eficiente.
banco de dados de modo que
sejam
faceis
de realizar,
nao
quaisquer efeitos colaterais.
banco
em
a forma como estruturar 0
em
de
uma
dados
n~o normalizado
mais tabelas
Oll
negativamente
diferentes
a segurany8,
para
pode
incluir dados
par nenhuma
eficiencia
que
est~o
razao aparente,
das
atualiza9~es e
integridade dos dados (Plew e Stephens, 2000).
Um
de dados
co nee ito fundamental envolvido no processo de normaliza9a.o de banco
e 0 das
de medir
formas norrnais. Primeiramente,
os niveis
normalizado
ou profundidade
(Plew e Stephens,
primeira forma
normal,
2000).
segunda
forma normal subseqOente
ate
os quais
um banco
e urna
maneira
de dados
foi
As tres formas normais mais comuns s~o:
forma
depende
a forma normal
normal e terceira
forma
dos pass os de normaliza9ao
normal.
Cada
executados
na
forma normal anterior.
A primeira forma normal tern como objetivo dividir os dados em unidades
16gicas: as tabelas.
Cada campo de uma tabela deve conter somente
tipo de dado. Para cada tabela, uma chave primaria
A segunda
forma
apenas parcialmente
Ja
e atribuida.
normal tern como objetivo
dependentes
urn (mico
pegar
as dados
que sao
da chave primaria e inseri-Ios em outra tabela.
a terceira forma normal objetiva remover as dados em urna tabela que
nao e dependente da chave prirnaria.
21
Gunderloy e Jorden (2001) VaG mais longe e destacam a normaliza9ao
avanc;ada,
que inclui a quarta e a quinta forma normal.
A quarta
conjuntos
normal
considera a casa em que surgem
Nesse
caso,
deve-se
dividir
a tabela
dependencias
que contem
de varios valores em duas, urna para cada relacionamento
dependencia
na primeira
forma
de entidades.
de
essa
implicito
tabela.
A quinta
forma
normal contempla
0
problema
que Dcorre
quando
uma
tabela nao pode ser decomposta em duas sem a perda de informa90es, mas pode
ser decomposta em mais de duas tabelas.
Um banco de dados normalizado possui beneficios como (Plew e
Stephens, 2000):
• Maior organiza9ao geral do banco de dados;
• Redu9aOde dados redundantes;
• Consistencia dos dados dentro do banco;
•
Projeto
mais flexivel
do banco de dad as;
• Melhor tratamento da seguran,a do banco de dados.
Dessa forma,
a organiza9~o
e
favorecida
trabalho de todos mais facil, desde
0
pela normaliz8c;:io,
tornando
usuario que acessa tabelas ate
0
0
administrador de banco de dados, responsavel pelo gerenciamento de cada
objeto no banco. A redundancia de dados
e reduzida, simplificando
as estruturas
de dados e economizando espayo em disco. Com a minimiza,ao de dados
duplicados,
Com
maior
a possibilidade
a banco
de dados
flexibilidade
para
de dados
inconsistentes
e
reduzida
normalizado
e dividido
em tabeJas
modificar
estruturas
existentes.
significamente.
menores,
A
ganha-se
22
incrementada
a
medida que
0
administrador
aces so a tabelas para determinados
de banco de dados pode conceder
usuarios.
2.2. LlNGUAGEM SOL
A linguagem SOL, Strtlctered
de consulta,
Query
e
Language,
uma linguagem padrao
at raves da qual os usuarios obtem informagOes de um banco de
dados relacional. A SOL, conforme Silberschatz et al. (1999), utiliza lima
combinayao
de construtores
em
algebra
e calculo
relacional,
os quais
sao
vastamente explorados e detalhados em (Date, 2000) e (Silberschatz et ai, 1999).
o
c;llculo relacional
e uma
linguagem nao-procedural, ou seja, 0 usuario
apenas descreve a informaga.o desejada sem precisar fornecer urn procedimento
especifico para obter tal
informagao. Desta forma,
0
a base para uma linguagem relacional de consulta.
tambem
e uma linguagem de consulta,
usuario deve dizer como
0
sistema
calculo relacional representa
Ja
e dita
LIma
deve
proceder
a algebra relacional, que
linguagem
para
procedural,
obter
0
pais
0
resultado
desejado. Assim, a algebra relacional define as opera,oes basicas usadas dentro
das linguagens relacionais de consulta.
2.2.1. Composi,20 da Linguagem SOL
Segundo Plew e Stephens (2000), a linguagem SOL
e
composta de
diversas partes:
• Linguagem de defini,ao de dados (Data Definition
Language
-
DOL):
Proporciona comandos para a defini,ao de esquemas de rela,oes,
23
exclusao de relac;Oes, criayao de indices e modifica9~o nos esquemas
de relac;Oes.Par exemplo, permite criar ou excluir uma tabela.
• linguagem
interativa de manipula980 de dados (Data Manipulation
Language - DMl): Abrange uma linguagem de consulta baseada tanto
na algebra
Compreende
relacional
tambem
quanto
no calculo
comandos para inser9~o,
relacional
de
tuplas.
exclusao e mOdificaC;ao
de tuplas no banco de dados.
• linguagem de selel'ao de dados (Data Query Language - DQl): Eo
composta
de apenas
urn comando,
0
select. Acompanhado
de muitas
oPI'0es e clausulas, e utilizado para compor consultas contra um banco
de dados relacional.
• linguagem de controle de dados (Data Control Language - Del):
Permite 0 controle do acesso a dados dentro do banco de dados. Os
comandos dessa
linguagem sao
relacionados
acesso
distribuic;ao
com
de privilegios
entre
utilizados para criar esquemas
usuario e tambem
de
para
controlar
a
usuarios.
Com and os de administrag:ao de dados:
Permitem realizar aUditorias e
realizar analises em operal'6es dentro do banco de dados. Tambem sao
utilizados para ajudar a analisar
0
desempenho do sistema.
• Comandos de contrale transacional: Permitem
transa90es de banco de dados.
0
gerenciamento de
24
2.2.2. Estruturas Basicas
Silberschaltz et at. (1999) descreve a estrutura basica de uma expressao
SOL, a qual consiste de tres clausulas: select, from e where.
A cl<3usula
select
e
usada
para
relacionar
as
atributos
desejadas
no
resultado de uma consulta.
A clausula
from assoda
as relaC;Des
que serao
pesquisadas durante a
evoluC;80 de uma expressao.
A clausula where
consiste em urn predicado envolvendo atributos da
relac;ao que aparecem na clausula
A SOL
from,
where
from.
forma urn produto cartesiano das relayDes indicadas na clausula
executa uma sele9ao em algebra relacional usando
e, entao, projeta
0
0
predicado da clausula
resultado sobre os atributos da clausula select.
Na
priltica, a SOL pode converter a expressao em uma forma equivalente, que pode
ser processada
de modo mais eficiente.
Uma consulta tipica em SOL tern 0 seguinte formato:
where
P
Cada Ai representa
urn atributo
e
cad a
fj ,
uma relac;ao e P
e uma
restric;ao.
Abaixo estao as tabelas utilizadas por Date (2000), as quais comp5em
0
banco de dados de uma empresa ficlicia chamada KnowWare. Esta empresa
passui varios projetos em diversas cidades, as quais sao abastecidos com pec;as
que sao fornecidas por diversos fornecedores. Assim sendo, existem quatro
25
tabelas,
sendo
uma de fornecedores,
contendo a rela9ao entre fornecedor,
uma de peyas,
uma de projetos e uma
peya. projeto e quantidade.
diante, este banco de dados sera referido como KnowWare
Oeste ponto em
e servira como base
para exemplificar as consultas que vir~o a seguir.
Tabela
1 - Tabela
de Fornecedores
Fornecedores
F#
F1
F2
F3
F4
F5
FONTE:
Tabela
-~
FNOME
Smith
Jones
Blake
Clark
Adams
de Pe9as
Pecas
P#
P1
P2
P3
P4
P5
P6
Tabela
PNOME
Porea
Pino
Parafuso
Parafuso
Came
Tubo
COR
Vermelho
Verde
Azul
Vermelho
Azul
Vermelho
PESO
12,0
17,0
17,0
14,0
12,0
19,0
CIDADE
Londres
Paris
Roma
Londres
Paris
Londres
Date (2000)
3 - Tabela
de Projetos
Projetos
J#
J1
J2
J3
J4
J5
J6
J7
FONTE'
CIDADE
Londres
Paris
Paris
Londres
Atenas
Date (2000)
2 - Tabela
FONTE:
STATUS
20
10
30
20
30
Date (2000)
JNOME
Classificador
Monitor
OCR
Console
RAID
EDS
Fita
CIDADE
Paris
Roma
Atenas
Atenas
Londres
Oslo
Londres
26
Tabela 4 - Tabela
de FornecedorPe9aProjeto
FornecedorPecaPro"eto
F#
P#
F1
P1
F1
P1
F2
P3
P3
F2
F2
P3
P3
F2
P3
F2
F2
P3
F2
P3
P5
F2
F3
P3
P4
F3
F4
P6
F4
P6
P2
F5
P2
F5
P5
F5
P5
F5
P6
F5
P1
F5
P3
F5
P4
F5
P5
F5
P6
F5
FONTE:
Date (2000)
-
J#
J1
J4
J1
J2
J3
J4
J5
J6
J7
J2
J1
J2
J3
J7
J2
J4
J5
J7
J2
J4
J4
J4
J4
J4
aDE
200
700
400
200
200
500
600
400
BOO
100
200
500
300
300
200
100
500
100
200
100
200
BOO
400
500
27
2.2.2.1.
Clausula
o
SELECT
resultado
consulta
ao banco
Sal e
de uma consulta
de dados
KnowWare:
uma relagao.
·'obter names
Considerando
de todos fornecedores
uma
da
relayao fornecedores~, temas:
select fnom~
from fornecedores
o
fnome,
resultado
e
uma relayao
como mostrado
consistindo
de um atributo simples
intitulado
abaixo:
FNOME
Smith
Jones
Blake
Clark
Adams
2.2.2.2.
Clausula
DISTINCT
Para for,ar
distinct
a elimina,ao
de duplicidade,
depois de select, da seguinte
select
from
o
clausula
distinct
pode-se
inserir
a palavra-chave
significar
"todos
as atributos"
forma:
fnome
fornecedores
asterisco
"." pade
ser utilizado
para
select * indica que todos as atributos de todas as relagoes
na clausula
from devem
A clausula
A
que aparecem
ser selecionados.
select tambem
pode conter
00""""ore. <."< <.« ."< <'<. 0,.""'00
expressOes
aritmeticas
envolvendo
<O~~l"""'O'"',",..
8";1\1'\
om
28
exemplo,
"selecionar
0
nome dos fornecedores
e a respectiv~
status
multiplicado
par 10%":
select
from
2.2.2.3.
fnome,
* 0.10
status
fornecedores
Clausula
FROM
A clausula
from e sempre
urn elemento
requerido
tabelas
ser acessadas
devem
Par exemplo,
"P1" fornecidas
utilizada
para qualquer
em conjunyao
consulta.
Seu
com a instruyao
prop6sito
e
select.
indicar
~
quais
pelo banco de dad os.
para obter "nomes
de fornecedores
e quantidades
de peyas
pelo mesmo"'
select
distinct
Fornecedores.fnome,
FornecedorPeyaProjeto.qde
from
Fornecedores,FornecedorPeyaProjeto
Hhere
and
2.2.2.4.
Fornecedores.f#
FornecedorPeyaProjeto.f#
FornecedorPeyaProjeto.
Clausula
nomeS de fornecedores
em Londres"
select
from
where
pH
= "Pl"
WHERE
Para "obter
trabalhem
=
a consulta
cujo status
seja maior
pode ser escrita como:
fnome
fornecedores
status
> 20 and cidade
"Londres"
que 20 e que
29
A Sal usa conectores
[6gieD not tambem
conectivQs
16gicos podem ser expressOes
A Sal tambem
valores
valor
que
possui
Esses
valores,
condicional.
respectiv~
pelo caracter
envolvendo
0 operador
Por exemplo,
status encontra-se
select
minima
de valores,
e maximo,
"encontrar
IT
where.
0 Gonector
Os operadores
operadores
de compara9ao
esta.o dentro de urn conjunto
maximo.
conjunto
16gicos and, or e not na clilUsula
pode ser representado
dos
de comparayao
between
para procurar
dado urn valor
sao incluidos
minima
como
as names dos fornecedores
e
parte
0
do
cnde
0
de uma rela980
a
na faixa que vai de 20 a 30":
fnome
from fornecedores
where
2.2.2.5.
status
Clausula
ORDER
A Sal fornece
serem apresentadas
resultado
between
select
from
30
BY
urn recurso
numa consulta.
para ordena98o
A clausula
das tuplas
order by faz com que as tuplas do
aparec;am em uma determinada
de uma consulta
ordem alfabetica
20 and
as nomes dos fornecedores~,
ordem.
Para
Klistar em
escreve-se:
fnome
fornecedores
order
by
Par padraa,
Para especificar
fnome
a clausula
a forma
order by relaciona
de ordena9aO
utiliza-se:
os itens em ordem
ascendente.
asc para ordem ascendente
e
30
desc
para
crescente
ordem
descendente.
e suas respectivas
Para
Mlistar
as nomes
das
cores em ordem descendente",
pec;:as
em
ordem
escreve-se:
s~lect pnome, cor
from
pe.:;as
by pnome
order
2.2.2.6.
CI'lUsula
Segundo
renomear
asc,
cor
AS
Plew
e Stephens
tanto rela90es
quanta
importante
as, que
de alias
e quando
Para S8 renomear
(2000).
atributos.
real da rela<;:a.o au do atributo
uniao.
desc
nao mudam
se precisa
a SOL
proporciona
Essa altera<;:8.o
no banco
unir a mesma
urna rela(f~o au urn atributo,
urn
e temporaria
de dados.
meio
e
0
para
nome
Urna utiliz89ao
rela9aO, OU seja, urna autopade-se
utilizar
a clausula
e opcional.
Utilizando
as:
a clallsula
s~lect
distinct
Fornecedores. fnome as I'}ome,
FornecedorPecal?rojeto.qde
from Fornecedores
wher~
and
F. f#
fPJ.
Omitindo
p#
as Qde
as F, FornecedorPe~aProjeto
as FPJ
fPJ. f#
"Pl"
a clausula
as, pode~se escrever
select distinct
a mesma
Fornecedores.fnome
consulta
Heme,
FornecedorPecaProjeto.qde
from Fornecedores
YJher~ F.f# :;::
FPJ.f#
acima como:
F, FornecedorPecaProjeto
Ode
FPJ
31
and FPJ.p#
"Pl"
2.2.2.7. Operador IN
o operador
in, segundo Plew e Stephens (2000), e util para se comparar
urn valor com urn conjunto
de Qutros valores.
o nome de todos os fornecedores
select
from
Como, par exemplo,
para "encontrar
que moram em Paris ou em Londres":
fnome
fornecedores
where
cidade
in
{"Londres","Paris"}
2.2.2.8. Operador LIKE
o
operador
semelhantes
like
utilizando
e
utilizado
para
comparar
urn
valor
com
valores
os caracteres:
•
Porcentagem:
•
Sublinhado:
0
0
caracter
caracter
% campara qualquer substring.
_ campara
qualquer
caracter.
Compara,oes desse tipo sao sensiveis ao tamanho das letras, isto
minusculas
nao sao iguais a maiusculas
e,
e vice-versa.
Por exemplo:
•
"Oen%" corresponde
•
"%cio%"
substring
a qualquer
corresponde
a
"cia", par exemple,
string que comeee
qualquer
"Marcia"
string
que
e "Fabricia".
com "Den",
possua
uma
32
•
"_"
corresponde
a
qualquer
string
com
exatamente
dais
caracteres.
•
"_F%"
corresponde
terceiro
caracter.
Par exemplo,
"para encontrar
a qualquer
string que tenha
as fornecedores
a letra "F" como
cujos nomes
terminam
com
o carMer "s"", escreve-se:
select
from
fnome
fornecedores
••.•
Ihere fnome like
2.2.2.9.
Operador
Plew
operador
"~s"
EXISTS
e Stephens
utllizado
(2000)
descrevem
para procurar
a presenya
0
operador
exists
como
de linhas em uma tabela
sendo
que atenda
a certos criterios.
Par exemplo,
para 'obter
onde a pe9a utilizada
SELECT
FROM
WHERE
F#,
seja da cor vemelho·:
P#,
J#,
Qde
FornecedorPecaProjeto
exists
(SELECT
FROM
todos os dados da tabela
•
Pecas
I'/HERE Pecas. P#
= Fornecedor.
AND Pecas. Cor
P#
= "Vermelho")
um
FornecedorPecaProjeto
33
Operador UNION
2.2.2.10.
Segundo Plew e Stephens (2000),
0
operador union
combinar os resultados de duas ou mais instruc;oes
e
utilizado para
select sem retornar nenhuma
tupla duplicada, nurn unico conjunto de resultados.
Para "obter cidades onde existam fornecedores au cidades onde existam
projetos, au ambos·, escreve-se:
(select
from
pe~as.cidade
pecas)
union
(select
from
projetos.cidade
projetos)
Para obter todas as repeti,Oes, utiliza-se union all. 0 numero de registros
repetidos
no resultado
sera
igual ao numero total de repetic;Oes
tanto em uma relac;:ao quanta
2.2.2.11.
que
aparecem
em Qutra rela9Bo.
Operador INTERSECT
Para Plew e Stephens (2000), a operadar intersect
duas instru,ces select,
e usado para combinar
retornando somente tuplas da primeira instru,ao select
que sao id~nticas a uma tupla na segunda instru93.o select.
Par
exemplo,
fornecedores
quanta
(select
from
"encontrar
projetos",
lodas
escreve~se:
pec;:as. cidade
pec;as)
intersect
para
as
cidades
onde
existam
tanto
34
(select
distinct
projetos.cidade
from projetos)
Para obter todas as repetiyOes, utiliza-se intersect
repetidas no resullado sera
0
all.
0 numere de tuplas
numero minima de repetj~Oes que aparecem
tanto
em uma rela9~o quanta em outra rela913o.
2.2.3. FunyOesAgregadas
FunyOes agregadas sao funyOes que agem sabre um conjunto de valores
como entrada,
retornando
urn valor simples, que podem representar
contagens,
totais au medias.
Silberschaltz et al. (1999) apresenta as cinco funyOes agregadas da SQL:
Avg: media;
•
Min: minima;
•
Max:
maximo;
•
Sum:
total;
•
Count:
contagem.
A entrada para sum e avg precisa ser urn conjunto de numeros,
enquanto
que as outras operayoes podem operar com conjuntos de tipos de dadas naonumericos. Par exemplo, utilizando a funyao AVG, pode-se ·obter a media dos
pesos das peyas da cidade Londres··
select
from
where
avg(peso)
pec;:as
cidade
"Londres"
35
Agora, utilizando a fun,ao MIN, "obter
0
menor valor dos pesos das pe,as
da cidade Londres":
select
from
min (peso)
pecas
where
cidade
"'""Londres"
A seguir, utilizando a fun,80 MAX, "obter
0
maior valor dos pesos das
pe,as da cidade Londres":
select
max (peso)
from pe<;as
where
cidade
:: "Londres"
Abaixo, utilizando a fun,ao SUM, "obter a soma dos valores dos pesos das
pe,as da cidade Londres":
select
sum (peso)
from pe<;as
where
cidade
= "Londres"
Para finalizar, utilizando a fun,ao COUNT, "obter
peyas da cidade Londres que sejam inferiores a 15,0":
select
where
and
count (peso)
pe<;as
from
cidade
peso
= "Londres"
< 15.0
0
numere de pesos das
36
2.2.4. CI,;usula
Pode-5e
aplicar
tuplas, mas tambel11
cl,;usula
By
Group
uma fUI1C;:io
agregada
nao somente
a urn grupo de conjunto de tuplas.
group by. 0 atributo
ou atributos
fornecidos
a urn conjunto
Para
de
isso, utiliza-se
no group by sao usados
a
para
formar grupos. Tuplas com as mesmos valores em todos as atributos da clausula
group by s~o colocadas
em grupo.
Exemplificando,
"obter a media dos pesos das
pec;as de cad a cidade~'
select
cidade,
avg (peso)
from .er;as
by cidade
group
2.2.5. ClilLlsula
Having
As vezes,
casa,
e interessante
emprega-se
aplicados
depois
a
definir
clausu[a having.
da formacao
condicOes
e aplica-Ias
Os predicados
dos grupos.
Por exemplo,
da
a grupos.
clausula having
select cidade,
having
avg(pesol
by cidade
de tuplas
Exemplificando,
>
avg(peso}
A funCao agregada
em
"encontrar
16.0
count
uma
0
sel.oect count{*'
from
16 libras":
pec;:as
group
numero
sao
"obter a media dos pesos
das pecas de cada cidade onde a media dos pesos seja maior que
from
Neste
forn cedores
e L1sada com
relaC;ao. Neste
numero
mLlita freqOencia
casa,
usa-5e
para
contar
a nota<;ao counW).
de tuplas da relaCao fornecedores":
0
37
2.2.6. UniOes
Segundo Soukup e Delaney (1999), e atraves da uniao de tabelas que se
obtem mais poder da linguagem SOL, a qual resulta na combinac;;a.o de atributos
de linhas correspondentes das tabelas envolvldas e retornando uma tabela virtual
contendo
as dados
Essas
resultantes
deste processo.
uniOes t~m por base as chaves
primarias e estrangeiras
das tabelas
'
envolvidas.
Par exemplo, para selecionar a quantidade total de peyas do projeto "J4",
cujos fornecedores
select
sejam
da cidade
de
WAtenas", escreV8-se:
sum(FPP.qde)
from FornecedorPecaProjeto
on FPP. J#
JOIN
FPP JOIN Projetos
PJ
PJ. J#
=
Fornecedores
F
on F.cidade
"Atenas"
=
Esta uniM utiliza a clausula ON que especifica como as tabelas se
relacionam
e define as criterios
de uniao. Para eliminar ambigUidades,
deve-S8
qualificar os atributos especificando-os na forma tabela.atributo.
A sintaxe utilizada para construir essa uniao e chamada ANSI JOIN SOL, 0
padrao atual. A seguir a construyao da mesma consulta, porem utilizando a
sintaxe "antiga", denominada ANSI SQL-92:
select
from
sum(FPP.qde)
FornecedorPecaProjeto
Fornecedores
where
FPP. Jii
and F.F#
=
FPP,
Projetos
PJ,
F
= PJ. J#
FPP.F#
and F.cidade
=
"Atenas"
, Uma chave estrangeira de uma tilbela e um atributo ou combinayao de atributos que
corresponde a chave primtula de outra tabela.
38
A ANSI JOIN utiliza a palavra-chave JOIN e separa as condi~Oes de uniao
das condi~Oes de pesquisa. Ainda segundo Soukup e Delaney, nao existe
diferem;a
de desempenho,
internamente
as operac;Oes sao as mesmas.
2.2.7. UniOes Externas
Ao
linhas das
contra rio da uni:r.o citada
respectivas
unioes externas
tabelas
preservam
acima,
onde
que correspondem
algumas
sao selecionadas
a
condiry8o
au todas as fileiras
apenas
de igualdade,
naD correspondentes.
linhas das tabelas que nao possuem uma entrada correspondente
as
as
As
sao
apresentadas com urn valor NULL'.
A seguir as quatro opera90es
•
possiveis:
Left Outer Join
Retorna todas as linhas para uma rela~ao entre TabelaA e TabelaB,
alt?m de retornar todas as linhas da TabelaA para as quais nao existe
linha correspondente na TabelaB.
Sintaxe: TabelaA LEFT OUTER JOIN TabelaB ON condi~ao
•
Right Outer Join
Retorna todas as linhas para uma rela~ao entre TabelaA e TabelaB,
alem de retornar
todas
as linhas da TabelaB
para as quais
n~o existe
linha correspondente na TabelaA
Sintaxe: TabelaA RIGHT OUTER JOIN TabelaB ON condi~ao
~ Urn valor NULL e urn valor ausente ou um atributo em uma linha de dados ao qual nao roi
atribuido nenhum valor.
39
•
Full Outer Join
Retorna
todas as linhas para uma relayao entre TabelaA
alem de retornar todas as linhas da TabelaB
linha
correspondente
na TabelaA,
bern
e TabelaB,
para as quais nao existe
como
todas
as
linhas
da
TabelaA para as quais nao existe linha correspondente na TabelaB.
Sintaxe: TabelaA FULL OUTER JOIN TabelaB ON condi9ao
•
Cross Join
Retorna todas as linhas para uma rela980 entre TabelaA combinadas
com tadas linhas da TabelaB. Nao existe nenhuma clausula ON para
especificar
urn relacionamento
entre as tabelas.
Essa operayao
retorna
urn produto cartesiano das duas tabelas.
Sintaxe: TabelaA CROSS JOIN TabelaB
2.2.6. Altera90es no Banco de Dados
A DML (Data
Manipulation
Language),
comentada na se,80
2.2.1,
possibilita a altera980 de dados em urn banco de dados relacional. Atraves da
DML, pode-se preencher rela90es com novos dados, atualizar os dados
existentes nas rela90es e excluir dados de rela90es Silberschaltz et al. (1999).
Remove-se
somente tuplas inteiras.
Nao e
permitido excluir valores de urn
atributo em particular. Em SOL, a rem09ao e expressa por:
40
d,,::l~te from
where
P
primeiro
representa urn predicado e r, uma rela~o.
todas as tuplas t em r para as quais P(t)
de r. Par exemplo:
delete
"remover
Para
todes as registros
de
€I:
0 comando
verdadeira
delete
e entao
encontra
remove-as
pet;as da cidade Londres":
from pe~a5
'rJhere cidade
2.2.6.2.
r
P
= "Londres"
remot;aO de todas as tuplas de r, omite-se a clausula
where.
inser,ao
Para inserir
dados
em uma relac;:c31o,pode-se
inserida ou escrever uma consulta cujo resultado
inserir. Neste tipo de opera'ta.o
Para inserir
a informa,ao
utiliza-s8
insert into fornecedor
51 ("~'6",
comando
"fornecedor
20 e que atua na cidade de Landres",
valu
0
"Joe"
especificar
e
uma tupla
a ser
urn conjunto' de tuplas a
insert.
Joe que possui
0
c6digo
F6 ,status
escreve-se:
(f#,
f
fnome, status, cidade)
"LondresO)
20,
Para incluir dados da tabela fornecedor.
onde a status seja 10, em uma
tabela ternporiuia:
insert into fornecedor_temp
select
f#,
from fornecedores
•••.
lhere
(f#, fnom , status, cidade)
fnome, status,
status = 10
cidade
41
2.2.6.3. Atualizal'oes
Para modificar
valores
dos atributos.
utiliza-se
0
comando
update.
Para "alterar as cidades dos fornecedores de Londres para Brasil~,
escreve-se:
update
set
fornecedores
cidade
= "Brasil"
·...•.
here cidade
=
"Londres"
42
2.3. iNDICES
Os indices podem ser utilizados para aprimorar
Sal realizadas
consultas
Pode·se
comparar
urn livra. Para localizar
indice,
urn indice de urn banco
as paginas
consulta·se
0
consulta·se
urn ou mais
mesma
numeros
rnaneira,
no sentido
dos dados
0
numeros
Enlretanto,
pade ser mais rapido verificar
de um lado a outro entre
banco de dados
0
indices,
no final de
topica,
alfabeticamente
especificos,
e apontada
caso
as paginas
e, em seguida,
sumarios,
capitulos
da tabela",
ou seja,
para a localiza9~o
0
bern
au nem
da mesma
fisica exata
de poucas
para obter as informa9~es
indice e as paginas
primeiro
nurn processo
livre seja pequeno,
0
paginas,
ao inves de ir
do livro. Nessa situac;~o, quando
nao faz uso do indice, diz~se que
completa
ao indice
0 indice de urn banco de dados funciona
de que a consulta
na labela.
·varredura
as t6picos
de paginas
livre n~o possuisse
de paginas.
de dados
em urn livra sabre determinado
que lista todos
mais fi3.pido do que se
desempenho das
0
dentro do banco de dados.
mesmo
0
banco
de dados
que folhear
realiza
um livro pagina
0
uma
por
pagina.
Quando
inteiramente
velocidade
a varredura
a tabela
do servidor
tabelas
com a utilizayao
indices
s~o utilizados,
de suas
dados.
caracteristicas
da tabela
provoca
de banco
reduc;~o
de dados,
de indices.
0
nao e util, ou seja, a operac;ao
uma
obter
lorna-se
Para isso,
comportamento
para
do desempenho
imprescindivel
e necessario
de cad a um deles
a maxima
proficiemcia
de percorrer
e a queda
de
organizar
as
saber quais tipos de
e como tirar proveito
na recupera9ao
dos
43
Segundo Gunderloy e Jorden (2001),
paginas5
0
SOL Server armazena os dados em
de 8 KB dentro de arquivos de banco de dados.
Essas paginas e as
dados contidos nelas nao estao organizados, necessitando, portanto, de urn
indice. Ap6s a cria9aOdo indice, haver;. paginas de Indices e paginas de dados.
cont~m as informa-;Oes que os usuarios inserem
As paginas
de dados
tabelas, e as
paginas de indices sao usadas para armazenar
os valores de uma coJuna indexada
aponta para onde esta
0
nas
uma lista com todos
(va/ores-chave) junto com urn ponteiro que
registro que contem
0
valor da tabela indexada. Por
exemplo, para urn indice da coluna sobrenome de uma tabela, urn valor-chave
poderia ser Silva 540819,
indicando que 0 primeiro registro com 0 valor Silva no
campo sobrenome estaria na extensao' 54, pagina 8, registro 19.
Soukup e Delaney (1999) citam os dois tipos de indices suportados pelo
Sal Server: indices setorizados
(au agrupados)
e indices nao setorizados
nao agrupados). Os dois tipos de indices tern como base a arvore B (ba/anced)
que significa arvare balanceada.
pesquisando
em urn valor-chave
A arvare
B propicia rapido acesso
a velocidade
de recuperac;ao coerente,
-
aos dados
do indice e, pelo fata de ser balanceada,
qualquer registro exiginda aproximadamente
(au
localiza
a mesma volume de recursas, sendo
pois
0
indice tem
0
mesma
alcance
por
toda a parte.
A arvore utilizada para representar
os indices
e compasta
de uma raiz (a
partir da qual a navegac;ao come<;:a), dos possiveis indices intermediarios
e das
paginas folha de nivel inferior. Os indices sao utilizados para localizar a pagina
folha c~rreta. 0 numero de niveis em um indice variara dependendo
de linhas existentes
na tabela e do tamanho
da coluna (ou colunas)
do numero
chave do
SA paginai e a menor unidade de armazenamento em um arquivo de dados SQl Server .
• Uma extensoo e um conjunto de oito paginas conUguas de um arquivo de dados.
44
indice. Ern urna recupera~ao ou exclusao qualificada, a pagina folha correta sera
a pagina inferior da arvore em que urna ou mais linhas com a chave (au chaves)
especificada
reside.
Em qualquer
indice,
contem cada valor-chave,
0 nivel folha
na seqO~nciade chave.
Figura 3 - Arvore B padrao para urn indice do Sal Server.
o
Niwtiooa3
(Raiz)
D-O-O
D-O--Q-O-O
N;velrrldi~2
(Nfve!
Int*,mediario)
NiVelindice1
(Nlvet
Intermedijrio)
Ni'vetindice0
(NiveIFotha)
Fonte: Soukup e Delaney (1999, p.245)
2.3.1. indice Setorizado
No indice setorizado,
apenas
em si
as chaves
s~o recuperados
o
0
de indice. Ao
e
nlvel folha contern as paginas de dados e nao
S8
percorrer
0
indice
ate
0
nivel tolha,
as dados
nao apenas apontados.
indice setorizado rnantern os dados ern urna tabela fisicarnente
ordenados
rela9~oa chav8, dessa torma, urna tabela pade canter apenas
em
indice setorizado,
pois as dados podem ser orientados
fisicamente
apenas
urn
de
urna maneira.
o
indice
setorizado
dados
sejam
localizados
rapido
para
as consultas
e
favorecido
diretarnente
delimitadas
pelo otimizador,
no nivel folha,
porque
permite
proporcionando
por urn intervalo
de valores,
que as
urn acesso
ou seja,
0
45
otimizador
detecta
que apenas
urn intervalo determinado
de
paginas de dados
deve ser percorrido.
Uma
maneira
de entender
como
os indices
setorizados
funcionam
e
atraves de um exemplo pratico dado por Gunderloy e Jorden (2001). A
organiza9ao
de urn indice setorizado pode ser comparada
a estrutura
urn diciomirio, au seja. para procurar uma palavra no dicionario,
palavra satelite, deve-se ir direto
utilizada em
par exemplo,
a seyao S do dieioniorio e continuar
a
praeurando
na lista de palavras organizadas em ordem alfabetica ate eneontrar a palavra
sate lite. No case de urn indice setorizado em uma cotuna de sabre nome colocaria
Adams
fisieamente antes de Bums.
Figura 4 - Arvore B para urn indice setorizado.
N6
Inlormroi,irio
N6
Folha
Fonte: Gunderloy e Jorden (2001, p. 344)
46
2.3.2. Indice Nao Setorizado
Ao contrario
naD passui
do indice setorizado,
dados
de verdade,
estao armazenados
indice
nas paginas
nao-setorizado
registro apontado
o indice
exige
0
nivel folha de urn indice na.o-setorizado
isto e, ele contern
ponteiros
de dados. A pesquisa
que a indice
naD reorganiza
paginas
de dados
apenas
urn indice nao-setorizado
sao organizadas,
Para entender
melhor
com urn indice remissivo
indice
localizar
preciso
remissivo,
trigger
fisicamente
urn
que a
0
Indice nao setorizado,
de urn livra (Gunderloy
° qual
no lndice
possui
uma
remissivo
desejada.
constantemente
as dadas,
portanto,
a
naD afeta 0 modo como as
e, par conseguinte,
nao S9 esta restrito
uma se~o
ir ate
a ter
0
procurar
a consultar
0
fazer uma analogia
2001).
a
numero
do livro e consultar
letra
T. Localizando
indicado
da pagina
por um intervalo
lndice
dados, ja que a maioria dos dados estara armazenada
Para encontrar
da letra T no livro como
°final
sec;ao dedicada
e 56 ir ate
Para
pode-se
e Jorden,
em vez disso e necessario
a informa,ao
voltar
usanda
e, em seguida,
par tabela.
em urn livra, nao havera
existe no diciomlrio,
palavra
que
seja recuperado.
na.o-setorizado
trigger
de dados
seja percorrido
presen98 ou aus~ncia de urn indice nao-setorizado
a palavra
para as dados
remissivo
em paginas
localizar
diferentes.
a
para
de valores,
para
°
e
os
47
Figura 5 - Arvore B para urn indice n30 setorizado.
N'
R.iz
NO
Interm!idiiriO
N'
Folha
Fonte: Gunderloy
Supondo
e Jorden
a
(2001, p. 348)
exist~ncia de uma tabela que possui indice setorizado
c6digo postal e indice nao-setorizado
Ulll
n6 de indice seria (Soukup
para sobrenome,
e Delaney,
1999):
Figura 6 - Pagina de n6 de indice.
Chave
Numero
,
Jackson
da Pagina
1:147
Jensen
1:210
Johnson
1:200
Jones
1:186
Juniper
1:202
Fonte: Soukup
e Delaney
(1999, p.771)
para
a visuaJizac;ao abstrata de
48
Neste exemplo,
a entrada
200), a qual esta no pr6ximo
Johnson
indica a pagina
1 :200 (arquivo
1, pagina
nivel do indice.
Uma pagina de indice nivel folha seria semelhante
a esta:
Figura 7 - Pagina de indice de nivel lolha.
Chave
Localizador
Johnson
98004(1)
Johnson
06801(3)
Johnson
70118
Johnstone
33801(2)
Johnstone
95014(2)
60013
Johnstone
Jonas
80863(2)
Jonas
32027
Jonasson
Fonte: Soukup
o
e Delaney
numero
22033(4)
(1999,
entre parenteses
p.771)
ap6s
0
c6digo postal
e aparece quando existem c6digos posta is duplicados.
e 0 identilicador
exclusivo
49
2.4. OTIMIZACAO
o
DE CONSULTAS
processamento
de consultas,
segundo
Silberschatz
et al. (1999),
e uma
atividade que permite extrair dados de um banco de dados. Esta atividade inclui a
traduyao de consultas expressas
em express6es
arquivos,
otimizayOes,
o custo
disco, que
tradu90es
e lento
se ela for complexa.
de tempo
entre uma estrategia
consulta,
de
ao
ha diversas
principalmente
boa e uma ruim, em termos
e freqOentemente
ConseqQentemente,
na seleyao
pelo acesso
Geralmente,
LIma determinada
de disco exigidos.
ser de grande magnitude.
significativa
a mem6ria.
acesso
aD
processar
A diferenya
aceSSDS
e determinado
de uma consulta
se comparado
para
no nivel fisico do sistema
e avaliayclo das consultas.
do processamento
estratE~gias possiveis
do numero de
em linguagens de alto nivel do banco de dados
pod em ser implementadas
que
significativa,
e pade
vale a pena gastar uma quantia
de uma estrategia
boa para
processar
urna
consulta.
Conforme
desafio,
Date
(2000),
a otimizaC;:80 representa
vista que, a otimizac;:ao e uma
quando se espera
ja
oportunidade,
relacionais
um desempenho
que
estarern
a otimizaC;:80
ao mesmo
tempo
exig~ncia para as sistemas relacionais
que atinja nfveis pre-determinadas,
e
urn
favorecida
em um nlvel semiintico
e uma
pel a fata das expressOes
adequado
para que seja passivel
essa otimizac;:ao.
Soukup
consultas
irnpartancia
e Delaney
para
melhorar
de saber
(1999)
0
como
recornendam
desernpenho
0 otimizador
forrnular uma cansulta au para entender
0
do
monitoramento
banco
de
de consultas
quais indices
dados
e otimizayao
e enfatizam
funciona
para
de
a
melhor
pod em ser criados. Deve-
50
se escrever as consultas da maneira rnais intuitiva e tentar
seu desempenho
na.o parecer suficientemente
Ah~m disso,
e
preciso
otimiza-Ias apenas
S8
born.
partir do presslIposto
da
existlmcia de uma boa
estrutura de banco de dados, adequadamente normalizada e que ja conte com
indices ute is.
Como
dito anteriormente,
funciona ajuda a entender
ter ideia
de como
0
de consultas
as diretrizes sabre quais indices devem
bern como as pianos de consulta a serem examinados
cada tabela envolvida
otimizador
na consulta
Sal,
0
no Query
otimizador
ser criados,
Analyzer.
Para
avalia as argumentos
de
pesquisa e considera quais indices estao disponiveis para estreitar a varredura de
uma labela, ou seja,
0
otimizador avalia ate que ponto
0
indice pode exciuir linhas
de uma seleyao. Quanta mais linhas puderem ser exc!uidas, melhor, porque deixa
menor numero de linhas para serem processadas.
Segundo Soukup e Delaney (1999), para cada combinayao de estrategia
de uni:io, ordem de uni:io de tabelas e indices,
estimativa
recursos
0
otimizador de consultas faz uma
de custo, levando em considera<;80 0 numero de leituras logicas e os
de mem6ria
otimizador com para
0
que
s:io exigidos
e estao
custo de cada plano e escolhe
disponiveis.
0
A partir dai,
que tiver a estimativa
0
mais
baixa. A otimizayao de consultas SOL acontece em tr~s lases principais: analise
da consulta, seleyM do indice e seleyao da uniao.
51
2.4.1. Otimizador
Date (2000)
isto
e,
usa
0
possibilidade
mel her que
enfatiza
de
um
a vantagem
"otimizador-,
do otimizador
usuario.
0
da utilizac;ao
Ele
destaca
desempenhar
Essa conclusao
da otimizac;ao
fata
0
a tarefa
se deve
de
haver
de otimizar
a varios
automatica,
fatores,
urna
real
urna
consulta
entre
eles,
as
seguintes:
•
urn born otimizador
seja, informac;Oes
de
tera urna grande
estatisticas.
Em conseqUencia,
tazer
urna
avaliayao
mais
estrategia
dada
para
implementac;ao
maior probabilidade
•
quantidade
casa
uma
de escolher
as estatisticas
precisa
da
0
de dados
otimizador
eficiencia
qualquer
e, assim,
torna-S8
reprocessar
Ja
no caso
reescrever
•
mudem
com
0
tempo,
entao
considerar
a
de
um sistema
e um
programa,
0 otimizador,
centenas
otimizador
conhecimentos
se tome
necessaria.
requisiC;ao relacional
Num
sistema
original
nao
relacional,
pelo otimizador
relacional,
urna
basta
do sistema.
a reotimizac;:ao
en valve
a programa;
a otimizador
humano.
•
a
ter
mais eficiente;
urna mudanc;a na estrategia seja interessante. Dessa forma,
reotimizac;ao
ou
e capaz
de
de consulta
a implementac;ao
do banco
de informat;:Oes,
logo
ao
de estrategias
pode
ser
e muito
contrario
um modo eficiente
um
como
a
que um usuario
e
capaz
incorpora<;30
programadores,
de conhecimentos
e econ6mico.
usuario,
de
de implementac;:ao;
considerado
e servi<;os "dos melhores"
um disponibilizador
mais paciente
de
fazendo
e servi<;os a todos,
dos
com
de
52
Date (2000),
na verdade,
ainda ressalta
que a possibilidade
urn trunfo dos sistemas
relacionais
de se utilizar a otimizador
e,
em relaC;ao aDs sistemas
de
arquivos convencionais7.
Entretanto,
nem sempre
0
otimizador
realiza com exito seu prop6sito, par
t6picos relevantes
isso, a seguir, serao descritos alguns
aD aprimoramento
do
desempenho de consultas SQl, os quais devem ser implementados pelo usuario
do banco de dados e nao pelo otimizador.
2.4.2. Analise
da consulta
Nesta primeira
fase da otimizayao,
conforme
Soukup
otimizador exam ina cad a cJausula da consulta e determina
limitayao
do volume
util como argumento
Uma
clausula
e Delaney
S8
de dados que devam
ser percorridos,
de pesquisa
au como parte dos criterios
utilizada
(SARG)
como
argumento
de
(1999),
au seja, se a clausula
pesquisa
a
ela pode ser util na
e
e
de uniao.
referida
sargable ou otimizavel e pode fazer usa de urn indiee para recuperac;ao
como
mais
rapida. Uma SARG limita uma pesquisa porque especifica uma correspondemcia
exata, urn intervalo
pela funyao
variavel que
de val ores au uma conjunc;ao
16gica AND.
e
Um SARG
contem
de dais ou mais itens unidos
uma expressao
constante
(au uma
transformada em uma constante) que atua sobre uma coluna
utilizando um operador.
o
nome da coluna pode aparecer em um unico lade do operador e a
constante ou variavel pode aparecer no outro lado. Caso uma coluna apare9a nos
7 Sistema de arquivos convencionais e um sistema informatizado caroacterizado par varios arquivos
onde os dados sao armazenados e ace·ssados diretamente por usuarios individuais.
53
dois lados do operador, a clausula nao
destacam-se: ~, >, <, ~>, <~, BETWEEN
o
exemplo,
impede
operador LIKE
e sargable
e sargable.
dependendo do tipo de caracter utilizado. Par
"Jo%" e sargable, mas LIKE
LIKE
Entre os operadores sargable,
e, as vezes, LIKE.
"%Jo"
nao e,
pois
0
caracter
no inicio
a utilizac;ao de urn indice.
Exemplos de cl"usulas sargable:
= "Joao"
nome
> 2000
salario
nome
= "Joao"
nome
LIKE
Varias
condif):6es podem
AND
> 2000
sal.3.rio
"Car%"
ser incluidas
em uma (mica SARG,
bastando
que
sejam usadas com a func;ao 169ica AND. Neste caso, um indice poderia operar
em todas as condic;6es que passassem pela func;ao 169ica AND. Par exemplo,
pode-s8
ter urn
clausula
nome
indice composto
= "Joao" AND
pelas colunas nome,
salario
> 2000
inteira
salario,
pode
de modo que a
ser considerada
urn
unico SARG e ainda ser avaliada para linhas qualificadas usando apenas um
indice.
Quando se escreve a cl"usula nome ~ "Joao" OR salano > 2000, a func;ao
169ica OR nao permite que uma unica varredura de indice qualifique os dois
termos,
pais
localizar
S8 0
apenas
e util, pois
principal
campo
as entradas
na chave de indice
e nome,
0
indice
e util para
"Joao". Tendo como criteria OR salario, a indice nao
tad as as lin has precisam
ser examinadas
e nao apenas
as entradas
"Joao".
Uma expressao que nao
urn indice nao
e utiI para
e sargable
essas expressoes.
nao pode limitar a pesquisa, portanto,
As expressoes
nao-sargable
incluern
54
os operadores
de nega9ao,
como NOT, !=, <>, !>, !<, NOT EXISTS,
NOT IN, NOT
LIKE.
Exemplos
de clausulas
salario
NOT
!> 2000
nome
like
nome
=
"\Jo"
"Joa.o"
No ultimo
cad a
> 2000)
(salaric
individualmente
nao-sargable:
"Jo~o"
<>
nome
OR
exemplo,
sargable.
> 2000
salario
cada
expressao
Entretanto,
em um dos
um Indice separado
lados
da fun9aO
e
OR
ainda pode ser uti I para
expressao.
2.4.3. Sele9aO do Indice
Na segunda
determina
fase da otimiza9ao
se existe
de consultas,
um Indice para cada cli~usula
indice determinando a seletividade da clausula e estima
lin has qualificadas.
e
Um Indice
usada no argumento
otimizador
0
sargable,
potencialmente
0
avalia
do
custo para encontrar as
uti I se sua
de pesquisa e esse argumento
de consultas
a utilidade
primeira
estabelece
coluna
e
urn limite inferior,
urn limite superior au ambos, para limitar a pesquisa. Ainda, se urn indice contiver
cada caluna referenciada em uma consulta, mesma que nenhuma dessas colunas
seja a primeira do [ndice, este indice
A cria9aO
obten9aO
de
substancialmente
e utiliza9aO
um
bom
e considerado
de Indices
desempenho,
a recupera9ao
uti I.
sao tarefas
muito
pois
os
Indices
e a sele9aO
de
dad os.
importantes
A
para
podem
acelerar
seguir
algumas
a
55
estrategias
de criac;ao e utilizaC;ao de indices
sugeridos
par Soukup
e Delaney
(1999).
2.4.3.1.
Escolha
cuidadosa
do indice setorizado
Os indices
setorizados
e para consultas
nas quais
chave de
setorizac;a.o.
por tabela, deve-se
2.4.3.2.
Tornar
Para
0
sao extremamente
as dados
uteis para consultas
devem
estar
Par 56 haver a possibilidade
escolM-lo
com extremo
indice na.o-setorizado
urn Indice
na.o-setorizado
porcentagem
que en valve
pagina de dados que
processo
indexa980
de
representa
um enorme
exempla,
uma
"Stankowski",
um indice
pravavelmente
mais
=
sexo
elirninaria
cerca
\IN"',
de
que
utilizadas
passui
ou
de arvores
seja,
seja
deve
do indice
B para
a pagina,
clausula
ser
nao-
determinar
a
repetir novamente
0
WHERE
a indice
de sobrenome
sera
B apenas
e seletiva.
Par
utH, eliminando,
Ja para a clausula
provavelrnente
para considera~ao.
geralmente
sobrenome
WHERE
para cansidera~aa.
urn indice nao-setorizado
das linhas
clausula
do quanta
a
nao-setarizado
da arvore
registros.
de recupera980
na
mas depende
de 90% das linllas
da rnetade
para ler as entradas
opera90es
ganha,
de entradas
que a mesma
de dados sejam lidas repetidamente.
colunas
cansulta
necessaria
de
e entaa recuperar
ate que varias paginas
A
rvHERE
a leitura
e apontada
de existir urn indice setorizado
e
ser Util,
ao mecanismo
setorizado,
a
alta mente seletivo
altamente
Isto se deve
de intervalo
para satisfazer
cuidado.
capaz de eliminar urna grande
seletivo.
orden ados
nao seria util, pais
Os passos
para ler as dados
exigern
E/S do que simples mente fazer uma ':mica varredura
repetidos
bern rna is
atraves
de
56
todos
as dadas.
colunas
Por conseguinte,
que nao possuem
ea
A seletividade
as indices
nao-setorizados
uma ampla dispersao
porcentagem
10101 _
for baixa,
indice
0
tem pouca
uma
Quando
e
registros,
2.4.3.3.
Atraves
0
contrinio,
0
urn outro indice
de uma varredura,
desquallfica
e uti I quando
percorrer
as paginas
grande
de dados,
a
de mais de 5%,
sera escolhido
as paginas
uma
total de registros
se for alta, a indice
nao-setorizado
indice tiver uma seletividade
au ainda
nao
e
au a
sao lidas apenas
porcentagem
tendo
cada
de
pagina
de cobertura
interessante
em consultas.
as valores
0
rcgi.l"(roJ
qualificados
e util, case
1999):
)
uma vez.
Obter indices
E.
indice
mais vantajoso
exatamente
Cas a
nao sera utilizado,
sera percorrida.
vez.
de _
de registros
seletivo
qlllllificadOJ
e nao e uti I. Um indice
5% au menos.
provavelmente
tabela
e altamente
seletividade
e de
relayao
entre a numero
por (Soukup e Delaney,
(regis/ro.f
Seletl. v~dade '"
Se a relayao
de valores.
expressa
. .
nao sao uteis em
procurar
obter
situa90es
possiveis
de cobertura
Para que um indice pass a cobrir a consulta,
de dados
necessarios
como
parte da chave
de indice
ele deve possuir
de indice.
todos
Par exemplo,
tendo a consulta
SELECT emp_nome, emp_sexo from
~vHEREemp_nome LIKE
e passu indo um indice
a coluna emp_sexo
empregado
"Sm'"
nao~setorizado
na chave de indice.
em emp_nome,
Nesse caso,
pode fazer sentido
0
anexar
indice ainda sera uti I para
57
a seleya.o, mas ainda tera
0
valor para emp_sexo.
a pagina de dados da linha para obter
Todo indice
da coluna
de chave do indice.
no nome, ele abrangera
0
indice de cobertura
Par exemplo,
as seguintes
possuindo
no case do interesse
urn indice
na.o-setorizado
•
Selecionar
todos as names que comer.;:am com a letra F;
•
Determinar
se a tabela contem
nao-setorizado
abranger
inclui a chave
todas as consultas
chave na.o-setorizada.
0
nome Denise.
e a tabela
possuir
seguir podem
ser satisfeitas
•
Selecianar
•
Determinar
sabrename
•
urn indice
Encontrar
0
urn indice
Ou seja,
setorizado,
todo
ele tambem
pode
do valor de chave setorizada,
se houver
setorizado
urn indice na.o-setorizado
no sobrenome,
acessando-se
sabrename
a
possua
de setoriza9ao.
que precisam
Par exemplo,
ler
consultas:
Allam do mais, case a tabela tambem
indice
na.o precisara
valor de emp_sexo.
e urn
na.o-setorizado
0 otimizactor
apenas
todas
as peginas
alem da
no nome
as consultas
folha da arvore
a
B:
de Olivia;
exist~ncia
de
quaisquer
cambinaC;Oes
de
nome
e
duplicadas;
0
prename
mais
comum
para
ainda
destacam
pes so as
com
sabrename
Santos.
Soukup
setorizado
pade
ou setorizado)
combinar.
destinadas
e Delaney
Portanto,
a retornar
(1999)
oferece
esse
exatamente
a maior
indice
que um indice
seletividade,
sera
uma linha.
mais
isto
uti I para
exclusivo
e, apenas
consultas
(nao-
uma linha
que
sao
58
2.4.3.4.
Aten9iio
na ordem de colunas
Urn indice
consulta
indice.
pode
ser
correspondem
Par exemplo,
(sobrenome+nome),
WHERE
util para
as colunas
tendo-s8
uma
que
consulta
estao
urn indiee
apenas
mais
S8
os criterios
a esquerda
na chave
de
composta
de
esquerda
na
que passui
uma
esse Indice sera uti I para uma consulta
sobrenome
=
sobrenome
=
chave
da
como:
"Silva"
ou
WHERE
Entretanto,
WHERE nome
Outra
chave
de
emp_sexo
AND
e
colocar
as colunas
nao-setorizados.
Par
como:
S8
emp_nome
0 indice
= "Joao"
nome
como:
"Joao"
e uti! para uma clausula
WHERE
Mas
=
dica importante
indices
"Silva"
naD sera uti I para uma consulta
esse indice
=
"Silva"
for definido
para a maior parte das recuperat;:oes.
podera excluir linhas suficientes
exemplo,
AND
como
urn
emp_sexo
emp_sexo,
A chave
para tomar
0
a
mais seletivas
mais
in dice
=
em
"Mil
emp_nome,
a esquerda,
indice util.
emp_nome,
nao sera
emp_sexo,
util
nao
59
2.4.4. Sele,ao
Nesta
da uniao
terceira
varias tabelas au
fase da otimiza9aO
e uma aulo-uniao,
uniao e seleciona
a estrategia
0
de consultas,
S8 a consulta
envolve
olimizador de consultas avalia a sele<;ao de
de uniao com a menor custo. 0 otimizador
pode
utilizar tres estrategias para processar unioes: unices de loop aninhado, unioes de
mesclagem e uniOes par hash
2.4.4.1.
Uniao de loop aninhado
Normalmente,
extraem
uma
as uniOes sao processadas
como urn conjunto de loops que
linha da primeira tabela e usam essa linha para percorrer a tabela
mais interna e assim par diante, ate que a resultado que satisfaga seja utilizado
para percorrer
loops
e
igual
resultados
a ultima tabela.
aD
a medida
e estreitado
de cada itera,iio
0 numero
numero de varreduras
de itera,Oes
que ele avan,a
de qualquer
Per exemplo,
de uma tabela
para outra dentro
para
utilizar a estrategia
de uniao de loop aninhado
FROM
\;HERE
AND
F.Fnome,
F.Status
Fornecedores
F. F#
=
FPC.Qde
OPTION
(LOOP
FPC.
> 200
JOIN)
F, FornecedorPecaProjeto
F#
na
(e respectivo status) que utilizam mais
200 pe,as em seus projetos":
SELECT
um dos
realizadas. 0 conjunto de
no loop.
consulta "obter os nomes dos fornecedores
de
atraves
que devem ser
FPC
60
2.4.4.2. Uniao de mesclagem
Pode-s8 utilizar uma uni:io de mesclagem quando nas duas tabelas a
serem unidas existirem indices setorizados
Geralmente,
otimizador
0
escolhe
na coluna de uni:io.
a estrategia
de uniao de mesclagem
quando as duas entradas de uniao (as tabelas a serem unidas) jil estao
classificadas
na cotuna uni:io.
Par exemple,
para utilizar a estrategia
de mesclagem
na consulta ~obter as
nomes dos fornecedores (e respectivo status) que utilizam entre 100 e 300 pe9as
em seus projetos";
SELECT
F.Fnome,
FROM
Fornecedores
NHERE F.F#
F.Status
F, FornecedorPecaProjeto
FPC
~ FPC.F#
AND FPC. Qde BET;lEEN 100 AND 300
OPTION
(LOOP
l~ERGE)
2.4.4.3. Uniao por hash
A uniao por hash pode ser utilizada quando nao existe nenhum indice util
na coluna de
item de
uniao em nenhuma das entradas.
dad os em particular corresponde
a
hash permite determinar
a urn valor
S8
urn
jil existente, dividindo as
dados existentes em grupos baseados em alguma propriedade. Os dados com
mesma
valor sao colocados
possui um correspondente
nurn hash bucket.
nos dados
Para verificar
existentes,
S8
simplesmente
0
urn novo valor
examina-se
0
bucket quanta ao valor correta.
Quando se utiliza hash para unir duas entradas,
0
SQL Server usa uma
entrada, chamada de entrada de constru~o, para efetivamente construir os hash
buckets.
Ap6s isso, uma linha de cada vez , ele inspeciona a outra entrada e tenta
61
encontrar
uma correspond~ncia
nos buckets
existentes.
A segunda
entrada
chamada de entrada de investiga9ao. Ao processar unioes par has/),
Server tenta utilizar a menor entrada como a entrada de
0
e
SOL
constrw;~o.
Por exemplo, para utilizar a estrategia de hash na consulta "obter as names
dos fornecedores
(e respectiv~ status) que utilizam 350 au 400 peg8s em seus
projetos":
SELECT
FROM
F.Fnome,
F.Status
Fornecedores
F,
FornecedorPecaProjeto
\;HERE F. F# ~ FPC. F#
AND
FPC.Qde
OPTION
(LOOP
IN
(350,400)
HASH)
FPC
62
2.5. OUTROS METODOS DE APRIMORAMENTO DE DESEMPENHO
Alem
de contar
com
hardware
e software
apropriados,
banco
de dados
adequadamente narmalizada e indices "teis, existem metodas empregados
eficazmente
na construc;ao
SQl que aprimoram
de consultas
as respectivDS
desempenhos.
Plewe Stephens (2000) destacam e explicam os seguintes metodas:
• unir tabelas adequadamente;
•
avaliar
primeiramente
•
utilizar apropriadamente
•
evitar
•
evitar a cltmsula
0
operador
a
condiC;ao mais restritiva;
as varreduras
de tabela;
or,
having;
• evitar operagOesde classificagilo grandes;
•
utilizar procedimentos
armazenados.
2.5.1. Uninda tabelas adequadamente
Dependendo de como
0
otimizador analisa a instrugilo SOL, a ordem de
tabelas na clausula from pode fazer diferenga. Na maioria das vezes, pode ser
mais
interessante
listar as tabelas
maiores
par ultimo
na clausula
from, dessa
forma:
FRO!"! menor
tabela,
maior tabela
o posicionamento
da tabela
base,
utilizada
nas jun90es,
o desempenho de consultas SOL. A coluna da tabela base
tambem
e colocada
favorece
no lado
direito de uma operagao de jungilo na clausula where. As tabelas silo unidas
a
tabela base na ordem de menor para maior. As condigoes de jungilo devem estar
63
nas primeiras
descrito
posi,ces
da clausula
where, seguida
de filtra, como
abaixo:
FROH tabelal,
da menor
tabela2,
para
tabelal.
col
tabela3.
AND tabela2.col
[AND
condi~~ol
[AND
condi~ao2J
2.5.2. Avaliando
Como as jun,ces
mais restritiva
primeiro
lugar,
assim,
geralmente
retornam
depois das
devido
a menor
n"mera
de
jun9Ao
condiyAo
de
filtro
condi9ao
de
filtro
de registras.
menor
da consulta.
where
de linhas, as
A
condic;ao
por ultimo na clausula
de dados
A maioria
que
e
retornado,
dos otimizadores
where, da seguinte
da menor
forma:
tabela
para
maior
tabelal.col
=
tabela2.col
condi9aol
Ela deve ser avaliada
J
Ie
da
para cima, portanto, nesse casa, coloca-se a
tabelal,
tabela3
[AND
condi9Ao
uma alta porcentagem
tabela2,
WHERE
jun9do
condic;oes mais restritivas.
ao subconjunto
as overheads
condi9aO mais restritiva
AND
de
normalmente, a chave para obter 6timos desempenhos em
parte inferior da clausula
FROt-1
condi9Ao
mais restritiva
SOL, pais retorna
reduzindo,
tabela3.col
5er avaliadas
e,
col
J
a condi,ao
condic;Oes devem
consultas
tabela
mai~r tabela (tabela base)
tabela3
t')HERE
em
pelas clausulas
tabela
(tabe!a
base)
: tabela3.col
condiyao
de
junyao
tabela3.col
condiy~o
de
junyao
menos
restritiva
64
[AND condi<;Ao2]
2.5.3. Utilizando
mais
varreduras
Em tabelas
restritiva
de tabela
pequenas,
0
otimizador
utilizar urn indice. No casa de tabelas
pade preferir a varredura
grandes
a varredura
completa
completa
evitada. A maneira rnais facil de evitar urna varredura completa na tabela,
certificar
que existem
indices
na tabela,
e usar condiyOes
a
deve ser
all~mde
where para
na clausula
que os dados sejam filtrados.
2.5.4. Evitando
o
operador
0
a velocidade
e substancialmente
2.5.5. Evitando
having faz com que 0 otimizador
assim a tempo de execUf;:aO
essa clausula
2.5.6. Evitando
nas consultas
grandes
subconjuntos
opera,Oes
order
execute
trabalho
da consulta. Se passive I, deve-se
extra,
evitar
SQl.
de grandes
by, group
de dados
de dados.
opera<;6es de classifica9ao
o processamento
das clausulas
or produz urna melhora
de recupera980
HA VING
a clausula
A claU5ula
aumentado
resposta
in no lugar do operador
usa do predicado
consistente
que
OR
devem
de classificay1io
devem
ser armazenados
de classifica,ao
de uma instru,ao
opera,Oes
by e having
SQL.
sao
realizadas,
envolvendo
ser evitadas.
0 uso
Isto porque
os
na memoria ou em disco sempre
as quais
afetam
0
tempo
de
65
2.5.7.
Utilizando
procedimentos
Outro passo importante
SQL
e a utiliza,iio
armazenados
para
0
melhoramento
do desempenho
de procedimentos armazenados (stored procedures).
Gunderloy e Jorden (2001), urn procedimento armazenado
fica armazenada
armazenada
enviar
de linhas
mesma e aumentando
0
cliente. A vantagem
de c6digos
pela
Segundo
uma consulta que
tempo de resposta.
e que as usuarios
rede,
determinado
procedimento.
Esses
congestionando
naD precisam
0
trafego
Em vez disso, as usuarios
apenas uma linha de codigo solicitando que
execute
e
em urn banco de dados em vez de ficar no c6digo da interface
na maquina
centenas
de consultas
0
da
enviam
servidor de banco de dados
procedimentos
armazenados
ja sao
pre-compilados e tern urn plano de execu,iio (conjunto de instru,oes destinadas
ao SGBD sobre como executar a consulta) guardado na memoria, signifrcando
que senlo executados
mais rapidamente
que as
consultas comuns.
66
2.6. SOL SERVER
o
SGSD escolhido para este projeto
conforme Soukup e Delaney
(1999)
e
a Microsoft SOL Server 7, que,
e
asseguram,
urn sistema de gerenciamento
de banco de dados relacional cliente I servidor de alto desempenho.
Soukup e
Delaney (1999) afirmam que este software foi projetado para oferecer suporte a
processamento de transagOes de alto volume de dados e que pode ser executado
em redes baseadas na plataforma Windows NT Server e na versao desktop em
maquinas com Windows NT WorkStation, Windows 95 e Windows 98. 0 SOL
Server ainda oferece suporte a urna variedade
de aplicativos solicitantes,
processamento de transagOeson-line (OlTP) e aplicativos de apoio
SOL, a versao da Microsoft da Structured
0
a decisao. No
a decis80 encontra-se a linguagem
nticleo desses recursos de apoio
como
TRANSACT-
Query Language.
2.6.1. Arquitetura do SOL Server
o
Microsoft
mecanisme
Sal Server
passui
urn mecanisme
interno,
chamado
de
responsavel par fornecer as diversos recursos
de banco de dados,
para que se possa gerenciar um banco de dados (Soukup e Delaney, 1999). Ele e
constituido
de
mecanismo
de armazenamento.
componentes
mecanismo
dais
componentes
0
0 mecanisme
necess~rios para
de armazenamento
principais:
analisar
compreende
e
mecanisme
relacional
otitniz8r
relacional
compreende
qualquer
os componentes
e
0
todos as
consulta.
necessarios
0
para
acessar e modificar realmente os dados no disco.
Segundo Soukup e Delaney (1999), a mecanismo relacional
principalmente dos seguintes m6dulos:
e
composto
67
•
Analisador
de comandos:
correta e transforma
para que
S8
0 analisador
possa trabalhar.
de comandos
Transact-Sal
comandos
Esse formata
verifica
a sintaxe
para urn formata interno
e
chamado
de arvore de
e emitido
consulta. Casa 0 analisador nao reconhega a sintaxe,
urn erro
de sintaxe.
•
Otimizador:
0 otimizador
recebe do analisador
de consulta e a prepara para execuC;ao,
comandos,
otimizando
a
consulta
e
de com and as a arvDre
compilando
verificando
urn late inteiro de
a
seguran98.
Da
otimizagao e compilagao da consulta resulta um plano de execllgao.
Para chegar
consulta
aD
plano de execu98o,
a consulta e otimizada, Oll seja,
dessa
consulta.
escolhe
e
a consulta
normalizada,
ista
e, a
e subdividida em varias consultas mais refinadas. Em seguida
Essa
e determinado um plano para execugao
otimizac;ao
a plano que custe menes
e
em
baseada
em custos,
0
otimizador
raza.o de metricas internas,
como
por exemplo requisitos de mem6ria estimados, utilizagao da CPU
estimada e numere estimado de operagOes E/S
considerado
0
tipo de instrugao solicitado, verificado
nas varias tabelas
afetadas,
examinados
cad a tabela e depois examinado
mantidos
dos valores
para cada indice au caluna referenciados
base nessas informayOes,
0
de uni~o que podem
a consulta e escolhe
0
se houver,
na consulta.
ser utilizadas
para
de dados
otimizador considera as varios metodos
aces so e estrategias
indices,
os indices disponiveis
uma amostra
E
solicitadas.
volume de dados
0
Com
de
para solucionar
plano de menor custo. Ele tambem decide quais
devem
ser utilizados
para cada tabela,
bern como a
ordem em que as tabelas devem ser acessadas, e a estrategia de uniao
68
a ser usada.
Todavia,
0 otimizador
escolha
de seu plano de consulta.
errado.
Par
isse,
Gonta
ha a necessidade
otimiza~ao de consultas
para
que
com
Assim sendo,
de
a probabilidade
as vezes
conhecer
S8 possa
as
optar
na
ele esta
tecnicas
por uma
de
Dutra
estrategia de consulta mais eficiente, diferente daquela adotada pelo
otimizador.
• Gerenciador de SOL: 0 gerenciador de SOL responsabiliza-se pelo
que
estiver
armazenados
armazenado
procedimento
relacionado ao
e seus
precisa
para
pianos.
gerenciamento de
Determina
ser recompilado
que
eles
quando
e gerencia
possam
ser
0
procedimentos
urn procedimento
cache
de pianos
reutilizados
per
de
Qutros
processos.
•
Gerenciador
de expressoes:
0 gerenciador
de
express~es trata do
calculo, da compara,ao e da movimenta,ao dos dados.
• Executor de consultas: 0 executor de consultas executa
0
plano de
execu,ao que foi produzido pelo otimizador, atuando como um
despachante para todos os comandos presentes no plano de execu,iio.
69
2.6.2. Arquitetura Cliente / Servidor
Date (2000) analisa a arquitetura cliente/servidor, citada anteriormente,
como
sendo
uma
estrutura
simples
composta
de dUBs partes:
urn
servidor
(tambem chamado back end) e um conjunto de clientes (tambem chamados front
end).
Segundo Date (2000),
servidor
0
e
0
proprio SGBD, admilindo lodas as
func;:6es
basicas de urn SGBD.
sabre 0
SGBD, tanto aplicac;:oes escritas par usuarios quanta aplic8c;:Oes internas,
Os
clientes sao as diversas aplicac;:Oes executadas
islo e, aplica90es fornecidas pelo fabricanle do SGBD ou por produlores
independenles.
Deve,se eslar clara a distin9ao que exisle entre um banco de dados
arquivo,servidor e
0
banco de dados cliente,servidor (Gunderloy e Jorden, 2001).
Em um banco de dados arquivo-servidor, as dados sao armazenados em urn
arquivo e as usuarios individuais dos dados abt~m diretamente as informa<;:oes
desejadas. Para alterar dados e/ou para exibir dad as desse arquiva,
que abrira
0
0
aplicativo e
arquiva, modificando e/au exibindo as dados. Dessa forma, no casa
de vinte usuarios utilizando esse sistema, todos as vinte usuiuios vao ler e
escrever no mesmo arquivo. Quando se trala de um banco de dados clienle,
servidor, as dados continuam armazenados em um arquivo, mas todos os
acessos aD arquivo sao controlados por urn unico programa-mestre,
0
servidor.
Um aplicativo, ao usar os dados existentes, enviara uma solicitac;aoao servidor. 0
servidor localiza os dados apropriados e os envia de volta. Quando
escrever dados no banco de dados, ele enviara os dados para
0
0
aplicativo
servidor, que
e
quem realmente escreve no arquivo. Nesse caso, somente urn programa I~ e
escreve nos arquivos de dados.
70
Bancos de dados que ulilizam a arquilelura clienle-servidor possuem
algumas vantagens com rela9aOa oulros banco de dados (Gunderloy e Jorden,
2001):
•
Existe
menor
probabilidade
de
que
alterayOes
au
travamentos
acidentais alterem dados, porque somente urn programa esta lendo e
escrevendo
dados;
• 0 programa de urn unico servidor pade funcionar como urn porteiro para
lodos os clienles, facililando a cria9aO e a implanla9ao de urn sistema
de seguran9a;
•
Os
bancos
de dados
cliente-servidor
usam
com
mais
efici~ncia a
largura de banda disponivel da rede que os bancos de dados arquivoservidor, pois somente as solicita90es e
0
resultados percorrem
0
meio
de comunica9ao;
•
Assim como todas as leituras e as grav890es
"nico compulador,
e rnais tacil
aumenlar
0
sao realizadas
par urn
desempenho do banco de
dados alualizando esse compulador;
•
Os banco de dados cliente~servidor
tendem
prolegem os dadas, como par exemplo:
recuperaC;8o de erros de disco e de rede.
0
a oferecer
recursos
que
regislro de Iransac;6es e
71
2.7. MONITORANDO 0 DESEMPENHO DAS CONSULTAS
Soukup e Delaney (1999) recomendam a obten9ao de algumas medidas de
desempenho
para que S8 possa comparar
0 comportamento
SOL antes e depois de efetuar as alterac;Oescom
consulta
2.7.1.
0
de uma consulta
objetivo de tornar essa
mais rapida.
Ferramentas
o
para monitorar
consultas
SOL Server fornece as seguintes ferramentas (OP90es SEn para
monitorar
consultas:
•
STA TlSTICS
10
•
STATISTICS
TIME
•
SHOWPLAN
o
Query Analyzer
tambern oferece caixas de sele9ao para que se possa
utilizar para ativar e desativar qualquer uma ou todas essas OP90es.
2.7.1.1.
Statistics
10
A ferramenta Statistics
10
fornece estatisticas sobre
0
trabalho realizado
pete Sal Server para processar uma consulta. Para isso, essa opc;:ao deve estar
definida como ON. A saida de SET STATISTICS
Reads,
Physical
•
Logical
Reads,
Reads
Read Ahead
Reads
10 ON
inelui os valores Logical
e Scan Count.
(Leituras Logicas): Indica
0
numera total de acessos de
pagina_necessarios para processar a consulta, sendo que toda pagina
!ida a partir do cache de dados, tenha side au nao necessaria
essa pagina do disco para
0
e
trazer
cache para uma leitura determinada. Uma
mesma pagina pode ser lida muitas vezes, portanto, a contagem de
72
Logical Reads
para urna tabela pade ser maior do que
0
numero de
paginas presentes na mesma;
•
Physical
Reads
(Leituras
Fisicas):
foram lidas do disco. Esse numero
Indica
nLJmero de paginas que
0
e sempre
menor au igllal ao valor de
Logical Reads. 0 valor de PllYsical Reads pode variar bastante e
diminuir substancialmente
carregado devido
a
com a segunda execu9ao,
pois
0
cache esta
primeira execugao. Tambem pode apresentar
val ores mais baixos se a pagina for carregada previamente at raves de
leituras antecipadas. Ao
Logical
Reads
mais coerentes.
S8
examinar as consultas individuais,
0
valor de
geralmente e mais interessante, pois as informa96es sao
Deve-s8
dar
aten~o especial ao Logical Reads
para
cada consulta e prestar bastante atengao na opera9ao de E/S fisica e
na ta)(a de utilizaC;:8odo cache para 0 servidor como urn todo.
• Read Ahead Reads (Leituras Antecipadas): Indica 0 numero de paginas
que foram lidas no cache usanda 0 mecanisme de leitura antecipada,
enquanto a consulta foi processada. Nem sempre essas paginas sao
utilizadas pela consulta. Caso uma pagina for necessaria, uma leitura
16gica sera contada, mas uma leitura fisica nao. Um valor alto significa
que
0
valor de Physical
utiliza~ao
Reads
e
provavelmente
do cache provavellllente
e
menor e que a taxa de
maior do qlle se uma leitura
antecipada nao fosse realizada.
• Scan Count (Quantidade de Acessos): Indica
0
n(,mero de vezes que a
tabela correspondente foi acessada. As t.abelas externas de lima uniao
de loop aninhado possuern urn valor igual a 1.
Ja
as tabelas internas. a
73
valor de Scan Count podera ser
0
nOmero de vezes que a labela loi
acessada alraves do loop.
2.7.1.2.
Statistics
Time
descrevem Soukup e Delaney (1999),
Como
STA TIS TICS TIME ON
CPU
necessarios
dais componentes:
e auto-explicativa, sendo mostrado
para processar
a consulta.
0
resultado de SET
0
tempo decorrido e de
Estes tempos
sao
separados
a tempo exigido para analisar e compilar a consulta e
0
em
tempo
exigido para executar a consulta.
2.7.1.3.
SIlowplan
Ha algumas maneiras de exibir 0 plano de
formate de texte, com au sem estimativas
execUC;ao de uma consulta: em
de desempenho
adicionais,
ou ainda,
atraves de uma representaylio grafica do plano de processamento da consulta
SOL (Soukup e Delaney, 1999).
As OP90es SET SHOWPLAN_
visualizaC;ao
do plano de consulta
OP9BoSHOWPLAN_
TEXT
da consulta, incluindo
0
e SET SHOWPLAN_ALL
TEXT
estimado
sem realmente
executar
permitem a
a consulta.
A
mostra todos os passos envolvidos no processamento
tipo de uniao utilizada, a ordem dos acessos
a tabela e
qual indice (ou indices) e utilizado para cada tabela. As c1assifica90es internas
tambem sao exibidas. A OP9aO SHOWPLAN_ALL,
informac;Oes, faz uma estimativa
satisfazer
aos criterios
resultantes,
0
de pesquisa
do numero
de linhas que sao esperadas
das consultas,
tempo de CPU estimado e
0
alem de lornecer essas
a tamanho
estimado
para
das lin has
custo total estimado que loi utilizado
para comparar esse plano com outros possiveis pianos de consulta.
74
A representa980
grilfica do plano de execu~o
estimada
SOL pode ser obtida atraves da op\,ao Display Estimated
Query
do SOL Server Query Analyzer.
qualquer
leone
do
plano
grafico
No entanto,
para
Execution
Plan
do menu
Esta representa\,ao gratica contem as
mesmas informa\,Oes exibidas pelo SHOWPLAN_ALL,
todas sao visiveis simultaneamente.
de uma consulta
ver
as
com a diferen\,a que nem
pode-se
mover
estimativas
de
0
cursor sabre
desempenho
adicionais.
Para visualizar
0
plano real de execu\,ao de uma consulta,
escolher a op\,ao Show Execution
Quety Analyzer.
Plan
pode-se
a partir do menu Query do SOL Server
3. 0 BANCO
o
DE DADOS
banco de dados utilizado para implementa9ao das consultas SQL,
conforme mencionado na se9ao 1.2.2, est" baseado no banco de dados PUBS
que acompanha
o
0
Sal Server.
software
fata de escolher
urn banco de dados pre-existente
como base para a
implementa9ao das consultas SQL, se deve:
•
Par
ser
0
objetivo
do
projeto
apresentar
tecnicas
de
otimiz89ao
empregadas para 0 aprimoramento de desempenho de consultas SQL e
de comprovar
nao
SGBD,
essas tecnicas
ha
a
atraves
necessidade
de ferramentas
de
desenvolver
especificas
0
projeto
do
e
implemental):a.o de urn banco de dados do inicio ao fim;
• Com a utiliza9ao da estrutura de um banco de dados pre-existente, que
segue as premissas
urna economia
da teoria de banco de dados relacional,
significativa
de tempo,
0
qual podera
ohtem-S8
ser mais bern
aproveitado em outras partes do desenvolvimenlo do projelo.
A seguir, e exibido
PUBS, conforme
0
0
Modelo Entidade-Relacionamento do banco de dados
modelo descrito por Rezende (1999):
76
Figura 8 - Diagrama
Entidade-Relacionamento
do banco de dados
Onde:
• ------.oE:representa um relacionamento
para muitos;
•--+: representa
para um.
um relacionamento
PUBS.
77
Abaixo,
segue
modelo apresentado
0
dicionario
por Rezende
de dados do banco de dados
Tabela
Nome
5 - Tabela Autores
Descric1io
codi 0 do autor
nome
descri~1io do prenome
sob rename
descricao do sobrenome
fane
descri~1io do telefone
endereco
descricao do endereco
cidade
descri~ao da cidade
estado
descriC1io do estado
cep
descri~ao do cep
contrato
identifica~1io de contrato
autor id
Tabela 7 Tabela TituloAutor
Nome
Descricao
c6digo do autor-titulo
autor id
titulo id
c6digo do titulo
ordem de livro publicado
autor ordem
royaltyper
tipo de direito autoral
Tabela 8 indices
Nome do Indice
UPKCL_taind
auidind
titleidind
PUBS,
utilizando
(1999) :
da Tabela TituloAutor
Descric1io do Indice
clustered,
unique,
localed on PRIMARY
noncluslered
localed
noncluslered
localed
Tamanho
11
40
20
12
40
20
2
5
1
Tamanho
11
6
1
4
Tipo
id
varchar
varchar
char
varchar
varchar
char
char
bit
Tipo
id
tid
Dominio
0 - nao
passui
contrato
1 - possui contrato
Dominic
tinvint
int
primary
on PRIMARY
on PRIMARY
key
Composicao
autoUd,
tituloJd
auter
titulo
id
id
0
78
Tabela 9
Nome
titulo id
titulo
tipo
Tabela Titulos
Tamanho
Oescricao
c6digo do titulo
6
descricao do titulo
80
categoria
qual 20
a
pertence 0 titulo
editora id
c6digo da editora
4
Ipreco
reco do titulo
8
promocional do 8
promo9ao
pre90
titulo
royalty
valor do direito autoral 4
do titulo
qtd_vendida
quantidade
vendida
do 4
titulo
obs
observacao
200
data_pub
data de publica~o
do 8
titulo
I
Tabela 10 indices
Nome do Indice
UPKCL_titieidind
Ititleind
Tabela 11
Nome
titulo id
limite_min
Dominic
Tipo
tid
varchar
char
char
money
money
int
int
varchar
datetime
da Tabela Titulos
OescriCao do Indice
clustered,
unique,
primary
key
localed on PRIMARY
noncluslered
localed on PRIMARY
Composicao
titulo_id
titulo
- Tabela Oireito Autoral
OescriCao
c6digo do titulo
valor minima do direito
autoral
Tamanho
6
4
Tipo
tid
int
limite_max
valor maximo do direito 4
int
royalty
autoral
valor do direito autoral
int
4
Dominic
,I
79
Tabela
Nome
13 - Tabela Vendas
DescriCBo
c6diQO da livraria
numero da ordem
num ordem
data ordem
data da ordem
Iqtd
I quantidade
tipo_pagto
descriyao
do tipo
pagamento
livraria id
titulo
id
Tamanho
4
20
8
2
de 12
6
c6digo do titulo
Tabela
Nome
15 - Tabela Livrarias
Tamanho
DescriCBo
c6digo da livraria
4
livraria id
descri,Bo
do nome da 40
nome
livraria
40
endereco
descri~ao do endere,o
Tipo
char
datetime
smallint
varchar
cidade
descri,80
descri,80
Tipo
char
varchar
varchar
da cidade
da
20
varchar
do estado
da
2
char
eep
da
5
char
livraria
livraria
cep
descri,Bo
do
livraria
Deseri
ao do ndice
clustered, unique, primary
located on PRIMARY
A Vista
30 Dias
60 Dias
tid
da livraria
estado
Dominic
varchar
Dominic
80
Tabela 17 Tabela Descontos
Nome
Descr~o
tipo_desconto
descri9ao
do
desconto
livraria id
td min
td max
desconto
Tabela
Nome
editora
name
18
id
cidade
estado
pais
Tabela 20
Nome
emp. id
nome
40
Tipo
varchar
4
2
2
5
char
smallint
smallint
decimal
Tamanho
4
da 40
Tipo
char
varchar
da
20
varchar
da
2
char
da
30
varchar
de
c6digo da livraria
quantidade minima
I Quantidade maxima
valor de desconto
I
Tabela Editoras
DescriC80
c6digo da editora
descri9ao do nome
editora
descri980 da cidade
editora
descri980 do estado
editora
descri9!io
do pais
editora
Dominic
Cliente Novo
Desc. Quantidade
Desc. Cliente
Dominic
. Tabela Empregado.
minit
sobrenome
cargo
jobJvl
Tamanho
tipo
id
editora id
data_admissao
Oescric;ao
Tamanho
c6dioo do empreqado
9
descri980
do nome do 20
empregado
letra inicial do nome 1
intermediario
do 30
descri9aD
sobrenome
do
empregado
c6digo do cargo
2
salafia
base
do 1
empreqado
c6digo da editora
4
data de admissao
do 8
empregado
Tipo
empid
varchar
char
varchar
smallint
tinyint
char
datetime
Dominic
81
Ta beaI 2 1 - ind'IceS d a Ta bela EmpreQado.
Nome do ndice
employee_ind
Descricao do Indice
clustered located 011 PRIMARY
PK_empjd
nOllelustared,
unique,
located on PRIMARY
Tabela 22 - Tabela Cargos.
Nome
DeseriC;ao
c6digo do cargo
cargo id
descricao do cargo
descricao
min Ivl
saliuio minima
max Ivl
salaria maximo
primary
Tamanho
2
50
1
1
key
Composieao
sobrenome,
minit
emp_id
Tipo
smallint
varchar
tinyint
tinyint
primary
Abaixo,
a quantidade
-
Tabela 24
Tabela
Tabela
Autores
Descontos
Empregado
Cargos
Editoras
Direito Autoral
Vendas
Livrarias
Titulo Autor
Titulos
de registros
de numero
existentes
em cad a tabela:
de linhas de cad a tabela
Numero de Linhas
97.534
29.857
9.820
14
9.760
9.742
64.986
10.000
23.166
36.208
Dominic
nome,
4.IMPLEMENTAC;;Ao
Com
0
DE CONSULTAS
objetivo de demonstrar
SQL
a execuy~o e monitoramento
de consultas
Sal, bern como comprovar a otimizac;ao obtida atraves das tecnicas descritas
anteriormente
para esle tim,
sao necessarias varias implementac;Oes de consultas
SOL.
Uma mesma consulta sera construida de maneiras diferentes, com
de
demonstrar
a
dilerenga
de
desempenho
entre
uma
e
outra
intuito
0
lorma
de
implementagao.
Para auxiliar
0
monitoramento destas consultas, serao utilizadas as opc;Oes
STA TIS TICS 10 e SHOWPLAN
E
importante
consultas
variar
serao
salientar
realizados,
substancialmente
ambiente
e composto
ME, sendo
executado
que lazem
0
parte da lerramenta
ambiente
onde
pois os tempos
para
SOL Server
em um computador
testes
de execugiles
de urn ambienta
de SGBD
estes
Query Analyzer.
cutro.
de validagOes
das consultas
Para
7.0, sistema
esse
das
podem
trabalho,
0
operacional
Windows
Pentium
III, 1Ghz e
com processador
128 RAM.
Ap6s cada consulta, haverc~ a
desconexao do banco de dados para que as
dados retornados nao permaneyam em memoria cache, impedindo, dessa forma,
a execw;:ao mais rapida da pr6xima consulta a ser realizada.
A
consultas.
seguir,
alguns
casas
que
serao
ilustrados
para
implementa930
de
83
4.1. cLAUSULA WHERE
Procurar, sempre que possivel, a utiliza,ao da clausula WHERE
numero de linhas retornadas
WHERE
assim,
evila que
tempo
0
pela consulta.
Isto porque
a utilizacao
de processamento
desnecessario.
a) Sem a utilizayao da c1ausula WHERE.
*
FROM Livrarias;
Tempo de execu,3o da consulta: 155
N°de linhas aletadas: 10.000
N" de linhas da tabela: 10.000
Seletividade:
N° de acessos
100%
a tabela
Livrarias:
Leituras Logicas: 102
Leituras
Fisicas:
0
da clausula
SOL Server realize uma varredura lotal da tabela, impedindo,
Exemplo 1: Selecionar todos os atributos da tabela Livrarias.
SELECT
para limitar
1
Leituras Antecipadas: 96
84
Exemple 2: Selecionar
todos as atributos da tabela Livrarias onde a cidade seja
Curitiba.
b) Com a utiliza,ao
da clausula
SELECT
*
FROM
Livrarias
I'1HERE cidade
Tempo
de execu,3o
N° de lin has afetadas:
==
"Curitiba";
da consulta:
1s
98
N° de lin has da tabela:
Seletividade:
WHERE
10.000
0,98%
a tabela
N° de acessos
Leituras
L6gicas:
Leituras
Fisicas:
Leituras
Antecipadas:
Livrarias:
102
78
32
Conclusao:
Neste
casa,
retornadas,
primeira
a segunda
proporcionando
consulta
e obtendo
consulta
limita
significamente
uma seletividade
0
numero
de menes de 1%
urn tempo de execuyao
bern menor.
de
lin has
contra 100%
da
85
4.2. SELECT
Segundo
<
McGehee
desnecesstuios,
(2002),
apenas
se deve
na.o usar SELECT
au seja,
for necessaria
nao
retornar
* para retornar
atributos
de
dados
todos as atributos
se
dados de alguns atributos. A busca par todos atributos
produz urn trabalho adicional, ja que sera necessario ler a pagina de dados de
cada linha para obter os valores dos atributos que nao fazem parte do indice,
casa
mesma
0
exista.
Exemplo: Selecionar os empregados que foram admitidos a partir de 01/01/2000.
a) Utilizando SELECT
SELECT
<
para retomar todos os atributos do registro.
*
FROM Empregado
WHEREdata
adm
>=
("2000-01-01");
Tempo de execu930 da consulla: 13 s
N°de linhas afetadas: 8.516
N°de linhas da tabela: 9.820
Seletividade: 86,7%
N°de acessos
iii
tabela Empregado:
Leituras Logicas: 94
Leituras
Fisicas:
2
Leituras Antecipadas: 88
86
b) Especilicanda as atributas na declara,aa SELECT.
SELECT
emp_id,
data_actm
FRO!>1Empregado
VlHERE data_adm
Tempo de
>~ ("2000-01-01");
execu~ao da consulta:
12 s
N°de linhas aletadas: 8.516
N°de linhas da tabela: 9.820
Seletividade: 86,7%
N°de acessas
a tabela Empregada:
Leituras L6gicas: 44
Leituras Fisicas: 0
Leituras Antecipadas: 0
Conclusao:
Na segunda cansulta, Icram especiflcadas apenas dais atributas a serem
retarnados, diminuinda em 1 so tempo de execu9ao.
87
4.3. cLAUSULAS
Nao utilizar
SARGABLE
clausulas
WHERE
que nao sejam
podem evitar que urn indice seja aproveitado
consulta, pais essas cliwsulas
sargable.
Clausulas
para melhorar
nao limitam a pesquisa,
0
nao-sargable
desempenho
da
au seja, teda linha deve
ser avaliada.
Exemplo: Selecionar
nome e telefone dos
auto res que residam no estado do Rio
de Janeiro.
clausula naa sargab/e e indice nao-setorizado
a) UtiHzando uma
atributos Estado, Nome e Fane.
SELECT
Nome, Fone
FROM Autores
IvHEREEstado
Tempo
de execu9ao
N' de lin has afetadas:
LIKE
da consulta:
5.681
N° de linhas da tabela:
Seletividade:
97.534
5,8%
N° de acessos
Leituras
"tJ";
a tabela
L6gicas:
Autores:
625
Leituras Fisicas: 2
Leituras Antecipadas:
556
4 s
composto pelos
88
b) Utilizando
sBrgable
urna Clclusula
e indice
nao-setorizado
composto
pelos
atributos Estado, Nome e Fone.
SELECT
Nome,
FROM
Autores
WHERE
Estado
Tempo de execuc;ao
Fone
=
~RJH;
da consulta:
2 s
N°de linhas afetadas: 5.681
N" de linhas da tabela: 97.534
Seletividade: 5,8%
N°de acessos
a tabela Autores:
Leituras L6gicas: 39
Leituras Fisicas: 0
Leituras Antecipadas: 33
Conclusao:
Atraves da utiliza9ao de uma clausula sargable
se a diferen9a entre usar uma clausula sargable
na segunda consulta, constatoue uma clausula nao-sargable.
diferen9a de execU9ao, 2 s, ficou por conta da clausula sargable
"RJ",
a qual
permitindo
limita
a pesquisa,
especificando
urna
E.tado
correspond~ncia
assim 0 usa correto do indice nao ...setorizado
no atributo
dessa forma contribuindo para 0 melhor desempenho da consulta.
A
exata,
Estado
e
89
4.4. iNDICE
Deve-se
SETORIZADO
usar indice setorizado
pOis ele mantem
os dados
em consultas
lisieamente
delimitadas
par intervalo
orden ados em uma tabela
de valores,
em rela,ao
chave, facilitando assim a busca por urn intervalo determinado
de dadas.
Exemplo:
N° de identiliea,ao
Selecionar
no intervalo
a)
todos
urna consulta
que tenham
FROM
de valores
sem a utiliza9aO
Autores
\'IHERE autor_id
de execu,ao
BETI,EEN
da consulta:
N° de linhas aletadas:
9.736
N° de linhas da tabela:
97.534
Seletividade:
de intervalo
*
SELECT
9,9%
N° de acessos
Leituras
dos autores
que vai de 40000 iI 50000 (inclusive).
Executando
Tempo
os dados
a tabela
L6gieas:
Autores:
1.634
Leituras
Fisieas:
Leituras
Anteeipadas:
59
1.640
40000
6 s
AND
50000;
de indices.
a
90
b) Executando
uma consulta
de intervalo
de valores
com a utiliza9aO
de indice
setorizado no atributo autor_id.
*
SELECT
FROM Autores
t'lHERE autor_id
Tempo
de execuc;ao
NO de linhas aletadas:
N° de linhas da tabela:
Seletividade:
da consulta:
.Zl..ND50000;
5 s
9.736
97.534
9,9%
N° de acessos
Leituras
BETWEEN· 40000
a tabela
Logicas:
Autores:
157
Leituras
Fisicas:
Leituras
Antecipadas:
24
0
Conclusao:
o
tempo de execu9ao
foi reduzido
em 2 s na segunda
consulta
devido
a utiliza9aO
de urn indice setorizado contendo a atributo avaliado na clausula WHERE.
91
4.5. iNDICE
NAo-SETORIZADO
Utilizar indice nao-setorizado
<=
5%).
varias
pais a
onde a consulta
seja altamente
seletiva
(seletividade
recupera9~o de dados usanda indice nao~setorizado envolve
leituras para determinar os dados a serem retornados.
Exemplo:
Selecionar
a) Executando
os itens de venda da ordem "5633".
uma consulta
sem a utiliza,ao
Num_Ordem,
Tipo_Pagto,
de indice nao-setorizado
Num_Ordem.
SELECT
FROt-1 Vendas
NHERE
Tempo
Num_Ordem
de execu~ao
da consulta:
N° de lin has afetadas:
N° de lin has da tabela:
Seletividade:
N° de acessos
Leituras
= "5633";
6
64.986
0,009%
a tabela
Logicas:
Vendas:
335
Leituras Fisicas: 13
Leituras Antecipadas:
281
3
5
Data_Ordem,
Qtd
na coluna
92
b) Executando
a consulta
anterior com a utiHzac;ao de indice nao-setorizado
na
cotuna Num_Ordem.
SELECT
FROM
Num_Ordem,
Tipo_Pagto,
N'HERE NUffi_Ordem
=
Qtd
"5633";
Tempo de execuy30
da consulta:
N° de linhas afetadas:
6
N° de linhas da tabela:
Seletividade:
0s
64.986
0,009%
N° de acessos
Leituras
Data_Ordem,
Vendas
a tabela
L6gicas:
20
Leituras
Fisicas:
12
Leituras
Antecipadas:
Vendas:
0
Conclusao:
Nesse
case, foi utilizado
seletividade
de
obtida
urn indice
pela consulta
nao-setorizado
tai de menes
na segunda
consulta,
de 1%, acarretando
ande a
em urn tempo
execUC;ao inferior a primeira consulta, au seja, urna diferenc;a de 3 s.
93
4.6. iNDICE
DE COBERTURA
Em consultas onde os atributos a serem
retornados
sejam
fixDs, e muito util a
inclusao destes como parte da chave do indice nao-setorizado.
necessidade
de
mencionados
leituras
adicionais
para
obteny80
dos
Com issa, naD
Qutros
hi!
atributos
na consulta.
Exemplo: Selecionar
sobrenome
e telefone dos
auto res que tenham a inieial L no
sobrenome.
a consulta com a utilizay80 de um indiee
a) Executando
sobrenome.
SELECT
Sobrenome,
Fone
FRON Autores
WHERE
Tempo
Sobrenome
de execu~ao
da consulta:
N° de linhas afetadas:
3.641
N° de linhas da tabela:
Seletividade:
N° de
aces
Leituras
LIKE
97.534
3,7%
50S
a tabela
L6gicas:
Autores:
1.546
Leituras
Fisicas:
Leituras
Antecipadas:
8
382
"L''';
6 s
nao-setorizado na coluna
94
b) Executando
a consulta
anterior
composto das colunas sobrenome
SELECT
FROM
Sobrenome,
com
0
usa
de
urn
indice
nao-setorizado
e telefone.
Fane
Autores
I-'lHERE Sobrenome
LIKE "L''';
Tempo de execu~ao da consulta: 5 s
N°de linhas afetadas: 3.641
N°de linhas da tabela: 97.534
Seletividade: 3,7%
N° de
aces
50S
a tabela
Autores:
Leituras Logicas: 25
Leituras
Fisicas:
2
Leituras Antecipadas: 21
Conclusao:
A diferen9a de 1 s da segunda consulta para primeira consulta fai devida aa usa
de urn indice de cobertura
contendo
os dois atributos
retornados
peta consulta.
95
4.7. cLAUSULA ORDER BY
A menos
ORDER
que
seja
realmente
necessaria,
na declara,ao SELECT
BY
adicional na consulta, degradando
Exemplo:
Selecionar
todos
as
0
deve-se
livros editados,
a) Utilizando ORDER BY.
FROM
T.Titulo,
Editoras
E,
E.Nome
Titulos
irlHERE
E. Edi tara
id
ORDER
BY T.Titulo;
=
T
T. Edi tara
Tempo de execu,3o da consulta: 1min 01s
Numero de linhas afetadas: 36208.
N°de acessos
a tabela Titulos:
Leituras Logicas: 647
Leituras Fisicas: 0
Leituras Antecipadas: 575
N° de acessos
a tabela
Editoras:
Leituras Logicas: 121
Leituras Fisicas: 0
Leituras Antecipadas: 72
a usa da cltlUsula
desempenho da mesma.
editoras.
SELECT
evitar
Esta clausula representa um overhead
id
bern
como
suas
respectivas
96
b) Sem a utiliza~ao de ORDER BY.
SELECT
FROl·l
T.Titulo,
Titulos
WHERE
T,
E.Home
Editoras
E
E.Editora~id == T.Editora
id;
Tempo de execu~ao da consulta: 56 s
Numero de linhas aletadas: 36208.
N° de acessos
a tabela
Titulos:
Leituras Logicas: 647
Leituras Fisicas: 0
Leituras Antecipadas: 575
N°de acessos
a tabela Editoras:
Leituras Logicas: 121
Leituras Fisicas:
a
Leituras Antecipadas: 113
Conclusao:
Atraves das duas consultas pode-se verificar a dileren9a de desempenho (5 s'.
Na segunda consulta loi omitida a clflUsula ORDER BY.
97
4.8. ORDEM
A ordem
DOS A TRIBUTOS
dcs atributos
criterios
da consulta
no Indice deve ser observada,
correspond
E
chave de indice.
numero
nome,
de sobrenome
a sobrenome
desejado.
em aos atributos
como uma lista telef6nica,
aJfabetica composta
ordem
conhecendo-se
DO iNDICE
basta
seguir
percorrer
localizados
e nome.
a lista, pois
0
ou seja, ela
a ordem
No casa de tentar localizar
sera necessaria
pois
0
0
Para
indice 56
mais
nome
S8
a esquerda
esta organizada
localizar
alfabetica
numero
e util
as
na
em
urn numero
para encontrar
conhecendo-se
0
apenas
0
poden3 estar em qualquer
pagina da lista.
Exernplo:
Selecionar
a) Executando
sobrenome
nome, sobrenome
a consulta
e nome
(nesta
e telelone
utilizando
ordem)
do autor Jose Oliveira.
urn indice
e utilizando
nao-setorizado
0
WHERE.
SELECT nome + " " + sobrenome,
FROM auto res
WHERE
Tempo
nome'"' "Jose";
de execu9ao
da consulta:
N° de linhas aletadas:
1
N° de linhas da tabela:
97.534
Seletividade:
N° de acessos
Leituras
0,0%
a tabela
L6gicas:
Autores:
669
Leituras Fisicas: 1
Leituras
Antecipadas:
594
2 s
fone
atributo
nome
composto
de
na c[ausu[a
96
b) Executando
sobrenome
a consulta
e nome (nesta
utilizando
ordem)
urn indice
e utilizando
nao~setorizado composto
0
atributo sobrenome
WHERE.
SELECT
FRO"'l
nOIne + " " + sobrenome,
autores
t'1HERE sobrenome
=
"Oliveira";
Tempo de execu~ao da consulta: 0 s
N°de linhas afetadas: 1
N°de linhas da tabela: 97.534
Seletividade:
N° de acessos
0,0%
it tabela Autores:
Leituras L6gicas: 6
Leituras Fisicas: 4
Leituras Antecipadas: 0
fone
de
na clausula
99
c) Executando
sobrenome
clausula
a consulta
utilizando
e nome (nesta ordem)
urn indice n~o·setorizado
e utilizando
0
atributo
composto
sobrenome
de
e nome na
WHERE.
SELECT
+ "
nome
"
+ 30brenome,
=
"Oliveira"
fone
FROM autores
~o1HERE sobrenome
da consulta:
Tempo de execu~ao
N° de linhas aletadas:
1
N° de linhas da tabela:
97.534
Seletividade:
N° de
AND
nome
"Jose";
0 s
0,0%
a tabela
aceSSDS
Leituras
Logicas:
6
Leituras
Fisicas:
a
Autores:
Leituras Antecipadas:
Conclusao:
Observando
retornam
porque
0
as
Ms
a primeira
consulta
nome (nesta ordem),
o atributo
consultas,
resultado em 0
sobrenome.
ao
5,
constata-se
que
as
duas
enquanto a primeira consulta
faz usa indevido
nao indiear
0
atributo
ultimas
0
do indice composto
que
pesquisas
faz em 2 s. Isto
de sobrenome
esta mais a esquerda,
e
no casa
100
4.9. ORDEM DAS TABELAS NUMA JUN<;Ao
Conforme afirma Plew e Stephens (2000), dependendo como
a consulta, a ordem das tabelas na clausula FROM
as tabelas
primeiro
menores
lugar,
primeiro.
Islo porque,
otimizador analisa
pade tazer diferen.ya, listando
avaliando
men or de dados
urn subconjunto
0
e
as tabelas
menores
em
retornado,
reduzindo
as
overheads da consulta.
Exemplo: Selecionar os nomes de todos os empregados, as descri90es dos
cargos ocupados,
a) Executando
bern como
a consulta
0
nome da editora
listando as tabelas
na qual trabalham.
a serem unidas da maior para a
menor e colocando a tabela base (maior) no lado esquerdo da opera9aO de
jun9ao.
SELECT
E.Nome,
~ROM Empregado
\,IHERE
AND
C.Descricao,
E, Editoras
E. Edi tara _ id
E Cargo_id
Tempo de execw;ao
=
=
ED. Edi tara _ id
C Cargo_id;
da consulta:
65
N°de linhas afetadas: 9.820
N°de acessos
a tabela Empregado: 14
Leituras L6gicas: 116
Leituras
Fisicas:
1
Leituras Antecipadas: 97
N°de acessos
a tabela Cargos:
Leituras
Logicas:
Leituras
Fisicas:
2
2
Leituras Antecipadas: 0
ED. Nome
ED, Cargos
C
101
a tabela
N° de acessos
Editoras:
Leituras L6gicas: 121
Leituras
Fisicas:
1
Leituras Antecipadas: 113
b) Executando a consulta anterior listando as tabelas a serem unidas da menor
para a maior e colocando a tabela base (maior) no lade direito da opera980 de
jun98o.
SELECT
E.Nome,
FRO~1 Cargos
vlHERE
AND
Tempo de
C,
C.Descricao,
ED. Nome
Editoras
Empregado
C.Cargo_id
ED,
"" E Cargo_id
ED.Editora
id
=
execu~ao da consulta:
E.Editora
6 s
N°de linhas afetadas: 9.820
N°de acessos
a tabela Empregado: 14
Leituras L6gicas: 116
Leituras
Fisicas:
0
Leituras Antecipadas: 97
N° de
aceSSDS
a tabela
Cargos:
Leituras L6gicas: 2
Leituras Fisicas: 2
Leituras Antecipadas: 0
N° de acessos
a tabela
Leituras L6gicas: 121
Leituras
Fisicas:
0
Editoras:
id;
E
102
Leituras Antecipadas: 113
Conclusao:
Analisando os resultados obtidos pelas duas consultas, verifica-se que
desempenho
mesrna
das duas
estrategia,
e
id~ntico, au seja,
indiferentemente
tabelas na clausula FROM.
0
otimizador
da forma
em
do
que
0
Sal Server adota a
sao
posicionadas
as
103
4.10. CONDI<;AO
Segundo
Plew
WHERE
MAIS RESTRITIVA
e Stephens
devem
Selecionar
cargos ocupados,
editora
deve
as condi90es
mais
restritivas
em primeiro lugar. Dessa forma,
da clausula
urn subconjunto
e retornado, reduzindo as overheads da consulta.
menor de dad os
Exemplo:
(2000),
ser avaliadas
NUMA JUN<;AO
os names
de todos os empregados,
as descri90es
bern como a nome da editora na qual trabalham,
ser do estado
do Parana
e
salario
0
minimo
pago
dos
sendo que a
ao empregado
deve ser de R$ 150,00.
a) Executando
menor
a consulta listando as tabelas
e colocando
a tabela
base (maior)
a serem
unidas da maior para a
no lado esquerdo
da opera9ao
de
jun9ao e as condi90es de restri9aO da mais restritiva para a menes restritiva.
SELECT
FROM
E.Nome,
C.Descricao,
Empregado
E,
Editoras
I'JHERE E. Editora_id=
AND
E.Cargo_id
=
C.Cargo_id
~ "PR"
AND
>= 150
Tempo de execuC;3o da consulta:
N° de linhas afetadas:
N° de acessos
Leituras
410
a tabela
L6gicas:
Cargos:
758
1516
Leituras Fisicas: 0
Leituras
Antecipadas:
N° de acessos
Leituras
a tabela
L6gicas:
94
ED,
ED. Editora_id
AND ED.Estado
C.Min_val
ED. Nome
0
Empregado:
25
Cargos
C
104
Leituras
a
Fisicas:
Leituras Antecipadas:
N" de aces
Leituras
50S
88
a tabela
L6gicas:
a
Leituras
Fisicas:
Leituras
Antecipadas:
b) Executando
Editoras:
121
113
a consulta
anterior
para a maior e colocando
junc;ao e
listando
a serem unidas da menor
direilo da operac;;ao de
as condic;Oes de restri<,;:ao da menos restritiva para a mais restritiva ..
SELECT
FROM
E.Nome,
Cargos
C,
C.Descricao,
ED. Nome
Editoras
Empregado
~'lHERE C.Cargo_id
AND
=
ED.Editora_id
ED,
E.Cargo_id
=
E.Editora
AND
C.Min_val
>= 150.00
AND
ED.Estado
~ "PR"
Tempo
as tabelas
a tabela base (maior) no lado
de execu~ao
N° de linhas aletadas:
N° de acessos
L6gicas:
Leituras
Fisicas:
Leituras
Antecipadas:
50S
410
a tabela
Leituras
N° de aces
da consulta:
Cargos:
758
1516
0
a tabela
Leituras
L6gicas:
Leituras
Fisicas:
94
0
0
Empregado:
2 s
id
E
105
Leituras Antecipadas: 88
N° de
aceSSDS
a tabela
Editoras:
Leituras L6gicas: 121
Leituras Fisicas: 0
Leituras Antecipadas: 113
Conclusao:
Ao analisar os resultados obtidos pelas duas consultas, verifica-se que
desempenho das duas
mesma
estrategia,
e id~ntico, ou seja, 0
indiferentel"!"lente da forma em que sa.o posicionadas
condi,Oes de restri,ao na clausula WHERE
0
otimizador do SOL Server adota a
as
106
4.11. ANSI JOIN X JOIN SOL-92
Esta
e
uma tentativa de verificar se
consultas
diferente
Exemplo:
que
retornam
hi! diferenc;a de desempenho
as mesmas
OS,
mas que
ao utilizar duas
sao escritas
de forma
(ANSI Join e Join SOL-92)
Listar
as c6digos
a 3010612002 que
a) Utilizando
detenham
a sintaxe
SELECT
direitos
T.titulo,
OA JOIN
a tabela
Leituras
L6gicas:
Leituras
Fisicas:
Leituras
Antecipadas:
N° de acessos
BET"EEN
6 s
773
Titulos:
217
Leituras
L6gicas:
34
Leituras
Fisicas:
1
Leituras
Antecipadas:
W de acessos
da consulta:
2
a tabela
a tabela
T
id = T.titulo_id
V.data_ordem
N° de linhas afetadas:
titulos
V
V.titulo
30")
N° de acessos
01/0412002
V.data_ordem
= T.titulo_id
DA.titulo_id
de execucao
de
autorais.
direito_autoral
ON
no periodo
do ANSI Join
ON
JOIN vendas
and
e titulos dos livros vendidos
T.titulo_id,
FROM
Tempo
dad
193
Direito_Autoral:
32
Vendas:
("2002-04-01")
AND
("2002-06-
107
Leituras
L6gicas:
Leituras
Fisicas:
Leituras
Antecipadas:
b) Utilizando
607
2
540
a sintaxe
SELECT
FRON
do Join SQL-92
T.titulo_id,
vendas
V,
T.titulo,
titulos
1-1HERE V. titulo_id
AllD
T. titulo_id
AllD V.clat
_ordem
=
T,
de execu~ao
T. titulo_id
BET;vEEll
da consulta:
6
N° de linhas afetadas: 773
N° de acessos
tabela Titulos:
a
Leituras
Logicas:
Leituras
Fisicas:
Leituras
Antecipadas:
N' de acessos
217
0
a tabela
Leituras
L6gicas:
34
Leituras
Fisicas:
1
Leituras
Antecipadas:
N° de acessos
Leituras
a tabela
L6gicas:
193
Direito_Autoral:
32
Vendas:
607
Leituras Fisicas: 0
Leituras
Antecipadas:
Conclusao:
539
DA
DA. titulo_id
=
30")
Tempo
v.data_ordem
direito_autoral
5
("2002-0Q-Ol")
Al,D
("2002-06-
108
Constatou-se
que
e
gerad~
0 mesmo plano de execuyao
do
atralles do mecanisme
SQl Server para ambas as consultas,
empregado
pelo otimizador
em tempos
de execU9aO iguais. Portanto,
elas sao equivalentes.
resultando
109
4.12. PREDICADO
Segundo
IN X OPERADOR
Plew e Stephens
lugar utilizar
0
predicado
Exemplo: Selecionar
(2000),
evitar
assim
0
0
uso do operador
desempenho
a qual pertence,
Medicina e Psicologia.
com a utiliza9ao
titulo_id,
OR e no seu
da consulta.
c6digo do livr~, seu pre90 e a categoria
0
a consulta
SELECT
deve-se
IN, aumentado
para as categorias Pedagogia,
a) Executando
OR
preco,
do operador
OR.
tipo
FROI'1 Titulos
vlHERE
tipo
"Pedagogia"
::z:
OR
tipo
::E
"Medicina"
OR
tipo
:a:
"Psicologia";
Tempo
de execu,ao
da consulta:
N° de lin has aletadas:
W de acessos
Leituras
5
3014
a tabela
L6gicas:
2
Titulos:
647
Leituras Fisicas: 0
Leituras Antecipadas:
b) Executando
SELECT
575
a consulta
com a utiliza9aO do operadar
titulo_id,
preco,
IN.
tipo
FROM Titulos
WHERE
Tempo
tipo
de execu9ao
N° de linhas aletadas:
N° de acessos
IN
( "Pedagogia",
da consulta:
3014
it tabela Titulos:
25
"llfedicina"
,"Psicologia");
110
Leituras L6gicas: 647
Leituras Fisicas:
a
Leituras Antecipadas: 575
Conclusao:
Ao contrario do que Plew e Stephens (2000) afirmam,
avalia e executa tanto
0
operador OR como
0
0
otimizador do SOL Server
predicado IN da mesma forma. Isto
fica demonstrado atrav';s da execu,ao das duas consultas, uma utilizando
operador OR e outra
0
predicado IN.
0
111
4.13. cLAUSULA
A utiliza9Bo
GROUP
da clausula
GROUP
lazendo
com que a tempo
deve-se
avaliar
Exemplo:
BY envolve
de resposta
com cuidado
Selecionar
pagamento
BY
a utiliza9ao
a codigo
da
grandes
da consulta
opera90es
das vendas onde a quantidade
SELECT
a quantidade
vend ida e a tipo
Qtd,
Tipo_pagto
FROM Vendas
>= 8 AND
WHERE
Qtd
GROUP
BY Livraria_id,
Tempo
de execu930
N° de linhas aletadas:
N° de acessos
Leituras
a tabela
Logicas:
607
Leituras
Fisicas:
0
Leituras
Antecipadas:
Data_ordem
da consulta:
4.623
Vendas:
539
>= "2001-05-01"
Qtd, Tipo_pagto;
7 s
de
for maior que 8 e a data de venda a
BY.
Livraria_id,
portanto,
da mesma.
livraria,
partir de 01/05/2001.
a) Com a utiliza91io de GROUP
de classilica9ao,
seja prejudicado,
112
b) Sem a utiliza,,~o
SELE
de GROUP
BY.
T Livraria_id,
Qtd,
Tipo~pagto
fROM Vendus
,1HERE Qtc
>~ 8 .~11DDat,,_ordem
Tempo de execu~ao
da consulta:
N° de linhas afetadas:
4.828
N° de acessos
a tabela
Leituras
Logicas:
Leituras
Fisicas:
Leituras
Antecipadas:
>~ "2001-05-01";
5 s
Vendas:
607
0
539
Conclusao:
o
usa da clausula
obtivesse
GROUP
GROUP
um desempenho
BYfoi
omitida.
BY
na primeira
consulta
muito inferior ao da segunda
fez com que a mesma
consulta,
onde a cl;\Usula
113
4.14. cLAUSULA HAVING
Plew e Slephens (2000) alirmam que a utiliza9aOda clausula HA VING deve ser
evitada,
pais
aumentando
Exemplo:
0
acreditam
que
a
mesma
provoca
urn
overhead
adicional,
tempo de execu~o da pesquisa.
Selecionar
c6digo
0
da livraria,
a quantidade
vend ida e
0
tipo de
pagamento das vendas onde a quantidade lor maior que 8 e a data de venda a
partir de 01/05/2001.
a) Sem a utiliza9ao de HAVING.
SELECT
FROM
Livraria
id,
Tipo_pagto,
SUM(Qtd)
Vendas
;IHERE Qtd
>~ 8 AND Data_ordem
>~ "2001-05-01"
Tipo~pagto = "30 dias"
GROUP
BY Livraria_id,
Tipo_pagto;
Tempo de execuC30 da consulta: 5 s
N°de linhas aletadas: 1.307
N° de acessos
a tabela
Leituras Logicas: 607
Leituras Fisicas: 539
Leituras Antecipadas: 0
Vendas:
AND
114
b) Com a uliliza9ao
SELECT
de HAVING.
Livraria_id,
Tipo-pagto,
SUM{Qtd)
FROl-I Vendas
WHERE
Qtd
GROUP
BY
Tipo_pagto
HAVING
Tempo
>% 8 AND
Llvrarla
de execul;3o
N° de linhas aleladas:
N° de acessos
Leiluras
ordem
>~ "2001-05-01"
Tlpo_pagto
~ "30
da consulta:
DIAS";
5 s
1.307
a labela
L6gicas:
Data
ld,
Vendas:
607
Leituras Fisicas: 539
Leiluras
Anlecipadas:
Conclusao:
Foi constatado,
atraves
utiliza a clausula
HAVING
da eXeCU9aO das duas
como quando
ambas consultas, inclusive retornando
consultas,
que tanto
nao se utiliza, a desempenho
0
mesmo plano de execu~ao.
quando
se
e igual para
115
4.15. STORED
A
utiliza9aO
desempenho
PROCEDURES
de
stored
procedures
de consultas
em um ambiente
estao pre-compilados
e contem
Exemplo:
c6digo
Selecionar
(procedimentos
melhora
0
de rede, vista que as mesmas
ja
um plano de execw;:ao
e nome
completo
armazenados)
guardado
na mem6ria.
dos empregados,
bem como
das
editoras em que trabalham, para as editoras que tiveram livros vendidos acima de
quatro unidades,
no m~s de agosto de 2001.
a) Sem a utiliza9aO de Stored Procedure.
SELECT
E.Emp_id
E.Sobrenome
fROM
"Codigo
"Nome
Empregado
E,
SELECT
distinct
FROM Titulos
NHERE
T,
T.Titulo
AND V. Data
Editora"
ED
Vendas V;
id
V.Titulo_id
BET~lEEN ("2001-08-01")
85
891
a tabela
Fisicas:
"
T.Editora_id
Ordem
N° de linhas afetadas:
L6gicas:
"
(
31") )
Leituras
"Nome
+
ED.Editora_id
=
IN
Tempo de execw;ao da consulta:
Leituras
E.Nome
> 4
AND V.Qtd
N° de acessos
ED. Nome
Empregado",
Editoras
~4HERE E.Editora_id
.7l.ND E. Editora_id
Empregado",
1.785
14
Leituras Antecipadas: 0
Editoras:
891
AND
("2001-08-
+
116
N° de aces
Leituras
a tabela
50S
Logicas:
Empregado:
94
Leituras
Fisicas:
Leituras
Antecipadas:
0
88
a tabela
N° de acessos
Leituras
Logicas:
Leituras
Ffsicas:
Vend as:
607
0
Leituras Antecipadas:
539
a tabela
N° de acessos
Leituras
Logicas:
Leituras
Fisicas:
Leituras
Antecipadas:
647
0
b) Com a utiliza~ao
CREATE
Titulos:
575
de Stored Procedure.
PROCEDURE
SELECT
E. Emp_ ict
E.Sobrenome
FROM
tvHERE
sp_premio_agosto
"Codigo
"Nome
Empregado
E.Editora_id
AND E.Editora_id
SELECT
1'1HERE T. Titulo
AND V Qtd
E. Nome
"Nome
+
"
Editora"
ED
ED.Editora
T.Editora
T, Vendas
id
id
id
V
V. Titulo_id
> 4
AND V.Data_Ordem
31") )
=
ED.Nome
IN (
distinct
FROM Titulos
Empregado",
Empregado",
E, Editoras
AS
BETVlEEN
1"2001-08-01")
AND
1"2001-08-
117
Tempo
de execuc;ao
da consulta:
N° de lin has aletadas:
N° de acessos
a tabela Editoras:
Leituras
L6gicas:
Leituras
Fisicas:
Leituras
Antecipadas:
2
Leituras
L6gicas:
Leituras
Fisicas:
Leituras
Antecipadas:
Leituras
L6gicas:
Fisicas:
Leituras
Antecipadas:
Leituras
Empregado:
94
Leituras
N° de acessos
0
0
a tabela
N° de acessos
891
1.785
a tabela
N° de acessos
85
891
88
Vendas:
607
0
a tabela
L6gicas:
539
Titulos:
647
Leituras Fisicas: 0
Leituras
Antecipadas:
575
Conclusao:
Pelo fatc de am bas consultas nao estarem sendo executadas em urn ambiente de
rede, no qual
0
tempo
gasto
para recompilar
a consulta
nao armazenada
mostra evidente, as resultados foram as mesmas para as duas consultas.
nao se
118
4.16. SELE<;AO
o
otimizador
DE UNIAo
geralmente
adota a melhor estrategia
de processar
uniOes (loop
aninhado, mesclagem au hash) em urna consulta. Entretanto, pode-se sugerir que
o mesmo
adote uma estrategia
Exemple:
Selecionar
diferente
daquela
escolhida
por ele.
as titulos dos livros, bern como seus respectivos
autores,
das editoras localizadas no Parana.
estrategia de unia.o.
a) Sem sugerir nenhuma
SELECT T.Titulo,
FROM Titulos
ON T. Titulo
id
ON TA.Autor_id
,IHERE
E. Estado
de execuc;ao
=
Leituras
L6gicas:
5.591
Fisicas:
1.037
Leituras Antecipadas:
Leituras
L6gicas:
Leituras
Fisicas:
Leituras
Antecipadas:
N° de acessos
Leituras
-
"PRo;
Autores:
L6gicas:
5.642
647
1.857
TituloAutor:
95
a tabela
16 s
0
a tabela
INNER
INNER
= E.Editora_id
1.857
a tabela
Leituras
N" de acessos
A.Autor_id
da consulta:
N° de linhas afetadas:
N° de acessos
JOIN TituloAutor
". TA. Titulo_id
ON T.Editora_id
Tempo
+ A.Sobrenome
A.Nome +
T INNER
0
Titulos:
2.811
TA
JOIN j'\utores
JOIN Editoras
A
E
119
Leituras
Fisicas:
Leituras
Antecipadas:
N° de acessos
Leituras
2
a tabela
Logicas:
a
Fisicas:
Leituras
Antecipadas:
113
a estrategia
SELECT
FROM
Editoras:
121
Leituras
b) Sugerindo
575
T.Titulo,
Titulos
T
ON T.Titulo
ON
TA.Autor_id
T.Editora_id
Tempo
(LOOP
de execu9ao
a tabela
Autores:
5.591
Leituras
Fisicas:
1.037
Leituras Antecipadas:
Logicas:
Leituras
Fisicas:
Leituras
Antecipadas:
1.857
0
a tabela
Leituras
17 s
1.857
Logicas:
N° de acessos
= "PR"
da consulta:
Leituras
Titulo.n..utor TA
id
= E.Editora_id
JOIN);
N° de linhas afetadas:
N° de acessos
JOIN
A.Autor_id
E.Estado
OPTION
+ " " + A.Sobrenome
A.Nome
INNER
id "" TA.Titulo
ON
WHERE
de uniao de loop aninhado.
TituloAutor:
5.642
95
0
2.811
INNER
INNER
JOIN
JOIN
Autores
Editoras
A
E
120
a tabela
N° de acessos
Editoras:
Leituras
L6gicas:
72.416
Leituras
Fisicas:
113
Leituras Antecipadas:
Leituras
L6gicas:
Leituras
Fisicas:
Leituras
Antecipadas:
c) Sugerindo
0
a tabela
N° de acessos
Tilulos:
647
2
575
a estrahlgia
de uniao de mesclagem.
SELECT T.Titulo,
FROM Titulo.
ILNome
T INNER
ON T.Titulo_id
=
ON T.Editora_id
E.Estado
OPTION
Tempo
N° de linhas aletadas:
a tabela
Leituras
L6gicas:
Leituras
Fisicas:
Leituras
Antecipadas:
N° de acessos
Leituras
JOIN
A.Autor
=
- "PRO
da consulta:
1.857
TituloAutor:
100
4
a tabela
L6gicas:
647
TituloAutor
94
Titulos:
12 s
INNER
id INNER
E.Editora_id
(t1ERGE JOIN);
de execu~ao
N° de acessos
+ " " + A.Sobrenome
TA.Titulo_id
ON TA.Autor_id
WHERE
36.208
TA
JOIN
Autore.
JOIN Editoras
A
E
121
Leituras Fisicas: 71
Leituras
Antecipadas:
Leituras
Logicas:
Leituras
Fisicas:
Antecipadas:
0
113
a tabela
N° de acessos
L6gicas:
Autores:
1.544
Leituras
Fisicas:
Leituras
Antecipadas:
d) Sugerindo
Editoras:
121
Leituras
Leituras
575
a tabela
N° de acessos
0
1.374
a estrategia
SELECT
de uniao par hash.
T.Titulo,
fROM Titulos
ON T.Titulo
A.Nome
T INNER
id
A.Autor
ON T.Editora_id
OPTION
Tempo
de
=
·PRo
(HASH JOIN);
execu~ao da consulta:
N° de linhas afetadas:
N" de acessos
a tabela
Leituras
L6gicas:
Leituras
Fisicas:
1.857
Autores:
666
2
Leituras Antecipadas:
594
5 s
INNER
id INNER
E.Editora_id
=
A.Sobrenome
JOIN TituloAutor
TA.Titulo_id
ON TA.Autor_id
WHERE E.Estado
+
+
TA
JOIN Autores
JOIN Editoras
A
E
122
N° de acessos
a tabela
Leituras
L6gicas:
Leituras
Fisicas:
1
Leituras Antecipadas:
N° de acessos
94
a tabela
Leituras
L6gicas:
Leituras
Fisicas:
Titulos:
647
0
Leituras Antecipadas:
N° de acessos
TituloAutor:
100
575
a tabela
Leituras
Logicas:
Leituras
Fisicas:
Editoras:
121
1
Leituras Antecipadas:
113
Conclusao:
Com base
nas execuyOes
otimizador
nem sempre
primeira
consulta,
onde
destas
escolhe
0
eXeCU9aO fica 11 segundos
55, obtido
particular
atraves
(quarta
escolhe
mais lento que
da estrategia
consulta).
consultas,
0
de uniao
Atraves
que tanto na estrategia
melhor
tra9ada
pelo otimizador
estrategias
iterac;:oes que extraem
realizam
verias
a tabela
da estrategia
as tabelas
rnais interna
sao
fata de que
Isto porque,
0
esta
de
e de
em
de uniao por hash,
a apenas
como
urn acesso,
na estrategia
superiores,
par diante,
na
tempo
que
de
pois ambas
uma linha da primeira
e assim
0
consulta
de resposta
para
sao reduzidas
as acessos
0
de uniao,
tempo
par hash
da utiliza9ao
nas leituras
de uniao.
a estrategia
uni30 de loop aninhado,
a utiliza para percorrer
fica evidente
a melhor estrategia
otimizador
os aces 50S as tabelas envolvidas
enquanto
quatro
tabela
acarretando
e
num
123
tempo maior de execuc;:ao. Apesar da estrategia
as tabelas envolvidas
uma (mica vez,
realizadas na tabela Autores
mesma tabela.
0
de uniao de mesclagem
acessar
numero de leituras 16gicas e antecipadas
e superior ao realizado pela estrategia
por hash na
124
4.17. cLAUSULA DISTINCT
Segundo McGehee (2002), GEHEE (2002), a clausula DISTINCT
overhead
adicional
na consulta,
conseqOentemente
mesma. Par isso, deve-.se utiliza-Ia
realmente
seja necessario.
Exemplo:
Selecionar
FROt<l
nome e sobrenome
DISTINCT
Nome,
dos autores
Sobrenome
Autores
WHERE Estado ~ "PR"
Tempo de execu~ao da consulta: 105
Numero de linhas afetadas: 3.577
N°de acessos
a tabela Autores:
Leituras Logicas: 10.742
Leituras Fisicas: 1.257
Leituras Antecipadas: 10
0
provoca um
desempenho
da
com extrema cuidado, apenas em casas ande
a) Utilizando DISTINCT.
SELECT
degrada
que residam
no Parana.
125
b) Nao utilizando
SELECT
FROM
Estado
de execuc;ao
Numero
N° de
Sobrenome
Autores
,IHERE
Tempo
DISTINCT.
Nome,
=
de linhas afetadas:
aces
50S
a tabela
Leituras
Logicas:
Leituras
Fisicas:
Leituras
Antecipadas:
"PR"
da consulta:
85
3.577
Autores:
10.742
1.257
10
Conclusao:
Apesar
de apresentarem
antecipadas,
overhead
a segunda
adicional
os mesmas
resultados
de leituras
16gicas, fisicas
consulta foi mais fapida do que a primeira
provocado
pela clllUsula
DISTINCT.
devido
e
ao
126
X SUBCONSULTA
4.18. JOIN
Segundo McGehee (2002), GEHEE (2002), se puder escolher entre a utiliza,ao
de uma JOIN ou de uma subconsulta,
o desempenho
de cada
e interessante testar os dois metodos, pOis
urn desses
metod as
pode
variar
para
determinada
consulta.
Exemplo:
Selecionar
nome
0
e sobrenome
dos autores
publicados.
a) Utilizando JOIN.
SELECT
FROM
ON
DISTINCT
Autores
nome, sobrenome
A INNER
T.Autor_id
=
JOIN
A.Autor_id
Tempo de execu,3o da consulta: 21 s
Numero de linhas aletadas: 20.611
N°de acessos
a tabela TituloAutor:
Leituras L6gicas: 100
Leituras Fisicas: 0
Leituras Antecipadas: 94
N° de acessos
a tabela
Autores:
Leituras Logicas: 1.546
Leituras Fisicas: 0
Leituras Antecipadas: 1.374
TituloAutor
T
que
possuem
tftulos
127
b) Utilizando
Subconsulta.
SELECT
nome,
50brenome
Autores
FROt>1
\;HERE
A
EXISTS
SELECT
T.Autor
id FROM TituloAutor
v1HERET.Autor_id
=
A.Autor_id)
Tempo
de execu~ao da consulta:
Numero
de linhas aletadas:
Logicas:
Fisicas:
Leituras
Antecipadas:
Leituras
20.611
100
Leituras
N° de acessos
12 s
it tabela TituloAutor:
N° de acessos
Leituras
T
0
94
it tabela Autores:
Logicas:
1.546
Leituras Fisicas: 0
Leituras Antecipadas:
1.374
Conclusao:
Utilizando
DISTINCT,
0
metoda
de subconsulta,
a qual produz
um overhead
foi desnecessaria
adicional
forma, para essa consulta em particular, obteve-se
segunda
consulta,
clausula
DISTINCT.
a qual se aproveita
do metoda
a existencia
na execu,ao
da clausula
da consulta.
Dessa
um melhor desempenho
da subconsulta
para eliminar
na
a
128
4.19
cLAUSULA
Segundo
NOT IN X cLAUSULAS
McGehee
(2002),
GEHEE
NOT EXISTS
(2002),
a utiliza9ao
I LEFT OUTER
da clausula
JOIN
NOT IN numa
consulta acarreta em urn loop aninhado, resultando em urn baixo desempenho da
consulta.
Par isso, deve-se
NOT EXISTS
Exemplo:
Selecionar
a categoria
a) Utilizando
NOT IN.
SELECT
das
DISTINCT
E.Nome
Ectitoras E
(SELECT
id
NOT
FROM Titulos
de execu9ao
T
"Pedagogia")
da consulta:
Numero de linhas aletadas: 8.428
N° de acessos
tabela Titulos: 2
a
Logicas:
1.294
Leituras Fisicas: 0
Leituras Antecipadas:
a tabela
N° de acessos
Leituras
Logicas:
Leituras
Fisicas:
735
Editoras:
121
0
Leituras Antecipadas:
b) Utilizando
IN
T.Editora_id
WHERE T.Tipo
Leituras
editoras
"Pedagogia".
~'lHERE E. Edi tora
Tempo
JOIN checando
nome
0
pertencentes
FROM
utilizar em seu lugar uma das seguintes
ou LEFT OUTER
113
NOT EXISTS.
6 s
;
a condi9ao
as
quais
clausulas:
NULL
nao
possuem
titulos
129
SELECT
FROM
DISTINCT
E.Nome
Editoras
E
\;HERE NOT EXISTS
(SELECT
FROM
T.Editora
Titulos
id
T
\'IHERE T. Edi tara
AND
T.Tipo
id
=
=
E. Edi tara _ id
"Pedagogia")i
Tempo de execu~ao da consulta: 5 5
Numero de linhas afetadas: 8.428
a tabela
N° de acessos
Editoras:
Leituras Logicas: 121
Leituras Fisicas: 1
Leituras Antecipadas: 113
N° de
a tabela Titulos:
aceSSDS
Leituras L6gicas: 647
Leituras
Fisicas:
0
Leituras Antecipadas: 575
c) Utilizando LEFT OUTER JOIN checando a condi.,ao NULL.
SELECT
DISTINCT
FROl-l (Editoras
LEfT
OUTER
ON
.l\.UD
JOIN
E.Nome
E
Titulos
(T.Editora_id
T.Tipo
=
l'IHERE T.Editora_id
T
= E.Editora
"Pedagogia"))
IS NULL;
Tempo de execu~ao da consulta: 55
id
130
Numero
de linhas aleladas:
N° de acessos
Leituras
a tabela
Logicas:
Leituras Fisicas:
Leituras
Leituras
647
a
Antecipadas:
a tabela
N" de aeessos
Logicas:
8.428
Titulos:
575
Editoras:
121
Leituras
Fisicas:
Leituras
Antecipadas:
1
113
Conclusao:
As duas
IN,
0
ultimas consultas comprovam, pelo fata de nao utilizarem a clausula NOT
melhor
desempenho,
consulta,
pais dessa
aninhado)
que seria causado
forma
cerea
de 1 segundo
evita-se
mais
fi'pido
urn processamento
pela clausula
NOT IN.
do que a primeira
desnecessiuio
(loop
5. CONCLUSAO
Este projeto atingiu com ~)(ito
tecnicas
de
otimiza9~o
prop6sito de melhorar
0
empregadas
0
objetivo geral, que fai
na construc;ao
de
0
de apresentar
consultas
SQl
com
0
desempenho dessas consultas.
Para isto, foram curnpridos todos as passos necessarios
seja, foram contemplados
as seguintes
abjetivos especificos:
esie tim, au
para
compreens~o
dos
fundamentos de banco de dados, aprendizado da linguagem SOL, implementa,ao
de consultas SOL, descri,ao
ferramentas
especificas
A grande
das tecnicas de otimiza,ao
para execuc;:ao e monitoramento
maioria das tecnicas
par seus respectivQs
significativamente
de otimizayao
autores, fai devidamente
tempo
0
de
execw;ao
de
das consultas.
pro pastas
aqui apresentadas,
comprovada
da
e utiliza,ao
consulta,
como eficaz e reduziu
contribuindo,
assim,
positivamente para 0 desernpenho da consulta.
No
confonne
entanto,
houve
tecnicas
de
previsto por seus respectivos
otimizac;ao
que
nao
S8
comportaram
auto res, isto e, nao corresponderam
a
expectativa de redu,ao do tempo empregado para a execu,;;o da consulta, ora
apresentando
0
mesmo tempo de execU(;~o, ora apresentando
urn tempo superior
de execu,ao.
Tambem
iguais obtidos
foram
apresentadas
comprovaram
tecnicas
as afirma90es
tecnicas de otimizayoes equivalentes
onde
feitas
os tempos
por seus
autores,
ou seja,
entre si.
Abaixo, uma tabela sintetizando as tecnicas de otimizayao,
respectivos autores e aproveitamentos:
de execuc;oes
bem como seus
132
Tabela
25 - Tabela
de tecnicas
de otimizayOes.
Aproveitamento
Autor
Tecnica
Plew e Stephens
Clausula
McGehee
Select'
Where
Sim
Sim
Clausulas
Sargable
Soukup
e Delaney
Soukup
e Delaney
; Indice Setorizado
Sim
Soukup
e Delaney
i Indice Nao-Setorizado
Sim
Soukup
e Delaney
Iindice
Sim
de Cobertura
Order By
Plew e Stephens
Clausula
Soukup
Ordem Atributos
e Delaney
Sim
Sim
no Indice
Sim
Plew e Stephens
Ordem T abelas na Junyao
Nao
Plew e Stephens
Condiyao
Nao
Soukup
Ansi Join x Join SlQ-92
e Delaney
mais Restritiva
Nao
In x Operador
Plew e Stephens
Operador
Plew e Stephens
Clausula
Group By
Sim
Plew e Stephens
Clausula
Having
Nao
Plew e Stephens
Siored Procedures
Sim
Soukup
Seleyao
de Uniao
Sim
McGehee
Clausula
Distincl
Sim
McGehee
Join x Subconsulta
McGehee
Clausula
e Delaney
Or
Nao
Sim
Nol In x Clausulas
Nol
Sim
Exisls / Left Ouler Join
Oessa
forma,
as
principais
aprimoramento
Este
Parana,
possam
este projeto
apresenta~se como uma importante
fonte de
area de banco de dad as, pais fornece, de maneira clara e objetiva,
pesquisa na
rnetodos
trabalho
sede
de
otimizay~o
do desempenho
Torres,
acessa-Io
estara
para
realmente
de consultas
disponivel
que
para consultas.
SQl
na biblioteca
prolessones,
eficazes
em ambiente
alunos
da
empregados
SQl
Universidade
e comunidade
no
Server.
Tuiuti
em
do
geral
133
5.1. TRABALHOS FUTUROS
Neste
projeto
fcram
construidas
e
analisadas
consultas
de
de forma mono-usuaca, em plalaforma Windows, ulilizando
(SELECT),
seleC;ao
0
SGBD
SOL Server.
Os trabalhos
futuros
relativQs a esse
projeto,
poderao
explorar
modos de consulta, como por exemplo consultas de inser,ao
atualiza,ao
consultas
(UPDA TE),
sejam
exclusao (DELETE),
executadas
em
abordados assuntos como transayoes
modo
Qutros
(INSERT),
ah'm de propiciar que essas
multi-usuario,
onde
poderao
ser
e bloqueios.
Esse projeto tambem abre caminhos para analise e otimiza,ao
de
consultas em outras plataformas (UNIX, LlNUX), bem como outros SGBD, como
por exemplo ORACLE, SYBASE, MYSOL e outros.
a
presente projeto esta sendo utilizado como referencia para
0
trabalho da
prof. Giulliana Martins de Souza Vicentin (integrante do corpo docente da
Universidade
Tuiuti
do
Parana):
"AvaliaC;ao
da
performance
de
sistemas
gerenciadores de bancos de dados sobre grandes bases de dados utilizando
ferramentas de Data Mining".
134
FONTES BIBLIOGRAFICAS
MCGEHEE, Brad M.. Transact-SOL SOL Server Perfomlance Tuning Tips, ago.
2000. Disponivel em: <http://www.sql-server-performance.comltransact_sql.asp>.
Acesso em: 24 mai. 2002.
DATE, C. J.. Introdu,ilo a sistemas de banco de dados. 7. ed. Rio de Janeiro:
Campus, 2000.
GUNDERLOY, Mike, JORDEN, Joseph L.
Dominando
0
SOL Server 2000. Sao
Paulo· Makron Books, 2001.
LAU, Henry. Microsoft SOL Server 7.0 - Perfonmance Tuning Guide.
PLEW, Ronald R., STEPHENS, Ryan K.. Aprenda em 24 horas SOL 2. ed. Rio de
Janeiro. Campus, 2000.
REZENDE, Denis Alcides, Engenharia de software e sistemas de infonma,oes.
Rio de Janeiro: Brasport, 1999.
SILBERSCHATZ, Abraham, KORTH, Henry F., SUDARSHAN, S.. Sistemas de
Banco de Dados. 3. ed. sao Paulo: Makron Books, 1999.
SOUKUP,
Ron,
DELANEY,
Kalen,
Server 7.0. Rio de Janeiro: Campus, 1999.
Desvendando
0
Microsoft
SOL