
Anderson Graf
Oracle Virtual Index ou Fake Index
Atualizado: 18 de ago. de 2021
O gerenciamento e ajuste de desempenho de consultas no banco de dados (tuning) é uma tarefa que requer conhecimento técnico e investimento de tempo considerável.
A criação de índices faz parte do dia a dia do tuning de consultas e algumas vezes pode ser uma tarefa um tanto quanto demorada, principalmente se o mesmo estiver sendo criado em tabelas muito grandes.
Levando em consideração que toda alteração deve ser testada antes de ser aplicada em produção, a criação de um índice não foge à regra.
Afim de reduzir o tempo gasto no processo de validação do índice criado, podemos utilizar a opção de criar um virtual index ou fake index. Desta forma podemos validar se o índice criado será utilizado pelo otimizador, sem termos realmente criado o mesmo. Ou seja, estamos reduzindo o tempo gasto na criação do índice para validação da utilização do mesmo pelo otimizador.
Um virtual index ou fake index é um índice cuja definição existe no dicionário de dados, porém o mesmo não possui segmentos criados. O principal propósito de um virtual index é simular a existência do mesmo sem a necessidade de realmente cria-lo.
Esta ação permite a execução de um explain para validação da utilização do índice, bem como verificarmos se o mesmo não terá um impacto negativo no plano de execução sem que ele realmente exista.
Para ilustrar seu funcionamento foi criado o seguinte cenário:
1) Tabela de teste
2) Explain de uma query aleatória sobre a tabela
3) Criando um índice virtual (fake) sobre a coluna de pesquisa
4) Confirmando que não existem segmentos criados para o índice da mesma forma que ele não consta na dba_indexes, mas é listado como qualquer outro objeto na dba_objects.
5) Reexecutando a query para verificar a utilização do índice
Observe que o index ainda não foi utilizado, isto ocorre pois é preciso ajustar o parâmetro _USE_NOSEGMENT_INDEXES para true na sessão.
6) Ajustando o parâmetro _USE_NOSEGMENT_INDEXES para TRUE
7) Reexecutando a query
Uma vez setado o parâmetro oculto o otimizador passa a utilizar o virtual index criado para a tabela.
Se a query for executada a partir de outra sessão, execute o “alter session” para o otimizador utilizar o virtual índice.
Algumas considerações sobre os índices virtuais ou fakes indexes:
É possível realizar analyze do virtual index:
Não é possível realizar rebuild de um virtual index. O erro ORA-8114 será gerado:
É possível remover o virtual index normalmente como qualquer outro índice:
Nas versões 11.2.0.3 e 11.2.0.4 existe a confirmação do BUG 18490543 que pode ocorrer e impedir o MOVE da tabela (ALTER TABLE .. MOVE). Quando o BUG ocorrer o processo de move será cancelado com o erro ORA-600 [25027][0][0]. Uma workaround é remover os índices virtuais antes do MOVE e recria-los (se necessário) após ou aplicar o patch/migrar para as versões onde o BUG foi corrigido (12.1.0.2 – Server Patch Set e 12.2.0.1 – Base Release).
Referências:
Virtual Indexes (Doc ID 1401046.1) Bug 18490543 - ORA-600 [25027][0][0] from ALTER TABLE .. MOVE with nosegment index (Doc ID 18490543.8)
Co-autor: Jhonata Lamim