[Resolvido] Utilizando Invisible Index no Oracle 11g

[RSS iMasters 5]

Saudações, pessoal! Neste artigo, conheceremos a funcionalidade do invisible index, que é uma feature do Oracle Database 11g

 

 

Introdução

A feature invisible index foi introduzida no Oracle Database 11g, portanto está presente desde o release 1 do produto. Essa feature torna um índice invisível para o otimizador, ou seja, o índice não será utilizado durante a composição do plano de execução. Diferentemente de um unusable index, ele será mantido pelo banco, logo, ele continuará sofrendo atualizações, inserções e deleções.

 

 

O uso

Difícil apontarmos exatamente todas as situações em que esse recurso é aplicável, porém de cara temos duas situações:

 

Testar o comportamento de uma aplicação antes do drop de um índice. Imaginem que você tem ím indice de 300 Gb (acreditem eles podem existir). Depois de monitorá-lo, você conclui que pode dropá-lo, pois durante o período de monitoração ele não foi usado. Nesse momento é que entra o invisible index, ele se torna mais uma opção de garantir que não teremos nenhum problema se droparmos o índice.Tenha em mente, um índice de 300Mb eu crio rapidamente, mas um índice de 300 Gb pode demorar horas, então, você pode tornar o índice invisível ao otimizador, aguardar um tempo seja ele qual for e depois disso dropá-lo.

 

SQL> alter index idx_tb_clientes invisible;

SQL> drop index idx_tb_clientes;

Nossa segunda opção é exatamente o contrário. Posso criar um invisible index e testar no nível de sessão se o novo índice será benéfico ou não.

 

SQL> create index idx_tb_clientes_nome on tb_clientes (nome) invisible;

SQL> alter session set optimizer_use_invisible_indexes=true;

SQL> explain plan for select * from tb_clientes where nome ='DANIEL DE OLIVEIRA';

 

Explained.

 

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

 

PLAN_TABLE_OUTPUT

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

Plan hash value: 3752082546

 

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

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

| 0 | SELECT STATEMENT | | 1 | 67 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| TB_CLIENTES | 1 | 67 | 2 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | IDX_TB_CLIENTES_NOME | 1 | | 1 (0)| 00:00:01 |

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

 

Predicate Information (identified by operation id):

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

 

2 - access("NOME"='DANIEL DE OLIVEIRA')

 

Note

-----

- dynamic sampling used for this statement

 

18 rows selected.O resto da aplicação continua trabalhando sem utilizar o novo índice.

 

 

Manipulação

Aqui, as técnicas para a manipulação do invisible index.

 

SQL> alter index nome_do_indice invisible;

SQL> alter index nome_do_indice visible;

SQL> create index nome_do_indice on nome_da_tabela (coluna) invisible;

Considerações

O parâmetro que determina do comportamento do invisible index se chama optimizer_use_invisible_indexes, o default é FALSE, ou seja,

o otimizador não utilizará invisible indexes na composição de um plano de execução, se alterarmos seu valor para TRUE, o otimizador

passará a utilizar invisible indexes na composição de planos.

 

Esse parâmetro pode ser alterado no nível de sessão:

 

SQL> alter session set optimizer_use_invisible_indexes=true;

A prática

Vamos consolidar a teoria. Nesse exemplo, vamos simular uma situação em que eu tenho um índice (idx_tb_clientes_id), mas eu não quero que o otimizador o utilize para compor um plano.

 

Criação do ambiente

 

SQL> set pages 300

SQL> set lines 300

SQL> create table tb_clientes (ID NUMBER(10),NOME VARCHAR2(50),ENDERECO VARCHAR2(50));

SQL> insert into tb_clientes values (1,'MARIA DA SILVA','RUA RUI BARBOSA 169');

SQL> insert into tb_clientes values (2,'MARIA DA SILVA','RUA RUI BARBOSA 169');

SQL> insert into tb_clientes values (3,'MARIA DA SILVA','RUA RUI BARBOSA 169');

SQL> insert into tb_clientes values (4,'MARIA DA SILVA','RUA RUI BARBOSA 169');

SQL> insert into tb_clientes values (5,'MARIA DA SILVA','RUA RUI BARBOSA 169');

SQL> insert into tb_clientes values (6,'MARIA DA SILVA','RUA RUI BARBOSA 169');

SQL> insert into tb_clientes values (7,'MARIA DA SILVA','RUA RUI BARBOSA 169');

SQL> insert into tb_clientes values (8,'DANIEL DE OLIVEIRA','RUA PRUDENTE DE MORAIS 1524');

SQL> commit;

SQL> create index idx_tb_clientes_id on tb_clientes(id);Verificando se o índice idx_tb_clientes_id está visível pelo otimizador

 

SQL> select index_name,table_name,visibility

SQL> from user_indexes                     

SQL> where table_name='TB_CLIENTES';        

INDEX_NAME                     TABLE_NAME                     VISIBILIT

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

IDX_TB_CLIENTES_ID             TB_CLIENTES                    VISIBLESim , ele está visível.

 

Verificando se o otimizador está sendo utilizado para compor planos de execução

 

SQL> explain plan for select * from tb_clientes where id =3;

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

 

PLAN_TABLE_OUTPUT

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

Plan hash value: 3101714416

 

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

| Id  | Operation                   | Name               | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT            |                    |     1 |    67 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| TB_CLIENTES        |     1 |    67 |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IDX_TB_CLIENTES_ID |     1 |       |     1   (0)| 00:00:01 |

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

 

Predicate Information (identified by operation id):

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

 

   2 - access("ID"=3)

 

Note

-----

   - dynamic sampling used for this statement

 

18 rows selected.

Sim, ele está sendo utilizado.

 

Tornando o índice idx_tb_clientes_id invisível pelo otimizador

 

SQL> alter index idx_tb_clientes_id invisible;Verificando se o índice idx_tb_clientes_id está visível pelo otimizador

 

SQL> select index_name,table_name,visibility

SQL> from user_indexes                     

SQL> where table_name='TB_CLIENTES';        

INDEX_NAME                     TABLE_NAME                     VISIBILIT

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

IDX_TB_CLIENTES_ID             TB_CLIENTES                    INVISIBLEVerificando novamente se o otimizador está sendo utilizado para compor planos de execução

 

SQL> explain plan for select * from tb_clientes where id =3;

 

Explained.

 

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

 

PLAN_TABLE_OUTPUT

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

Plan hash value: 2382977096

 

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

| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT  |             |     1 |    67 |     4   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| TB_CLIENTES |     1 |    67 |     4   (0)| 00:00:01 |

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

 

Predicate Information (identified by operation id):

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

 

   1 - filter("ID"=3)

 

Note

-----

   - dynamic sampling used for this statement

 

17 rows selected.Não, agora o otimizador deixa de utilizar o índice idx_tb_clientes_id e faz um table access full. Esse foi um simples exemplo em um ambiente pequeno, o conceito e a aplicabilidade sempre vão além.

 

 

Conclusão

Como vimos, a feature invisible index torna a manutenção de índices mais flexível, diminuindo os riscos de perda parcial no nível de resposta de uma aplicação, rotina, ou o que for. A dica que dou é sempre a mesma, em qualquer ambiente de produção, sempre, Desenvolver, Testar, Homologar e Implementar.

 

Um forte abraço e até a próxima.

 

 

 

http://imasters.com.br/artigo/22533/oracle/utilizando-invisible-index-no-oracle-11g