Índices Invisíveis no Oracle 11g

Neste artigo abordarei sobre índices invisíveis no Oracle 11g, importante dispositivo que permite testar e validar a utilização e necessidade de um índice num ambiente produtivo de banco de dados.

O Oracle 11g permite que os índices sejam marcados como invisíveis. Os índices invisíveis são mantidos como qualquer outro índice, mas eles são ignorados pelo otimizador, a menos que o parâmetro OPTIMIZER_USE_INVISIBLE_INDEXES esteja definido como TRUE. Os índices podem ser criados como invisíveis, usando a palavra-chave INVISIBLE, e sua visibilidade pode ser alterada usando o comando ALTER INDEX.

Exemplo de criação de um índice invisível:

CREATE INDEX ON () INVISIBLE;

Ativação/desativação de um índice:

ALTER INDEX INVISIBLE;

ALTER INDEX VISIBLE;

O script a abaixo cria e preenche uma tabela, em seguida, cria um índice invisível.

Criação da tabela “TEST_TAB”:

CREATE TABLE TEST_TAB(id  NUMBER);

Preenchimento de dados na tabela “TEST_TAB”.

BEGIN

  FOR i IN 1..1000 LOOP

    INSERT INTO TEST_TAB VALUES (i);

  END LOOP;

  COMMIT;

END;

/

Criação do índice invisível, “TEST_TAB_ID” para a tabela “TEST_TAB”.

CREATE INDEX TEST_TAB_ID ON TEST_TAB(id) INVISIBLE;

Atualização das estatísticas da tabela “TEST_TAB” e do índice “TEST_TAB_ID”.

EXEC DBMS_STATS.gather_table_stats(TEST, 'TEST_TAB', cascade=> TRUE);

Para demonstrar como um índice invisível funciona, vamos executar uma consulta usando a coluna indexada, definida na cláusula WHERE.

SET AUTOTRACE ON

SELECT * FROM test_tab WHERE id = 999;

------------------------------------------------------------------------------

| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------==--------------------------------------

|   0 | SELECT STATEMENT  |          |     1 |     3 |     7   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| TEST_TAB |     1 |     3 |     7   (0)| 00:00:01 |

------------------------------------------------------------------------------

Pode-se verificar que o índice foi ignorado, pois a operação de “TABLE ACCESS FULL” é apresentada no plano de execução da consulta, essa operação realiza a varredura completa da tabela desprezando o índice da tabela.

Ao definir o parâmetro OPTIMIZER_USE_INVISIBLE_INDEXES como TRUE e ativando a “visibilidade” do índice, o otimizador utiliza o índice durante a execução da consulta, conforme demonstrado abaixo.

ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES=TRUE;

SELECT * FROM test_tab WHERE id = 999;

--------------------------------------------------------------------------------

| Id  | Operation        | Name        | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT |             |     1 |     3 |     1   (0)| 00:00:01 |

|*  1 |  INDEX RANGE SCAN| TEST_TAB_ID |     1 |     3 |     1   (0)| 00:00:01 |

--------------------------------------------------------------------------------

Porém, ao tornar o índice visível, mas com o parâmetro OPTIMIZER_USE_INVISIBLE_INDEXES definido como FALSE, essa instrução ainda indicará ao otimizador que o índice ainda disponível para uso.

ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES=FALSE;

ALTER INDEX test_tab_id VISIBLE;

SELECT * FROM test_tab WHERE id = 9999;

--------------------------------------------------------------------------------

| Id  | Operation        | Name        | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT |             |     1 |     3 |     1   (0)| 00:00:01 |

|*  1 |  INDEX RANGE SCAN| TEST_TAB_ID |     1 |     3 |     1   (0)| 00:00:01 |

--------------------------------------------------------------------------------

Para resolver esse impasse deve-se modificar o índice para INVISIBLE.

Índices invisíveis podem ser uteis para processos com necessidades específicas de indexação, onde a presença dos índices pode afetar negativamente outras áreas funcionais. Eles também são úteis para testar o impacto de descartar um índice.

A visibilidade dos índices pode ser implementada configurando o parâmetro OPTIMIZER_USE_INVISIBLE_INDEXES como TRUE, para todo o banco de dados através da configuração do arquivo “spfile” ou do “init.ora” ou ainda pode ser implementado através da sessão de um usuário. A ativação ou desativação desse parâmetro é dinâmica.

Para verificar os estados de visibilidade de um índice deve-se verificar a coluna “VISIBILITY” das visões [DBA | ALL | USER] _INDEXES.

Rubens Oliveira

DBA Oracle Consultor

olivert.dba@consultant.com