Índices inutilizáveis

Índices inutilizáveis

Se um índice torna-se inutilizável, ele sempre deverá ser reparado antes que possa ser usado. Diferentemente de um objeto PL/SQL por exemplo, pois a primeira vez que é acesso o objeto é recompilado automaticamente pelo Oracle.

Mais quando um índice se torna inutilizável?

Se os rowids da tabela forem modificados por qualquer razão, então os índices será marcado como inutilizável, isso por que o rowid é o ponteiro físico para a localização da linha a qual a chave de índice se refere.

Como identificar índices inutilizáveis?

Índices inutilizáveis são detectados utilizando a seguinte consulta.

SQL> select index_name, table_name from dba_indexes where status = 'UNUSABLE';

Versões anteriores do Oracle, quando se executava uma instrução SQL que tentasse usar um índice inutilizável, retornaria um erro e a instrução toda falharia. Com o 10g em diante, a instrução que vou utilizar um índice inutilizável, o banco reverterá para um plano de execução que não necessite de índice, mas isso pode causar uma grande queda de desempenho. Esse comportamento pode ser trocado mudando o parâmetro de inicialização SKIP_UNUSABLE_INDEXES para false, que por padrão é true.

SQL> alter system set skip_unusable_indexes=false;

Como reparar índices inutilizáveis?

Índices são reparados utilizando o comando ALTER INDEX … REBUILD. Isso gerará um novo índice com ponteiros das rowid atualizados para a chave de índice.

Os mais importantes parâmetros do comando rebuild são TABLESPACE, NOLOGGING, ONLINE.

TABLESPACE : Se você deseja construir seu índice em um tablespace diferente da sua tabela.

NOLOGGING: Diz ao Oracle a não gerar redo para a operação de construção de índice. Isso torna a reconstrução muito mais rápida, mais assim o índice não sobreviverá a um dado causado que requeira o uso do restore e recovery. Se for utilizar essa opção, lembre-se sempre de gerar um backup do tablespace, assim você está seguro a falhas.

ONLINE: A geração de índices exige um certo esforço do banco de dados, principalmente se a tabela for muito grande. Por padrão o processo de reconstrução bloqueia todo DML que vier de encontro a tabela no momento da reconstrução, isso pode ser evitado usando ONLINE no comando.

Reparando Índices

Vamos a um exemplo prático, onde será necessário reconstruir um índice.

SQL> create table t
2  (
3         id number
4  ) tablespace users;
5  /

 Table created.

SQL> create index id_idx on t(id);

 Index created.

Vamos confirmar que o índice está valido.

SQL> select index_name, status from user_indexes;

 INDEX_NAME                   STATUS
------------------------------ --------
ID_IDX                            VALID

Agora, vamos movimentar a tabela T do tablespace USERS para o tablespace TB_TESTE. Assim os índices vão perder todas as suas referências, conseqüentemente se tornará inutilizável pelo plano de execução Oracle.

SQL> alter table t move tablespace tb_teste;

Table altered.

Se verificarmos o status do índice, ele estará com o status alterado para UNUSABLE.

SQL> select index_name, status from user_indexes;

INDEX_NAME                        STATUS
------------------------------ --------
IDX_ID                         UNUSABLE

Para tornar o índice válido, apenas devemos reconstruir os ponteiros das rowids executando o comando rebuild.

SQL> alter index idx_id rebuild online nologging;

Index altered.

SQL> select index_name, status from user_indexes;

INDEX_NAME                   STATUS
------------------------------ --------
ID_IDX                            VALID

Após o comando REBUILD, o índice já estará pronto para ser utilizado novamente.