Como determinar se um índice precisa ser recriado

Sabemos que no banco de dados Oracle um índice é um objeto que pode acelerar a leitura de linhas usando um ponteiro.

No banco de dados Oracle pode-se criar índices em uma ou mais colunas de uma tabela para acelerar a velocidade de execução de uma instrução SQL. Caso a tabela não possua um índice criado, então o Oracle irá fazer uma varredura completa da tabela (FULL TABLE SCAN).

Durante a execução de uma consulta, um índice reduz o I/O em disco utilizando um método de acesso rápido para localizar os dados rapidamente. Por padrão, o Oracle cria índices do tipo B-tree.**

** ( B-tree – É uma estrutura de árvore de dados que mantém os dados ordenados e permite pesquisas, de acesso seqüencial, inserções e deleções. Este algoritmo é comumente utilizado em bancos de dados e sistemas de arquivos ).

Porém quando uma tabela tem um grande número de inserções, atualizações ou exclusões, o índice

pode tornar-se desbalanceado e fragmentado podendo atrapalhar o desempenho de uma consulta.

Mas como saber quando reconstruir os índices?

Deve-se primeiro ter uma idéia do estado atual do índice, usando o comando ANALYZE INDEX VALIDATE STRUCTURE.

A opção VALIDATE STRUCTURE pode ser executada sem preocupações, pois sua execução não afeta o otimizador de consultas do SGBD Oracle. A opção VALIDATE STRUCTURE atualiza a tabela SYS.INDEX_STATS. A tabela SYS.INDEX_STATS pode ser acessada publicamente através do sinônimo público INDEX_STATS. O sinônimo INDEX_STATS irá apresentar as informações de validação para o(s) índice(s).

Dica: Deve-se consultar este sinônimo antes de validar a estrutura do índice.

Abaixo um exemplo dos conteúdos do sinônimo INDEX_STATS.


Validando a estrutura do índice.

SQL> ANALYZE INDEX IDX_GAM_ACCT VALIDATE STRUCTURE;

Statement processed.

SQL> SELECT name, height,lf_rows,lf_blks,del_lf_rows FROM INDEX_STATS;

NAME         HEIGHT   LF_ROWS   LF_BLKS  DEL_LF_ROW

------------ ------   -------   -------  ----------

DX_GAM_ACCT  2        1         3        6

1 row selected.

IMPORTANTE: Existem duas regras de ouro para ajudar a determinar se o índice precisa ser reconstruído.

1)     Se o índice tem altura maior que quatro, deve-se reconstruir o índice - ver a coluna (HEIGHT).

2)     As linhas de folhas excluídas devem ser inferior a 20% - ver a coluna (DEL_LF_ROW). 

Se os valores apresentados pelo sinônimo INDEX_STATS desobedecerem às regras
acima descritas, então esses parâmetros indicam que o índice precisa ser
reconstruído, isto pode ser realizado pelo comando ALTER INDEX REBUILD  | REBUILD ONLINE. 

NOTA: Não é recomendado, executar este comando durante o
horário de expediente. A alternativa será remover e re-criar o índice. Pois durante
a criação, o índice utiliza a tabela como fonte de dados e para executar essa
atividade, o SGBD Oracle precisa colocar um “LOCK” na tabela envolvida. O
índice também não estará disponível durante a criação.

Para este exemplo, a coluna HEIGH apresenta claramente o valor 2. Este valor não representa a necessidade para a reconstrução do índice. Para a maioria dos índices, a altura do índice serão baixos, ou seja, 1 ou 2. Como exemplo, vale citar que numa tabela com 2 milhões de linhas os índices apresentavam a altura com valores iguais a 2 ou 3. Um índice com altura superior a 4 pode indicar a necessidade de reconstrução, pois esse valor indica uma estrutura de árvore que está inclinada. A inclinação de uma árvore pode levar o banco de dados a bloquear linhas desnecessariamente enquanto lê informações do índice. Abaixo segue outro exemplo.

SQL> ANALYZE INDEX IDX_GAM_FID VALIDATE STRUCTURE;

Statement processed.

SQL> SELECT name, height, lf_rows, del_lf_rows,(del_lf_rows/lf_rows)*100 as ratio

FROM INDEX_STATS;

NAME         HEIGHT LF_ROWS DEL_LF_ROW RATIO    

------------ ------ ------- ---------- -------

IDX_GAM_FID       1     189         62   32.80

1 row selected.

Neste exemplo, a relação entre as linhas de folhas excluídas (coluna DEL_LF_ROW) para linhas de folha totais (coluna RATIO) estão claramente acima dos 20%. Este índice é um bom candidato para a reconstrução. 

Abaixo um exemplo de reconstrução do índice.

SQL> ANALYZE INDEX IDX_GAM_FID REBUILD;

Statement processed.

SQL> ANALYZE INDEX IDX_GAM_FID VALIDATE STRUCTURE;

Statement processed.

Após a reconstrução do índice é realizada uma nova verificação.

SQL> SELECT name, height, lf_rows, del_lf_rows,(del_lf_rows/lf_rows)*100 as ratio

FROM INDEX_STATS;

NAME          HEIGHT LF_ROWS DEL_LF_ROW RATIO    

------------- ------ ------- ---------- -------

IDX_GAM_FID        1     127          0       0

1 row selected.

Examinando novamente o índice, pode-se observar que 62 linhas de folha excluídas foram retiradas do índice, após a reconstrução. Observe que o número total de linhas de folha (coluna LF_ROWS) foi de 189 para 127, que a diferença de 62 linhas de folha (189-127). A partir da reconstrução este índice deverá proporcionar um melhor desempenho para a aplicação.

Rubens Oliveira

DBA Oracle Consultor

olivert.dba@consultant.com