Índices invisíveis? Abordando uma nova funcionalidade do Oracle 11g

Olá,

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.