Otimizando consultas no Oracle utilizando índices

Veja neste artigo como realizar a otimização de consultas à bancos de dados Oracle através da utilização do índices.


Figura 1: Aprenda a utilizar índice no Oracle para otimizar consultas

Pelo fato de ser um assunto extremamente importante nos dias atuais, principalmente nas organizações que possuem um grande volume de dados e muitos acessos simultaneamente, sabemos que uma consulta mal projetada pode trazer lentidão ao servidor ou até mesmo fazer com que o servidor permaneça fora do ar por um período, deixando o usuário impossibilitado de utilizar algum serviço do sistema ou site, gerando prejuízo para a organização. E otimizar uma consulta nem sempre é tão simples como se pode imaginar, existem diversos fatores que integram a otimização de um banco de dados, um deles é o Índice.

Utilizamos o termo índice ou index para fazer referência a um recurso do Banco de Dados que possibilita que uma consulta seja retornada com uma maior rapidez, entre outras palavras, índices são estruturas opcionais adicionadas ao(s) campo(s) de uma tabela, com o objetivo de agilizar a procura dos dados numa tabela que satisfaça a uma determinada condição. A utilização do índice afeta diretamente a otimização de consultas, sendo então, a tentativa de melhorar ao máximo o tempo das respostas às instruções e é uma maneira de reduzir o acesso ao disco (I/O). Se não houver nenhum índice na tabela que está sendo consultada, o Oracle irá executar o comando chamado de "TABLE ACCESS FULL", ou seja, serão lidas linha a linha da tabela até encontrar o item solicitado, resultando no acesso total à tabela e possivelmente num maior gasto de tempo para retornar o resultado da consulta se comparado com o uso de índices.

O índice leva o acesso diretamente à linha da tabela onde se encontra o dado requisitado, evitando o gasto de tempo com a leitura de todos os dados da tabela. Porém, o índice deve ser utilizado adequadamente, pois existem campos da tabela onde o índice não irá favorecer a consulta. Um exemplo bem comum para demonstrar onde há a necessidade ou não de um index, é a utilização de um índice de um livro qualquer. Pense num formato de um livro ou apostila que contenha um índice remissivo (lista onde contém os assuntos, figuras, quadros e suas respectivas páginas onde eles aparecem), este índice remissivo irá fazer com que o leitor economize tempo ao tentar encontrar um assunto específico, pois o leitor ao invés de folhear lentamente, palavra por palavra, cada página deste livro ou apostila, ele irá localizar pelo índice em qual página está a informação que ele está buscando, otimizando então a sua consulta. Agora pense, se este livro ou apostila contém apenas 3 assuntos e cada assunto está numa única página, rapidamente eu conseguirei encontrar a informação desejada sem a necessidade de utilizar um índice remissivo para isso, ou seja, eu poderia gastar mais tempo consultando o índice e depois encontrando a página do que folhear diretamente o livro ou apostila página a página e encontrar a informação desejada. É neste ponto que verificamos a real necessidade de utilizar um índice na tabela ou não. Em campos que não irão sofrer muita variação como o campo "sexo" é recomendável a não utilização de índices, pois o índice no banco de dados ocupa espaço para armazenamento e, além disso, um índice precisa ser atualizado sempre que o dado ao qual está se referindo o índice é alterado. Conclui-se que o índice pode ser vantajoso se utilizado em campos de uso estritamente necessário, pois aumenta o custo de inserção e atualização.

A instrução SQL enviada ao Banco de Dados Oracle não requer nenhuma mudança na redação de qualquer instrução quando se está usando o índice num dos campos solicitados na consulta. O índice pode ser inserido ou excluído a qualquer momento sem interferir nos dados da tabela, porque a função dele é apenas servir como um indicador para a localização da informação solicitada. Depois de criado um índice, o Oracle faz a manutenção automática do mesmo e sua devida utilização, sem precisar da intervenção do usuário para a realização desta tarefa. Mudanças dos dados da tabela, como inserção, alteração ou exclusão de linhas (tuplas), são automaticamente propagados para todos os índices que serão afetados por essas modificações. Caso todos os índices da tabela sejam excluídos, as funcionalidades do banco de dados não serão alteradas, a única mudança ocorrerá na performance.

Podem ser criados mais de um índice para uma única tabela, ou seja, teremos dois ou mais campos com índice dentro de uma mesmo tabela. Voltemos ao exemplo do livro ou apostila, imagine que neste livro há dois índices, um índice contém somente os assuntos e suas respectivas páginas, e há um outro índice onde encontra-se apenas as figuras, quadros e símbolos com suas respectivas páginas onde eles aparecem. Se considerarmos que este livro tem 500 páginas, este tipo de índice composto será mais favorável, haja vista que o tempo para encontrar apenas uma figura será reduzido se comparado com a utilização de um único índice simples.

A sintaxe de criação de índices no Oracle é bem simples. Será necessário informar um nome para o índice, o nome da tabela e o campo onde você deseja que o índice seja criado.

Listagem 1: Exemplo da criação de índice no Oracle

CREATE INDEX nome_do_indice ON nome_da_tabela (nome_do_campo_onde_ficara_o_indice);

Como comentado anteriormente, pode ser necessária a criação de índices em mais de um campo da tabela, para isso, precisamos definir um nome para o índice, o nome da tabela e os campos onde você deseja que o índice seja criado.

Listagem 2: Exemplo da criação de índice no Oracle em mais de um campo na tabela

CREATE INDEX nome_do_indice ON nome_da_tabela (nome_do_campo_onde_ficara_o_indice, nome_do_campo_onde_ficara_o_indice);

Se houver a necessidade de mudança no nome do índice, podemos fazer essa alteração com o nome do índice atual e o nome para o novo índice.

Listagem 3: Exemplo da alteração de índice no Oracle

ALTER INDEX nome_do_indice_atual RENAME TO nome_do_novo_indice;

É possível examinar a quantidade de índices que há em uma determinada tabela. Lembrando que se sua tabela tem mais de 5 índices, é necessário ficar alerta se há a real necessidade dos mesmos, pois o excesso de índices também pode prejudicar a otimização das consultas.

Listagem 4: Exemplo para checar a quantidade de índices em uma tabela no Oracle

SELECT nome_da_tabela as "NOME DA TABELA", count(*) as "QTDE. DE INDICES" FROM dba_indexes WHERE owner=''''USUARIO'''' GROUP BY nome_da_tabela;

Podemos excluir um índice no Oracle sem qualquer prejuízo ao funcionamento do banco de dados, porque os índices são lógica e fisicamente independentes da tabela que indexam. E ao excluir uma tabela que contém um índice, automaticamente este índice também será deletado.

Listagem 5: Exemplo da exclusão de índice no Oracle

DROP INDEX nome_do_indice;

Existe a possibilidade de obrigar o SGBD a utilizar um índice numa consulta através de linha de comando.

Listagem 6: Exemplo de uma consulta forçando a utilização de índice no Oracle

SELECT INDEX (nome_do_indice) campos_da_tabela FROM nome_dos_campos WHERE cláusulas;

E por aqui eu finalizo este artigo, no qual vimos algumas dos benefícios que nos traz o uso de índices e como utilizá-los no Oracle. Desejo que possamos nos encontrar nos próximos artigos.

Um grande abraço.

Artigos relacionados