Utilizando Invisible Index no Oracle 11g

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

I – Introdução

A feature invisible index foi introduzida no Oracle Database 11g, portanto está presente deste a release 1 do produto.
Essa feature torna um indice invisível para o otimizador, ou seja, o indice 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.

II – O uso
Dificil apontamos exatamente todas as situações em que esse recurso é aplicável, porem de cara temos duas situações.
1º –  Testar o comportamento de uma aplicação antes do drop de um indice.
Imaginem que, você tem um indice de 300 Gb (acreditem eles podem existir), depois de monitora-lo você conclui que pode dropa-lo, pois durante o período de monitoração ele não foi usado, ótimo perfeito, 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 indice. Tenha em mente, um indice de 300Mb eu crio rapidamente, mas um indice de 300 Gb, pode demorar horas, então, você pode tornar o indice invisível ao otimizador, aguardar um tempo, seja ele qual for e, depois disso dropa-lo.

SQL> alter index idx_tb_clientes invisible;
SQL> drop index idx_tb_clientes;

2º –  Nossa segunda opção é exatamente o contrário, posso criar um invisible index, e testar no nível de sessão se o novo indice
será benéfico ou não.

SQL> alter index idx_tb_clientes invisible;
SQL> drop index idx_tb_clientes;
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 indice

III – Manipulação
Aqui, as técnias 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;

IV – Considerações
O parametro que determina do comportamento do invisible index se chama optimizer_use_invisible_indexes, o default é FALSE, ou seja,o otimizador não utilizará invible indexes na composição de um plano de execução, se alterarmos seu falor para TRUE, o otimizador passará a utilizar invisible indexes na composição de planos.

Esse parametro pode ser alterado no nível de sessão:
SQL> alter session set optimizer_use_invisible_indexes=true;

V – A prática
Vamos consolidar a teoria. Nesse exemplo, vamos simular uma situação onde eu tenho um indice (idx_tb_clientes_id), mas, eu não que o otimizador utilize-o para compor um plano.

1 – 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);

2º – Verificando se o indice idx_tb_clientes_id está visível pelo otmizador

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                    VISIBLE

Sim , ele está visível.

3º – Verificando se o otimizador está utilizando ele 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

4º – Tornando o indice idx_tb_clientes_id invisível pelo otmizador.

SQL> alter index idx_tb_clientes_id invisible;

5º – Verificando se o indice idx_tb_clientes_id está visível pelo otmizador

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                    INVISIBLE

6º – Verificando novamente se o otimizador está utilizando ele 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 indice idx_tb_clientes_id e faz um table access full. Esse foi um simples exemplo, em um ambiente pequeno, o conceito e aplicabilidade sempre vai alem.

VI – Conclusão.
Como vimos, a feature invisible index, torna a manutenção de indices 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.

 

Anúncios
Comente ou deixe um trackback: URL do Trackback.

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair /  Alterar )

Foto do Google

Você está comentando utilizando sua conta Google. Sair /  Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair /  Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair /  Alterar )

Conectando a %s

%d blogueiros gostam disto: