Arquivo da tag: Invisible Index

Utilizando Invisible Index no Oracle 11g

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                    VISIBLE

Sim , 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

QL> 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

Verificando 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.

Fonte: João Paulo Spaulonci/IMasters