Um pouco sobre índices invisíveis, disponíveis a partir do Oracle 11g

Por Eduardo Legatti
Postado em Dezembro 2014

Revisado por Marcelo Pivovar - Solution Architect

Imagine uma tabela e um índice. Se quisermos que durante o processamento de uma sentença SQL o otimizador ignore o índice ao acessar os dados da tabela, teremos que dropar o índice ou marcá-lo como inutilizável. Se utilizarmos a primeira alternativa, e caso houver uma degradação de performance da query pelo fato do índice ter sido dropado, e quisermos que o mesmo seja novamente utilizado, teremos que recriá-lo (CREATE INDEX ...). Agora, se utilizarmos a segunda alternativa, então teremos apenas que reconstruí-lo (ALTER INDEX ... REBUILD). A partir do Oracle 10g, o parâmetro skip_unusable_indexes que pode ser modificado tanto em nível de sessão como em nível de sistema, foi introduzido de forma que se o mesmo estiver setado como TRUE (valor default), o otimizador CBO irá ignorar qualquer índice marcado como inutilizável (unusuable) suprimindo o erro ORA-01502 caso o Oracle tente acessar o índice. Este recurso é útil porque não teremos mais que dropar um índice, mas apenas marcá-lo como inutilizável. A desvantagem desta abordagem é que, se quiséssemos que o otimizador enxergasse novamente o índice, teríamos que reconstruí-lo (rebuild), o que poderia causar uma overhead desnecessária.

A partir do Oracle 11g, um novo recurso foi adicionado ao gerenciamento de índices permitindo que um índice fique invisível ou não ao otimizador. Caso uma degradação de performance seja notada ao marcar um índice como invisível, poderemos então, marcá-lo novamente como visível sem precisar ter que reconstruí-lo. Portanto, no Oracle 11g um índice marcado como invisível será invisível ao otimizador, a não ser que o parâmetro optimizer_use_invisible_indexes que pode ser modificado tanto em nível de sessão (ALTER SESSION ...) como em nível de sistema (ALTER SYSTEM ...), seja setado para TRUE. Aliás, o valor padrão deste parâmetro é FALSE. Abaixo, irei demonstrar tanto a abordagem de marcar um índice como inutilizável, como a de marcar um índice como invisível no Oracle 11g. Vamos então a um exemplo prático:

-- Irei criar uma tabela de teste chamada T1:

SQL>  create table t1 (id number);
Tabela criada.

-- irei criar um índice no campo ID:

SQL>  create index i_t1 on t1 (id);
Índice criado.

-- Irei popular a tabela com valores aleatórios:

SQL> insert into t1 select level from dual
     2  connect by level <= 10000;   
     10000  linhas criadas.
SQL> commit;
     Commit concluído.

-- Verificando a configuração atual:

SQL>  show parameter skip_unusable_indexes

NAME                            TYPE        VALUE
------------------------------  ----------- ------------------
skip_unusable_indexes           boolean     TRUE

-- Gerando o plano de execução do SQL:

SQL>  explain plan for
   2  select * from t1 where id = 100;


Explicado.

SQL>  select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------
Plan hash  value: 2966378588

-------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |   1   |    13 |      1  (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| I_T1 |   1   |    13 |      1  (0)| 00:00:01 |
-------------------------------------------------------------------------


Podemos ver no plano de execução acima, que o otimizador utilizou o índice I_T1. O que acontecerá se marcamos o índice como inutilizável e setarmos o parâmetro skip_unusable_indexes para FALSE?

SQL>  alter session set skip_unusable_indexes = FALSE;
Sessão alterada.

SQL> alter index i_t1  unusable;
Índice alterado.


-- Verificando o estado do índice:

SQL>  select index_name,status from user_indexes where index_name = 'I_T1';

INDEX_NAME                     STATUS
-----------------------------  --------
I_T1                           UNUSABLE

SQL> explain plan for
     2  select * from t1 where id = 100;
 explain plan for 
* 
ERRO na linha 1: 
ORA-01502: índice 'TEST.I_T1' ou  a sua partição está em estado não-utilizável


Podemos ver acima que ao tentar acessar o índice, o Oracle emitiu o erro ORA-01502. Então, vamos setar novamente o parâmetro skip_unusable_indexes para TRUE:

SQL>  alter session set skip_unusable_indexes = TRUE;
Sessão  alterada.

SQL>  explain plan for
   2  select * from t1 where id = 100;

Explicado.

SQL>  select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
Plan hash  value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    13 |     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |    13 |     7   (0)| 00:00:01 |
--------------------------------------------------------------------------


Podemos ver acima, que o otimizador não considerou o índice I_T1 no plano de execução gerado, o que significa que o índice foi totalmente ignorado pelo mesmo. Se quisermos que o índice seja novamente utilizado pelo otimizador, teremos que reconstruir o índice.

-- Reconstruindo o índice:

SQL> alter index i_t1 rebuild;
Índice alterado.

-- Verificando o estado do índice:

SQL>  select index_name,status from user_indexes where index_name = 'I_T1';

INDEX_NAME                     STATUS
------------------------------ --------
I_T1                           VALID
 

Agora, irei mostrar o conceito de índices invisíveis no Oracle 11g, na qual não precisaremos mais nos preocupar em reconstruir os índices inválidos ou marcados como inutilizáveis.

SQL>  select * from v$version;

BANNER
-------------------------------------------------------------------------
Oracle  Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL  Release 11.1.0.6.0 - Production
CORE    11.1.0.6.0      Production
TNS for  Linux: Version 11.1.0.6.0 - Production
NLSRTL  Version 11.1.0.6.0 - Production

-- Verificando a configuração atual:

SQL> show parameter visible

NAME                               TYPE        VALUE
---------------------------------- ----------- --------------------------
optimizer_use_invisible_indexes    boolean     FALSE

-- Verificando a visibilidade do índice:

SQL>  select index_name,visibility from user_indexes where index_name='I_T1';

INDEX_NAME                     VISIBILIT
------------------------------ ---------
I_T1                           VISIBLE

Podemos ver no resultado acima, que uma nova coluna VISIBILITY foi incluída na view USER_INDEXES. Esta nova coluna introduzida nas views *_INDEXES, mostra se o índice em questão está visível ou não ao otimizador CBO.

-- Marcando o índice como invisível:

SQL> alter index i_t1  invisible;
Índice alterado.

-- Verificando a visibilidade do índice:

SQL>  select index_name,visibility from user_indexes where index_name='I_T1';

INDEX_NAME                     VISIBILIT
------------------------------ ---------
I_T1                           INVISIBLE

-- Gerando o plano de execução:

SQL> explain plan for
   2  select * from t1 where id = 100;

Explicado.

SQL>  select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
Plan hash  value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    13 |     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |    13 |     7   (0)| 00:00:01 |
--------------------------------------------------------------------------


Podemos ver acima, que o índice foi totalmente ignorado pelo otimizador ocasionando uma varredura integral (FTS) na tabela. Se mesmo assim ainda quisermos que o índice fique visível ao otimizador mesmo estando invisível, teremos ainda a opção de setar o parâmetro optimizer_use_invisible_indexes para TRUE.

SQL>  alter session set optimizer_use_invisible_indexes = TRUE;
Sessão  alterada.

SQL>  explain plan for
   2  select * from t1 where id = 100;

Explicado.

SQL>  select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------
Plan hash  value: 2966378588

-------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |    13 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| I_T1 |     1 |    13 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------


Agora, independente do valor do parâmetro optimizer_use_invisible_indexes, se quisermos que o índice volte a ser visível ao otimizador, precisaremos apenas marcar o índice como visível sem a necessidade de ter que reconstruí-lo:

-- Marcando o índice como visível:

SQL> alter index i_t1 visible;
Índice alterado.

-- Verificando a visibilidade do índice:

SQL>  select index_name,visibility from user_indexes where index_name='I_T1';

INDEX_NAME                     VISIBILIT
------------------------------ ---------
I_T1                           VISIBLE

Em resumo, esta nova funcionalidade nos permitirá testar a utilização de um novo índice sem afetar o plano de execução para as sentenças SQL existentes, ou até mesmo, a de testar o efeito da execução de uma sentença SQL ao dropar um índice existente sem a necessidade realmente de ter que dropá-lo.


Eduardo Legatti é Analista de Sistemas e Administrador de banco de dados. É pós graduado em Gerência da Tecnologia da Informação, possui as certificações OCA 9i - OCP 9i/10g/11g – OCE SQL Expert, e vem trabalhando como DBA Oracle desde a versão 8.0.5. Se interessa particularmente em planejar estratégias de backup/recovery, performance tuning e projetos de bancos de dados (modelagem física e lógica) atuando como consultor. Como Oracle ACE, ele tem o hábito de disseminar seu conhecimento através de artigos, grupos de discussão (Oracle OTN Forums) e dedica-se a compartilhar informações de forma a motivar novos DBAs.

Este artigo foi revisto pela equipe de produtos Oracle e está em conformidade com as normas e práticas para o uso de produtos Oracle.