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
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
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.
De 0 a 10, o quanto você recomendaria este artigo para um amigo?