O que é um Índice?

 

No Oracle, um índice é uma estrutura de dados que melhora a velocidade das operações de consulta em uma tabela. Índices são criados em uma ou mais colunas de uma tabela e são usados pelo Oracle para localizar rapidamente linhas específicas sem precisar verificar todas as linhas da tabela.

Vantagens de Usar Índices

  • Aumento da Performance: Índices podem melhorar significativamente a performance das consultas, especialmente em tabelas grandes.
  • Acesso Rápido aos Dados: Eles permitem acesso mais rápido a registros específicos, reduzindo o tempo de resposta.
  • Otimização de Consultas: Índices ajudam o otimizador de consultas do Oracle a gerar planos de execução mais eficientes.
  • Redução de Operações de I/O: Como os índices ajudam a localizar os dados mais rapidamente, eles podem reduzir significativamente o número de operações de leitura em disco.
  • Facilitação na Ordenação: Índices podem ser utilizados para acelerar a ordenação de registros em uma consulta.

Tipos de Índices

Índice B-tree

É o tipo mais comum, adequado para a maioria das consultas. Ele organiza os dados em uma estrutura de árvore balanceada. Veja um exemplo:

 

CREATE INDEX idx_func_nome ON funcionarios (nome);

Neste exemplo, o índice idx_func_nome é criado na coluna nome da tabela funcionarios.

 


Índice Bitmap

Usado para colunas com poucos valores distintos e alta cardinalidade. É eficiente para consultas de baixa seletividade. Veja um exemplo:

 

CREATE BITMAP INDEX idx_func_status ON funcionarios (status);

Aqui, o índice idx_func_status é criado na coluna status da tabela funcionarios.

 

Observação: Certifique-se de que a funcionalidade de índices bitmap está habilitada no seu banco de dados Oracle. Para verificar se os índices bitmap estão habilitados, execute a seguinte consulta:

 

SELECT * FROM v$option WHERE parameter = 'Bit-mapped indexes';

Se a consulta retornar um valor indicando que a opção está habilitada, você pode usar índices bitmap em seu banco de dados.

 


Índice Único

Garante a unicidade dos valores na coluna indexada, prevenindo duplicatas. Veja um exemplo:

 

CREATE UNIQUE INDEX idx_func_email ON funcionarios (email);

Este índice garante que não haverá duplicatas na coluna email da tabela funcionarios.

 


Índice de Função

Indexa o resultado de uma função ou expressão, útil para consultas que usam funções na cláusula WHERE. Veja um exemplo:

 

CREATE INDEX idx_func_upper_nome ON funcionarios (UPPER(nome));

Este índice permite consultas rápidas usando a função UPPER na coluna nome.

 


Índice Composto

Indexa duas ou mais colunas juntas, permitindo consultas que filtram por múltiplas colunas. Veja um exemplo:

 

CREATE INDEX idx_func_nome_salario ON funcionarios (nome, salario);

Neste exemplo, o índice idx_func_nome_salario é criado nas colunas nome e salario da tabela funcionarios.

 


Índice Clustered

Organiza fisicamente os dados na tabela de acordo com a ordem do índice. No Oracle, isso é implementado através de clusters. Veja um exemplo:

 

CREATE CLUSTER emp_dept_cluster (codigo_departamento NUMBER(4));
CREATE INDEX idx_emp_dept ON CLUSTER emp_dept_cluster;

CREATE TABLE empresas (
  codigo_empregado NUMBER(6),
  nome_primeiro VARCHAR2(20),
  nome_sobrenome VARCHAR2(25),
  codigo_departamento NUMBER(4)
) CLUSTER emp_dept_cluster (codigo_departamento);

Aqui, a tabela empresas é fisicamente organizada de acordo com a coluna codigo_departamento usando um cluster.

 

Observação: Um índice clustered no Oracle é implementado através de clusters, que organizam fisicamente os dados com base na coluna indexada. Isso pode melhorar a eficiência das consultas que filtram ou ordenam dados por essa coluna, reduzindo a necessidade de leituras adicionais.

Consultando Dados com Índices

 

O Oracle utiliza índices automaticamente ao executar consultas, se o otimizador de consultas determinar que o uso do índice será benéfico. Veja um exemplo de consulta que pode se beneficiar de um índice:

 

SELECT id, nome, email, salario, status
  FROM funcionarios
 WHERE nome LIKE '%an%';

Observação: Embora o uso de LIKE com curingas % em ambas as extremidades da string ('%an%') possa não aproveitar totalmente os índices, consultas como nome LIKE 'an%' são mais propensas a se beneficiar de índices. Avalie a necessidade de índices de função (function-based indexes) se sua aplicação frequentemente usa expressões como UPPER(nome) LIKE 'AN%'.

Monitorando e Mantendo Índices

 

Monitorar a eficiência dos índices é crucial para garantir que eles estejam contribuindo para o desempenho das consultas. Aqui estão algumas abordagens e ferramentas para monitorar e manter a eficiência dos índices no Oracle:

Coletar Estatísticas:

O comando a seguir coleta estatísticas para a tabela especificada, ajudando o otimizador de consultas a fazer escolhas mais eficientes:

 

EXEC DBMS_STATS.GATHER_TABLE_STATS('NOME_DO_ESQUEMA', 'NOME_DA_TABELA');
Verificar Estatísticas Coletadas:

Para verificar as estatísticas coletadas, consulte as visualizações do dicionário de dados, como DBA_TABLES e DBA_INDEXES:

  • Estatísticas da Tabela:
    SELECT table_name, num_rows, blocks, empty_blocks, avg_space, chain_cnt
      FROM dba_tables
     WHERE table_name = 'NOME_DA_TABELA';
    
  • Estatísticas do Índice:
    SELECT index_name,
           table_name,
           uniqueness,
           num_rows,
           leaf_blocks,
           distinct_keys
      FROM dba_indexes
     WHERE table_name = 'NOME_DA_TABELA';
    
Visualizar o Plano de Execução:

Para entender como os índices estão sendo utilizados pelas consultas, examine o plano de execução das queries:

 

EXPLAIN PLAN FOR SELECT * FROM funcionarios WHERE status = 'ATIVO';
		
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
Monitorar o Desempenho dos Índices:

Utilize a visualização V$SEGMENT_STATISTICS para monitorar a atividade dos índices e tabelas:

 

SELECT object_name, statistic_name, value
  FROM v$segment_statistics
 WHERE statistic_name IN ('logical reads', 'physical reads')
   AND object_name = 'NOME_DO_INDICE_OU_NOME_DA_TABELA';

Essa visualização fornecem informações detalhadas sobre o desempenho e uso dos índices e tabelas, permitindo ajustes e manutenção eficazes.

Atualizando Índices

 

Índices são atualizados automaticamente quando os dados nas colunas indexadas são modificados. No entanto, é importante monitorar e manter os índices para garantir que eles continuem a melhorar a performance.

Recompilando e Analisando Índices

 

Recompilar e analisar índices pode ajudar a manter a eficiência. Use os comandos ALTER INDEX e ANALYZE:

 

ALTER INDEX idx_func_nome REBUILD; 
		
ANALYZE INDEX idx_func_nome VALIDATE STRUCTURE;

Observação: Para verificar o status da análise dos índices, consulte a visualização DBA_INDEXES:

 

SELECT index_name, status, num_rows, leaf_blocks
  FROM dba_indexes
 WHERE index_name = 'IDX_FUNC_NOME';

Particionamento de Índices

 

Para tabelas muito grandes, particionar índices pode melhorar significativamente a performance e a gerenciabilidade. O particionamento de índices permite que o Oracle divida o índice em várias partes menores, cada uma correspondendo a uma partição da tabela. Aqui está um exemplo de criação de um índice particionado:

 

CREATE INDEX idx_func_nome_part
ON funcionarios (nome)
LOCAL (PARTITION p1 VALUES LESS THAN ('M'),
       PARTITION p2 VALUES LESS THAN (MAXVALUE));

 

Observações Importantes:

  1. Particionamento da Tabela:
    • A tabela para a qual o índice é particionado também deve estar particionada de forma correspondente. Caso contrário, o particionamento do índice não terá efeito.
    • O particionamento da tabela deve ser configurado antes da criação do índice particionado para garantir a consistência e eficiência do particionamento.
  2. Habilitação do Recurso de Particionamento:
    • O particionamento de índices é um recurso avançado que deve ser habilitado na instância do Oracle. Certifique-se de que sua versão do Oracle e a configuração do banco de dados suportam particionamento de índices.
    • Verifique se o particionamento está ativado e disponível no banco de dados com a consulta:
      SELECT * FROM v$option WHERE parameter = 'Partitioning';
    • Se o recurso não estiver habilitado, você pode precisar ajustar a configuração ou considerar alternativas para gerenciar grandes volumes de dados.

O particionamento de índices pode trazer benefícios significativos para a performance em tabelas grandes, mas é crucial garantir que a tabela e o banco de dados estejam corretamente configurados para suportar esse recurso.

Excluindo um Índice

 

Se um índice não for mais necessário, ele pode ser excluído com o comando DROP INDEX:

 

DROP INDEX idx_func_nome;

Considerações Finais

 

Índices são uma ferramenta poderosa no Oracle para otimizar a performance das consultas e melhorar a eficiência do acesso aos dados. No entanto, é importante entender as melhores práticas e monitorar o desempenho para evitar degradação causada por índices desnecessários ou mal configurados.

 

Se quiser saber mais, veja a documentação oficial do Oracle sobre Índices para obter informações detalhadas e exemplos práticos.

 

Se você tiver alguma dúvida ou sugestão, deixe um comentário abaixo!

Deixe um comentário

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

Rolar para cima