Junções e
Índices em Tabelas
Prof. Fernanda Baião
[email protected]
SGBD Considerados
‰
MySQL (http://www.mysql.org)
– SGBD gratuito e simples, sem muitos recursos avançados
– Fácil de instalar e utilizar
‰
PostgresSQL (http://www.postgresql.org)
– SGBD gratuito e com muitos recursos
– Instalação complexa (especialmente no MS Windows)
‰
SQL Server (http://www.microsoft.com/sql/default.asp)
– SGBD com muitos recursos para o ambiente Windows
– Pago, mas possui uma versão gratuita (porém limitada)
‰
Oracle (http://www.oracle.com)
– SGBD com muitos recursos para diversos ambientes
– Também possui uma versão gratuita com tempo limitado
SQL – Tabelas Exemplo
TbAutor
TbAutor
ID
ID
Nome
Nome
TbLivro
TbLivro
ID
ID
Nome
Nome
ISBN
ISBN
IDAutor
IDAutor
IDEditora
IDEditora
TbEditora
TbEditora
ID
ID
Nome
Nome
CNPJ
CNPJ
TbEstoqueLivros
TbEstoqueLivros
TbLoja
TbLoja
ID
ID
IDLoja
IDLoja
IDLivro
IDLivro
Estoque
Estoque
ID
ID
Nome
Nome
CNPJ
CNPJ
Endereco
Endereco
TbVendaLivro
TbVendaLivro
ID
ID
IDCliente
IDCliente
Data
Data
IDLoja
IDLoja
IDLivro
IDLivro
Quantidade
Quantidade
TbCliente
TbCliente
ID
ID
Nome
Nome
Endereco
Endereco
Telefone
Telefone
Índices
‰
Índices são utilizados para aumentar o desempenho
– Um índice permite que o SGBD realize consultas por
determinados registros de forma muito mais rápida do que
seria feito sem um índice
– Porém os índices acrescentam um custo às operações de
inserção, atualização e remoção dos registros de uma tabela
– Sendo assim, os índices devem ser criados apenas para as
colunas mais utilizadas nas consultas do sistema
‰
Índices e consultas
– Campos que participam com freqüência de comandos WHERE
geralmente contém índices
– Imagine uma consulta onde o SGBD deve procurar todos os
elementos onde um campo é igual a uma constante
Índices
‰
Índices e consultas
– No caso da consulta abaixo, se não houver nenhum índice, o
SGBD deve percorrer todos os registros da tabela de livros,
identificando aqueles que possuem determinado autor
– Este método é claramente ineficiente quando a tabela possui
muitos registros
– Construindo-se um índice sobre esta coluna (IDAutor), o SGBD
pode realizar uma consulta mais otimizada sobre a tabela
– Se o índice está ordenado pela coluna, por exemplo, o SGBD
pode realizar uma busca binária
SELECT
SELECT *
*
FROM
FROM TbLivro
TbLivro
WHERE
WHERE IDAutor
IDAutor =
= 90
90
Criando Índices
‰
O comando CREATE INDEX
– Este comando cria um novo índice em uma tabela
– Todo índice deve ter um nome e indicar a tabela indexada
– O comando também deve indicar os campos indexados (entre
parênteses, após o nome da tabela)
– Uma vez criado o índice o SGBD o atualiza automaticamente
após a execução de comandos sobre suas tabelas
‰
Muitos índices são criados automaticamente pelo SGBD
– Como exemplo, sempre que criamos uma restrição de verificação
de valor, o SGBD cria um índice para facilitar o tratamento desta
restrição nas operações de inserção e atualização
CREATE
CREATE INDEX
INDEX livro_autor_index
livro_autor_index ON
ON TbLivro
TbLivro (IDAutor)
(IDAutor)
Removendo Índices
‰
O comando DROP INDEX
– Este comando remove um índice do banco de dados
– O comando recebe o nome do índice desejado
DROP
DROP INDEX
INDEX livro_autor_index
livro_autor_index
Índices de Múltiplas Colunas
‰
Um índice pode conter mais que uma coluna em uma tabela
– Índices com mais de uma coluna são utilizados quando duas ou
mais colunas são utilizadas em consultas conjuntivas (ligadas
por AND)
– Neste caso, as colunas aparecem separadas por vírgulas dentro
dos parênteses no comando de criação de índices
– Deve-se ter um critério para a definição destes índices, visto
que o custo de sua atualização é relativamente alto
CREATE
CREATE INDEX
INDEX livro_diversos_index
livro_diversos_index ON
ON TbLivro
TbLivro (Nome,
(Nome, IDAutor)
IDAutor)
Índices Únicos
‰
Índices únicos são utilizados em restrições de unicidade
– Estes índices são utilizados nas chaves primárias e nas colunas
com restrições de unicidade (UNIQUE)
– Um detalhe: valores nulos não são manipulados pelos índices
únicos
– Assim, dois registros da tabela podem conter valores nulos em
um campo com restrição de unicidade sem que isto gere erro
CREATE
CREATE UNIQUE
UNIQUE INDEX
INDEX livro_nome_index
livro_nome_index ON
ON TbLivro
TbLivro (Nome)
(Nome)
Índices Clusterizados
‰
Manipulação física da tabela
– Índices normalmente são armazenados como estruturas a parte
da tabela, mantendo ponteiros que referenciam os registros da
tabela segundo uma ordenação
– Os índices clusterizados, por outro lado, exigem a reorganização
física da tabela, fazendo com que registros consecutivos na
tabela apresentem a ordem determinada pelo índice
– Índices clusterizados impõem um alto custo da inserção e na
atualização de registros, embora acelerem ainda mais as
consultas
– Como só pode haver uma ordenação física dos registros de uma
tabela, só pode haver um índice clusterizado por tabela
Regras para a Criação de Índices
‰
Índices devem ser criados para
–
–
–
–
–
‰
Colunas
Colunas
Colunas
Colunas
Colunas
que
que
que
que
que
componham a chave primária da tabela
participam de cláusulas de ordenação (ORDER BY)
são utilizadas em junções
atendem a restrições de verificação de valor
são comumente analisadas em cláusulas WHERE
Índices não devem ser criados para
– Tabelas com poucos registros
– Colunas que são raramente referenciadas em consultas
– Tabelas que recebam muitos comandos de atualização
Junções
‰
Uma mesma consulta pode acessar diversas tabelas
– Estas consultas decorrem do processo de normalização, onde
um conjunto de informações é partido entre diversas tabelas
– Estas consultas geralmente possuem junções (joins)
– Considere, por exemplo, a consulta de todos os livros de um
determinado autor
– Devemos relacionar a chave estrangeira que indica o autor de
cada livro com a chave principal do livro
– Este relacionamento pode ser realizado através da consulta
abaixo
– Observe que como o campo ID existe nas duas tabelas, ele
deve ser precedido pelo nome da tabela e um ponto
SELECT
SELECT *
*
FROM
FROM TbLivro,
TbLivro, TbAutor
TbAutor
WHERE
WHERE IDAutor
IDAutor =
= TbAutor.ID
TbAutor.ID
Junções
‰
As junções podem ser escritas pelo comando INNER JOIN
– Este comando indica que a tabela principal da consulta, dada
no comando FROM, está relacionada com outras tabelas
– Ele pode ser utilizado diversas vezes em uma mesma consulta,
cada comando relacionando o resultado gerado pelo anterior
com outras tabelas do banco de dados
– Uma condição indica quando a tabela associada (junção) deve
ser incluída nos resultados da consulta
– A condição pode usar qualquer operador de comparação (>,
>=, <, <=, = e <>) e operadores lógicos (AND, OR, NOT)
– OBS: este comando não está disponível em todas as versões de
todos os bancos de dados !
SELECT
SELECT *
*
FROM
FROM TbLivro
TbLivro
INNER
INNER JOIN
JOIN TbAutor
TbAutor ON
ON TbAutor.ID
TbAutor.ID =
= TbLivro.IDAutor
TbLivro.IDAutor
Produto Cartesiano
‰
Características do produto cartesiano (CROSS JOIN)
– Operação de junção que não envolve nenhuma condição de
junção entre as 2 tabelas
– Sejam duas tabelas: T1 com N registros e T2 com M registros
– O resultado da junção terá todas as colunas de T1 e T2
– Para cada registro de T1 serão gerados M registros no
resultado da junção (um para cada registro de T2)
– O resultado da junção conterá N * M registros, contendo todas
as combinações entre registros de T1 1 T2
– O produto cartesiano é equivalente a colocar o nome das duas
tabelas no comando FROM, sem um operador de junção entre
elas
SELECT
SELECT *
*
FROM
FROM TbLivro
TbLivro
CROSS
CROSS JOIN
JOIN TbAutor
TbAutor
SELECT
SELECT *
*
FROM
FROM TbLivro,
TbLivro, TbAutor
TbAutor
Tipos de Junção
‰
INNER JOIN
– Possui uma condição relacionando as duas tabelas que
participam na junção
– Para cada registro de T1 o resultado da junção conterá um
registro para cada elemento de T2 que atenda a condição
‰
INNER JOIN ... USING (...)
– O comando USING recebe uma lista de colunas que devem
estar presentes nas duas tabelas onde ocorrerá a junção
– O comando é equivalente a uma condição de igualdade dos
valores das colunas nas duas tabelas
‰
NATURAL INNER JOIN
– O comando NATURAL é equivalente a um comando USING sem
a indicação das colunas analisadas nas tabelas
– O comando NATURAL determina as colunas que ocorrem nas
duas tabelas e monta a lista USING com estas colunas
Tipos de Junção
T1
SELECT
SELECT *
*
FROM
FROM T1
T1
INNER
INNER JOIN
JOIN T2
T2 ON
ON T1.a
T1.a =
= T2.b
T2.b
a
T2
b
c
d
a
b
c
SELECT
SELECT *
*
FROM
FROM T1
T1
INNER
INNER JOIN
JOIN T2
T2 USING
USING (a,
(a, b)
b)
SELECT
SELECT *
*
FROM
FROM T1
T1
INNER
INNER JOIN
JOIN T2
T2 ON
ON (T1.a
(T1.a =
= T2.a)
T2.a) AND
AND
(T1.b
(T1.b =
= T2.b)
T2.b)
SELECT
SELECT *
*
FROM
FROM T1
T1
NATURAL
NATURAL INNER
INNER JOIN
JOIN T2
T2
SELECT
SELECT *
*
FROM
FROM T1
T1
INNER
INNER JOIN
JOIN T2
T2 ON
ON (T1.a
(T1.a =
= T2.a)
T2.a) AND
AND
(T1.b
(T1.b =
= T2.b)
T2.b) AND
AND (T1.c
(T1.c =
= T2.c)
T2.c)
Tipos de Junção
‰
LEFT OUTER JOIN
– Primeiro um INNER JOIN é realizado
– Em seguida, para cada registro da tabela T1 que não
satisfaz a condição de junção com nenhum registro de
T2, um registro é incluído nos resultados de junção com
valores nulos nas colunas T2
– O resultado da junção conterá pelo menos um registro
para cada registro de T1
‰
RIGHT OUTER JOIN
– O mesmo que o LEFT OUTER JOIN, porém invertendo o
comportamento da tabela T1 para T2
Tipos de Junção
‰
Existe uma outra notação para OUTER JOINs
– A notação com asteriscos pode ser utilizada em condições
da cláusulas WHERE
SELECT
SELECT **
FROM
FROM T1
T1
LEFT
LEFT OUTER
OUTER JOIN
JOIN T2
T2 ON
ON T1.a
T1.a =
= T2.a
T2.a
SELECT
SELECT **
FROM
FROM T1,
T1, T2
T2
WHERE
WHERE T1.a
T1.a *=
*= T2.a
T2.a
SELECT
SELECT **
FROM
FROM T1
T1
RIGHT
RIGHT OUTER
OUTER JOIN
JOIN T2
T2 ON
ON T1.a
T1.a =
= T2.a
T2.a
SELECT
SELECT **
FROM
FROM T1,
T1, T2
T2
WHERE
WHERE T1.a
T1.a =*
=* T2.a
T2.a
Tipos de Junção
‰
FULL OUTER JOIN
– Primeiro um INNER JOIN é realizado
– Em seguida, para cada registro da tabela T1 que não
satisfaz a condição de junção com nenhum registro de
T2, um registro é incluído nos resultados de junção com
valores nulos nas colunas T2
– Finalmente, para cada registro da tabela T2 que não
satisfaz a condição de junção com nenhum registro de
T1, um registro é incluído nos resultados de junção com
valores nulos nas colunas T1
– O resultado da junção conterá pelo menos um registro
para cada registro de T1 e T2