Quando devo reconstruir ou "fazer REBUILD" de índices?

Olá pessoal,
 

     No artigo de hoje vou comentar sobre um assunto polêmico, que é muito frequente em fóruns de discussão, e que muitos alunos me perguntam nas aulas de SQL Tuning: quando devo reconstruir índices no Oracle Database?

     Há muitos profissionais afirmando que nunca se deve reconstruir (REBUILD) índices, outros que o fazem com frequência, e outros que não sabem muito bem quando fazer isso e o impacto desta operação. É importante ressaltar que estamos falando de REBUILD em índices do tipo b-tree.

   
    Um doc antigo do MOS (Lists All Indexes that Benefit from a Rebuild (Doc ID 122008.1)) dizia que eles deveriam ser reconstruídos quando mais que 20% de suas linhas fossem apagadas ou quando eles tivessem um nível de galhos (blevel) maior que 4. Esse doc tinha como referência as versões de 7.3 até 10.2 do Oracle Database, e foi atualizado em 04/02/2016, informando que nas versões mais recentes do Oracle Database devemos considerar o doc Index Rebuild, the Need vs the Implications (Doc ID 989093.1).

My Oracle Support - Doc ID 989093.1

     O doc ID 989093.1 informa que é raro necessitarmos reconstruir índices b-tree, pois eles são auto-gerenciados e auto-balanceados, mas mesmo assim, muitos profissionais justificam o REBUILD nas seguintes situações:

         1- Quando o índice está fragmentado;
         2- Quando o índice cresce muito e o espaço de linhas deletadas não é reutilizado;
         3- Quando há um alto fator de clusterização.

     De um modo geral a Oracle afirma que um REBUILD resolveria o problema de fragmentação (item 1) e espaço (item 2) nos índices apenas temporariamente, pois após a execução de muitos novos INSERTs, UPDATEs e DELETEs na tabela relacionada, o índice teria novamente alguma fragmentação e blocos vazios, e você precisaria executar outro REBUILD para ter novamente o índice em uma "estrutura perfeita". A Oracle recomenda que na maior parte dos casos você não execute este ciclo vicioso de REBUILDs e deixe o índice alcançar o seu equilíbrio naturalmente. Quanto ao item 3, é inútil reconstruir um índice quando ele possui um alto fator de clusterização. Para diminui-lo é necessário reordenar os dados na tabela, e não no índice!

     O REBUILD de índices deve ser evitado sempre que possível, pois ele consome muita CPU e I/O, impactando de forma negativa no desempenho dos sistemas em produção. Além do mais, para analisar índices e verificar quais deveriam sofrer REBUILD, muitos profissionais executam o comando ANALYZE INDEX ... VALIDATE STRUCTURE, que apresenta um sério problema (principalmente em grandes índices): ele faz um lock exclusivo na tabela e isso pode gerar um impacto negativo muito grande no desempenho das operações DML que estiverem ocorrendo nela durante a execução do comando.
  

     De um modo geral, ao invés de um REBUILD, prefira executar o comando ALTER INDEX ... COALESCE. pois ele não tem a mesma sobrecarga do REBUILD, podendo deste modo, ser executado em modo online (com os sistemas em execução). Apesar do COALESCE não fazer uma reestruturação completa dos índices (como o REBUILD faz), ele faz uma desfragmentação combinando os dados dos blocos das folhas, liberando desse modo, blocos que poderão ser usados para novas entradas nos índices, prevenindo-os de crescerem demasiadamente. Existe também o SHRINK. Para mais informações sobre este assunto veja a apresentação Oracle B-Tree Index Internals:Rebuilding The Truth de Richard Foote.

  

     Vimos nos parágrafos anteriores que o REBUILD deve ser evitado na maior parte dos casos, então quando é que ele realmente deve ser executado? Ele deve ser executado em situações bem específicas, como por exemplo:
   

         a) Quando você precisar mover o índice para um novo tablespace: ALTER INDEX ... REBUILD TABLESPACE ...;
  

         b) Quando você tiver um cenário em que uma aplicação removeu, por exemplo, uma grande quantidade de dados da tabela e não há previsão de que novos dados sejam inseridos nela. Para tornar este exemplo mais concreto, pense em uma tabela que tem 1.000.000 de linhas e um determinado sistema removeu 900.000 linhas. Dentro do período de 1 ano você prevê que ninguém mais irá inserir dados nessa tabela. Neste caso faça um REBUILD, pois com a estrutura atual contendo 90% dos blocos vazios, este índice não será eficiente. Além do mais, executar um COALESCE, neste caso, não seria bom. O custo dele ao tentar otimizar a estrutura de um índice em que a quantidade de blocos vazios é maior que a quantidade de blocos ocupados seria bem maior que o custo de um REBUILD. 

     Para verificar se um índice realmente precisa de um REBUILD você pode executar o script compartilhado no doc ID 989186.1 - Script to investigate a b-tree index structure do MOS. Se você quiser aprender mais sobre índices, assista a palestra que irei apresentar no evento DBA BRASIL 1.0 (que irá ocorrer em 16/4/16) ou participe dos meus treinamentos de SQL Tuning.
 
   

CONCLUSÃO:

     Cuidado com o REBUILD de índices, evite-o sempre que possível! A maior parte dos índices não precisa de manutenções, ou precisam apenas de um COALESCE!

Bom pessoal, por hoje é só!

Espero que o artigo lhe seja útil! Se tiver alguma dúvida, deixe um comentário!
  

[]s


Referências:

     - Index Rebuild, the Need vs the Implications (Doc ID 989093.1), My Oracle Support

     - Script: Lists All Indexes that Benefit from a Rebuild (Doc ID 122008.1), My Oracle Support

     - Index Rebuild vs. Coalesce vs. Shrink Space (Pigs – 3 Different Ones), Richard Foot´s Oracle Blog