Oracle Invisible Index - 11g

A partir da versão do Oracle 11g, é possível criar e alterar os índices para INVISIBLE/VISIBLE. 

Ao alterar um índice para invisible, por default o otimizador do Oracle não utiliza o mesmo para montar um plano de execução. A menos, que o parâmetro optimizer_use_invisible_indexes (default FALSE) esteja definido como TRUE a nível de instância ou de sessão. Neste último, o índice também será usado apenas a nível de sessão.

Com esta feature, é possível realizar a criação de um índice invisible em produção sem afetar os planos de execução existentes. Essa possibilidade é bastante útil quando, por exemplo, não temos um ambiente de testes e precisamos validar o impacto da criação do índice direto no ambiente de produção.

Abaixo vou exemplificar o uso desta feature:

Primeiramente iremos criar e popular uma tabela e realizar a criação do índice invisible, para que possamos realizar os testes com o uso da feature invisible index.

SQL> conn lamim/lamim
SQL> create table tst_invisible_ix (

Connected.
SQL>
numero number
);
SQL> BEGIN

2    3
Table created.

    INSERT INTO tst_invisible_ix VALUES (i);

FOR i IN 1 .. 10000 LOOP
  END LOOP;
  COMMIT;
PL/SQL procedure successfully completed.

END;
/  2    3    4    5    6    7
SQL> SQL>
SQL>

Antes de efetivamente realizar os testes, irei realizar uma coleta de estatística da tabela recém criada.

 
SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'tst_invisible_ix', cascade=> TRUE);

PL/SQL procedure successfully completed.

Agora vamos validar o comportamento do plano de execução com e sem um índice em modo invisible.

SQL>  show parameter invisible;

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

SQL>  select * from tst_invisible_ix where numero=123;

    NUMERO
----------
       123


Execution Plan
----------------------------------------------------------
Plan hash value: 978504830

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

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("NUMERO"=123)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         23  consistent gets
          0  physical reads
          0  redo size
        525  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>

No exemplo acima é possível perceber que a consulta realiza um acesso full a tabela e não utiliza o índice criado com o invisible. Para testarmos a utilização do índice, podemos usar um hint na query ou alterar o parâmetro optimizer_use_invisible_indexes a nível de sessão, evitando assim que o índice impacte em outros processos e/ou aplicações.

 SQL> select * from tst_invisible_ix where numero=123;

    NUMERO
----------
       123


Execution Plan
----------------------------------------------------------
Plan hash value: 1674343203

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

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("NUMERO"=123)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        525  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Podemos notar que após a alteração do parâmetro a consulta não realiza mais um acesso full e passa a utilizar o índice. Lembrando que o uso do índice ocorre apenas na sessão atual, devido a alteração do parâmetro optimizer_use_invisible_indexes.

Para tornar o índice visível para todo o banco, basta executar o comando alter index index_name visible, da mesma forma se desejarmos alterar um índice já criado para invisivel, basta executar um alter index_name inivisible.

SQL> conn lamim/lamim
Connected.
SQL> alter index LAMIM.IX_TST_INVISIBLE_IX01 visible;

Index altered.

SQL> set autotrace on
SQL> select * from lamim.tst_invisible_ix where numero=123;

    NUMERO
----------
       123


Execution Plan
----------------------------------------------------------
Plan hash value: 1674343203

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

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("NUMERO"=123)


Statistics
----------------------------------------------------------
         24  recursive calls
          0  db block gets
         35  consistent gets
          0  physical reads
          0  redo size
        525  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
          1  rows processed

Afim de validar se um índice está visivel ou invisível, podemos consultar a coluna visibility da dba/user_indexes.

 SQL>  select index_name, visibility from dba_indexes where index_name='IX_TST_INVISIBLE_IX01';

INDEX_NAME                     VISIBILIT
------------------------------ ---------
IX_TST_INVISIBLE_IX01          VISIBLE

Fonte:https://docs.oracle.com/cd/B28359_01/server.111/b28310/indexes004.htm

http://www.orafaq.com/wiki/Invisible_indexes