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