Invisible Index
Utilizando Invisible Index no Oracle 11g
Em 13, nov 2011 | Sem Comentários | Em Blog | Por Allison
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
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 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.
