Um pouco sobre índices invisíveis, disponíveis a partir do Oracle 11g
Por Eduardo Legatti Revisado por Marcelo Pivovar - Solution Architect,
Postado em Dezembro 2014
Imagine uma tabela e um índice. Se quisermos que durante o processamento de uma sentença SQL o otimizador ignore o índice ao acessar os dados da tabela, teremos que dropar o índice ou marcá-lo como inutilizável. Se utilizarmos a primeira alternativa, e caso houver uma degradação de performance da query pelo fato do índice ter sido dropado, e quisermos que o mesmo seja novamente utilizado, teremos que recriá-lo (CREATE INDEX ...). Agora, se utilizarmos a segunda alternativa, então teremos apenas que reconstruí-lo (ALTER INDEX ... REBUILD). A partir do Oracle 10g, o parâmetro skip_unusable_indexes que pode ser modificado tanto em nível de sessão como em nível de sistema, foi introduzido de forma que se o mesmo estiver setado como TRUE (valor default), o otimizador CBO irá ignorar qualquer índice marcado como inutilizável (unusuable) suprimindo o erro ORA-01502 caso o Oracle tente acessar o índice. Este recurso é útil porque não teremos mais que dropar um índice, mas apenas marcá-lo como inutilizável. A desvantagem desta abordagem é que, se quiséssemos que o otimizador enxergasse novamente o índice, teríamos que reconstruí-lo (rebuild), o que poderia causar uma overhead desnecessária.
A partir do Oracle 11g, um novo recurso foi adicionado ao gerenciamento de índices permitindo que um índice fique invisível ou não ao otimizador. Caso uma degradação de performance seja notada ao marcar um índice como invisível, poderemos então, marcá-lo novamente como visível sem precisar ter que reconstruí-lo. Portanto, no Oracle 11g um índice marcado como invisível será invisível ao otimizador, a não ser que o parâmetro optimizer_use_invisible_indexes que pode ser modificado tanto em nível de sessão (ALTER SESSION ...) como em nível de sistema (ALTER SYSTEM ...), seja setado para TRUE. Aliás, o valor padrão deste parâmetro é FALSE. Abaixo, irei demonstrar tanto a abordagem de marcar um índice como inutilizável, como a de marcar um índice como invisível no Oracle 11g. Vamos então a um exemplo prático:
-- Irei criar uma tabela de teste chamada T1:
SQL>
create table t1 (id number);
Tabela criada.
-- irei criar um índice no campo ID:SQL>
create index i_t1 on t1 (id); Índice criado.
-- Irei popular a tabela com valores aleatórios:SQL> insert into t1 select level from dual
2 connect by level <= 10000;
10000 linhas criadas.
SQL> commit;
Commit concluído.
-- Verificando a configuração atual:SQL>
show parameter skip_unusable_indexes
NAME TYPE VALUE
------------------------------ ----------- ------------------
skip_unusable_indexes boolean TRUE
-- Gerando o plano de execução do SQL: SQL>
explain plan for
2 select * from t1 where id = 100;Explicado.
SQL>
select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT -------------------------------------------------------------------------
Plan hash value: 2966378588 -------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| I_T1 | 1 | 13 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------Podemos ver no plano de execução acima, que o otimizador utilizou o índice I_T1. O que acontecerá se marcamos o índice como inutilizável e setarmos o parâmetro skip_unusable_indexes para FALSE?
SQL>
alter session set skip_unusable_indexes = FALSE;Sessão alterada.
SQL> alter index i_t1 unusable;
Índice alterado.
-- Verificando o estado do índice:
SQL>
select index_name,status from user_indexes where index_name = 'I_T1';
INDEX_NAME STATUS
----------------------------- --------
I_T1 UNUSABLE SQL> explain plan for
2 select * from t1 where id = 100; explain plan for * ERRO na linha 1: ORA-01502: índice 'TEST.I_T1' ou a sua partição está em estado não-utilizável Podemos ver acima que ao tentar acessar o índice, o Oracle emitiu o erro ORA-01502. Então, vamos setar novamente o parâmetro skip_unusable_indexes para TRUE:
SQL>
alter session set skip_unusable_indexes = TRUE;Sessão alterada.
SQL> explain plan for
2 select * from t1 where id = 100; Explicado.
SQL>
select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation
| Name | Rows | Bytes | Cost (%CPU)| Time
| --------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 7 (0)| 00:00:01 | |* 1
| TABLE ACCESS FULL| T1 | 1 | 13 | 7 (0)| 00:00:01 | -------------------------------------------------------------------------- Podemos ver acima, que o otimizador não considerou o índice I_T1 no plano de execução gerado, o que significa que o índice foi totalmente ignorado pelo mesmo. Se quisermos que o índice seja novamente utilizado pelo otimizador, teremos que reconstruir o índice.
-- Reconstruindo o índice:
SQL>
alter index i_t1 rebuild; Índice alterado.
-- Verificando o estado do índice:SQL>
select index_name,status from user_indexes where index_name = 'I_T1';
INDEX_NAME STATUS
------------------------------ --------
I_T1 VALID
Agora, irei mostrar o conceito de índices invisíveis no Oracle 11g, na qual não precisaremos mais nos preocupar em reconstruir os índices inválidos ou marcados como inutilizáveis.
SQL>
select * from v$version;
BANNER
-------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production CORE 11.1.0.6.0
Production TNS for Linux: Version 11.1.0.6.0 - Production NLSRTL Version 11.1.0.6.0 - Production
-- Verificando a configuração atual:SQL> show parameter visible
NAME TYPE VALUE
---------------------------------- ----------- --------------------------
optimizer_use_invisible_indexes
boolean FALSE -- Verificando a visibilidade do índice:SQL>
select index_name,visibility from user_indexes where index_name='I_T1';
INDEX_NAME VISIBILIT
------------------------------ ---------
I_T1 VISIBLE Podemos ver no resultado acima, que uma nova coluna VISIBILITY foi incluída na view USER_INDEXES. Esta nova coluna introduzida nas views *_INDEXES, mostra se o índice em questão está visível ou não ao otimizador CBO.
-- Marcando o índice como invisível:
SQL>
alter index i_t1 invisible; Índice alterado.
-- Verificando a visibilidade do índice:
SQL> select index_name,visibility from user_indexes where index_name='I_T1';
INDEX_NAME VISIBILIT
------------------------------ ---------
I_T1 INVISIBLE
-- Gerando o plano de execução:SQL> explain plan for
2 select * from t1 where id = 100; Explicado.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation
| Name | Rows | Bytes | Cost (%CPU)| Time
| --------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 7 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 13 | 7 (0)| 00:00:01 |
--------------------------------------------------------------------------Podemos ver acima, que o índice foi totalmente ignorado pelo otimizador ocasionando uma varredura integral (FTS) na tabela. Se mesmo assim ainda quisermos que o índice fique visível ao otimizador mesmo estando invisível, teremos ainda a opção de setar o parâmetro optimizer_use_invisible_indexes para TRUE.
SQL>
alter session set optimizer_use_invisible_indexes = TRUE;Sessão alterada.
SQL> explain plan for 2 select * from t1 where id = 100;
Explicado.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------
Plan hash value: 2966378588
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| I_T1| 1 | 13 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------- Agora, independente do valor do parâmetro optimizer_use_invisible_indexes, se quisermos que o índice volte a ser visível ao otimizador, precisaremos apenas marcar o índice como visível sem a necessidade de ter que reconstruí-lo:
-- Marcando o índice como visível:
SQL>
alter index i_t1 visible; Índice alterado.
-- Verificando a visibilidade do índice:SQL>
select index_name,visibility from user_indexes where index_name='I_T1';
INDEX_NAME VISIBILIT
------------------------------ ---------
I_T1 VISIBLE
Em resumo, esta nova funcionalidade nos permitirá testar a utilização de um novo índice sem afetar o plano de execução para as sentenças SQL existentes, ou até mesmo, a de testar o efeito da execução de uma sentença SQL ao dropar um índice existente sem a necessidade realmente de ter que dropá-lo.
Eduardo Legatti é Analista de Sistemas e Administrador de banco de dados. É pós graduado em Gerência da Tecnologia da Informação, possui as certificações OCA 9i - OCP 9i/10g/11g – OCE SQL Expert, e vem trabalhando como DBA Oracle desde a versão 8.0.5. Se interessa particularmente em planejar estratégias de backup/recovery, performance tuning e projetos de bancos de dados (modelagem física e lógica) atuando como consultor. Como Oracle ACE, ele tem o hábito de disseminar seu conhecimento através de artigos, grupos de discussão (Oracle OTN Forums) e dedica-se a compartilhar informações de forma a motivar novos DBAs. Este artigo foi revisto pela equipe de produtos Oracle e está em conformidade com as normas e práticas para o uso de produtos Oracle.